Debugging temporary SPs?

Sorry for cross-post, not really sure which group is best...
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
>> Hi
>> Can you post the script
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
>> well I am getting confused here, do you generate the procedure code
>> dynamically using a string variable, because the error message
>> Incorrect syntax near the keyword 'SET'
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>|||>> Incorrect syntax near the keyword 'SET'
By removing the SP and putting the statement directly in code, the SQL
that ODBC/SQLServer generates is definitely wrong.
Here is what I send to prepare then try to execute:
Select Distinct (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like ? and LOCATION_LEVEL_2 LIKE ? and
LOCATION_LEVEL_3 = '' and LOCATION_LEVEL_2 <> ''
and here is what ODBC/SQLServer generates (my ***emphasis***):
declare @.P1 int
set @.P1=NULL
declare @.P2 char(6)
set @.P2=' '
exec sp_prepexec @.P1 output, N'@.P1 char(6),@.P2 char(6) OUTPUT', N'Select
Distinct (LOCATION_LEVEL_2 + '' - '' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like @.P1 and LOCATION_LEVEL_2 LIKE @.P2
***OUTPUT*** and LOCATION_LEVEL_3 = '''' and LOCATION_LEVEL_2 <> ''''
Why it adds the 'OUTPUT' part to the middle of the 'where' clause seems
hard to fathom. I am looking into how they are bound...but it any case,
the ODBC/SQLServer interface is producing the wrong results.
Thanks for the help & suggestions...if you have any more thoughts, they
are always welcome.
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>