"Eric" wrote:

> Hello,
>
> I am using SQL Server 2005 Profiler to troubleshoot a stored procedure.
>
> When I execute the SP using a normal SqlClient connection (in .NET code), it
> displays all parameters in Profiler as you would expect:
>    EXEC usp_my_procedure 'myParameter'
>
> However, when I run the same SP using an ODBC connection that references a
> system DSN, Profiler only shows the stored procedure name, not the "EXEC" or
> any of the parameters:
>    usp_my_procedure
>
> Is there anything I can do to force Profiler to show the more verbose
> version of SP call for both SQL and ODBC connections?
>
> Thank you,
>
> Eric
>

> Found the problem.  It is actually the quirkiness OBDC driver itself.  It
> requires the CommandText of stored procedures to be formatted as:
>
> { call SP_NAME (?, ?, ?) }
>
> ...with one "?" for each parameter.  The ODBC driver then translates that
> into a series of SQL statements used to harvest the output parameters:
>
> declare @p1 varchar(50)
> set @p1 = 'p1 value'
> declare @p2 varchar(50)
> set @p2 = 'p2 value'
> declare @p3 varchar(50)
> set @p3 = 'p3 value'
> exec usp_MyProcedure @p1 output, @p2 output, @p3 output
> select @p1, @p2, @p3

"Erland Sommarskog" wrote:

> Eric (E***@discussions.microsoft.com) writes:
> > Found the problem.  It is actually the quirkiness OBDC driver itself.  It
> > requires the CommandText of stored procedures to be formatted as:
> >
> > { call SP_NAME (?, ?, ?) }
> >
> > ...with one "?" for each parameter.  The ODBC driver then translates that
> > into a series of SQL statements used to harvest the output parameters:
> >
> > declare @p1 varchar(50)
> > set @p1 = 'p1 value'
> > declare @p2 varchar(50)
> > set @p2 = 'p2 value'
> > declare @p3 varchar(50)
> > set @p3 = 'p3 value'
> > exec usp_MyProcedure @p1 output, @p2 output, @p3 output
> > select @p1, @p2, @p3
>
> Actually, that is not when the ODBC driver is doing. The ODBC calls
> the procedure through RPC (Remote Procedure Call) and does not generate
> any SQL at all.
>
> What you see in Profiler, is how SQL Trace represents the RPC.
> 
> --
> Erland Sommarskog, SQL Server MVP, esq***@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
>

"Eric" wrote:

> Hello,
>
> I am using SQL Server 2005 Profiler to troubleshoot a stored procedure.
>
> When I execute the SP using a normal SqlClient connection (in .NET code), it
> displays all parameters in Profiler as you would expect:
>    EXEC usp_my_procedure 'myParameter'
>
> However, when I run the same SP using an ODBC connection that references a
> system DSN, Profiler only shows the stored procedure name, not the "EXEC" or
> any of the parameters:
>    usp_my_procedure
>
> Is there anything I can do to force Profiler to show the more verbose
> version of SP call for both SQL and ODBC connections?
>
> Thank you,
>
> Eric
>

> Found the problem.  It is actually the quirkiness OBDC driver itself.  It
> requires the CommandText of stored procedures to be formatted as:
>
> { call SP_NAME (?, ?, ?) }
>
> ...with one "?" for each parameter.  The ODBC driver then translates that
> into a series of SQL statements used to harvest the output parameters:
>
> declare @p1 varchar(50)
> set @p1 = 'p1 value'
> declare @p2 varchar(50)
> set @p2 = 'p2 value'
> declare @p3 varchar(50)
> set @p3 = 'p3 value'
> exec usp_MyProcedure @p1 output, @p2 output, @p3 output
> select @p1, @p2, @p3

"Erland Sommarskog" wrote:

> Eric (E***@discussions.microsoft.com) writes:
> > Found the problem.  It is actually the quirkiness OBDC driver itself.  It
> > requires the CommandText of stored procedures to be formatted as:
> >
> > { call SP_NAME (?, ?, ?) }
> >
> > ...with one "?" for each parameter.  The ODBC driver then translates that
> > into a series of SQL statements used to harvest the output parameters:
> >
> > declare @p1 varchar(50)
> > set @p1 = 'p1 value'
> > declare @p2 varchar(50)
> > set @p2 = 'p2 value'
> > declare @p3 varchar(50)
> > set @p3 = 'p3 value'
> > exec usp_MyProcedure @p1 output, @p2 output, @p3 output
> > select @p1, @p2, @p3
>
> Actually, that is not when the ODBC driver is doing. The ODBC calls
> the procedure through RPC (Remote Procedure Call) and does not generate
> any SQL at all.
>
> What you see in Profiler, is how SQL Trace represents the RPC.
> 
> --
> Erland Sommarskog, SQL Server MVP, esq***@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
>