setting up linked server in SQL 2005

[kc0gcm]

here goes,

right click on "linked servers" click "new linked server"

in the dialog box that pops up add the following info:

Linked Server: GAMESERVER
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: gameserver
Provider String: DRIVER={SQL Server};SERVER=<servername>;UID=;PWD=;

ok in the string above u need to replace <servername> with the name of ur SQL server. to do this u will need to know the name so right click on the very top line (3 lines above ur ACCOUNT database in the object explorer of server management studio) and the top line on the right will say name copy and paste that into where <servername> is in the string.. or you can put ur windows IP address and see if that will work (it didn't for me but it may for you)

now click "security" on the left menu of Linked Server Properties

select the option "Be made without using a security context"

now click on "Server Options" now here is a list of the settings:


Collation Compatible: true
Data Access: true
Rcp: true
Rcp out: true
Use Remote Collation: true
Collation name: LEAVE BLANK
connection timeout: 0
Query Timeout: 0

that should do it now just click ok and it will be created..

this is how i set it up and it works for me if anyone else has done it different please post ur method..

 

[~NaraShikamaru]

Can you post the name of the icon and how to get there?

 

[kc0gcm]

its 3 lines above ur ACCOUNT database in the object explorer of server management studio

 

[bapakayam]

it is work?

 

[jdla1990]

nice thnks man excelent aport!

 

[BUDS4EVER]

Whate make linked server sql 2000?

 

[hafifthzx]

Woow..Cool Guide

 

[nitro+]

here goes,

right click on "linked servers" click "new linked server"

in the dialog box that pops up add the following info:

Linked Server: GAMESERVER
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: gameserver
Provider String: DRIVER={SQL Server};SERVER=<servername>;UID=;PWD=;

ok in the string above u need to replace <servername> with the name of ur SQL server. to do this u will need to know the name so right click on the very top line (3 lines above ur ACCOUNT database in the object explorer of server management studio) and the top line on the right will say name copy and paste that into where <servername> is in the string.. or you can put ur windows IP address and see if that will work (it didn't for me but it may for you)

now click "security" on the left menu of Linked Server Properties

select the option "Be made without using a security context"

now click on "Server Options" now here is a list of the settings:


Collation Compatible: true
Data Access: true
Rcp: true
Rcp out: true
Use Remote Collation: true
Collation name: LEAVE BLANK
connection timeout: 0
Query Timeout: 0

that should do it now just click ok and it will be created..

this is how i set it up and it works for me if anyone else has done it different please post ur method..

it doesn't work for me it says server doesn't exists or acces denied.help me!!!!

it doesn work for me it says server doesn't exists or acces denied plsss help me!!!
tnx.

 

[Cath22]

u need to replace <servername> , did you put that !!!

 

[Fleetaks]

Thanks for the guide.

 

[Djirko]

how about for sql 2000? where to find that linked server thing ?

 

[jacques]

nice!!
that's save me!

 

[Thesnowman]

Ok it has been a long time since i set up a cabal server... I totally forgot how.

I followed step by step and i get this:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

The OLE DB provider "MSDASQL" for linked server "GAMESERVER" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "GAMESERVER".
OLE DB provider "MSDASQL" for linked server "GAMESERVER" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------

I changed the server name to KYLE\SQLEXPRESS which is the name and the user id was sa and then i put my pass.

 

[chumpywumpy]

I changed the server name to KYLE\SQLEXPRESS which is the name

The server name should not be that if installed correctly, which yours isn't. You have done a default install of 2005 express which installs as a named instance instead of a default one (won't work) and you probably left remote connections disabled too.

My guide:

To view the content, you need to sign in or register

Also a linked server is not needed if you modify 2 stored procedures.
http://forum.ragezone.com/f460/how-to-remove-the-need-for-a-linked-server-576629/

 

[Deco_Boss]

which means you're going to have to reinstall MSSQL 2005, don't forget to untick the 'hide options' upon reinstall, that's probably where you went wrong the last time

 

[Yamachi]

which means you're going to have to reinstall MSSQL 2005, don't forget to untick the 'hide options' upon reinstall, that's probably where you went wrong the last time

Congratulations on bumping a thread that's just shy of a year old...

 

[zekmester]

MSDASQL linked blabla error...

 

[LostSpirit]

MSDASQL linked blabla error...

Please if you have any problem or doubt is clear...
But it will not make spam topics/post's, even more tutorials.

Regards.

 

[aerolite]

Do we have to change the {SQL Server} or no? and for the SERVER= is it suppose to be the IP of the server? or name of the server? thanks in advance!

 

[bernZ07]

how to solve this??:?: