Why does INSERT INTO interfere with returned recordset?

I need to return a recordset to an ASP webpage from SQL Server 2000, something I have done many times before.

The procedure:

CREATE PROCEDURE BOIT_SELECTDUPLICATES AS
BEGIN

DECLARE @TEMPTABLE TABLE (PN VARCHAR(50))

INSERT INTO @TEMPTABLE (PN)
SELECT 'TEST'

SELECT 1

END

Open in new window

in mind should return one recordset with one record and one field (1) but when called from my generic output as CSV method:

Function WriteCSVFile(Filename,SQLQuery,FirstLineFieldNames)
	'this method attempts to write a CSV file containing the select output from the SQLQuery expression supplied
	
	'	Filename = the name of the output file
	'	SQLQuery = the SQL expression to be run against the database connection
	'	FirstLineFieldNames = Flag as to whether the output should include the fieldnames as the first line (true/false)
	On Error Resume Next
	err.Clear
	WriteCSVFile = ""

	'get real pathname
	'first get the folder name
	Dim folderName : folderName = Server.MapPath(constantCSVFolderName)
	
	'open text file to receive the output
	Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
	Dim fileCSV : Set fileCSV = objFSO.OpenTextFile(folderName & "\" & Filename, 2, True, 0)
	If err.Number <> 0 Then
		WriteCSVFile = "Error writing file - " & err.Description
		err.Clear
		
	Else
		
		'create database connection
		Dim objConn : Set objConn = Server.CreateObject("ADODB.Connection")
		objConn.Open MM_CONNECT_STRING
									
		'initialise the database command
		Dim objRS : Set objRS = objConn.Execute(SQLQuery)
		If err.Number <> 0 Then
			WriteCSVFile = "Error Selecting Data - " & Err.Description
			err.Clear
			
		Else		
			If objRS.EOF = True Then
				'there is no data so return a failure condition
				fileCSV.WriteLine("There was no data to output")
				
			Else
						
				'get reference for line writer
				Dim csvLine
				
				'set flag for first line
				Dim isFirstLine : isFirstLine = true
		
				'iterate through the recordset- writing each line to the file
				Do	
					dim f : Set f=Server.CreateObject("ADODB.Field")
					'if this is the first line and we have been asked to output the field names as first line then do this now
					If FirstLineFieldNames and isFirstLine then
						'reset the first line flag so we only output the field names once
						isFirstLine = false
						
						'output the field names
						Dim titleLine : titleLine = "" 'string to hold the titleLine to be output
						For each f in objRS.Fields
							titleLine = titleLine & """" & f.Name & ""","
						Next
						
						'remove any trailing comma
						titleLine = RemoveTrailingComma(titleLine)
						
						'write the line to the file
						fileCSV.WriteLine(titleLine)
					End If
					
				
					'output the line data
					'initialise the line writer
					csvLine = ""
					
					'iterate through the field in this line and put values into string
					For each f in objRS.Fields
						csvLine = csvLine & """" & f.Value & ""","
					Next
					
					'remove any trailing comma
					csvLine = RemoveTrailingComma(csvLine)
					
					'write the line to the file
					fileCSV.WriteLine(csvLine)
					
					'move to next record (if it exists)
					objRS.MoveNext
					
				Loop While Not objRS.EOF	
			End If
		End If
		
		'close the recordset and the connection and release resources
		objRS.Close
		Set objRS = Nothing
		objConn.Close
		Set objConn = Nothing
	End If
	
	Set fileCSV = Nothing
	Set objFSO = Nothing
		
End Function

Open in new window

The only output is "There was no data to output" where I would have expected "1"

(the actual SQL query is rather complex but this seems to illustrate the problem)

If I use:

ALTER PROCEDURE BOIT_SELECTDUPLICATES AS
BEGIN

DECLARE @TEMPTABLE TABLE (PN VARCHAR(50))

--INSERT INTO @TEMPTABLE (PN)
--SELECT 'TEST'

SELECT 1

END

Open in new window

then the output is "1" as expected
BOITAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
<wild guess, without looking at the asp code>

On top of your SP add this...

SET NOCOUNT ON
GO

You may be experiencing the '1 row inserted' informational message being returned to your ASP, instead of the 'SELECT 1'.
0
 
BOITAuthor Commented:
Thanks for that. I really should have known better!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
,,, and know you do.  Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.