Connect to another SQL Server Database Within a Stored Proc

  • I am writing a stored procedure in SQL Server 2005 on my local instance of SQL Server. I want to connect to a SQL Server database on a different PC. I have the IP address of that database server as well as the other pertinent connection information. I am not sure how to actually connect to that database with a stored proc. I do this all of the time in Visual Basic using the ADO connection object.

  • The normal way to do this is to setup a linked server pointing to the other server. You can then access it's data by using ServerName.databasename.Schema.tablename. You should be able to use the IP address instead of the servername but you'll need to enclose it inside [], i.e. [192.168.0.10].database.............



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I am looking at sp_AddLinkedServer, but am having syntax problems. I am using @server='LinkedServerName',@DataSrc='1.1.1.1' and am getting error that it needs a provider name. When I add @Provider='SQLNLCI', I then get the error that Null is an invalid product name. I then add @SrvProduct='SQL Server', it tells me that I cannot specify a provider or any properties.

    How do I set up a linked server for the specific SQL Server on the IP address?

    I do see that after creating the linker server, I then need to do the sp_addlinked srvlogin to log into the database name with the uid and pwd.

    Is this correct?

  • I normally use SSMS to add linked server. It's a GUI, so making mistakes are less ;). Your connection method seems to be fine.

    Hope this helps,

    Rajesh

  • Thank you.:)

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

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