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 error

Hi friends,

I have written an openquery that selects data scattered over two servers in multiple tables. It uses a cursor to read the rows and then performs validations to get other data.

while running it runs for around 4000 lines of table and then fails giving following error--

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

any help will be appreciated

regards

anurag

My assumption is that you are constructing dynamic strings. So, that would mean that you are hitting a piece of data that when concatenated into your string is throwing an error. Instead of executing the dynamic queries, have you just tried dumping them out as strings so that you can find the one that is not generating properly?|||Another possible cause, the table which you are accessing doesn't have valid permission.

Linked Server - On Connection Error Resume

I have a stored procedure which connects to multiple linked servers. If one connection fails, the stored procedure ends with error.

Is it possible to have the stored proc continue instead of quit?

I have tried the following and none work:
- connect in a different sp
- use sp_executesql

Thanks

Oh yeah...I am using SQL SERVER 2000 SP4 and I know SQL SERVER 2005 has a sp to test a linked server, but I cannot upgrade.

I once had the problem to check for the availbility of linked server. The solution (in our case) was to ping the servers on a regular basis, by using a SQL Server Agent job, which did something like

--INSERT INTO PingTable
--SomeSelectonTheServer, mainly something like SELECT 1, or SELECT * FROM dual (in this case for Oracle)
--UPDATE PingTable, update the status of the ping record to successfull.

Therefore we could query the table for the successfull connectivity, if the column Successfull was not filed or updated properly we threw a manual error.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de