Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linking servers on the fly in a SP?

Status
Not open for further replies.
gbaughma

gbaughma

IS-IT--Management
Staff member
Nov 21, 2003
4,772
US
I *thought* I had this working, and for some reason it's just... not.

Code: 
IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = N'MIRRORMAS')
BEGIN

EXEC master.dbo.sp_addlinkedserver @server = N'MIRRORMAS', @srvproduct=N'SILENTMAS', @provider=N'MSDASQL', @datasrc=N'SILENTMAS'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MIRRORMAS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MIRRORMAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
END

-- Delay until linked server appears in sys.servers
WHILE (1=1)
BEGIN
	-- Loop here
	IF EXISTS (SELECT '1' FROM sys.servers WHERE name='MIRRORMAS') 
	BREAK;
END

-- DO a bunch of table replication...


-- Unlink MAS90 Server
EXEC master.dbo.sp_dropserver 'MIRRORMAS', 'droplogins'

When I run the SP, it says that the MIRRORMAS doesn't exist in sys.servers
This *did* work the first day I wrote it, then it just stopped working. It's like it's just totally ignoring the linked server routine.

If I run the commands just as a query, and not as a SP, it links the server fine.
I have tested my loops, and they are working fine.
I'm going bald over this one... I don't understand why my SP is just choosing to ignore the linking of the server, and hopping straight on to the table replication, where it fails.

Note: If I link the server, then run the routine, the routine runs fine.

Any thoughts or insight?

TIA!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Sort by date Sort by votes
Why do you create the linked server on the fly? Why not leave them permanently in place?

the only obvious thing i can see is that the drop server is using a varchar input, and the create server is using an nvarchar input. If you have any special characters, they may be getting dropped in the name of the server to drop. Other than that, could the delay be applying to the gap between the sp_addlinkedserver call and the sp_addlinkedsrvlogin call?
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #3
I don't leave them linked permanently because it causes issues with MAS90 (our ERP/Accounting software)
Keeping the server linked leaves files locked, even with opportunistic locking disabled, and corrupts the MAS90 databases if it can't lock them to clean up.
It never gets to the dropserver, because it doesn't create the link and the SP fails.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
So, since the code never makes it to the drop server section, then the holdup must be just after the sp_addlinkedserver call. Can you run that separately? What happens when you move your delay loop up between sp_addlinkedserver and sp_addlinkedsrvlogin?
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #5
Tried that... tried making the addserver call a separate SP... if I run that separate SP on its own, the server links fine. If I run the commands just as a query, the server links fine. If I link the server then run the rest of the routine, it works fine.

It's only when I do it all at once that it fails, and says that it can't find the server in sys.servers

It's making me crazier than I already am.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #6
Well, it looks like it was a permissions issue...
When the website was calling the SP, the SP didn't have any "run as" permissions assigned to it... and seems the web server didn't have permission to link the server.

I *think* I have it working now...


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #7
I was wrong. It's still not firing properly. XD



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
Can you add a couple of PRINT statements to see how far exactly the execution gets? At least one before you add the linked server, one after it's added and one before the loop. Report the outcome please.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #9
Added print statements all over the place...

No output. Even a PRINT 'Starting routine' at the beginning didn't show up.
It's like it's doing a pre-compile, looking to make sure that everything is cool before it even *tries* to run...
... and erroring before it even executes the link command.

This is... stupid.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #10
(Additional note on this... and perhaps a clue)

If I *modify* the SP, I can't even do the modification unless the linked server is established first.
I have to link the server, then save the modified SP.
This is telling me that SQL Server is checking for the existence of the linked server before it even makes an attempt to run the SP.
Since the SP is what does the linking to start with, this is causing my problem.
Is there a way to run a stored procedure without the precompile checking? Or without precompiling at all? Seems like kind of a silly thing... you can't save the SP to link a server unless the server is already linked.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #11
Ah-hah!
I think I got it this time.

OK, so the problem was the compiling and the validation of objects when it compiles...
Since the linked server wasn't yet existing, and the compiler is too stupid to realize that it will exist by the time it gets to those statements, it was failing.

Therefore... here was my solution:
Run the table replication as a dynamic statement...

so..

Code: 
LTER PROCEDURE [dbo].[MirrorMASTables]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here

DECLARE @SQLCommand varchar(MAX)

IF NOT EXISTS (SELECT '1' FROM sys.servers WHERE name = N'MIRRORMAS')
BEGIN


EXEC MAS_AES.dbo.LinkMASforMirror

END

-- Delay until linked server appears in sys.servers
WHILE (1=1)
BEGIN
	-- Loop here
	IF EXISTS (SELECT '1' FROM sys.servers WHERE name='MIRRORMAS') 
	BREAK;
END

DROP TABLE IP_SO_SALESORDERHEADER
SET @SQLCommand = 'SELECT * INTO IP_SO_SALESORDERHEADER FROM OPENQUERY(MIRRORMAS,''SELECT * FROM SO_SALESORDERHEADER'')'
EXEC(@SQLCommand)

-- Etc... until it's done then unlink the server again.

That way, the compiler doesn't validate the information in SQLCommand, it can be updated, and it's running properly.

YAY ME!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
Glad you figured it out!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
 
Upvote 0 Downvote
  • Thread starter
  • Moderator
  • #13
Fired the routine last night without a hitch. :D



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
230
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
151
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
398
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
532
Chris Miller
Chris Miller
Colin Burton
  • Locked
  • Question
Determine if a file exists on s different server to the SQL Server 1
Replies
2
Views
424
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top