Name output file based on SQL variable 1

[Welshbird]

This may not be possible, but at present I run many SQL queries each week (as a tactical reporting solution) and output the results to a tilde-delimited file.

In all cases the output file is named the same as the SQL file plus the weekending date, which is a variable within the SQL.

Is there anyway to use these to prompt the file name?

For example, one SQL script is called:
OrdersWE.sql and in it is a variable called @RPTDATE which for today's reports = '20090117'.

I therefore want the output to be called OrdersWE20090117.rpt

Is this at all possible?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Bump.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[dhulbert]

How are you creating the output files, Is it in a SQL script or are you using SSIS or something?

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Welshbird]

At the moment I run the query in sql management studio and output the results to a tilde delimited file.

Would there be a way to do this via BCP or summat then do ya think?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[MarcLodge]

Fee,
A very similar question was posed in the DB2 forum recently. See thread178-1525585

The general opinion is that it cannot be done from SQL but would need to be manipulated outside in some sort of script language.

Marc

 

[jrbarnett]

Can you get the name of the variable outside the script?
If so you can use the -o outputfile option on sqlcmd or bcp to write the output data.

Possible approach:
Use one SQL batch to generate your file and write this to a fixed filename.

Use a second batch to read this file and set it to an operating system variable.

Use a third batch to run your full script to create your output file and output to the environment variable.

Meanwhile you've got me trying to get this to work rather than doing real work. Will report back later...

Second step is easy - from the command prompt, assuming the file is e:\output.txt:

for /f %%i in (e:\output.txt) do set FILENAME=%%i

Third step is calling sqlcmd or bcp out with -o =%FILENAME%

Its just a matter of getting the first step to work.

John

 

[jrbarnett]

Done it. Replace @variable with your report name

declare @variable varchar (100)
set @variable = '20090130.rpt'

declare @command varchar (250)

set @command = 'echo ' + @variable + ' > e:\output.txt'

exec xp_cmdshell @command

Result: one file on your e: drive on your server that can be picked up by the following batch file. If somebody has a solution that doesn't need SQLCMD I'd be glad to hear of it.

John

 

[mutley1]

Fee,

Following on from John, these are steps I have in a SQL 2000 DTS package. (XLS Example) Basically, I have a template so when the package fires, it copies the template from template_name.xls to report_name.xls, populates that file then renames it to report_date.xls. Just a bit of code you can mess with.

DECLARE @CMD varchar (500)
DECLARE @CMD2 varchar (500)
DECLARE @RPTNAME varchar (50)
SET @CMD = 'copy D:\testreport\Template_myreport.xls D:\testreport\myreport.xls'
EXEC master..xp_cmdshell @CMD
--INSERT YOUR NORMAL CODE IN HERE TO POPULATE FLATFILE
SET @RPTNAME = (select 'TESTING_' + (select convert(varchar, GetDate(), 112) as '112 Conversion')+'.xls')
SET @CMD2 = 'copy D:\testreport\myreport.xls D:\testreport\'+@RPTNAME
EXEC master..xp_cmdshell @CMD2

HTH a bit

M.

 

[Welshbird]

Guys - you are all fantastic to have thought through my issue. I'll try tomorrow at the office and let y'all know how it turns out.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen