linked server problem with @@SERVERNAME

Hey all,
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
Message posted via http://www.droptable.comThe brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQ
droptable.com...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error
:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
Message posted via http://www.droptable.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQ
droptable.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.droptable.com

linked server problem with @@SERVERNAME

Hey all,
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
--
Message posted via http://www.sqlmonster.comThe brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQLMonster.com...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
--
Message posted via http://www.sqlmonster.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-part´name of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQLMonster.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.sqlmonster.com

linked server problem with @@SERVERNAME

Hey all,
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
Message posted via http://www.droptable.com
The brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.droptable.co m...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
Message posted via http://www.droptable.com
|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.droptable.co m...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.droptable.com
sql

Linked Server problem when querying

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:

EXEC sp_addlinkedserver @.server = 'Achilles\Mixed', @.srvproduct = ' ', @.provider = 'SQLNCLI', @.datasrc = 'Archilles\Mixed', @.catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.

Hi,

the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Please check two things:
1. make sure SQL 2005 allow remote access
2. make sure you set right userid/password for linked server.

Thanks

|||

Hi,

I have the same problem. But the distributed query seems to work on the management studio of the server but bot accross the network on other management studio with the same impersonated logins.

|||

I had to create a SQL authenticated login on the remote server (loginname) and then all local logins are mapped to the one remote SQL login using the Security page of the Linked Servers Properties dialogue.

I could not get it to pass Windows authenticated logins to the remote server at all.

In the Security Page of Linked Servers Properties dialogue.

For a Login not defined in the above list Connections will:

Be made using this security context:

Remote Login: Loginname

With Password: LoginPassword

Linked server problem

Hi,

I am connecting two sql servers together as linked server, the servers are running windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.

However, my problem appeared when I tried to stress test my application, the connection on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
my transaction details:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) ro prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller), and then
5) The transaction is committed.

The above scenario is working properly for 53 and less cocurrent connection! In case more cocurrent connections are
issued, the connection are blocked almost immediately and never returns till the execution times out. The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.

Please note that:
1) the test was conduct separately from any other activity on the SQL servers. i.e. they was the only connections on the servers.
2) When we moved the the Linked Server database to the local server, i.e. we removed the linked server, all connections completed their commands execution successfully and without timeouts. Also, note that I have ran the test successfully, on the local server, with 256 and 512 connections.

I do highly appreciate any help or support on this.

Regards,Any one can help?|||This is beyond my ability to test. I've never seen the problem with any configuration that I use, so I'd suspect that it is either an interaction between the software versions that you are using or something specific to your code.

My advice is to open an incident with Microsoft Premier Support. If the problem is in your code, it will cost you roughly $1000 US to prove it. If the problem is in their code, it won't cost you a thing and you'll have had some pretty valuable review of your code and configuration, plus you'll get a solution (usually a patch) for the problem too!

-PatP|||You could set a max on the Query Timeout for the linked server. It would still take awhile to resolve. The bigger questions is what's causing the locking in the first place? Is it always one process that's causing this or one area such as a particular table?

Linked Server Performance with Stored Procedure

I have set up a stored procedure to do a quick lookup similar to
dbo.get_Value_for_Id @.id int

When I invoke the stored procedure from a database on the same server instance as the stored procedure lives, it processes 10K lookups in about 4 seconds.

When I invoke the stored procedure from a different server (via MSSM with a Linked server), it took about 4 minutes to perform 10K lookups. Not good. My question is what can I do to get back to the much faster response times.

I set up a little .Net app to do the remote calls, and it was back to the 4 second range for the 10K calls, in my mind eliminating the network as the primary bottleneck.

Items I've tried:

SET XACT_ABORT is OFF on both ends. No visible results.

SET REMOTE_PROC_TRANSACTIONS OFF . This appears to have put the distributed transactions issue to bed.

