Linked server problems connecting to Merant database

I have tried all kinds of things to set up a Linked Server to our Merant
database and can't seem to get it to work. I am on a Win2K server using
SQL2000.
I used the following to try to set it up:
****************************************
*****
sp_addlinkedserver @.server = 'tomsvantage',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'vantage'
****************************************
*****
This seemed to work OK. "vantage" is an ODBC dsn. It works OK with DTC.
I then try to set up the login as:
****************************************
************************
exec sp_addlinkedsrvlogin 'tomsvantage',FALSE, 'odbcvdw','pass'
****************************************
***************************
I get the following error:
****************************************
***************************
Server: Msg 15067, Level 16, State 1, Procedure sp_addlinkedsrvlogin,
Line 56
'odbcvdw' is not a local user. Remote login denied.
****************************************
****************************
Does this mean I have to have an NT user of ODBCVDW as well as a logon
on SQL Server - or just a Logon on Sql Server?
Thanks,
Tom.Thomas Scheiderich wrote:

> I have tried all kinds of things to set up a Linked Server to our Merant
> database and can't seem to get it to work. I am on a Win2K server using
> SQL2000.
> I used the following to try to set it up:
> ****************************************
*****
> sp_addlinkedserver @.server = 'tomsvantage',
> @.srvproduct = '',
> @.provider = 'MSDASQL',
> @.datasrc = 'vantage'
> ****************************************
*****
> This seemed to work OK. "vantage" is an ODBC dsn. It works OK with DTC.
> I then try to set up the login as:
> ****************************************
************************
> exec sp_addlinkedsrvlogin 'tomsvantage',FALSE, 'odbcvdw','pass'
> ****************************************
***************************
> I get the following error:
> ****************************************
***************************
> Server: Msg 15067, Level 16, State 1, Procedure sp_addlinkedsrvlogin,
> Line 56
> 'odbcvdw' is not a local user. Remote login denied.
> ****************************************
****************************
> Does this mean I have to have an NT user of ODBCVDW as well as a logon
> on SQL Server - or just a Logon on Sql Server?
I think I just answered my own question. I was able to set the login up
using SQL Authentication.
Now when I try to set up a Stored Procedure to read a table I get an
error. I get the same error if I try it using Sql Query.
If I use the following:
select * from openquery(tomsVantage,'select * from GLACCT')
I get:
****************************************
*******************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Specified driver could not be loaded
due to system error 126 (MERANT 3.60 32-BIT Progress SQL92 v9.1D).]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
******************************
I need to figure this out quickly as I am under the gun here.
Thanks,
Tom.

> Thanks,
> Tom.
>sql

Linked server problems connecting to Merant database

I have tried all kinds of things to set up a Linked Server to our Merant
database and can't seem to get it to work. I am on a Win2K server using
SQL2000.
I used the following to try to set it up:
*********************************************
sp_addlinkedserver @.server = 'tomsvantage',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'vantage'
*********************************************
This seemed to work OK. "vantage" is an ODBC dsn. It works OK with DTC.
I then try to set up the login as:
************************************************** **************
exec sp_addlinkedsrvlogin 'tomsvantage',FALSE, 'odbcvdw','pass'
************************************************** *****************
I get the following error:
************************************************** *****************
Server: Msg 15067, Level 16, State 1, Procedure sp_addlinkedsrvlogin,
Line 56
'odbcvdw' is not a local user. Remote login denied.
************************************************** ******************
Does this mean I have to have an NT user of ODBCVDW as well as a logon
on SQL Server - or just a Logon on Sql Server?
Thanks,
Tom.
Thomas Scheiderich wrote:

