Output T-SQL to a text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Output T-SQL to a text file

Author

Topic

NicJ
Starting Member

15 Posts

Posted - 2009-06-09 : 05:12:37

I have been searching high and low to find the equivalent of the INFORMIX "unload to" command syntax.

If you have never used it - the Informix unload command takes this form:-

unload to "C:\myfile.txt" delimiter "|"
select name,address,insideleg from customers where custid > 1000;

Which would run the select statement and put the output, delimited by pipes "|", into a text file called myfile.txt on the C:\ drive of the server.

What is the equivalent in SQL server 2008?

I keep finding complex solutions with BCP or stored proceedures (?) but no simple equivalent - any help would be appreciated.

Nic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:14:09

BCP.
Or if this is an ad-hoc solution, chang the result mode in query window to text file.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:16:49

Not that complicated...

declare @command varchar(1000)

set @command = 'BCP "select name,address,insideleg from customers where custid > 1000" queryout "C:\myfile.txt" -t|'

exec xp_cmdshell @command

E 12°55'05.63"
N 56°04'39.26"

NicJ
Starting Member

15 Posts

Posted - 2009-06-09 : 05:17:46

quote:
Originally posted by Peso

BCP.
Or if this is an ad-hoc solution, chang the result mode in query window to text file.

So there is no simple equivalent then? - That seems a shame.

Can I use complex queries with BCP (upto 100 lines of SQL with mutiple unions etc) or are their restrictions on it?

Cheers

Nic

NicJ
Starting Member

15 Posts

Posted - 2009-06-09 : 05:19:45

quote:

Originally posted by Peso

Not that complicated...

declare @command varchar(1000)

set @command = 'BCP "select name,address,insideleg from customers where custid > 1000" queryout "C:\myfile.txt" -t|'

exec xp_cmdshell @command

I assume this is a T-SQL command and not a .bat file or anything else?

Nic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:23:01

BCP is an external application file which is run with the command shell.

E 12°55'05.63"
N 56°04'39.26"

NicJ
Starting Member

15 Posts

Posted - 2009-06-09 : 05:26:15

quote:
Originally posted by Peso

BCP is an external application file which is run with the command shell.

So its a .bat file then or a step in an SQL job or similar?

Nic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 06:40:15

No, it's an command line application that accepts input parameters.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-09 : 07:08:44

http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

NicJ
Starting Member

15 Posts

Posted - 2009-06-09 : 08:43:22

quote:
Originally posted by Transact Charlie

http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

So refering back to one of the previous posts - I may have many 10's of lines of SQL that will need to be run to get the result I want outputted to text file - do I just enclose the whole lot in Double quotes - if so what happens if I want to use Quotes in the query EG:-

declare @command varchar(1000)

set @command = 'BCP "
select name,address,insideleg from customers where custid > 1000 and Town = "LONDON"
" queryout "C:\myfile.txt" -t|'
exec xp_cmdshell @command

I dont want to do this as and when - but scheduled in the job scheduler or the SQL job scheduler or similar.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-09 : 08:56:13

yes -- because it's a dynamic call you will need to escape the single quote ' by adding another.

two single quotes is totally different from 1 double quote character.

if you have set result sets you want to present it would probably be easier to write a stored proc that returns that result and then just call that in the BCP call rather than trying to build the query each time. Would be quicker too.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources