Share via

write to a txt file direct from a tsql query ?can it be done?

  • Article

Question

Wednesday, August 7, 2013 11:36 PM

Below is the query I have to out put some data about each schools teachers is there a way from the query to wrte/create files?

I know I can read from a file in a query using something like:

FROM '\pinnaclereport\scheduleimport\BET\BETcomments.csv' --< Change this line
WITH (FIELDTERMINATOR = '|');

but can i go the other way.

  DECLARE @SchoolCode VARCHAR(4)
  DECLARE SchoolCursor CURSOR FOR
  select distinct school_code from [MWTST_GSCS].[dbo].[mwvw_GSCS_Teacher] order by school_code

  open SchoolCursor
  FETCH NEXT FROM SchoolCursor into @SchoolCode

WHILE @@FETCH_STATUS = 0   
BEGIN
  select 
  '"' + LEFT([Email],CHARINDEX('@',[Email])-1) + '"'  as LocalID
  ,'"' + Last_Name + '"' as LastName
  ,'"' + First_Name + '"' as FirstName
  ,'"LDAP"' as UserType
  ,'""' as Nothing
  FROM [MWTST_GSCS].[dbo].[mwvw_GSCS_Teacher]
  where School_Code = @SchoolCode
  FETCH NEXT FROM SchoolCursor into @SchoolCode
END
CLOSE SchoolCursor   
DEALLOCATE SchoolCursor

    

All replies (5)

Thursday, August 8, 2013 3:29 AM ✅Answered | 1 vote

Yes, there are many ways to write out a text file, including osql/sqlcmd, bcp, COM calls, xp_cmdshell, etc.  Here are a few links with code examples:

https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

http://www.nigelrivett.net/SQLTsql/WriteTextFile.html

http://stackoverflow.com/questions/8132663/creating-a-text-file-on-local-machine-using-sql-server-2008

Keep in mind that most of the methods are writing the file to the server by default. 

Thanks,
Sam Lester (MSFT)

http://blogs.msdn.com/b/samlester

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Thursday, August 8, 2013 3:44 AM ✅Answered

Refer below,

 data definitioncreate table [XML]([xml] nvarchar(max),xmlname nvarchar(50))insert into [XML] values ('<TEST><NAME>Testing</NAME></TEST>','XMLFILE1')insert into [XML] values ('<TEST><NAME>Testing1</NAME></TEST>','XMLFILE2')select * from [XML] DECLARE @fileName VARCHAR(50)   DECLARE @sqlStr VARCHAR(1000) DECLARE @sqlCmd VARCHAR(1000)   SET @path = 'C:\TESTING\'SELECT @sqlStr=[xml] from [XML]EXECUTE spWriteStringToFile @sqlStr , @path, 'test.xml' user define functionCREATE PROCEDURE spWriteStringToFile (@String Varchar(max), --8000 in SQL Server 2000@Path VARCHAR(255),@Filename VARCHAR(100)--)AS   DECLARE  @objFileSystem int         ,@objTextStream int,            @objErrorObject int,            @strErrorMessage Varchar(1000),         @Command varchar(1000),         @hr int,            @fileAndPath varchar(80)    set nocount on  select @strErrorMessage='opening the File System Object'    EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT    Select @FileAndPath=@path+'\'+@filename if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'  if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'        , @objTextStream OUT, @FileAndPath,2,True   if @HR=0 Select @objErrorObject=@objTextStream,         @strErrorMessage='writing to the file "'+@FileAndPath+'"' if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String  if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'    if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close' if @hr<>0     BEGIN       DECLARE             @Source varchar(255),           @Description Varchar(255),          @Helpfile Varchar(255),         @HelpID int                 EXECUTE sp_OAGetErrorInfo  @objErrorObject,@source output,@Description output,@Helpfile output,@HelpID output           SELECT @strErrorMessage='Error whilst '             +coalesce(@strErrorMessage,'doing something')               +', '+coalesce(@Description,'')         RAISERROR (@strErrorMessage,16,1)       END EXECUTE  sp_OADestroy @objTextStream    EXECUTE sp_OADestroy @objFileSystem

Regards, RSingh

Friday, September 20, 2013 11:02 AM

In the stored procedure spWriteStringToFile the last 2 lines should be

EXECUTE sp_OADestroy @objTextStream

EXECUTE sp_OADestroy @objFileSystem

instead of

EXECUTE sp_OADestroy @objTextStream

EXECUTE sp_OADestroy @objTextStream

Wednesday, March 12, 2014 4:57 PM

In a "while" sentence, after 256 files, the files cannot be generated...  but replacing the following lines it works successfully

replace these lines

EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

with these lines

**EXECUTE sp_OADestroy @objTextStream**
**EXECUTE sp_OADestroy @objFileSystem**

Tuesday, October 14, 2014 7:44 PM

These links really helped and wanted to add more details on some items i have tried with perspective to limitations

if we use the following command you would run into character limitation of 8000exec master..xp_cmdshell 'echo hello > c:\file.txt'

if we exceed character limit of 8000 its going to error out as follows. This is true in SQL 2012 as well.

Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.

but if you use the following system methods you would overcome the character limit.

sp_OACreate 
sp_OAMethod

Thanks,

Indika