StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Can you use OLE Automation? It's ugly, and you could probably use some set based string building techniques instead of the cursor but here goes...</p> <pre><code> Declare @Dir varchar(4000) Set @Dir = 'c:\some\path\accessible\to\SQLServer' If @Dir IS NULL Begin print 'dir is null.' Return 1 End declare @FilePath as varchar(255), @DataToWrite as varchar(8000) If right(@DataDir,1) <> '\' Set @DataDir = @DataDir + '\' Set @FilePath = @DataDir + 'filename.csv' DECLARE @RetCode int , @FileSystem int , @FileHandle int EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0) begin RAISERROR ('could not create FileSystemObject',16,1) End declare @FileExists int Execute @RetCode = sp_OAMethod @FileSystem, 'FileExists', @FileExists OUTPUT, @FilePath --print '@FileExists = ' + cast(@FileExists as varchar) If @FileExists = 1 Begin RAISERROR ('file does not exist',16,1) /*return 1*/ End --1 = for reading, 2 = for writing (will overwrite contents), 8 = for appending EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @FilePath, 8, 1 IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0) begin RAISERROR ('could not create open text file',16,1) End DECLARE CSV CURSOR READ_ONLY FOR Select Anything From MyDataTable order by whatever DECLARE @fld1 nvarchar(50) ,@fld2 nvarchar(50) OPEN CSV FETCH NEXT FROM CSV INTO @fld1, @fld2 WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN Set @DataToWrite = @fld1 + ',' + @fld2 + char(13) + char(10) EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Write' , NULL , @DataToWrite IF (@@ERROR|@RetCode > 0) begin RAISERROR ('could not write to file',16,1) End END FETCH NEXT FROM OpenOrders INTO @fld1, @fld2 END CLOSE CSV DEALLOCATE CSV EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close' , NULL IF (@@ERROR|@RetCode > 0) RAISERROR ('Could not close file',16,1) EXEC sp_OADestroy @FileSystem return 0 End </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload