Post reply

OUTPUT OF A QUERY /STORED PROCEDURE INTO A EXCEL FILE

  • November 2, 2005 at 6:34 am

    #67925

     

    Hi,

    THIS IS THE DATA IN MY TABLE

    ENO      ENAME

    1           NAME1

    2           NAME2

    3           NAME3

    4          NAME4

    I WANT TO WRITE A STORED PROCEDURE

    THIS WILL BE IN THE LOOP

    set @I=1

    WHILE (I<5)

    SELECT * FROM EMP WHERE ENO=@I

    set @I=@I+1

    LOOP

    THE ABOVE STATEMEMNT WILL EXECUTE 4 TIMES ,EACH TIME IT WILL GIVE ONE RECORD,

    EACH RECORD I WANT TO STORE IN AN EXCEL FILE.

    IN THE ABVOE I WANT TO STORE 4 RECORDS IN 4 EXCELFILES WITH DIFFERENT NAME.

    Is it possible in sql server?

    From DTS by using global variables we can insert only one file but i want to insert more records ,more files has to be created .can u let me know how to do?

    In the excel file i will do some modifications,after generating the excel file.

     

  • November 3, 2005 at 1:46 am

    #602049

    I'm not sure if this will help, but if you are going to open the exel files anyway, you could put the calls to the stored procedures in the Excel file. That way you could pass a different parameter to the stored procedure depending on which exel file it was from. If you really need to use DTS, you could put 4 separate target excel files into the package.

    There are a number of ways to extract data from sql server into excel (eg VBA using ADODB, pivot tables etc) but the easiest way is to use the Import Data from the Excel menu (available in Excel 2003 but not sure about earlier versions). Choose Data->Import External Data->Import Data. Create a new datasource to point to your database, remembering to un-check "Connect to a specific table". Once created, choose the datasource and click Open. Enter the password (if required) and choose a table (any table at this stage). Now you should be in the Import Data form. Click "Edit Query" and change the command type from Table to SQL. Now you can edit the query text to call your stored procedure with the relavent paramters, eg "EXEC MyStoredProc @param1 = 1, @param2 = 2". Give it a try, it's very easy.

    Regards

    Aranda

  • November 4, 2005 at 11:06 am

    #602411

    You can also use spreadsheet ML to create XML which becomes native .xls.
     
     

Viewing 3 posts - 1 through 2 (of 2 total)

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