We have a complex stored procedure that is execute via 24x7. We have been experienceing problems with certain xp_cmdshell statements that cause the connection to be reset thus terminating the process when the profile is set as MS SQL Server 7. However, using the the profile as an ODBC type allows the connection to persist and execution to complete normally. I would like know if anyone can shed any light on why this is the case.

Please check that you use SA user for both connections.

Also please enable database tracing in 24x7 options and check the trace file for low level commands messages and errors generated/received by 24x7. This can help you to find out why the connection is terminated prematurely and who is responsible for that (server or 24x7).

: We have a complex stored procedure that is execute via 24x7. We have been
: experienceing problems with certain xp_cmdshell statements that cause the
: connection to be reset thus terminating the process when the profile is
: set as MS SQL Server 7. However, using the the profile as an ODBC type
: allows the connection to persist and execution to complete normally. I
: would like know if anyone can shed any light on why this is the case.

Security is correct. Connection terminates when using "MS SQL Server 7" type with exception 3902 (Commit tran with no corresponding begin tran) and an exception 3617 (undefined). Running using an ODBC profile with the sam e credentials works and that is our solution. My question is why? (just your thoughts or theories) and moreover is the MS SQL Server 7 type a dblive connection?

"MS SQL Server 7" type uses DB-Library (DB-Lib). If in your stor.proc. you don create temp tables, just uncheck AutoCommit option in the profile. I think this will help to avoid exception 3902. If in other jobs you do need AutoCommit option, you can create a separate profile just for this job

: Security is correct. Connection terminates when using "MS SQL Server
: 7" type with exception 3902 (Commit tran with no corresponding begin
: tran) and an exception 3617 (undefined). Running using an ODBC profile
: with the sam e credentials works and that is our solution. My question is
: why? (just your thoughts or theories) and moreover is the MS SQL Server 7
: type a dblive connection?