Generate output and place in a specific folder

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

  • 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.

  • just a tought..

    code the path to your stored proc or

    sert the path to your scheduled jobs..

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

  • 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

  • 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