LINKED SERVER to external sql server

[davejam]

Hi All,

Just can't get my head around this one.

I have to update records on an external sql server and trying to setup a linked server but can't seem to get it working.

I am working with sql 2005.

All i have for the external server is the IP address, dbName, user and password. I have dbo rights to my database with the user setup.

I can connect to it as a server through management studio with the details, but i'm trying to make an automated update from an internal system so need the sql server here link to the external one.

Haven't done this in years so really am struggling.

Does anyone have any examples, tips or links, went through the microsoft information, but unless you know what you need it reads as gibberish to me.

Ideally I would like to set it up with a name such as ExtSQL and refer to this within my procedures as ExtSQL.DBName.DBO.TABLE, Then define the IP and user etc within this link.

Cheers

daveJam

it works on my machine, so technically i win!

 

[mrdenny]

Bring up the new linked server screen. Name the linked server. Select the Other Data Source radio button. For the provider select "SQL Native Client". In the Product Name and Data source enter the FQDN or IP address of the server name. For the Catalog enter in the database name.

Use the security tab to map the permissions.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[davejam]

large delay on this one!!! been involved on getting other deadlines sorted

got it working from my pc using what was specified above and very happy with it....

unfortunately when linking from our server I'm getting an issue..

setup is as follows
internal server, sql 2005 (i think)
external server, sql 2008

unfortunately this is the only setup i have...

from internal, I setup linked server as i have on my pc and created a stored procedure to get a simple one row table from external

select * from extLink.extSql.dbo.table_1

when trying to compile i get the following error

OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Procedure testlink, Line 4
OLE DB provider 'SQLNCLI' reported an error. The provider did not give any information about the error.

Any ideas or suggestions greatly appreciated...

cheers

daveJam

it works on my machine, so technically i win!

 

[Crowley16]

it's probably the server versions that's giving you problems.

try upgrading to 08 or downgrading to 05...

--------------------
Procrastinate Now!

 

[PRPhx]

Take a look at this link. It offers some info and insight as to what else you may have to do to get more info on your error.:

http://support.microsoft.com/kb/314530

 

[mrdenny]

Linked servers between versions should work fine.

What are the security settings you have on the linked server?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog