Why is this INSERT returning a recordset

Question

Post reply

Why is this INSERT returning a recordset

Cindy Hallum

SSCommitted

Points: 1808
More actions
November 1, 2005 at 6:32 pm

#102898

I have been running the following insert statement as part of a stored procedure every hour for a couple of months. Today it decided to return the results of the SELECT statement, rather than doing the INSERT. Any ideas?
INSERT INTO dbo.W3ManifestBL (Manifest, Origin, Dest, Trailer, Chassis, Booking, VesselVoyage, CarrierCode, DateShip, DateETA)
SELECT Manifest, Origin, Dest, Trailer, Chassis, Booking, VesselVoyage, CarrierCode, DateShip, DateETA FROM dbo.D3UpdateManifestBL
WHERE Manifest IN (SELECT DISTINCT Manifest FROM dbo.W3ManifestXref) OR (DateShip >= DATEADD(yyyy,-1,getdate()));
We are running SQL 2000 and I rebooted the server to no avail. Please help. TIA

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Have you recompiled the stored procedure? It may have become corrupt...

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

November 1, 2005 at 10:32 pm

#601739

Do you have set nocount on?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Has someone perhaps put a trigger on the table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Agreed, there is something else in the works here.

A INSERT <table> SELECT ... FROM... does not return a resultset.

/Kenneth

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Are you sure it is the insert statement? By the sounds of it, you have other statements in the proc as well apart from the insert given. Change your code to

SELECT 1 as TEST1

INSERT INTO dbo.W3ManifestBL (Manifest, Origin, Dest, Trailer, Chassis, Booking, VesselVoyage, CarrierCode, DateShip, DateETA)

SELECT Manifest, Origin, Dest, Trailer, Chassis, Booking, VesselVoyage, CarrierCode, DateShip, DateETA FROM dbo.D3UpdateManifestBL

WHERE Manifest IN (SELECT DISTINCT Manifest FROM dbo.W3ManifestXref) OR (DateShip >= DATEADD(yyyy,-1,getdate()));

SELECT 2 as TEST2

And then see if your mystery recordset is appearing between the two recordsets. If so, then yes, your insert statement has gone strange!? Perhaps the insert line has become commented with /* */ or --. If it is outside the recordsets, then you are looking at the wrong part of the proc.

Hope that helps diagnose the problem!

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Good thinking! Done that myself during testing. Forgot to comment out or remove some return code...

I wasn't born stupid - I had to study.

Cindy Hallum SSCommitted Points: 1808 More actions · Answer 7

Just got back to this client today and got your responses. Thanks to everyone for the ideas.

Recompile did the trick.

Thanks again.

Ian Yates SSCoach Points: 19738 More actions · Answer 8

Really, just a recompile?? Even after a server reboot? What service pack level are you running?

Cindy Hallum SSCommitted Points: 1808 More actions · Answer 9

Actually, after the recompile, it still messed up. I ran it 6 times. It performed the INSERT 4 times and displayed the recordset 2 times. So I retyped the whole statement and it ran successfully 5 times. So I'm crossing my fingers that it is fixed and moving onto the million other things I have to do.

select @@version gives me:

Thanks for your help.

Ian Yates SSCoach Points: 19738 More actions · Answer 10

You have SP3 installed... Could always try upgrading to SP4?