SQLCMD help entering a FILENAME based on GETDATE() ....?

Experts:

I'm trying to use this SQLCMD to schedule an export of my data to my harddrive for FTP transfer.

       sqlcmd -S 192.168.10.4 -i c:\ftp\sqlscripttest.sql -o c:\ftp\sqloutputdata.csv

I'm currently trying to have this SQLCMD "name" the file correctly.

I need the FILENAME to be this:     sqloutput02102008.csv

I need the DATE to be based on GETDATE() from SQL server...in this format:

replace(convert(varchar(10), getdate(),101), '/', '')

Can you help me undestand HOW to add this logic to my SQLCMD so that the FILENAME that is created will have the correct DATE in it..?

Thanks
MikeV
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
You can easily achieve sqloutput2008-02-10.csv file name:

sqlcmd -S 192.168.10.4 -i c:\ftp\sqlscripttest.sql -o c:\ftp\sqloutput%date%.csv

If you run sqlcmd from SQL Server using xp_cmdshell 'sqlcmd ...' then you can have the format more flexible:

DECLARE @date char(8)
DECLARE @sqlcmd varchar(8000)
SELECT @date = REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/','')
SET @sqlcmd = 'sqlcmd -S 192.168.10.4 -i c:\ftp\sqlscripttest.sql -o c:\ftp\sqloutput' + @date + '.csv'
EXEC xp_cmdshell @sqlcmd
0
MIKESoftware Solutions ConsultantAuthor Commented:
I MUST...use SQLCMD from the COMMAND line...because I'm using SQL Management Studio Express and I can't schedule much of anything in my FREE version.

So,...can I use your suggestion using the COMMAND PROMPT...so that I can "schedule" the process to run each day...?

IF SO,...how....?

THanks
MikeV
0
brejkCommented:
There is a schtasks.exe command line utility that allows scheduling tasks. So, all you have to do is to create a .bat file with call for SQLCMD inside.

Just open cmd.exe and type: schtasks /? and press enter.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

MIKESoftware Solutions ConsultantAuthor Commented:
I'm using the WINDOWS version of this I think...it is working fine....the only issue is the FILENAME....having the DATE in the above format.

So can you give me a sample of the SQLCMD to use that will utilize your STORED PROCEDURE approach...OR...is that not possible..???

MikeV
0
brejkCommented:
Ok:

1) Create SQL script named C:\ExecSQL.sql like below:

DECLARE @date char(8)
DECLARE @sqlcmd varchar(8000)
SELECT @date = REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/','')
SET @sqlcmd = 'sqlcmd -E -S 192.168.10.4 -i "$(input)" -o "$(output)' + @date + '.csv"'
EXEC xp_cmdshell @sqlcmd

2) Create a batch named C:\RunExecSQL.bat like below:

sqlcmd -E -S 192.168.10.4 -i ExecSQL.sql -v input="c:\ftp\sqlscripttest.sql" output="c:\ftp\sqloutput"

3) In command line run schtasks, for example:

schtasks /Create /U WindowsUser /P Passord /SC HOURLY /MO 6 /TR "C:\RunExecSQL.bat" /RU WindowsUser /RP Password

Of course, replace user names and passwords with your user name and password (run as Windows user that is a sysadmin in SQL Server).

Also, make sure xp_cmdshell is enabled on your SQL Server instance (if not, then enable this server option with sp_configure system procedure - see Books Online for more details).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MIKESoftware Solutions ConsultantAuthor Commented:
What does this part of the CODE below do???

/SC HOURLY /MO 6 /TR

I need it run DAILY at 8pm or so....
0
brejkCommented:
/SC and /MO -  interval (HOURLY means that /MO value is measured in hours - every 6 hours something will happen), /TR - what will happen (in this example - our batch will run).
0
MIKESoftware Solutions ConsultantAuthor Commented:
I've tried scheduling it...but nothing happens....

question....I'm not sure if the NAMING part of this logic is correct....tell me HOW it knows to save the RESULTS of the SQL query...INTO...the FILENAME....?

output="c:\ftp\sqloutput"

should this be something else....what is sqloutput.....?????
0
MIKESoftware Solutions ConsultantAuthor Commented:
Does the BATCH have to be saved in C:\...????

0
brejkCommented:
To run the batch everyday at 8 PM use these options:

/SC DAILY /MO 1 /ST 8:00
0
brejkCommented:
No the batch can be saved enywhere. Just change the path in your schtasks call.
0
brejkCommented:
output="c:\ftp\sqloutput"

This is SQLCMD variable used in sql script ($(output) - this is the place you reference it). It is just a prefix for file path. The rest of the file path is date and csv extension. All is done by concatenation.

First try to run the .bat batch itself to check if it works. Then try to schedule.
0
MIKESoftware Solutions ConsultantAuthor Commented:
Invalid syntax,...manditory optino for /tn is missing.....?
0
brejkCommented:
Ooops, sorry :-) You have to define task's name:

/TN GiveThisTaskSomeName
0
MIKESoftware Solutions ConsultantAuthor Commented:
Cannot specify USER NAME without specifying SYSTEM NAME.....????

     : )
0
MIKESoftware Solutions ConsultantAuthor Commented:
This is how it looks at present...got the above error on this......

schtasks /Create /U MYNAME /P MYPASSWORD /SC DAILY /MO 1 /ST 8:00 /TN DailyCallImport /TR c:\ftp\RunExecSQLDailyCallData.bat" /RU MYNAME /RP MYPASSWORD
0
brejkCommented:
I'm not sure, maybe you are providing user name inproperly (MACHINE\USER - I think this is the best format).

Also instead of schtasks you may try Task Scheduler system windows application located in Accessories - System Tools in Windows. Perhaps scheduling with GUI will be easier :-)
0
MIKESoftware Solutions ConsultantAuthor Commented:
Ok...I think it has to do with the  xp_cmdshell...thing.

When I try to run it in SQL server I get this error:

Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'xp_cmdshell'

I guess I may need to tell it to be in MASTER...? Or make sure its turned on...??
0
MIKESoftware Solutions ConsultantAuthor Commented:
I think I'm getting the concept...I changed my sequal to use MASTER db...and now I think it may work...I'm trying again...

MikeV
0
brejkCommented:
Another my mistake: of course you should call xp_cmdshell like below.

EXEC master.dbo.xp_cmdshell ...
0
MIKESoftware Solutions ConsultantAuthor Commented:
I'm still working on this...but thinking ahead.... so...is there a way to have this TASK REPEAT ITSELF...IF... it did NOT process correctly or errored out>...?

Also...is there a way to send myself an email...stating either that it completed OR..that it did not RUN...???

Thanks
MikeV
0
MIKESoftware Solutions ConsultantAuthor Commented:
Well doesnt' seem to be working...and I'm not sure where or when it's breaking...?

Any logs created that I can look at....?

MikeV
0
brejkCommented:
AFAIR if you use Task Scheduler there is a log created for each task. Task Scheduler also has possibility to send e-mail (sending an e-mail is one of possible actions in tasks).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.