Two SQLServer instances running on same computer->how to call stored procedures found on one using the other?

Question

0.00/5 (No votes)

See more:

The key is that both instances are running simulataneously and I just don't want to go through the harrowing experience of having to create all these stoprocs that I've authored on the instance that needs to use them.

My guess would be that there's a way in T-SQL to make sure I'm targetting the "remote" [there] instance when I'm executing with the

USE [here].[dbo].[...]

local expression.

What's the form of the pre-USE, so to speak?

[Edit]

Like "USE [(leftarrow)servername(rightarrow)\

(leftarrow)instancename(rightarrow)].[here].[dbo].[allthosestoredprocedures]" ...

[End Edit]

Posted 4-Nov-11 6:52am

RedDk

Updated 4-Nov-11 7:06am

v4

Add a Solution

Comments

RedDk 14-Dec-11 13:00pm

redaction (oh yes they are ...)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2011-11-04T07:01:00

Solution 1

You can connect to the other instance by creating a linked server. see here :
http://www.quackit.com/sql_server/sql_server_2008/tutorial/linked_servers.cfm[^]

You will have to create all your SP's on your here instance as well as calling the SP on the linked server will change data on that server instance not on the here one.

Posted 4-Nov-11 7:01am

Mehdi Gholam

Comments

RedDk 4-Nov-11 13:22pm

Well,

The trouble is that the SQL Server instance is also an SQLClient10.0 server. From the first couple of lines in the help file for ssmse I can see that there's a prospective solution like yours if you're dealing with OLE DB data sources. Not for a bonafide SQL Native.

RedDk 4-Nov-11 13:25pm

Improvement on that locus:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/6ef578bf-8da7-46e0-88b5-e310fc908bb0.htm

I see that I'm confused here, on OLE DB pass through, but the errors are specifically about "SQLNCLI10". What's the THAT option doing in the drop-down if that's not OLEDB too?

RedDk 4-Nov-11 13:38pm

Also can't leave "Product name" blank as in the quackit link

Amir Mahfoozi 4-Nov-11 14:19pm

+5