Run SP_SERVEROPTION "collation compatible" option to true for the server link. Ran this on both ends. This setting supposedly tells SQL Server the collation sequence and character sets match on both ends. Probably a good idea, even though it didn't make much difference here.

Run the stored procedure remotely using OpenQuery. Took some creativity, given OpenQuery doesn't accept variables as arguments. Got this one working, with run times down to the 90-135 second range (1:30-1:45).

I'm still an order of magnitude slower than I want to be. I suppose I could implement a .NET solution (select the key out of a database on server x, lookup the key on server y), but that sounds more like a workaround than actually using SQL Server capabilities. Any suggestions?

try

exec linkedserver.mydb.dbo.sp_executesql N'exec mysp'

|||

Thanks for the suggestion, Nigel.

I setup two variations of the sp_executesql call, one calling the stored procedure, the second invoking the main select in the stored procedure. In both cases, when run during an MSSM session that was logged into the server that was doing the work, I could loop through 10000 calls in 4-5 seconds. And, in both cases, when logged into my desktop, it took about 4 and a half minutes to run the same script. So this didn't resolve the issue. Interestingly enough, I did see some surging in the activity of my desktop msdtc.exe (in windows task manager), so I'm guessing this process is somehow getting the distributed transaction server involved.

To confirm the involvement of the distributed transaction server, I went into my desktop and turned off the msdtc service. With this service turned off, the script would not run (MSG 8501 MSDTC on server xxx not available). So that seems to confirm I have not evaded the overhead of MSDTC.

To minimize confusion, following is the test script I am using...

set nocount on

set XACT_Abort off

set REMOTE_PROC_TRANSACTIONS off

declare @.Encrypt_id int

declare @.c int

declare @.t table (UnEncrypted_Card varchar(19))

declare @.sql nvarchar(500)

declare @.ParamDef nvarchar(500)

set @.c = 0

while (@.c < 10000)

Begin

set @.Encrypt_id = Rand() * 100000

set @.sql = N'exec dbo.p_Get_Card_Number_By_Id_inst @.Enc_id , ''123456'' ';

-- set @.sql = N'select Value_Encrypted as Value from dbo.tb_Encrypted_Store where Encrypt_Id = @.Enc_Id';

set @.ParamDef = N'@.Enc_Id int';

-- print @.sql

insert into @.t exec linkservername.dbname.dbo.sp_executesql @.sql, @.ParamDef, @.Enc_id = @.Encrypt_Id

set @.c = @.c + 1;

End

Linked Server Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
Could you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

Linked Server Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Lockes - StressTesting

Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Lockes - StressTesting

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Lockes - StressTesting

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
Do you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Lock Problem

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
You could perhaps reduce the size of the transaction. Locks are held till
the end of the transaction.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
sql

Linked Server Lock Problem

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemYou could perhaps reduce the size of the transaction. Locks are held till
the end of the transaction.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Lock Problem

Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemYou could perhaps reduce the size of the transaction. Locks are held till
the end of the transaction.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql

Linked server from SQL 2005 64-bit to SQL 7

I've experienced the problem with creating a linked server from SQL 2005 64-bit to SQL 2000.

"The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

I have resolved this by upgrading the system stored procs on the 2000 machine by running instcat.sql, documented in http://support.microsoft.com/default.aspx/kb/906954/en-us . Works great.

However, I'm now experiencing the same error when linking from SQL 2005 64-bit to SQL 7. I'm concerned about applying 2000 code onto a SQL 7 machine. Any ideas on how to handle this case?

Thanks

Paul

Hi Paul,

You can gohead and apply the 2000 code instcat.sql in SQL server 7.0, Upgrading the catalog stored procedures does not affect the operation of existing SQL Server clients...

But for this you should have Admin rights and please do take a backup of the Master DB and then apply the code.

Thanks

Thamizhan

Linked server error in SQL 2005 RTM

I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ========================================
=============
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get th
e
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:

> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linke
d
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ========================================
=============
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.g
bl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:41073
5
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the tem
p
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1
,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" return
ed
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>