January 4, 2009 at 11:24 pm
Hi folks
This might be a no brainer, but was just wondering if it is possible to create a file system based subscription in RS 2005 where the filename is dynamically generated with a datestamp?
Thanks very much!
joannapea
January 5, 2009 at 6:51 am
I don't believe this is possible, but it has been awhile since I tried it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 7:02 am
This is a hack but it will do the job : run subcription with default name.
Create a windows task that renames the file with for exemple today's date (might have to use vbs to do this by generating the correct renaming command in DOS).
August 13, 2010 at 11:43 am
Dynamic SQl
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 13, 2010 at 11:58 am
Care to elaborate or was to posted in the wrong thread?!?
August 13, 2010 at 12:09 pm
Sorry. Yes I was giving a direction for problem resolution.
To dynamically name a file you will use dynamic SQL. Dynamic SQL allows you to Dynamically write SQL code; within your code.
Work Example:
I recently wrote this code. It has a static file name however the data that is exported to a flat file is dynamic. Because the data I am exporting to a file is unique to a client I must use dynamic SQL to DYNAMICALLY query tables based upon the ClientID passed in.
Here is the code:
SET @bcpCommand =
'bcp " SELECT TOP 1 ih.InvoiceNumber, ih.CustomerID, ih.ProcessingCenterID, ih.ProcessingCenterID, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), 1, 8, cast(datepart(year, ih.InvoiceDate) as varchar) + right(''0'' + cast(datepart(day, ih.InvoiceDate) as varchar), 2) + right(''0'' + cast(datepart(month, ih.InvoiceDate) as varchar), 2), id.Quantity, 0, 0, 0, 0, 0, 0, 0, ih.InvoiceTotal, ih.InvoiceTotal, 0, 0, 0, '' '',c.CustomerName, coalesce(ca.Address1, c.CustomerContact), ca.City, ca.State, ca.ZipCode, 0, 0, ih.ProcessingCenterID from eDirect..InvoiceHeader as ih JOIN eDirect..InvoiceDetail as id on id.InvoiceNumber = ih.InvoiceNumber JOIN eDirect..Customers c on c.CustomerID = ih.CustomerID JOIN eDirect..CustomerAddress as ca on ca.CustomerID = c.CustomerID AND ca.AddressTypeID = 1 WHERE ih.InvoiceNumber IN (SELECT InvoiceNumber FROM eDirect..InvoiceHeader WHERE Movedto PDS= 0) ORDER BY id.Quantity DESC" queryout'
SET @bcpCommand = @bcpCommand + ' d:\ffmun\PS\PS_InvHeader.txt -w -T -U sa -P sa","-CRAW'
EXEC eDirect..xp_cmdshell @bcpCommand
Hope this helps you out. Dynamic SQL can get tricky to follow... Works well however only use it in cases where direct SQL scripting will not do the job. Dynamic SQL is not efficient.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 13, 2010 at 12:19 pm
Awesome. Is it possible to do a similar trick when exporting a report from SSRS to let's say Excel or PDF?
This is where I really hit a wall when trying to get it done. BTW sql 2005 standard 64 bit.
August 13, 2010 at 12:26 pm
Yeah you can. I am not good with SSIS to be honest so someone else can jump in here.
I am 2010 64bit.. Version not an issue with regards to this however.
You may (and I don't know your project) want to write this as a stored procedure and simply schedule a sql job.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 13, 2010 at 12:32 pm
Hmm, can you give me a 50 000 foot review?
I never used ssis but I guess I'd figure it out with a fairly detailed plan.
August 13, 2010 at 1:16 pm
That's just it.. I am not an SSIS guy.. Never actually used it and I really need to get up to speed on it to be honest.
Your picture, is that current? Are you really that young and coding SQL?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 14, 2010 at 6:24 am
It's the Anakin Skywalker avatar. Back when I set it up everybody where using star wars avatars :w00t:.
But ya I'm young too :-P.
January 10, 2012 at 11:26 am
Both of you are heading in the wrong track. The owner of this topic wants to know whether a file delivered via SSRS can have a dynamic file name ?
The example you gave runs a "SELECT" statement and then creates a text file. What good will that be ?
The only best answer is have a ssis package with a script object and then rename the SSRS output file ( Which I believe is a PDF file )
January 10, 2012 at 11:29 am
mw112009 (1/10/2012)
Both of you are heading in the wrong track. The owner of this topic wants to know whether a file delivered via SSRS can have a dynamic file name ?The example you gave runs a "SELECT" statement and then creates a text file. What good will that be ?
The only best answer is have a ssis package with a script object and then rename the SSRS output file ( Which I believe is a PDF file )
Like I said possible, but big pain in the arse.
January 10, 2012 at 11:31 am
Ah, 2 years old thread.
There's another answer, in the subscription use @TIMESTAMP to output the date and time of the file. It's perfect but it's pretty good and answers the original question.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply