Tuesday, March 27, 2012

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

No comments:

Post a Comment