Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.
in mind should return one recordset with one record and one field (1) but when called from my generic output as CSV method:
CREATE PROCEDURE BOIT_SELECTDUPLICATES AS BEGIN DECLARE @TEMPTABLE TABLE (PN VARCHAR(50)) INSERT INTO @TEMPTABLE (PN) SELECT 'TEST' SELECT 1 END
The only output is "There was no data to output" where I would have expected "1"
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
then the output is "1" as expected
ALTER PROCEDURE BOIT_SELECTDUPLICATES AS BEGIN DECLARE @TEMPTABLE TABLE (PN VARCHAR(50)) --INSERT INTO @TEMPTABLE (PN) --SELECT 'TEST' SELECT 1 END
Join the community of 500,000 technology professionals and ask your questions.