SQL code 8179: Could not find prepared statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL code 8179: Could not find prepared statement

Author

Topic

ings
Starting Member

3 Posts

Posted - 2008-03-06 : 12:28:31

Hi
I am intermittently getting a problem with a query and get the SQL code 8179 "Could not find prepared statement with handle ..." and then the handle number.

One minute my query is working OK, the next I get the error.

Now the really dumb part - if I just add another carriage return anywhere at the end of a line in my query, save & re-run, the query works again.

It doesn't happen on one specific query - it occurs randomly on any query.

I'm accessing an SQL Server 2000 DB from an enterprise wiki, via (I think) a Java ODBC jTDS.

Does anyone have any idea of what I could check?

Thank you!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-06 : 13:15:23

check if you set the correct size of any of your paramters in the code.
do you use stored procedures or build your sql in code?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

ings
Starting Member

3 Posts

Posted - 2008-03-07 : 05:16:06

I don't use stored procedures, and it also happens even when I'm not explicitly defining parameters. For example I have a very simple query:

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
;
SELECT
'string'+columnA+'string'+columnA+'string' AS ColumnA,
columnB AS columnB,
CASE
WHEN columnC = 'Yes'
AND columnD = 'Yes'
THEN 'CD'
WHEN columnC = 'Yes'
THEN 'C'
WHEN columnD = 'Yes'
THEN 'D'
ELSE 'none'
END
AS 'ColumnCD',

FROM TableA
LEFT JOIN
TableB
ON TableA.Key = TableB.Key
WHERE ColumnA = 'string'
AND ColumnB = 'string'

ORDER BY columnA, columnB
;

After I get the problem, I can solve it just (for example) by changing:

SELECT
'string'+....

to

SELECT

'string'+...

(with an extra carriage return - anywhere in the query). I'm guessing that by doing this, the SQL ODBC thinks it is a new query and ignores any prepared statements made before??

What is a prepared statement anyway???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-07 : 05:50:33

will this do?
http://msdn2.microsoft.com/en-us/library/aa197533.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

ings
Starting Member

3 Posts

Posted - 2008-03-07 : 12:06:39

Thanks - I know a lot more about prepared statements now than I did a couple of hours ago!

I tried configuring our jTDS driver so that it doesn't use prepared statements (so it prepares & executes the statement each it is run), but that didn't seem to solve the problem.

If I get any solution I will re-post here in the future

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources