Generate output and place in a specific folder

Question

Generate output and place in a specific folder

Mh-397891

SSCertifiable

Points: 6704
More actions
June 2, 2008 at 3:00 pm

#71611

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!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

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.

paksyeet SSCommitted Points: 1596 More actions · Answer 2

just a tought..

code the path to your stored proc or

sert the path to your scheduled jobs..

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 3

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!!

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

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

satya-619607 Valued Member Points: 62 More actions · Answer 5

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