ODBC Query ... getting Where Clause

[wjreichard@comcast.net]

OK ... I am using UPS Worldship that issues an ODBC query to my MS2K
server ... Worldship can query either a table or a view and retreive
shipping info for a supplied orderid.

I need to create a DB table that will track the orderids requested
from Worldship so that I can stop doubleships. That is to set up a
function to allow the info to be sent only once to worldship.

I need to execute a stored procedure to write to a table and enforce
biz logic.

So .. I've created a view that Worldship can execute an ODBC query
against (v_upsPull) ... in which I guess the query issued will be
like: SELECT * FROM v_upsPull WHERE orderid = 123456

The view is:

CREATE VIEW dbo.v_upsPull
AS
SELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]',
'exec sp_ups_pull')

When the ODBC query calls the view the sp_ups_pull store procedurer is
executed.

However ... I do not have access to the original Where clause in the
ODBC query in the stored procedurer.

Is there a way I can get access to the ODBC Where clause and pass it
into the stored procedurer?

If not is there some other way I can create a DB table and run a
select against it ... based on the Worldship query?

[Marcin A. Guzowski]

Re: ODBC Query ... getting Where Clause

wjreichard@comc ast.net wrote:

OK ... I am using UPS Worldship that issues an ODBC query to my MS2K
server ... Worldship can query either a table or a view and retreive
shipping info for a supplied orderid.
(..)
If not is there some other way I can create a DB table and run a
select against it ... based on the Worldship query?

IMHO you won't get successful this way...

Perhaps SQL Server trace would be a solution?
You could capture all select queries executed against shipping
table/view and insert collected data into a table and then enforce
particular logic. The question is: how fast do you need the information
that certain orderid was retrieved? The only problem with trace-based
solution is that it can be potentially not fast enough.. (you can't read
the most current trace file if the trace is still running).

You may also try to implement some kind of 'select trigger':

http://solidqualitylearning.com/blogs/dejan/archive/2004/11/25/214.aspx

--
Best regards,
Marcin Guzowski

http://guzowski.info

[wjreichard@comcast.net]

Re: ODBC Query ... getting Where Clause

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
DECLARE @spid int
DECLARE @dbcc_cmd varchar(512)

CREATE TABLE #who (
spid int,
ecid int,
status varchar(255),
loginname varchar(255),
hostname varchar(255),
blk int,
dbname varchar(255),
cmd varchar(2048)
)
INSERT INTO #who EXEC ('sp_who worldshipuser')

SET @spid = (SELECT spid FROM #who)
SET @dbcc_cmd = 'DBCC INPUTBUFFER(' +
rtrim(ltrim(con vert(char,@spid ))) + ')'

CREATE TABLE #buffer (
EventType varchar(512),
Parameters int,
EventInfo varchar(2048)
)

INSERT INTO #buffer EXEC (@dbcc_cmd)

SELECT EventInfo FROM #buffer

[Additional biz logic etc.]
GO

---------------------------------------------------------------------------------------------------------------------------------------------
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?

[Erland Sommarskog]

Re: ODBC Query ... getting Where Clause

(wjreichard@com cast.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@sommarsk og.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