[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Why does INSERT INTO interfere with returned recordset?

Posted on 2012-08-14
3
Medium Priority
?
665 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:BOIT
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38291975
<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
 

Author Closing Comment

by:BOIT
ID: 38292023
Thanks for that. I really should have known better!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38292034
,,, and know you do.  Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question