How do I export to a file with a random generated name?

[ranchan02]

Hi Everyone

I created a stored procedure, what I want it to do is use a random generated number and use that as the name for a file that I will be exporting to a file.

Here's what I have conceptually:

...

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
as daRandomNumber

EXEC master..xp_cmdshell 'bcp "SELECT * FROM myRecords" queryout c:\'  + daRandomNumber + ' -c '

Is this the correct way? or is there a better way to do this?

 

[vongrunt]

> Is this the correct way?

Conceptually - yes. Note there are lots of things to finish. Above query returns one record with float value; you need an integer variable converted to string. EXEC won't let you concatenate values etc etc.

Still, there is always small chance that file with generated name exists... this can be solved by combining current datetime converted to 'yyyymmddhhmmss' and some random numbers.... there are many options.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]