Post reply

Connect to another SQL Server Database Within a Stored Proc

  • October 21, 2008 at 11:05 am

    #383156

    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.

  • October 21, 2008 at 11:15 am

    #887743

    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]

  • October 21, 2008 at 11:55 am

    #887762

    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?

  • October 21, 2008 at 12:57 pm

    #887787

    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

  • October 21, 2008 at 1:05 pm

    #887795

    Thank you.:)

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

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