June 2, 2008 at 3:00 pm
I have a stored proc, once the stored proc runs it generates data with respect to different categories for example below is sample data:
category1 101
category2 103
category1 106
category3 100
category2 110
etc
So what I want is place the respective data into the folder wheer it belong to on the network, for example I want to place all the info of category1 in category1 folder, all the information of category2 in the category2 folder etc...
This will run as a scheduled job every morning, please advice how I can do this. Thanks!!
June 2, 2008 at 3:29 pm
How do you expect this data to get into folders? What type of result set?
The data from SQL Server can be easily exported using DTS or bcp and you can set paths for where the export goes. The SQL Service or SQL Agent service accounts need rights to those folders if they are not on the local machine.
June 2, 2008 at 8:24 pm
just a tought..
code the path to your stored proc or
sert the path to your scheduled jobs..
June 3, 2008 at 2:36 pm
Steve Jones - Editor (6/2/2008)
How do you expect this data to get into folders? What type of result set?The data from SQL Server can be easily exported using DTS or bcp and you can set paths for where the export goes. The SQL Service or SQL Agent service accounts need rights to those folders if they are not on the local machine.
Thanks for your response,
The output should go in excel format to each category folder.
The following is the resultset from the stored proc execution:
Column1Column2Column3
category1101NY
category2 103Florida
category1 106Colorado
category3 100Georgia
category2110NJ
category3200Minnesota
We will have folders with the follow names:
category1, category2, category3 ... upto category60
Since we have 60 categories, so we will have 60 folders.
Per the above resultset,
1. The following data should go into category1 folder in excel format:
Column2Column3
101NY
106Colorado
2. The following data should go into category2 folder in excel format:
Column2Column3
103Florida
110NJ
Etc...
Please let me know how we can do this as I want to schedule this to run daily morning.
Thanks!!
June 3, 2008 at 4:42 pm
You can do this by specifying CategoryID as one of the input for your stored procedure and then calling stored procdure once fr each 60 category IDs. Procedure needs to be modified to return results only for the specific category ID supplied as an input to stored procedure.
Then you can output these results of individual stored procedure execution to an hardcoded destination folder location using BCP(supplying exec procedurename inputparameter as an parameter to queryout switch ).
Manu
June 4, 2008 at 6:20 am
You can do this from the following query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\testing.xls',
'SELECT emp_cd, emp_name FROM [Sheet1$]')
SELECT emp_cd, emp_name FROM emplopyee
GO
Note: make sure that excel fiile is created and should not be in use, also column name should be present in excel file.
-Satya N Tailor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply