SQL error Msg 8179

[TonyKhela]

Hi All,

I have a strange issue.

In a distributed query (see below)

SELECT c.dtepublished
FROM its.dbo.internetinspection a
LEFT OUTER JOIN [ITS].[dbo].[itsimsinspectionxref] b
ON a.Insp_no=b.Insp_no
LEFT OUTER JOIN LSD1HQ.[IMS2000_database].[dbo].[tblInspection] c
ON c.[lngInspection_Key]=b.lngInspection_Key
WHERE a.batch_number=641

I get the following error:

Server: Msg 8179, Level 16, State 1, Line 1
Could not find prepared statement with handle -1.

If I run the same query but with c.* rather than c.dtepublished in the SELECT statement, it runs fine.

I could get round the issue but doing a SELECT INTO a temporary table and then selecting from it, but it’s the principle of it.

I wanted to find out why.

Any ideas??

Thanks in advance

TK

 

[gmmastros]

What version of SQL Server are you using?

Select @@Version and post here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Does this work?

select x.dtepublished 
from
(
SELECT c.*
FROM its.dbo.internetinspection a
LEFT OUTER JOIN [ITS].[dbo].[itsimsinspectionxref] b 
ON a.Insp_no=b.Insp_no 
LEFT OUTER JOIN LSD1HQ.[IMS2000_database].[dbo].[tblInspection] c 
ON c.[lngInspection_Key]=b.lngInspection_Key
WHERE a.batch_number=641
) x

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[TonyKhela]

Alex,
I had already tried this before and it did not work.
In any case, as I said I can find other ways around it but I wanted to get to the bottom of this. Thanks.

George,

The first server is SQL 2000 and the Linked Server is 2005.

select @@version --SQL 2000
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: )


select @@version --SQL 2005 Linked server
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Thanks

TK

 

[TonyKhela]

George,

Any ideas why this would give this error?

Thanks in advance
TK.

 

[gmmastros]

No. Sorry. I'm stumped. Hopefully someone else will know what causes this error. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom