SQL Server Performance

Export from Procedure to excel through SP

Discussion in 'General DBA Questions' started by ramkumar.mu, Jul 6, 2006.

  1. ramkumar.mu New Member

    Hi,

    Is there any way to load data from table into an excel sheet through a SP/T-SQL instead of DTS and Import/Export data wizard. Just wanna know.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. dineshasanka Moderator

    use bcp

    ----------------------------------------
    http://dineshasanka.blogspot.com/
  3. Madhivanan Moderator

  4. merrillaldrich New Member

    You can also make the spreadsheet "pull" the data from SQL Server by putting a query in the spreadsheet itself.
  5. ramkumar.mu New Member

    Thanks Dinesh.

    I tried this query to get the data into an excel sheet...

    DECLARE @CMD VARCHAR(8000)
    SELECT @CMD = 'bcp "select * from ram.dbo.c " queryout c:eek:utput.xls -c -S' + @@servername + ' -T'
    exec master..xp[underscore]cmdshell @CMD

    I need to put those data in a particular location (Sheet1!$A$2). do we have an option to specify that?

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  6. Madhivanan Moderator

    Did you read the link I posted?

    Madhivanan

    Failing to plan is Planning to fail
  7. satya Moderator

    I hope not and it will definetly saves the time by searching old posts in the forum [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided �AS IS� with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  8. ramkumar.mu New Member

    I ran this query...

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 4.0;Database=D: esting.xls;',
    'SELECT * FROM [Sheet1$]')
    select * from datetab

    and i got an error...

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

    Do i need to do anymore before running this query???

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  9. Madhivanan Moderator

    At the time of running that query EXCEL file should be closed

    Madhivanan

    Failing to plan is Planning to fail
  10. ramkumar.mu New Member

    Oh!! I ran that now and again getting an error

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D: esting.xls',
    'SELECT * FROM [Sheet1$]')
    SELECT * FROM DateTab

    Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.


    Also, how do i specify a particular cell where data is to be inserted. (like A12 or C3 etc)



    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  11. Madhivanan Moderator

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D: esting.xls',
    'SELECT * FROM [Sheet1$A12:C3]')
    SELECT * FROM DateTab


    Madhivanan

    Failing to plan is Planning to fail
  12. ramkumar.mu New Member

    what do those numbers A12 and C3 represent actually???

    when i executed the below query (different tablename - aa with 2 columns and 8 rows)

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:ResultTest.xls',
    'SELECT * FROM [test$A12:C3]')
    SELECT * FROM aa

    i got the same error...

    Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    even for the same table (datetab), when i changed the value 'A12:C3' to 'A2:C12', it didnt work. i couldnt understand this logic.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  13. Adriaan New Member

    As suggested before, if the data needs to end up in a pre-defined Excel sheet, it is probably easier if you PULL the data into Excel (which is worksheet-oriented) and not push it from SQL Server (which is table-oriented).

    There was also a problem recently where the default driver used for OPENQUERY did not support data export to an XLS file. I wonder if it's the same underlying issue here.
  14. Madhivanan Moderator

    >>(different tablename - aa with 2 columns and 8 rows

    Then you should add two column names in EXCEL and try

    Madhivanan

    Failing to plan is Planning to fail
  15. ramkumar.mu New Member

    is it like <br /><br />'SELECT * FROM [test$A12:C22]')<br />SELECT * FROM aa<br /><br />It didnt work [<img src='/community/emoticons/emotion-6.gif' alt=':(' />].<br /><br />The previous table "datetab" had 3 columns a 407 rows. i dont understand how it worked for table "datetab"<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  16. ramkumar.mu New Member

    quote:Originally posted by Adriaan

    As suggested before, if the data needs to end up in a pre-defined Excel sheet, it is probably easier if you PULL the data into Excel (which is worksheet-oriented) and not push it from SQL Server (which is table-oriented).

    There was also a problem recently where the default driver used for OPENQUERY did not support data export to an XLS file. I wonder if it's the same underlying issue here.

    Is there any fix/solution for this?

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  17. Adriaan New Member

    quote:Originally posted by ramkumar.mu


    quote:Originally posted by Adriaan

    As suggested before, if the data needs to end up in a pre-defined Excel sheet, it is probably easier if you PULL the data into Excel (which is worksheet-oriented) and not push it from SQL Server (which is table-oriented).

    There was also a problem recently where the default driver used for OPENQUERY did not support data export to an XLS file. I wonder if it's the same underlying issue here.

    Is there any fix/solution for this?

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    If you say the same technique worked before, then sorry for the confusion.

    You mention a table aa with "2 columns and 8 rows", but your Excel worksheet range seems to cover 3 columns: A, B and C.
  18. ramkumar.mu New Member

    Yes! the previous table [datetab] had 3 columns. if i give [a:1:c1] for datatab, it didnt accept.

    i was trying all the numbers that were working... the query accepts., a(>6) and c(any value)

    also, it hung my system thrice and i have to restart all the time it hung.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  19. Adriaan New Member

    Not sure - does it work now? Then what did you change?
  20. ramkumar.mu New Member

    I made it work for datetab, but i couldnt produce the same trick work for the other table. weird!!!

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  21. Adriaan New Member

    Open the XLS sheet, see if there's anything wrong there.
  22. ramkumar.mu New Member

    I was over precautious.[:I] every time, before i run this query, i drop and recreate that sheet to be on the safe side.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page