> I have tried all kinds of things to set up a Linked Server to our Merant
> database and can't seem to get it to work. I am on a Win2K server using
> SQL2000.
> I used the following to try to set it up:
> *********************************************
> sp_addlinkedserver @.server = 'tomsvantage',
> @.srvproduct = '',
> @.provider = 'MSDASQL',
> @.datasrc = 'vantage'
> *********************************************
> This seemed to work OK. "vantage" is an ODBC dsn. It works OK with DTC.
> I then try to set up the login as:
> ************************************************** **************
> exec sp_addlinkedsrvlogin 'tomsvantage',FALSE, 'odbcvdw','pass'
> ************************************************** *****************
> I get the following error:
> ************************************************** *****************
> Server: Msg 15067, Level 16, State 1, Procedure sp_addlinkedsrvlogin,
> Line 56
> 'odbcvdw' is not a local user. Remote login denied.
> ************************************************** ******************
> Does this mean I have to have an NT user of ODBCVDW as well as a logon
> on SQL Server - or just a Logon on Sql Server?
I think I just answered my own question. I was able to set the login up
using SQL Authentication.
Now when I try to set up a Stored Procedure to read a table I get an
error. I get the same error if I try it using Sql Query.
If I use the following:
select * from openquery(tomsVantage,'select * from GLACCT')
I get:
************************************************** *********
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Specified driver could not be loaded
due to system error 126 (MERANT 3.60 32-BIT Progress SQL92 v9.1D).]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
************************************************** ********************
I need to figure this out quickly as I am under the gun here.
Thanks,
Tom.

> Thanks,
> Tom.
>

Linked server problems connecting to Merant database

I have tried all kinds of things to set up a Linked Server to our Merant
database and can't seem to get it to work. I am on a Win2K server using
SQL2000.
I used the following to try to set it up:
*********************************************
sp_addlinkedserver @.server = 'tomsvantage',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'vantage'
*********************************************
This seemed to work OK. "vantage" is an ODBC dsn. It works OK with DTC.
I then try to set up the login as:
****************************************************************
exec sp_addlinkedsrvlogin 'tomsvantage',FALSE, 'odbcvdw','pass'
*******************************************************************
I get the following error:
*******************************************************************
Server: Msg 15067, Level 16, State 1, Procedure sp_addlinkedsrvlogin,
Line 56
'odbcvdw' is not a local user. Remote login denied.
********************************************************************
Does this mean I have to have an NT user of ODBCVDW as well as a logon
on SQL Server - or just a Logon on Sql Server?
Thanks,
Tom.Thomas Scheiderich wrote:
> I have tried all kinds of things to set up a Linked Server to our Merant
> database and can't seem to get it to work. I am on a Win2K server using
> SQL2000.
> I used the following to try to set it up:
> *********************************************
> sp_addlinkedserver @.server = 'tomsvantage',
> @.srvproduct = '',
> @.provider = 'MSDASQL',
> @.datasrc = 'vantage'
> *********************************************
> This seemed to work OK. "vantage" is an ODBC dsn. It works OK with DTC.
> I then try to set up the login as:
> ****************************************************************
> exec sp_addlinkedsrvlogin 'tomsvantage',FALSE, 'odbcvdw','pass'
> *******************************************************************
> I get the following error:
> *******************************************************************
> Server: Msg 15067, Level 16, State 1, Procedure sp_addlinkedsrvlogin,
> Line 56
> 'odbcvdw' is not a local user. Remote login denied.
> ********************************************************************
> Does this mean I have to have an NT user of ODBCVDW as well as a logon
> on SQL Server - or just a Logon on Sql Server?
I think I just answered my own question. I was able to set the login up
using SQL Authentication.
Now when I try to set up a Stored Procedure to read a table I get an
error. I get the same error if I try it using Sql Query.
If I use the following:
select * from openquery(tomsVantage,'select * from GLACCT')
I get:
***********************************************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Specified driver could not be loaded
due to system error 126 (MERANT 3.60 32-BIT Progress SQL92 v9.1D).]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
**********************************************************************
I need to figure this out quickly as I am under the gun here.
Thanks,
Tom.
> Thanks,
> Tom.
>

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 Performace dip

Hi -
We have designed an Integration Architecture with SQL Linked Servers and the
layout will be a single SQL Server(Publisher) connecting to multiple Linked
Servers (Subsribers).
We have a bottle-nect in the performance when we try to execute an update
statement, which takes 30 mins for 16 records. It is a simple update
statement, present inside a local cursor
<<
update NCCHRASQLAPP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
When I replace the @.status with a hard-coded value (say 'P'), then procedure
runs very fast.
Is this related to Collation Compatibility. Pls. let me know what could be
possible glitch.
Attaching the primary part of the Proc if you need it for review.APP30 is
the linked server.
<<<<<
CREATE procedure dbo.Process_Test
as
declare
@.invoice varchar(10) , @.reason varchar(500), @.status
char(2) , @.v_apptranscd varchar(50), @.v_mailboxid varchar(15
)
begin
SET XACT_ABORT ON
SET NOCOUNT ON
declare reject_invc_cursor CURSOR for
select Invc_Nmbr,Reason,upper(ar_stts),app_tran
s_cd,mailbox_Id from
dbo.INVOICE_INBOUND where upper(ar_stts) in('E','A','W','R') for read only
open reject_invc_cursor
FETCH NEXT FROM reject_invc_cursor INTO @.invoice, @.reason , @.status ,
@.v_apptranscd, @.v_mailboxid
IF @.@.FETCH_STATUS = 0
BEGIN
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.v_apptranscd = 'MCLNC079_AR'
update APP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
else if @.v_apptranscd = 'MCLIL089_AR'
update APP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
else if @.v_apptranscd = 'MCLCA089_AR'
update APP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
FETCH NEXT FROM reject_invc_cursor INTO @.invoice, @.reason ,@.status
, @.v_apptranscd, @.v_mailboxid
END
END
CLOSE reject_invc_cursor
DEALLOCATE reject_invc_cursor
commit
select 1
return 1
Thanks/Shriram.Hi Mark -
Thanks for a quick response. But I cannot chartout in a single query as
based on the ID it should update different Linked Servers.
As a work around, I moved the status as part of the IF condition and
hard-coded in the query, it works extremely fast...
Thanks/Shriram.
"Mark Williams" wrote:
> To see the best performance increase, lose the cursor and go with a set-ba
sed
> approach:
> UPDATE APP30_DATA_02.data_02.dbo.AROPNFIL_SQL
> SET filler_0001 = t2.status
> FROM
> APP30_DATA_02.data_02.dbo.AROPNFIL_SQL t1
> INNER JOIN
> (
> SELECT Invc_Nmbr, upper(ar_stts) AS "status" FROM dbo.INVOICE_INBOUND
> WHERE upper(ar_stts) IN ('E','A','W','R')
> AND app_trans_cd IN ('MCLNC079_AR','MCLIL089_AR','MCLCA089_A
R')
> ) t2
> ON t1.doc_no = t2.Invc_Nmbr
>
> --
>
> "shriram2977" wrote:
>

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 from Sql 2000 to Sql 2005

I'm having issue in connecting to sql 2005 from sql 2000 via linked server.

Please advice what I should do to overcome the problem.

Thanks.

-EC

Do you get an error message ?

.

http://www.sqlserver2005.de

|||

True that helps to resolve the issue.

Refer to http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx fyi.

|||

Error 7399: OLE DB provider 'SQL OLEDB' reported an error.

OLE DB error trace [OLE/DB Provider 'SQL OLEDB' IDBInitialize returned 0x80004005: ].

-

Problem still exist, please advice.

linked server error & tuning for OLE ODBC database

Hi,
I have SQL 2005 connecting a ODBC database via linked servers. The issues
are 2:
1. select * from linksever.db.owner.invoicedetail takes longer for the
results to come. The table has 4,700,050 records. Is there any suggestion
for performance tuning.
2. I get an error when creating the view for the above table. The error is:
Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time.
http://support.microsoft.com/Default.aspx?id=266337
The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
Any suggestion is appreciated.
Regards,
ChinsHi Chins
"Chinnasamy Arumugam" wrote:

> Hi,
> I have SQL 2005 connecting a ODBC database via linked servers. The issues
> are 2:
> 1. select * from linksever.db.owner.invoicedetail takes longer for the
> results to come. The table has 4,700,050 records. Is there any suggestion
> for performance tuning.
Have you tried using OPENQUERY or executing a procedure on the linked server?">
> 2. I get an error when creating the view for the above table. The error is
:
> Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
> supplied inconsistent metadata. An extra column was supplied during
> execution that was not found at compile time.
> http://support.microsoft.com/Default.aspx?id=266337
> The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
>
Do you explicitly name the columns being used? How about posting DDL (table
and view)?

> Any suggestion is appreciated.
> --
> Regards,
> Chins
>
John

linked server error & tuning for OLE ODBC database

Hi,
I have SQL 2005 connecting a ODBC database via linked servers. The issues
are 2:
1. select * from linksever.db.owner.invoicedetail takes longer for the
results to come. The table has 4,700,050 records. Is there any suggestion
for performance tuning.
2. I get an error when creating the view for the above table. The error is:
Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time.
http://support.microsoft.com/Default.aspx?id=266337
The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
Any suggestion is appreciated.
Regards,
Chins
Hi Chins
"Chinnasamy Arumugam" wrote:

> Hi,
> I have SQL 2005 connecting a ODBC database via linked servers. The issues
> are 2:
> 1. select * from linksever.db.owner.invoicedetail takes longer for the
> results to come. The table has 4,700,050 records. Is there any suggestion
> for performance tuning.
Have you tried using OPENQUERY or executing a procedure on the linked server?
> 2. I get an error when creating the view for the above table. The error is:
> Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
> supplied inconsistent metadata. An extra column was supplied during
> execution that was not found at compile time.
> http://support.microsoft.com/Default.aspx?id=266337
> The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
>
Do you explicitly name the columns being used? How about posting DDL (table
and view)?

> Any suggestion is appreciated.
> --
> Regards,
> Chins
>
John

linked server error & tuning for OLE ODBC database

Hi,
I have SQL 2005 connecting a ODBC database via linked servers. The issues
are 2:
1. select * from linksever.db.owner.invoicedetail takes longer for the
results to come. The table has 4,700,050 records. Is there any suggestion
for performance tuning.
2. I get an error when creating the view for the above table. The error is:
Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time.
http://support.microsoft.com/Default.aspx?id=266337
The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
Any suggestion is appreciated.
--
Regards,
ChinsHi Chins
"Chinnasamy Arumugam" wrote:
> Hi,
> I have SQL 2005 connecting a ODBC database via linked servers. The issues
> are 2:
> 1. select * from linksever.db.owner.invoicedetail takes longer for the
> results to come. The table has 4,700,050 records. Is there any suggestion
> for performance tuning.
Have you tried using OPENQUERY or executing a procedure on the linked server?
> 2. I get an error when creating the view for the above table. The error is:
> Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
> supplied inconsistent metadata. An extra column was supplied during
> execution that was not found at compile time.
> http://support.microsoft.com/Default.aspx?id=266337
> The KB says it is for SQL 7.0. Has anyone had this error on SQL 2005.
>
Do you explicitly name the columns being used? How about posting DDL (table
and view)?
> Any suggestion is appreciated.
> --
> Regards,
> Chins
>
John

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access denied
Hi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:

> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> it works fine. It not working on SQL Server 7.0. Is there a restriction of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied
|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Server
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access deniedHi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:
> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> it works fine. It not working on SQL Server 7.0. Is there a restriction of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Server
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:
> > I have an error message of connecting to a SQL Server 2005 from SQL Server
> > 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> > it works fine. It not working on SQL Server 7.0. Is there a restriction of
> > connecting to SQL Server 2005 from 7.0? Thanks!
> >
> > Error Message: Error 2: Access denied