exec cmdshell from sp

[Larrabbb]

I am trying to get the following sp to work:

CREATE PROCEDURE BulkCopy
AS
declare @FileName varchar(100)
declare @string varchar(1000)

SET @filename = 'C:\test'+CONVERT(char(8),GETDATE(),10)+'.txt'

SET @string='bcp "select name, count(recordid) as categorization from
contacts where datetime between (getdate()-7) and (getdate()-1)
group by name order by name" queryout' +@filename +'-c -Sservername -Uusername -Ppassword'

EXEC master..xp_cmdshell @string
GO

when I execute it, I get the following as a result set with no files created:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

and a list of all the arguments below it.

Can someone help me figure out why this isn't working?

 

[ClaireHCM]

Try this

SET @string='bcp "select name, count(recordid) as categorization from
dbname.dbo.contacts where datetime between (getdate()-7) and (getdate()-1)
group by name order by name" queryout ' +@filename +' -c -Sservername -Uusername -Ppassword'

*note*
"contacts" you should be modified to "dbname.dbo.contacts"

 

[Larrabbb]

I have dbname.dbo.contacts in my query and it still is giving me the same recordset without creating records. Here is the complete recordset that is labeled output:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-6 6x file format] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

I don't know if this is helpful. Again, not for sure why this isn't working. I think I have everything covered.

 

[ClaireHCM]

The problem exists in "pressing enter" in set @string.

In Query Analyzer,a statement can be linked even if you seperate a statement into 3 parted line.
ex.
select au_fname,
au_lname from
pubs..authors

But in command prompt,once you press enter,it means you are giving command to executing the current statement!

So dont press any enter key in set @string statement!!

---------
SET @string='bcp "select name, count(recordid) as categorization from contacts where datetime between (getdate()-7) and (getdate()-1) group by name order by name" queryout' +@filename +'-c -Sservername -Uusername - Ppassword'

Nice question!

 

[Bob2]

Hi

I need to fo the same thing and therefore tryed the script with everything in a single row, but when I try to run the the procedure from query analyzer, no file is being produced....

Any ideas?


/M

 

[JamesLean]

Remember this script will produce the file in the C: drive of the server not the local machine. Have you checked that?

--James

 

[Larrabbb]

I had a problem after this, and had to work with our IP engineer to figure out how I could do this. I got it to write a file using the SQL, but when I did it from a SP, it still wouldn't work. Evidently there is a bug in SQL server. Here is the workaround that I ended up using(although I am still having problems, see below for more):

CREATE PROCEDURE BulkCopy_From_Log_Table AS

declare @FileName varchar(100)
declare @bcpcommand varchar(1000)

declare @netuse varchar(1000)
set @netuse = 'NET USE O: \\server\drive /USER:username password'
exec master..xp_cmdshell @netuse

SET @filename = 'O:filename\' + CONVERT(char(8),GETDATE()-7,10) + ' thru ' + CONVERT(char(8),GETDATE()-1,10) + '.xls'

SET @bcpcommand='bcp "select name name, count(recordid) as categorization_vol from table where datetime between (getdate()-7) and (getdate()-1) group by name order by name" queryout "' + @filename + '" -U username -P password -c'

print @bcpcommand

EXEC master..xp_cmdshell @bcpcommand

declare @netunuse varchar(1000)
set @netunuse = 'NET USE /DELETE \\server\drive'
exec master..xp_cmdshell @netunuse
GO

In order to get the file to write to a different drive, not the C: drive of the server, we had to use the 'net use' function to connect to the other drive. There are probably better ways around this, but that is what we used.

The problem I am still having is that my SP won't run automatically. I can manually run it and it works fine, but when I schedule it, it errors out. Not for sure why, if anyone has any insight, that would be much appreciated.

 

[Larrabbb]

One other thing, when you run the above SQL, it actually errors out, but it still writes the file to the location. Not for sure why it does that.