Linking Two SQL Servers

[m.ramana]

Two SQL Servers
1) "Test"
Payroll DB
Transaction Table
pr_SubmitTransaction in Payroll DB
********************************************
2) "Production"
Payroll DB
Transaction Table
Pr_SubmitTransaction in Payroll DB
Accounts DB
Pr_VerifyAccounts (@AcctNumber)
********************************************

I want to use same pr_VeriftAccounts in both test and production
Pr_SubmitTransaction stored procedure.
The pr_SubmitTransaction 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_addlinkedServer ?? 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.

[Erland Sommarskog]

(m.******@gmail.com) writes:

Two SQL Servers
1) "Test"
Payroll DB
Transaction Table
pr_SubmitTransaction in Payroll DB
********************************************
2) "Production"
Payroll DB
Transaction Table
Pr_SubmitTransaction in Payroll DB
Accounts DB
Pr_VerifyAccounts (@AcctNumber)
********************************************

I want to use same pr_VeriftAccounts in both test and production
Pr_SubmitTransaction stored procedure.
The pr_SubmitTransaction 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_addlinkedServer ?? 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.

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

sp_addlinkedserver PRODUCTION

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

Both these procedures are well described in Books Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[m.ramana]

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_addlinkedserver,
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_droplinkedserver 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_addlinkedserver" on
mycmd.ExecuteNonQuery, It is executing the stored procedure perfectly
(all my insert statements in stored procedures are executed).

[Erland Sommarskog]

(m.******@gmail.com) writes:

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_addlinkedserver,
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_droplinkedserver or
something like that) in stored procedure?

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_addlinkedserver in
Books Online should have a See Also to that topic.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[m.ramana]

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

[m.ramana]

still issues......
In stored procedure itself I have two statements which says
Exec sp_addlinkedServer 'PRODUCTION'
Exec sp_addlinkedsrvlogin 'PRODUCTION','false',NULL,@LoginName,@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_addlinkedserver 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]

(m.******@gmail.com) writes:

still issues......
In stored procedure itself I have two statements which says
Exec sp_addlinkedServer 'PRODUCTION'
Exec sp_addlinkedsrvlogin 'PRODUCTION','false',NULL,@LoginName,@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_addlinkedserver 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 ??

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[m.ramana]

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