ODBC Call To sp_prepexec Failing

Hi all,
I'm working on creating a ms-sql database that will work with an existing
third-party application. As such, I have full control over the DB schema,
but no control over the third-party source.
The application is using ODBC to talk to my DB.
I have everything working fine, up to where an INSERT is performed by the
ODBC driver. The INSERT statement is translated to the following by the
driver:
declare @.p1 int
set @.p1=9
exec sp_prepexec @.p1 output,N'@.P1 varchar(80),@.P2 varchar(80),@.P3
varchar(80),@.P4 int,@.P5 varchar(80)',N'INSERT INTO tracks (titlesort, url,
title, tag, ct)
VALUES (@.P1, @.P2, @.P3, @.P4, @.P5)
','MP3','file:///D:/MP3','MP3',1,'dir'
select @.p1
This fails with the following error:
Msg 8179, Level 16, State 2, Procedure sp_prepexec, Line 1
Could not find prepared statement with handle 9.
(1 row(s) affected)
Now, if I manually try the same query but set @.p1 to NULL instead of 9, THEN
it works. Is there anything I can tweak in the SQL Server ODBC driver to fi
x
this behaviour? Or does anyone know what I can change in the database to
make this work correctly?
Thanks,
GeoffHi
I am not sure if sp_prepexec would be called from ODBC directly, are you
using ADO?
In which case unless you are repeating the statement multiple times you do
not need to prepare it. See
http://msdn.microsoft.com/library/d...
etchapt12.asp
The value 9 is a previously prepared handle, it seems this may have gone out
of scope. You may want to use profiler to see what is happening on a longer
time frame.
John
"GeoffB" <GeoffB@.discussions.microsoft.com> wrote in message
news:DC2D0A76-BB47-49CA-A65E-1F6685803B27@.microsoft.com...
> Hi all,
> I'm working on creating a ms-sql database that will work with an existing
> third-party application. As such, I have full control over the DB schema,
> but no control over the third-party source.
> The application is using ODBC to talk to my DB.
> I have everything working fine, up to where an INSERT is performed by the
> ODBC driver. The INSERT statement is translated to the following by the
> driver:
> declare @.p1 int
> set @.p1=9
> exec sp_prepexec @.p1 output,N'@.P1 varchar(80),@.P2 varchar(80),@.P3
> varchar(80),@.P4 int,@.P5 varchar(80)',N'INSERT INTO tracks (titlesort, url,
> title, tag, ct)
> VALUES (@.P1, @.P2, @.P3, @.P4, @.P5)
> ','MP3','file:///D:/MP3','MP3',1,'dir'
> select @.p1
> This fails with the following error:
> Msg 8179, Level 16, State 2, Procedure sp_prepexec, Line 1
> Could not find prepared statement with handle 9.
> (1 row(s) affected)
> Now, if I manually try the same query but set @.p1 to NULL instead of 9,
> THEN
> it works. Is there anything I can tweak in the SQL Server ODBC driver to
> fix
> this behaviour? Or does anyone know what I can change in the database to
> make this work correctly?
> Thanks,
> Geoff|||Hi John,
Thanks a lot for your reply. I'm still stuck though - I

> I am not sure if sp_prepexec would be called from ODBC directly, are you
> using ADO?
I'm actually just using a System DSN ODBC entry. The application that is
doing the calling is written in Perl, so I assume this means that ADO is
definitely not being used.

> The value 9 is a previously prepared handle, it seems this may have gone o
ut
> of scope. You may want to use profiler to see what is happening on a longe
r
> time frame.
The statement being executed by the application is (I believe):
INSERT INTO tracks (titlesort, url, title, tag, ct)
VALUES ('MP3', 'file:///D:/MP3', 'MP3', 1, 'dir')
Of course, if I execute this in Query Analyzer, it works fine. Profiler is
where I spotted that this was being translated. When I look up sp_prepexec,
everything I can find indicates that this is what the ODBC driver translates
any INSERT statement into.
The statements in Profiler leading up to the error are:
SQL:BatchStarting declare @.p1 int [...]
SQL:StmtStarting set @.p1=9
SQL:StmtCompleted set @.p1=9
SQL:StmtStarting exec sp_prepexec @.p1 output,[...]
Exception Error: 8179, Severity: 16, State: 2
User Error Message Could not find prepared statement with handle 9
Any other ideas?
Thanks
Geoff|||Hi
You don't post your code, but look at using a pass through query
instead and then it should not call sp_prepexec.
John|||"John Bell" wrote:
> You don't post your code, but look at using a pass through query
> instead and then it should not call sp_prepexec.
Unfortunately, I don't have access to the application code, since it is
contained in a third-party app. I have no way to control how the app talks
to the database, and was hoping to find out how I can 'tweak' the ODBC drive
r
behaviour, or prevent the sp_prepexec from causing an error.
Anyone have any other ideas?
Thanks
Geoff|||Hi
You should report this back to the application vendor as they seem to
be incorrectly releasing handles. You may want to check to see if they
already have any patches!
John|||"John Bell" wrote:
> You should report this back to the application vendor as they seem to
> be incorrectly releasing handles. You may want to check to see if they
> already have any patches!
>
Thanks John - I think I'll do as you suggest. I am already running their
latest release; the problem is that they run against MySQL by default, and
I'm trying to generate a SQL Server schema that works.
My gritty alternative is to learn Perl, and start trying to work my way
through the code myself!
Cheers
Geoff