Creating a csv with sqlcmd

Question

I'm trying to create a csv using SQL Command. I am using a simple table as a test. I've written this so far:

DECLARE @sql VARCHAR(1000),
        @cmd VARCHAR(100),
        @sqlCommand VARCHAR(1000)

Set @cmd = 'Select * From DSG.Pawtucket.counts'

SET @sqlCommand = 'SQLCMD -S <server> -U <user> -P <password> -d <database> -W -Q "' + @cmd + '"
-s "|" -o <network path> -h-1' 

EXEC master.dbo.xp_cmdshell @sqlCommand

I see the query results in the 'results' tab and I receive no errors but my file is never created. I know I am using a valid network path. I even tried just creating the file on the SQL servers local C: drive with no success. I also tried using bcp with much frustration and no results. Any help would be greatly appreciated!

Conrad Frix · Accepted Answer · 2012-05-03 22:02:53Z

4

Get rid of the line break in the literal string

What you wrote

SET @sqlCommand = 'SQLCMD -S <server> -U <user> -P <password> -d <database> -W -Q "' + @cmd + '"
-s "|" -o <network path> -h-1'

Sends two commands to xp_cmdshell

The first command which gives you the results from @cmd in your results tab.

SET @sqlCommand = 'SQLCMD -S <server> -U <user> -P <password> -d <database> -W -Q "' + @cmd + '"

And then this which does nothing

   -s "|" -o <network path> -h-1'

Below is what you want. Aside from removing the carriage return I also added the space after '" since -s needs to be separated from whatever is in @cmd

SET @sqlCommand = 'SQLCMD -S <server> -U <user> -P <password> -d <database> -W -Q "' + @cmd 
+ '" -s "|" -o <network path> -h-1'

edited May 3, 2012 at 22:02

answered May 3, 2012 at 21:45

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cloud311 Over a year ago

Thanks, that was it! Now I've just gotta patch up that huge hole I made in the wall from banging my head against it. Seriously... I appreciate it. I should have been able to figure that out but I was already so frustrated that a fresh pair of eyes really helped.

Collectives™ on Stack Overflow

Creating a csv with sqlcmd

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related