cant add a linked server

Question

Post reply

cant add a linked server

winston Smith

SSCoach

Points: 19500
More actions
March 30, 2006 at 7:14 am

#111601

what is wrong with this statement to add a linked server? im trying to add an sql database as a linked server

sp_addlinkedserver

@server
= 'dbdmsql01',
@provider
= 'SQLNCLI',
@location
= '\\dbdmsql01',
@srvproduct
= 'SQL Server'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello,

You have not mentioned whether you are adding a linked server of a SQL default or named instance.

[ @srvproduct = ] 'product_name'

Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name, data_source, location, provider_string, and catalog do not need to be specified.

You can go through the BOL on topic sp_addlinkedserver.

Thanks and have a nice day!!!

Lucky

winston Smith SSCoach Points: 19500 More actions · Answer 2

its to an sql instance on a server i think.

i got the link to go ok, but when i make any changes to tables on the linked server, althought it says "1 rows affected" or whatever, when i check, no changes have been made to the linked database.

here is the code i use to create the linked server now:

dbdmsql01 is the name of the server that the sql db is on.

EXEC

sp_addlinkedserver

@server

= 'dbdmsql01',

@srvproduct

= '',

@provider

= 'SQLNCLI',

@datasrc

= NULL,

@location

= '\\dbdmsql01',

@provstr

= 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;Integrated Security=SSPI;'

Andreas AJV SSC Eights! Points: 800 More actions · Answer 3

The code bellow updates just fine the Remote Table:

exec sp_addlinkedserver 'OutServer', N'SQL Server'

go

update [OutServer].MyDB.dbo.Items set Price = Price * 1.1

go

exec sp_dropserver 'OutServer', 'droplogins'

go

Did you try something like tha above?

Also, i COPY the very usefull comments of Scott Coleman (thanx Scott - i hope that u dont mind...) on a previous post about the above code.

Scott Coleman posts:

"The linked server approach assumes that the two servers can see each other. The example given shows no security info, so it is also assuming they are in the same domain and trusted logins will work.

There may be network or security reasons why this won't work. For instance, servers in my office can link to the database servers at our web hosting site over our VPN, but the remote servers can't link back to our office. The links also need SQL login username/password because the servers are in different domains. We can join tables from both sites in a connection to a local server, but not on the remote servers.

It wouldn't be difficult to imagine a scenario where the DTS package is running from a location that can connect to both server1 and server2, but no linked server definition is possible to do a join. In this case you might use a lookup transform in DTS (poor performance for large amounts of data), or transfer the data from one server to a temp table on the other to allow a join.

If you upgrade to SQL 2005, SSIS has a join transform that could do the job."

------------
When you 've got a hammer, everything starts to look like a nail...