August 3, 2004 at 6:29 am
I am trying to link to an Excel spreadsheet programatically. Calling sp_addlinkedserver works great from script as long as the statement is followed by "GO". However, when called from a stored procedure, the operation fails, saying that the "server" can not be found in sysservers and that I need to execute sp_addlinkedserver. Does someone know why I am having trouble with this?
c barnhart
August 3, 2004 at 9:51 pm
Before creating the stored procedure, manually create the linked server. Then create the stored procedure (without the GOs). Once the stored procedure is successfully created, manually drop the linked server. The stored procedure should then execute properly (I'm assuming you're dropping the linked server in the stored procedure).
SQL Server is checking the validity of your linked server at the time of stored procedure creation. If it doesn't exist, you'll get that server cannot be found error. That's the reason for the manual creation of the linked server connection before you run the CREATE PROC statement. Once the stored procedure is created, you can drop the linked server safely.
K. Brian Kelley
@kbriankelley
August 4, 2004 at 2:53 pm
Thanks for the quick reply.
The reason I was attempting this is that I saw it done in a recent SQL Server Mag article (Aug 2004 issue) entitled "Embedded Scripting" by William Barton. In his article, William shows 3 stored procedures. The main proc (which calls the other 2) creates a link to an Excel spreadsheet (sp_addlinkedserver), retrieves a result from it (EXEC (@sqlstr)), then destroys the linked server (sp_dropserver) all within the SAME procedure (Listing 3, Page 31).
Being able to create, use, then destroy a linked Excel server inside a stored procedure seems very, very useful. So, I was just puzzled why I am not able to do the same.
My SQL Server 2000 is running on Windows 2003 Enterprise Server and I have had several difficulties of the security nature. I just wonder if that has something to do with it.
I tried using OPENDATASOURCE with much better success but have been unable to get the result into a table to work with (get an MSDTC error).
Will keep trying.
Thanks.
August 4, 2004 at 3:26 pm
Have you looked at the following?
817064 - How to enable network DTC access in Windows Server 2003
329332 - PRB: You Receive Error 7391 When You Run a Distributed Transaction Against a Linked Server
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply