Azure Linked Server - Could not find stored procedure 'sp_reset_connection'

Hi

I am trying to work with an Azure database as a linked server connecting through SQL Management Studio. When executing stored procedures I am getting the message:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_reset_connection'.

The client is:

Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
 Jun 12 2012 13:05:25
 Copyright (c) Microsoft Corporation
 Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

This is not stopping the stored procedures from executing but I have an inherent dislike of unresolved warning messages. I understand that failing to reset the connection correctly can have an impact on client statistics etc.

I would appreciate any insight as to why this may be happening.

BTW: I have checked using a non-Azure linked server and do not get this message so I believe it is rspecific to Azure.

May 4th, 2013 12:20pm

Exactly I can't able to process regarding this. But I am requesting to verify the following link

http://stackoverflow.com/questions/641120/what-exec-sp-reset-connection-shown-in-sql-profiler-means
Free Windows Admin Tool Kit Click here and download it now
May 4th, 2013 2:14pm

Hi,

Since the issue is related to SQL Azure, I have moved this thread to SQL Azure forum for getting a better support.

Thanks for your understanding.

May 6th, 2013 2:10am

why not connect to sql azure directly? it may work
Free Windows Admin Tool Kit Click here and download it now
May 6th, 2013 7:47am

Thanks for the response.

I have specific reasons for using a Linked Server so this is not an option. I am keen to understand why Azure behaves the way it does in this scenario and whether I need to be concerned about connection state. 

May 6th, 2013 1:21pm

Have you found a resolution to this? I'm experiencing the same issue because I am trying to remotely execute stored procedures on SQL Azure from my on-premise SQL Server 2008 R2 as a workaround to the lack of a SQL Server Agent on SQL Azure. My set up is similar - I have a Linked Server on my on-premise SQL Server 2008 R2 through which I am trying to execute stored procedures.
Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2013 10:34pm

There's a lot of wrong information about linked server to Windows Azure SQL Database (formerly known as SQL Azure).  The feature supported as of the November 2012 release, and you should follow the instructions here:

http://blogs.msdn.com/b/windowsazure/archive/2012/09/19/announcing-updates-to-windows-azure-sql-database.aspx

And use SQLNCLI instead of MSDASQL for your linked server OleDb provider.

David

July 3rd, 2013 10:40pm

Me too getting same error when trying to execute a stored proc on sql azure thru on-premise sql server. I did make sure that linked server is created using SQLNCLI and it doesn't solve the issue. Could anybody know whats going on?
Free Windows Admin Tool Kit Click here and download it now
May 27th, 2014 6:29pm

Try calling the stored procedure using the EXEC ('proc') at SERVER syntax.

eg

EXEC sp_addlinkedserver
@server='MyLinkedServer', -- here you can specify the name of the linked server
@srvproduct='',     
@provider='sqlncli', -- using SQL Server native client
@datasrc='sdkjf239835.database.windows.net',   -- add here your server name
@location='',
@provstr='',
@catalog='test'  -- add here your database name as initial catalog (you cannot connect to the master database)

-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'MyLinkedServer',
@useself = 'false',
@rmtuser = 'adminUser',             -- add here your login on Azure DB
@rmtpassword = 'MyPassword' -- add here your password on Azure DB
EXEC sp_serveroption 'MyLinkedServer', 'rpc out', true;

go
exec ('dbo.foo') at [MyLinkedServer]

David

May 27th, 2014 9:09pm

Has anyone found a solution to this issue? I am also facing similar issue. Any pointers to the solution?
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2014 8:09pm

Has anyone found a solution to this issue? I am also facing similar issue. Any pointers to the solution?

I am unaware of any issue.  Can you post a repro?

David

October 2nd, 2014 8:24pm

Execute a sql string works, but this approach makes it harder to pass variables easily into the stored procedure.  For instance, what if I wanted to pass a table variable into the stored procedure.  I could serialize this into XML, but I would prefer to just pass the table.  The odd thing is that the stored procedure does execute, but it also returns the error.  
  • Edited by C Rogala 11 hours 6 minutes ago spelling
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 3:20pm

Execute a sql string works, but this approach makes it harder to pass variables easily into the stored procedure.  For instance, what if I wanted to pass a table variable into the stored procedure.  I could serialize this into XML, but I would prefer to just pass the table.  The odd thing is that the stored procedure does execute, but it also returns the error.  
  • Edited by C Rogala 10 hours 55 minutes ago spelling
March 27th, 2015 3:25pm

