Posted by Erland Sommarskog on 04/11/07 21:34

(wjreichard@comcast.net) writes:
> OK ... I think I got something that might work? I will create a unique
> SQL login for the Worldship application and then using the above
> method posted in my 1st post execute a stored procedurer and then
> access the ODBC SQL with code prototyped in the following SP:
>
> CREATE PROCEDURE dbo.sp_ups_pull AS

Don't use sp_ as the first letters in your object names. This prefix
is reserved from system objects.

> Which return the ODBC SQL ... which I will parse the orderid from the
> WHERE cluase.
>
> So does anyone see major issues using this method? How do Input
> Buffers relate to ODBC connections ... I guess I will need to ensure
> that there is only ever one row returned from sp_who for my unique DB
> users, Any one see other problems .... or a better solution?

A variation which is possibly even uglier, but nevertheless somewhat
more robust. Write a table-valued function. From this function call
xp_cmdshell, to start a new session in OSQL that runs the DBCC INPUTBUFFER
command. The point here is that you can read @@spid in the function
and this to the command string to OSQL. So at least that part is nicer.
But on the other hand you must arrange for worldshipuser to have privleges
to run xp_cmdshell, which is not to take lightly.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]