problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

problem with identifier lengths when connecting Analysis Services to Oracle

Hi All,

I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:

Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0

Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.

I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.

Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?

Any help would be appreciated.

Kind regards,

Miles

You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:

<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>

<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>

Restart the server/tools

Note that this is not a supported technique but should unblock you...

HTH,

Akshai

|||

Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)

For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.

Kind regards,

Miles

|||Dear All,

I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.

The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||

Hi 4lb3rt,

Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.

Cheers,

Miles

|||Dear Miles,

I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?

Thanks in advance.sql

problem with identifier lengths when connecting Analysis Services to Oracle

Hi All,

I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:

Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0

Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.

I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.

Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?

Any help would be appreciated.

Kind regards,

Miles

You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:

<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>

<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>

Restart the server/tools

Note that this is not a supported technique but should unblock you...

HTH,

Akshai

|||

Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)

For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.

Kind regards,

Miles

|||Dear All,

I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.

The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||

Hi 4lb3rt,

Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.

Cheers,

Miles

|||Dear Miles,

I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?

Thanks in advance.|||I did not have this problem (identifier too long) until I tried to design aggregations with multiple partitions. None of my Oracle data exceeds the 30 character limit, so I'm kinda lost. It is new however, since I added some additional columns to my sql for the dsv and the partitions, but they do not exceed 30! I can process, I just can't design aggregations and I sure would like to. If anyone has any more ideas, let me know. AS 2005.

Problem with Delete On Linked Server

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.

NeilCorrection: The syntax used against the linked server is the four-art
syntax:

Delete From svr.db.dbo.MyTable Where PKID=12345

Also, the same problem occurs with updates as with deletes (but not
inserts).

Thanks,

Neil

"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.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|||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.earthli nk.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|||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.earthli nk.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
>|||Couldn't run a trace against the linked server, as I don't have permissions
to execute the trace sp. Sent a note to the web admin to give me
permissions.

In the meantime, I used OpenQuery as you suggested. This time at least I got
an error message (yea!), though somewhat cryptic:

"Could not process object 'Delete From images Where ImageID=99986'. The
OLE DB provider 'SQLOLEDB' indicates that the object has no columns."

Any ideas about what that means?

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.earthli nk.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
>>>
>|||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/defaul...b;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.earthli nk.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
>>>
>

Problem with Delete On Linked Server

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.
NeilCorrection: The syntax used against the linked server is the four-art
syntax:
Delete From svr.db.dbo.MyTable Where PKID=12345
Also, the same problem occurs with updates as with deletes (but not
inserts).
Thanks,
Neil
"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
>|||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
>|||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...
>|||Couldn't run a trace against the linked server, as I don't have permissions
to execute the trace sp. Sent a note to the web admin to give me
permissions.
In the meantime, I used OpenQuery as you suggested. This time at least I got
an error message (yea!), though somewhat cryptic:
"Could not process object 'Delete From images Where ImageID=99986'. The
OLE DB provider 'SQLOLEDB' indicates that the object has no columns."
Any ideas about what that means?
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:
>
>|||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/defaul...;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:
>
>