So just to test a theory, I create a stored procedure called sp_reset_connection in my Azure database.  I granted execute permissions to the sp and the error message went away:

CREATE PROCEDURE sp_reset_connection 
AS
BEGIN
	DECLARE @i INT = 1;
END
GO

Not sure this is ideal, but it did remove the error message.  

Btw - I don't believe this is the ideal solution, and I am very open to someone else's opinion.  

Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 4:44pm

Execute a sql string works, but this approach makes it harder to pass variables easily into the stored procedure.  For instance, what if I wanted to pass a table variable into the stored procedure.  I could serialize this into XML, but I would prefer to just pass the table.  The odd thing is that the stored procedure does execute, but it also returns the error.  
  • Edited by C Rogala Friday, March 27, 2015 8:10 PM spelling
March 27th, 2015 7:20pm

Execute a sql string works, but this approach makes it harder to pass variables easily into the stored procedure.  For instance, what if I wanted to pass a table variable into the stored procedure.  I could serialize this into XML, but I would prefer to just pass the table.  The odd thing is that the stored procedure does execute, but it also returns the error.  
  • Edited by C Rogala Friday, March 27, 2015 8:10 PM spelling
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 7:20pm

Execute a sql string works, but this approach makes it harder to pass variables easily into the stored procedure.  For instance, what if I wanted to pass a table variable into the stored procedure.  I could serialize this into XML, but I would prefer to just pass the table.  The odd thing is that the stored procedure does execute, but it also returns the error.  
  • Edited by C Rogala Friday, March 27, 2015 8:10 PM spelling
March 27th, 2015 7:20pm

Execute a sql string works, but this approach makes it harder to pass variables easily into the stored procedure.  For instance, what if I wanted to pass a table variable into the stored procedure.  I could serialize this into XML, but I would prefer to just pass the table.  The odd thing is that the stored procedure does execute, but it also returns the error.  
  • Edited by C Rogala Friday, March 27, 2015 8:10 PM spelling
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 7:20pm

So just to test a theory, I create a stored procedure called sp_reset_connection in my Azure database.  I granted execute permissions to the sp and the error message went away:

CREATE PROCEDURE sp_reset_connection 
AS
BEGIN
	DECLARE @i INT = 1;
END
GO

Not sure this is ideal, but it did remove the error message.  

Btw - I don't believe this is the ideal solution, and I am very open to someone else's opinion.  

Can you post a repro script demonstrating the issue? It is unclear how the problem can be reproduced.
March 30th, 2015 1:41pm

So just to test a theory, I create a stored procedure called sp_reset_connection in my Azure database.  I granted execute permissions to the sp and the error message went away:

CREATE PROCEDURE sp_reset_connection 
AS
BEGIN
	DECLARE @i INT = 1;
END
GO

Not sure this is ideal, but it did remove the error message.  

Btw - I don't believe this is the ideal solution, and I am very open to someone else's opinion.  

Can you post a repro script demonstrating the issue? It is unclear how the problem can be reproduced.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2015 1:41pm

So just to test a theory, I create a stored procedure called sp_reset_connection in my Azure database.  I granted execute permissions to the sp and the error message went away:

CREATE PROCEDURE sp_reset_connection 
AS
BEGIN
	DECLARE @i INT = 1;
END
GO

Not sure this is ideal, but it did remove the error message.  

Btw - I don't believe this is the ideal solution, and I am very open to someone else's opinion.  

Can you post a repro script demonstrating the issue? It is unclear how the problem can be reproduced.
March 30th, 2015 5:40pm

So just to test a theory, I create a stored procedure called sp_reset_connection in my Azure database.  I granted execute permissions to the sp and the error message went away:

CREATE PROCEDURE sp_reset_connection 
AS
BEGIN
	DECLARE @i INT = 1;
END
GO

Not sure this is ideal, but it did remove the error message.  

Btw - I don't believe this is the ideal solution, and I am very open to someone else's opinion.  

Can you post a repro script demonstrating the issue? It is unclear how the problem can be reproduced.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2015 5:40pm

So just to test a theory, I create a stored procedure called sp_reset_connection in my Azure database.  I granted execute permissions to the sp and the error message went away:

CREATE PROCEDURE sp_reset_connection 
AS
BEGIN
	DECLARE @i INT = 1;
END
GO

Not sure this is ideal, but it did remove the error message.  

Btw - I don't believe this is the ideal solution, and I am very open to someone else's opinion.  

Can you post a repro script demonstrating the issue? It is unclear how the problem can be reproduced.
March 30th, 2015 5:40pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics