Prev: Fragmentation
Next: Sql Server 2005. Best configuration and parameters.
From: Sejoo on 12 Feb 2010 14:47
I've link server set up and running the store procedure , the link
server user is sysadmin. The sproc was running fine till 3 weeks
suddenl its failing giving below error. Any help is appreciated

(Message 0) OLE DB provider "SQLNCLI" for linked server "ServerA"
returned message "No transaction is active.". [SQLSTATE 01000]
(Message 7412). The step failed.

Any help is appreciated
From: Erland Sommarskog on 12 Feb 2010 18:02
Sejoo (sejal1410(a)gmail.com) writes:
> I've link server set up and running the store procedure , the link
> server user is sysadmin. The sproc was running fine till 3 weeks
> suddenl its failing giving below error. Any help is appreciated
>
> (Message 0) OLE DB provider "SQLNCLI" for linked server "ServerA"
> returned message "No transaction is active.". [SQLSTATE 01000]
> (Message 7412). The step failed.

Not that linked server issues are ever fun to troubleshoot, but this
is impossible to tell without further information.

I would suggest that you run the procedure from Mgmt Studio to see
if this works. You can also run from Agent and use Profiler to trace
what is going *on both servers*, and include Error:Exception and
Error:UserMessage in the trace. Often with linked servers, the real
error is lost, and all you see is a generic useless message.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Fragmentation
Next: Sql Server 2005. Best configuration and parameters.