|
|
Posted by Neil on 05/17/06 22:54
OK, I found out that the error I got originally (per other message) was due
to the fact that Delete doesn't return rows, as OpenQuery is looking for.
So, per http://support.microsoft.com/default.aspx?scid=kb;en-us;Q270119, I
changed it to:
Delete OPENQUERY(abcweb, 'Select ImageID From images Where
ImageID=99987')
and it worked! Would still be good to find out why the original method
wouldn't work for deletes or updates, but did work for inserts. But at least
this works.
Thanks!
Neil
"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
>
> -Sue
>
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@nospam.net>
> wrote:
>
>>I ran a profiler trace while inserting and then deleting a row from the
>>table on the linked server. I added all events to the trace. However, for
>>both the insert and delete, it only showed a single SQL:BatchCompleted
>>event -- except that after the line for the delete command, there was a
>>second, blank, line in the trace. The two lines for the delete didn't show
>>in the profiler until the batch was canceled. Below are the lines from
>>Profiler. Any assistance is appreciated.
>>
>>Thanks,
>>
>>Neil
>>
>>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>>Writes Duration Connection ID SPID Start Time
>>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
>> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>>02:23:56.827
>>
>>
>>
>>"Neil" <nospam@nospam.net> wrote in message
>>news:_Jqag.5464$u4.5402@newsread1.news.pas.earthlink.net...
>>>I am getting time-out errors when I try to perform a simple delete on a
>>>linked server. The command is:
>>>
>>> Delete From MyTable Where PKID=12345
>>>
>>> I have tried executing this command directly from Query Analyzer, but it
>>> just times out. However, when I run it from QA against the server itself
>>> (rather than from my local server against a linked server), it executes
>>> immediately. Similarly, if I run the same SQL command through an ODBC
>>> linked table in an Access 2000 MDB file (tweaking the syntax slightly),
>>> it
>>> also executes immediately. Only if I run it from SQL 7 as a stored
>>> procedure or a QA command against a linked server does it time-out.
>>>
>>> I have no problems inserting rows using any of the three methods noted
>>> above. It is only with deleting rows through the linked server that it
>>> times out.
>>>
>>> Thanks for any assistance.
>>>
>>> Neil
>>>
>>
>
Navigation:
[Reply to this message]
|