0

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!

Ben
  • 51,770
  • 36
  • 127
  • 149
cloud311
  • 3,101
  • 5
  • 20
  • 20

1 Answers1

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'
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • 1
    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. – cloud311 May 03 '12 at 22:19