Can I keep x number of iterations in XP_CMDSHELL

Question

Post reply

Can I keep x number of iterations in XP_CMDSHELL

jbalbo

SSCertifiable

Points: 7583
More actions
May 8, 2013 at 12:49 pm

#293616

Hi
I'm using the following to write a query out to a file...
I will be running this daily, but I want to keep 30 iterations of it?
and to be honest, I figured this out via google and I get what I want
but if someone know a better solution or where I can read up on the switches etc..
that would be great.. thanks
declare @sql varchar(255)
set @sql='bcp "SELECT lname as last, fname as first FROM db.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),type" queryout "c:\temp\1.xml" -c -T -SUCDB01 -Usa -Ppas'
exec master..xp_cmdshell @sql

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

is the question "how do i run this query every x minutes?"

the answer is to create a scheduled job in the SQL Server Agent, that reccurs every hour or whatever you are after:

here's a screenshot from a similar question:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 2

What does "want to keep 30 iterations of it" means?

You want to run it daily 30 times? Loop through it. Or save results of 30 iterations of job?

Calibear Ten Centuries Points: 1377 More actions · Answer 3

Are you saying you don't want to overwrite the file? You want to keep thirty days worth of files? If so, you can use cmdshell to delete files also.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Sorry for being vague..

I want to sched this to run daily once and keep thirty days

Thanks

Calibear Ten Centuries Points: 1377 More actions · Answer 5

You need to be able to dynamically name your file and then use bcp to delete the file (using Command Prompt commands, calling DEL) from 30 days ago.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

Lowell SSC Guru Points: 323518 More actions · Answer 6

in that case, i'd change the name of the file to something that was generated to represent teh actual date, like this: (but why only for Wilson?)

--20130508-16-23-28-040.xml

SELECT REPLACE(CONVERT(VARCHAR(35),getdate(),112) + '-' + CONVERT(VARCHAR(35),getdate(),114),':','-') + '.xml'

and then change your code to feature that minor change, which you'd put into a scheduled job;

declare @sql varchar(255)

set @sql='bcp "SELECT lname as last, fname as first FROM db.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),type" queryout "c:\temp\'

+ REPLACE(CONVERT(VARCHAR(35),getdate(),112) + '-' + CONVERT(VARCHAR(35),getdate(),114),':','-')

+ '.xml' + '" -c -T -SUCDB01 -Usa -Ppas'

exec master..xp_cmdshell @sql

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jbalbo SSCertifiable Points: 7583 More actions · Answer 7

Thanks for the info ,

It is now working using

declare @sql varchar(255)

set @sql='bcp "SELECT lname as last, fname as first FROM DB.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),type" queryout "c:\temp\' + REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')+'Clubhouse.xml' + '" -c -T -SUCDB01 -Usa -Ppas'

exec master..xp_cmdshell @sql

Now I'm working on the DEL part.

BTW: good site to readup on all the switches? SO I know what the Heck is going on...lol

Thanks

Joe

jbalbo SSCertifiable Points: 7583 More actions · Answer 8

jbalbo

SSCertifiable

Points: 7583

May 8, 2013 at 2:59 pm

#1613543

BTW: Wilson query is just a test 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 9

well, you can get the quick list for bcp (or sqlcmd, it's newer, updated replacement)

by running it from the command line window with the /? flag

bcp /?

sqlcmd /?

for the basic details on the switches, MS has got it all here:

http://msdn.microsoft.com/en-us/library/ms162802.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!