Linking Two SQL Servers

**Erland Sommarskog** · Jul 23 '05, 08:48 AM

Re: Linking Two SQL Servers

(m.ramana@gmail .com) writes:[color=blue]
> Two SQL Servers
> 1) "Test"
> Payroll DB
> Transaction Table
> pr_SubmitTransa ction in Payroll DB
> *************** *************** **************
> 2) "Production "
> Payroll DB
> Transaction Table
> Pr_SubmitTransa ction in Payroll DB
> Accounts DB
> Pr_VerifyAccoun ts (@AcctNumber)
> *************** *************** **************
>
> I want to use same pr_VeriftAccoun ts in both test and production
> Pr_SubmitTransa ction stored procedure.
> The pr_SubmitTransa ction in production works fine when I say
> Exec Payroll.dbo.pr_ VerifyAccounts as it is on same server. (WORKS
> FINE)
> Exec [PRODUCTION].Payroll.dbo.pr _VerifyAccounts (DOES NOT WORK)
>
> Should I use sp_addlinkedSer ver ?? to do this ?. Please provide me some
> feedback.
> I know I can acheive this by front end, but I was do it in one stored
> procedure.[/color]

Yes, you would use sp_addlinkedser ver. Of Production is called
that, and is an SQL Server as well, it's as easy as:

sp_addlinkedser ver PRODUCTION

Hm, well, OK, maybe authentication does not work out of the box. In
this case you need to use sp_addlinkedsrv login.

Both these procedures are well described in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**m.ramana@gmail.com** · Jul 23 '05, 08:49 AM

Re: Linking Two SQL Servers

Thanks for taking your time and effort replying to my question.Looks
like its working, I can see the results in query analyzer but with one
error
Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedser ver,
The server 'PRODUCTION' already exists. (How can I get rid of this????)

Is it because PRODUCTION is already registered in my enterprise manager
or is it because PRODUCTION is not removed (sp_droplinkeds erver or
something like that) in stored procedure?

When I run the stored procedure from front end I am getting an Error
"Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedser ver" on
mycmd.ExecuteNo nQuery, It is executing the stored procedure perfectly
(all my insert statements in stored procedures are executed).

**Erland Sommarskog** · Jul 23 '05, 08:49 AM

Re: Linking Two SQL Servers

(m.ramana@gmail .com) writes:[color=blue]
> Thanks for taking your time and effort replying to my question.Looks
> like its working, I can see the results in query analyzer but with one
> error
> Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedser ver,
> The server 'PRODUCTION' already exists. (How can I get rid of this????)
>
> Is it because PRODUCTION is already registered in my enterprise manager
> or is it because PRODUCTION is not removed (sp_droplinkeds erver or
> something like that) in stored procedure?[/color]

Depends on what you mean with "registered in Enterprise Manager". If
you mean a server listed directly under a server group in the tree,
no this has nothing to do with linked servers. The registered servers
are stored in the local registry, out of reach for SQL Server itself.

On other hand if you mean that you had previously registered PRODUCTION
as a linked server from Enterprise Manager, your understanding is right.
In fact, all the management GUI:s in Enterprise Manager are basically
only wrappers on SQL commands and system procedures. So, in fact if
you want to do what EM does, but from code, you can use the Profiler
to eavesdrop on what Enterprise Manager emits.

I don't remember for sure, but I believe that the command to drop a
linked server is sp_dropserver. The topic for sp_addlinkedser ver in
Books Online should have a See Also to that topic.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**m.ramana@gmail.com** · Jul 23 '05, 08:55 AM

Re: Linking Two SQL Servers

As you suggested I had to use exec sp_dropserver
'PRODUCTION','d roplogins'
Thanks a lot. Everything works great.

**m.ramana@gmail.com** · Jul 23 '05, 08:56 AM

Re: Linking Two SQL Servers

still issues......
In stored procedure itself I have two statements which says
Exec sp_addlinkedSer ver 'PRODUCTION'
Exec sp_addlinkedsrv login 'PRODUCTION','f alse',NULL,@Log inName,@Pwd
Insert into [PRODUCTION]. --bla -bla
Insert into <Localtable> --bla bla
And in the end I have sp_removeserver [PRODUCTION]

When I compile, it throws an error saying PRODUCTION is not in
sysservers. But If I issue sp_addlinkedser ver from outside, then it
will let me compile.
If you check I am doing that inside the code, so when ever it requires
it, it is there.
Presently I have to call three procedures from front end, One to
Linkserver, one to do my regular insertion and all, and finally to drop
server as a work around.
Can this be put in one stored procedure ??

Thanks again

**Erland Sommarskog** · Jul 23 '05, 08:56 AM

Re: Linking Two SQL Servers

(m.ramana@gmail .com) writes:[color=blue]
> still issues......
> In stored procedure itself I have two statements which says
> Exec sp_addlinkedSer ver 'PRODUCTION'
> Exec sp_addlinkedsrv login 'PRODUCTION','f alse',NULL,@Log inName,@Pwd
> Insert into [PRODUCTION]. --bla -bla
> Insert into <Localtable> --bla bla
> And in the end I have sp_removeserver [PRODUCTION]
>
> When I compile, it throws an error saying PRODUCTION is not in
> sysservers. But If I issue sp_addlinkedser ver from outside, then it
> will let me compile.
> If you check I am doing that inside the code, so when ever it requires
> it, it is there.
> Presently I have to call three procedures from front end, One to
> Linkserver, one to do my regular insertion and all, and finally to drop
> server as a work around.
> Can this be put in one stored procedure ??[/color]

You could have a procedure that first sets up the linked server, and
then calls the inner procedure.

As for the linked server having to be set up when you create the procedure,
there is not much to do about it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**m.ramana@gmail.com** · Jul 23 '05, 08:58 AM

Re: Linking Two SQL Servers

I think I can live with it. I got two procedures now one to add/remove
linked server and one for business logic. Thanks for all your help

Linking Two SQL Servers

Linking Two SQL Servers

Comment

Comment

Comment

Comment

Comment

Comment

Comment