SQLCMD help with SYNTAX... using SQL SCRIPT.......?

Experts:

I'm using the following SQL Script that uses the SQLCMD to run another SQL Script...then save the results in a .CSV file.....

DECLARE @date char(1000)
DECLARE @sqlcmd varchar(8000)
DECLARE @savefile varchar(8000)
SET @date = (SELECT REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/',''))
SET @savefile = ('\\sqlstation\ftp\WOWCO_HMB_HMTB_'+@date+'.csv')
SET @sqlcmd =
'sqlcmd -S 192.168.10.4 -U mavega -P vega1 -i "\\sqlstation\ftp\calldataimport.sql" -o "\\sqlstation\ftp\WOWCO_HMB_HMTB_'+@date+'.csv"'
EXEC master.dbo.xp_cmdshell @sqlcmd

I know it can work..but the problem I'm having is trying to assign the SAVED FILE NAME using the GETDATE() formated within the filename.

Can someone take a look at the syntax for this section:

-o "\\sqlstation\ftp\WOWCO_HMB_HMTB_'+@date+'.csv"'
EXEC master.dbo.xp_cmdshell @sqlcmd

I'm trying to have the results SAVED in this filename format:

WOWCO_HMB_HMTB_02122008.csv

SQL keeps telling me this:

Sqlcmd: Error: Error occurred while opening or operating on file \\sqlstation\ftp\WOWCO_HMB_HMTB_02122008                  
   .csv (Reason: The system cannot find the path specified).
         NULL              


The above 3 error lines aer separated in the error....???

Thanks
MikeV                                                                                          
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
David ToddConnect With a Mentor Senior DBACommented:
Hi Mike,

I got this to work. Hope it helps.

use tempdb
go

execute master.dbo.xp_cmdshell 'dir c:\ > \\ntws133\c$\directory.txt'

declare @SQL nvarchar( 4000 )
set @SQL = 'use master; execute xp_cmdshell ''dir c:\ > \\ntws133\c$\dir_test.txt'''
print @SQL
execute sp_executesql @statement = @SQL

I'm using the sp_executesql instead of the execute statement on its own.

Cheers
  David
0
 
David ToddSenior DBACommented:
Hi,

does this work from the server?
execute master..xp_cmdshell 'sqlcmd -S 192.168.10.4 -U mavega -P vega1 -i "\\sqlstation\ftp\calldataimport.sql" -o "\\sqlstation\ftp\WOWCO_HMB_HMTB_02122008.csv"'

Does the server have rights on \\sqlstation\ftp?

Cheers
  David

PS For adding dates into filenames, I find that yyyymmdd works better, as the character sort is the same as the date sort ...
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Your sample Script gives this error:

Sqlcmd: Error: Error occurred while opening or operating on file \\sqlstation\ftp\WOWCO_HMB_HMTB_02122008.csv (Reason: Access is denied).

Keep in mind the file "WOWCO_HMB_HMTB_02122008.csv " does not exist..YET...

Yes... the folder FTP\ is shared.......full rights....
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MIKESoftware Solutions ConsultantAuthor Commented:
I want to process to create that filename....when it is run each day...and the DATE should change with each run...
0
 
David ToddSenior DBACommented:
Hi,

I understand that you want the date to change with each day.

I was trying to divide and conquer on your problem - is is the dymanic script, or the command itself.

Does the above query I posted work or have the same error?

Cheers
  David
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
The error I posted above...was the error I received when I tried your script on my workstation, I don't have access to try it "on the server..".........

I can run a script...and have it saved onto my C:\ drive...which is what I want....now,...I'm just trying to get the file NAMED correctly...

Once I get that corrected,....then... I'll work on my next step which is trying to "automate" the transfer of this file from my drive...to a FTP location. The DATE format is a requirment for the destination FTP location.

MikeV
0
 
David ToddSenior DBACommented:
Hi Mike,

try
execute master..xp_cmdshell 'dir > \\sqlstation\ftp\SQLSeverDirTest.txt'

and let me know the results.

Cheers
  David
0
 
MIKESoftware Solutions ConsultantAuthor Commented:

A file was saved in that directory named:

SQLSeverDirTest.txt

Full of Directory Text files..etc..etc..

So it has access to the drive...right...?
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Just some additional information regarding my original post.

The error looks stranges in that there are EMPTY LINES inbetween the rows like this below:

1 Sqlcmd: Error: Error occurred while opening or operating on file
2 \\sqlstation\ftp\WOWCO_HMB_HMTB_02122008
3
4                
5                                           .csv (Reason: The system cannot find the path specified).
6   NULL              

This is a close to what it looks like....i'm just curious as to why the ERROR msg SEPARATES the error like this....it leads me to believe that there is something wrong with the SYNTAX for that area of the SQL script...maybe a blank or space in the wrong area....????

Oh well...just passing it along...may or may not mean anything...?????

I appreciate your expertise and HELP....

MikeV
0
 
David ToddSenior DBACommented:
Hi Mike,

Change this
DECLARE @date char(1000)

to this:
DECLARE @date varchar(1000)

HTH
  David
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
When I changed it to the above...this is the error now...? Strange.... I wonder if there is a code that I need to tell it to CREATE a NEW file...????

Sqlcmd: Error: Error occurred while opening or operating on file "\\sqlstation\ftp\WOWCO_HMB_HMTB_02122008.csv (Reason: The filename, directory name, or volume label syntax is incorrect).
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thanks...I'll check it out in the morning...my eyes are bleeding....lol... I really...really...appreciate your help and expertise!!!!

Be in touch...thanks again....MikeV
0
All Courses

From novice to tech pro — start learning today.