Link to home
Start Free TrialLog in
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

asked on

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                                                                                          
Avatar of David Todd
David Todd
Flag of New Zealand image

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 ...
Avatar of Marcus Aurelius

ASKER

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....
I want to process to create that filename....when it is run each day...and the DATE should change with each run...
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
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
Hi Mike,

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

and let me know the results.

Cheers
  David

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...?
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
Hi Mike,

Change this
DECLARE @date char(1000)

to this:
DECLARE @date varchar(1000)

HTH
  David
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).
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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