Capturing queries sent to SQL Server

I use a software package that queries a Microsoft SQL Server 2000 database
from a client side Windows application. The client application has a built
in report generator that allows users to easily produce complex reports.
The client application was written in Delphi, and uses Borland's BDE to
connect to the SQL Server.
I would like to trap the SQL code that is being produced and sent to the
SQL Server from the report generator (so that I can get a get a better
understanding of how to directly query the database outside of the report
generator).
Any suggestions on how to do this? Are there any third party products that
can provide me with this capability?
Thanks,
DeanOne of my favourite tools will do this "Profiler". It is in your SQL Server
program group.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"dean" <deanbillings@.yahoo.com> wrote in message
news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> I use a software package that queries a Microsoft SQL Server 2000 database
> from a client side Windows application. The client application has a
built
> in report generator that allows users to easily produce complex reports.
> The client application was written in Delphi, and uses Borland's BDE to
> connect to the SQL Server.
> I would like to trap the SQL code that is being produced and sent to the
> SQL Server from the report generator (so that I can get a get a better
> understanding of how to directly query the database outside of the report
> generator).
> Any suggestions on how to do this? Are there any third party products
that
> can provide me with this capability?
> Thanks,
> Dean|||Dean,
You don't need a third party product. Take a look a SQL Profiler that comes
with SQL Server. The following article will help you get the most out of
Profiler http://www.sql-server-performance.com/sql_server_profiler_tips.asp.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"dean" <deanbillings@.yahoo.com> wrote in message
news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> I use a software package that queries a Microsoft SQL Server 2000 database
> from a client side Windows application. The client application has a
built
> in report generator that allows users to easily produce complex reports.
> The client application was written in Delphi, and uses Borland's BDE to
> connect to the SQL Server.
> I would like to trap the SQL code that is being produced and sent to the
> SQL Server from the report generator (so that I can get a get a better
> understanding of how to directly query the database outside of the report
> generator).
> Any suggestions on how to do this? Are there any third party products
that
> can provide me with this capability?
> Thanks,
> Dean|||And if you have already looked there and could not find anything but
parameter passing. Get the SPID and find the first calls that define the
procedure.
If it is totally obscure, Net Monitor will show you the text in
any packet sent to your machine.
I think that you should use SQL Profiler so that you have this skill
in your tool bag anyway.
"Largo SQL Tools" <nospam@.yahoo.com> wrote in message
news:unNOnjGSDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Dean,
> You don't need a third party product. Take a look a SQL Profiler that
comes
> with SQL Server. The following article will help you get the most out of
> Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp.
> J.R.
> Largo SQL Tools
> The Finest Collection of SQL Tools Available
> http://www.largosqltools.com
>
> "dean" <deanbillings@.yahoo.com> wrote in message
> news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> > I use a software package that queries a Microsoft SQL Server 2000
database
> > from a client side Windows application. The client application has a
> built
> > in report generator that allows users to easily produce complex reports.
> > The client application was written in Delphi, and uses Borland's BDE to
> > connect to the SQL Server.
> >
> > I would like to trap the SQL code that is being produced and sent to the
> > SQL Server from the report generator (so that I can get a get a better
> > understanding of how to directly query the database outside of the
report
> > generator).
> >
> > Any suggestions on how to do this? Are there any third party products
> that
> > can provide me with this capability?
> >
> > Thanks,
> > Dean
>|||Profiler is definitely your best option. One of the best tools available
and well worth the perceived 'non productive' time spent using/learning it.
"dean" <deanbillings@.yahoo.com> wrote in message
news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> I use a software package that queries a Microsoft SQL Server 2000 database
> from a client side Windows application. The client application has a
built
> in report generator that allows users to easily produce complex reports.
> The client application was written in Delphi, and uses Borland's BDE to
> connect to the SQL Server.
> I would like to trap the SQL code that is being produced and sent to the
> SQL Server from the report generator (so that I can get a get a better
> understanding of how to directly query the database outside of the report
> generator).
> Any suggestions on how to do this? Are there any third party products
that
> can provide me with this capability?
> Thanks,
> Dean

Capture Returned Value From Exec(@Build) into another variable

I am building a SQL statement that returns a number.
when I execute the Built SQL statment EXEC(@.Build). What I need to do
now is take that number that comes back and store it in another
variable so I can do some conditional logic. Any ideas? See SQL below.

Something like @.Count=Exec(@.Build) which I know doesnt work.

Thanks,
Phil

DECLARE @.PullDate varchar(12)

SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''

PRINT(@.PullDate)

DECLARE @.COUNTER BIGINT

DECLARE @.SELECT VARCHAR(500)
DECLARE @.SELECT2 VARCHAR(1000)
DECLARE @.BUILD VARCHAR(5000)

SET @.SELECT='

SELECT COUNTER FROM
OPENQUERY(PROD,'

SET @.SELECT2='''
SELECT
COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
FROM
COLLECTOR_RESULTS,
WHERE
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCOUNT.END_DATE ) =
to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
)
GROUP BY
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'

SET @.BUILD=@.SELECT+@.SELECT2
PRINT(@.BUILD)
EXEC(@.BUILD)

--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
INTO @.COUNTER so I can do a conditional statement.)
if @.COUNTER>=1
begin
print('yes')
end<philipdm@.msn.com> wrote in message
news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
>I am building a SQL statement that returns a number.
> when I execute the Built SQL statment EXEC(@.Build). What I need to do
> now is take that number that comes back and store it in another
> variable so I can do some conditional logic. Any ideas? See SQL below.
> Something like @.Count=Exec(@.Build) which I know doesnt work.
> Thanks,
> Phil
>
>
> DECLARE @.PullDate varchar(12)
> SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> PRINT(@.PullDate)
> DECLARE @.COUNTER BIGINT
> DECLARE @.SELECT VARCHAR(500)
> DECLARE @.SELECT2 VARCHAR(1000)
> DECLARE @.BUILD VARCHAR(5000)
>
> SET @.SELECT='
> SELECT COUNTER FROM
> OPENQUERY(PROD,'
> SET @.SELECT2='''
> SELECT
> COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESULTS,
> WHERE
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
>
> SET @.BUILD=@.SELECT+@.SELECT2
> PRINT(@.BUILD)
> EXEC(@.BUILD)
> --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
> INTO @.COUNTER so I can do a conditional statement.)
> if @.COUNTER>=1
> begin
> print('yes')
> end

Instead of EXEC(), you can use sp_executesql with an output parameter:

declare @.sql ntext, @.counter int
set @.sql = 'select @.counter = counter from openquery(...)'
exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
select @.counter

See here for an example:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon|||I am able to get this to return a value but I cant get this to work.
Any ideas?

IF @.Counter>1
Print('Yes')

Simon Hayes wrote:
> <philipdm@.msn.com> wrote in message
> news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
> >I am building a SQL statement that returns a number.
> > when I execute the Built SQL statment EXEC(@.Build). What I need to
do
> > now is take that number that comes back and store it in another
> > variable so I can do some conditional logic. Any ideas? See SQL
below.
> > Something like @.Count=Exec(@.Build) which I know doesnt work.
> > Thanks,
> > Phil
> > DECLARE @.PullDate varchar(12)
> > SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> > +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> > +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> > PRINT(@.PullDate)
> > DECLARE @.COUNTER BIGINT
> > DECLARE @.SELECT VARCHAR(500)
> > DECLARE @.SELECT2 VARCHAR(1000)
> > DECLARE @.BUILD VARCHAR(5000)
> > SET @.SELECT='
> > SELECT COUNTER FROM
> > OPENQUERY(PROD,'
> > SET @.SELECT2='''
> > SELECT
> > COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> > FROM
> > COLLECTOR_RESULTS,
> > WHERE
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> > to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > )
> > GROUP BY
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
> > SET @.BUILD=@.SELECT+@.SELECT2
> > PRINT(@.BUILD)
> > EXEC(@.BUILD)
> > --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD
STORED
> > INTO @.COUNTER so I can do a conditional statement.)
> > if @.COUNTER>=1
> > begin
> > print('yes')
> > end
> Instead of EXEC(), you can use sp_executesql with an output
parameter:
> declare @.sql ntext, @.counter int
> set @.sql = 'select @.counter = counter from openquery(...)'
> exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
> select @.counter
> See here for an example:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
> Simon|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. All I need to do is set a
@.variable=@.Output Variable.
Thanks for your help Simon!
Phil|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!