Viewing ExecuteQuery String

13 Jan 2006 3:37 PM

jim_adams

Given a technique similar to the following, instead of actually
executing the Stored Procedure, I need the resulting SQL command string
written to a file and then manually executed in Query Analyzer.

Dim objSqlConn As New SqlClient.SqlConnection
Dim objSqlDA As New SqlClient.SqlDataAdapter
Dim objSqlCmd As New SqlClient.SqlCommand
objSqlConn = New SqlConnection(strSqlConn)
With objSqlCmd
     objSqlConn.Open()
     .CommandTimeout = CommandTimeout
     .CommandType = CommandType.StoredProcedure
     'Add some SPROC parameters
     .Parameters.Add(DbParameter)
     .CommandText = Command
     .Connection = objSqlConn
End With
objSqlDA.SelectCommand = objSqlCmd
if blnWriteToScriptFile then
     'How to do this????????
else
     objSqlDA.Fill(objDS)
end if

Any ideas are greatly appreciated?

Thanks,

Jim

13 Jan 2006 6:51 PM

William (Bill) Vaughn

The final product generated by ADO.NET is not exposed as a public property
unless you enable Trace. What you can do is use the Profiler to see what's
generated and sent to the server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

<jim_ad***@hotmail.com> wrote in message

Show quote

news:1137166671.193223.179290@z14g2000cwz.googlegroups.com...
> Given a technique similar to the following, instead of actually
> executing the Stored Procedure, I need the resulting SQL command string
> written to a file and then manually executed in Query Analyzer.
>
> Dim objSqlConn As New SqlClient.SqlConnection
> Dim objSqlDA As New SqlClient.SqlDataAdapter
> Dim objSqlCmd As New SqlClient.SqlCommand
> objSqlConn = New SqlConnection(strSqlConn)
> With objSqlCmd
>     objSqlConn.Open()
>     .CommandTimeout = CommandTimeout
>     .CommandType = CommandType.StoredProcedure
>     'Add some SPROC parameters
>     .Parameters.Add(DbParameter)
>     .CommandText = Command
>     .Connection = objSqlConn
> End With
> objSqlDA.SelectCommand = objSqlCmd
> if blnWriteToScriptFile then
>     'How to do this????????
> else
>     objSqlDA.Fill(objDS)
> end if
>
> Any ideas are greatly appreciated?
>
> Thanks,
>
> Jim
>

13 Jan 2006 7:40 PM

jim_adams

Hi Bill,

Thanks for the suggestion.

The problem is that to generate the Trace, the SQL statements have to
be run. Certain times, especially when dealing with shared SQL
clusters, a DBA needs to manually review and run scripts via Query
Analyzer. Having a binary app do this without DBA review is against
policy. So in this case, instead of running the SPROCS, my app needs
to produce a work file.

Thanks again for your help.

Jim