xp_cmdshell timeout

Bill Gauvey

unread,

Jul 25, 2002, 10:28:41 AM7/25/02

to

I have a trigger that uses xp_cmdshell to execute an application that pushes
the updated record to different systems. It works fine from the command
prompt, however, it timesout with xp_cmdshell. I have given the user execute
permissions to xp_cmdshell as well.
Here is the text of the message.

Source=Microsoft OLE DB Provider for ODBC Drivers. Msg= Error: -2147217871.
Msg: IDispatch error #3121. Description: [Microsoft][ODBC SQL Server
Driver]Timeout expired

Thanks

--billg

Andrés Taylor

unread,

Jul 25, 2002, 10:46:54 AM7/25/02

to

Bill,

This is not SQL Server timing out, but your host application. But I
wouldn't, ever, advise anyone to use a trigger that in turn uses
xp_cmdshell. It's WAY too slow to be involved in a trigger. Remember that
until the trigger is finished, all locks for the initial statement are held
fast. This affects concurrency and performance.

Anyway, if you really want to use this, make sure that you set the timeout
to a large enough value in your host application.

--
Andrés Taylor

visit me @
http://www.sql.nu/

"Bill Gauvey" <bga...@alltel.net> wrote in message
news:uaFZ1d#MCHA.1996@tkmsftngp12...

Sunil

unread,

Jul 25, 2002, 11:10:38 AM7/25/02

to

do you have any command line parameters for this application which are used
to connect to the different system? if so can you post the command line
string.

"Bill Gauvey" <bga...@alltel.net> wrote in message
news:uaFZ1d#MCHA.1996@tkmsftngp12...

Dan Guzman

unread,

Jul 25, 2002, 11:16:43 AM7/25/02

to

The likely cause is that the external xp_cmdshell process is being
blocked by the statement that fired the trigger. You can verify this
with sp_who.

A trigger always executes in the context of a transaction and the
external process runs on a different connection. This causes an
undetected deadlock if the external process tries to access modified
data.

A workaround, which I'm not recommending, is to specify READ UNCOMMITTED
or NOLOCK hint. A better option is to have the trigger insert the
updated data into a separate table. You can schedule a separate process
via a SQL Agent job to process data from that table.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Bill Gauvey" <bga...@alltel.net> wrote in message
news:uaFZ1d#MCHA.1996@tkmsftngp12...

Bill Gauvey

unread,

Jul 25, 2002, 11:19:33 AM7/25/02

to

The application usually finishes in 2-3 seconds from the command prompt, but
it takes the 30+ seconds before a timeout is incurred. I really dont want to
put a service on the server.

"Andrés Taylor" <and...@rotselleri.com> wrote in message
news:O3Kmpm#MCHA.1936@tkmsftngp12...

Bill Gauvey

unread,

Jul 25, 2002, 11:21:43 AM7/25/02

to

yes there are parameters on exe.

here is the trigger code.

CREATE TRIGGER update_shipping_info ON dbo.ShippingInfo
FOR UPDATE
AS
DECLARE @@order CHAR(12),
@@suffix CHAR(1),
@@cmd VARCHAR(255)

SELECT @@order= acxiom_order_number, @@suffix = suffix_code FROM inserted

IF @@suffix = NULL SELECT @@suffix = '_'
IF @@suffix = ' ' SELECT @@suffix = '_'

SELECT @@cmd = 'D:\MTSShare\MKS\bin\PostShipInfo.exe ' + RTrim(@@order) + '
' + RTrim(@@suffix)
EXECUTE master..xp_cmdshell @@cmd, NO_OUTPUT

"Sunil" <sunil_...@yahoo.com> wrote in message
news:eGqNX0#MCHA.1620@tkmsftngp10...

Sunil

unread,

Jul 25, 2002, 11:36:41 AM7/25/02

to

change following lines

IF @@suffix = NULL SELECT @@suffix = '_'
IF @@suffix = ' ' SELECT @@suffix = '_'

IF @@suffix is NULL SELECT @@suffix = '_'
IF @@suffix is ' ' SELECT @@suffix = '_'

"Bill Gauvey" <bga...@alltel.net> wrote in message

news:uagmd7#MCHA.2492@tkmsftngp12...

Sunil

unread,

Jul 25, 2002, 11:39:55 AM7/25/02

to

sorry IF @@suffix = ' ' SELECT @@suffix = '_'
statement is fine

"Bill Gauvey" <bga...@alltel.net> wrote in message

news:uagmd7#MCHA.2492@tkmsftngp12...

Bill

unread,

Jul 25, 2002, 12:13:03 PM7/25/02

to

hmmm, that still did not work

"Sunil" <sunil_...@yahoo.com> wrote in message

news:#JOkuE$MCHA.2492@tkmsftngp12...

Bill

unread,

Jul 25, 2002, 12:13:57 PM7/25/02

to

it is my NT account running the insert/update; and I am an admin on the
server.

Thanks

"Dan Guzman" <dang...@nospam-earthlink.net> wrote in message
news:ON4wp4#MCHA.2096@tkmsftngp12...

Mikhail Berlyant

unread,

Jul 25, 2002, 12:20:49 PM7/25/02

to

You need to debug somehow what is going on.
Try this:
--****************************************************
CREATE TABLE TEMP _FOR_DEBUG(line varchar(1000))
GO

CREATE TRIGGER update_shipping_info ON dbo.ShippingInfo
FOR UPDATE
AS
DECLARE @@order CHAR(12),
@@suffix CHAR(1),
@@cmd VARCHAR(255)

SELECT @@order= acxiom_order_number, @@suffix = suffix_code FROM inserted

IF @@suffix = NULL SELECT @@suffix = '_'
IF @@suffix = ' ' SELECT @@suffix = '_'

SELECT @@cmd = 'D:\MTSShare\MKS\bin\PostShipInfo.exe ' + RTrim(@@order) + '
' + RTrim(@@suffix)

INSERT TEMP_FOR_DEBUG VALUES (@@cmd)
--EXECUTE master..xp_cmdshell @@cmd, NO_OUTPUT
GO
--****************************************************

The idea is - instead of executing something - you will have what are you
trying to execute in debug table.
HTH.

Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience http://launch.yahoo.com
Brainbench MVP for Visual Basic www.brainbench.com

"Bill" <bga...@acxiom.com> wrote in message
news:O7roJY$MCHA.2320@tkmsftngp11...

Dan Guzman

unread,

Jul 25, 2002, 8:11:45 PM7/25/02

to

This is not a permissions issue. The likely problem is that the
executed process it attempting to read uncommitted data.

Have you tried executing sp_who or sp_who2 to see if this is the case?
If you do observe blocking, please re-read my response.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Bill" <bga...@acxiom.com> wrote in message
news:#m8JqY$MCHA.2368@tkmsftngp10...

Roman Tumaykin

unread,

Sep 13, 2002, 11:31:27 AM9/13/02

to

I believe that this problem happens because the SQL Server startup account
does not have enough rights on the network to execute something. Remember
that when you execute the command in the command line it uses the rights of
the user who runs it. But when you do the xp_cmdshell then it runs under the
account of the SQL Server which could be a localsystem for example. Also the
application may also depend on the mapped network drives. If you're using
the SQL Server account other than the LocalSystem then try to login as that
user and execute the command from the command line (with only the local
drives attached). Also make sure that the application you're trying to run
does not require any user input such as dialogs and such.

Regards

Roman Tumaykin

"Bill Gauvey" <bga...@alltel.net> wrote in message

news:e#lsQ6#MCHA.2492@tkmsftngp10...