Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dwalker79
Yak Posting Veteran
54 Posts |
 Posted - 2008-05-27 : 20:03:02
|
| Hello,I'm still a fairly new DBA so I'm not complete up to speed on some of the development techniques.I have been tasked with scheduling various stored procedures to run on the last day of the month. These stored procedures create reports. We don't have Reporting Services so I've been tasked with trying to figure out a way to extract the results of the stored procedures to .csv files. I first looked at using DTS but I couldn't quite figure it out. One of my issues is that I have a parameter that is passed to the sp's that needs to be set to the current date. Here is an example of one of the stored procedures with the parameters:spams_s_rept_ar_report2 @rc = 1,@type = 2,@report_date = '2-29-2008', @org_type = 0The @report_date parameter actually needs to be set to a variable that is equal to the current date. Is there a way to do this with a DTS package and then I could schedule the package to run on the last day of every month. I'm hoping there is a way that the DTS can create a .csv file with the results of the sp?Any help would be greatly appreciated! |
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2008-05-28 : 07:54:28
|
| Thanks for the quick reply nr.I have a couple questions. How would I set the @report_date equal to a variable using bcp? Also could you give me an example of what the bcp command line would look like in my case?Thanks so much! |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-28 : 15:46:34
|
| declare @d datetimeset the datedeclare @cmd varchar(1000)select @cmd = 'bcp "exec mysp ''' + convert(varchar(8),@d,112) + '''" queryout -S' + @@servername + ' -T -c'exec master..xp_cmdshell @cmd==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Extract Results of Stored Proc with parameters?