Export from Procedure to excel through SP

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

You can also make the spreadsheet "pull" the data from SQL Server by putting a query in the spreadsheet itself.

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 cutput.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..."

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

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

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

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

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

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.

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

Not sure - does it work now? Then what did you change?

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

Open the XLS sheet, see if there's anything wrong there.

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