Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kd12345
Starting Member
12 Posts |
 Posted - 2008-03-10 : 13:09:59
|
| Hi I have DTS Package set up to export the data from SQL to Excel.I have stored procedure which execute the insert statement.INSERT into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=\\pc17917\c$\testing.xls','SELECT * From [DailyAllCases$]')SELECT CASE_ID, CASE_TYPE, CASE_SUBTYPE, PROVIDER_GRP_ID, RC_STATUS, RC_PRIORITY, RB_ANONYMOUS_FLAG, ROW_ADDED_DTTM, ROW_ADDED_OPRID, ROW_LASTMANT_DTTM, ROW_LASTMANT_OPRID, RC_SUMMARY, RC_DESCRLONGFROM PS_RC_CASE WHERE ROW_ADDED_DTTM BETWEEN CONVERT(VARCHAR(10), GETDATE() - 1, 120) + ' 00:00:00.000' AND CONVERT(VARCHAR(10), GETDATE() - 1, 120) + ' 23:59:59.000' ORDER BY CASE_TYPE, CASE_SUBTYPE, ROW_ADDED_DTTMI also used SQL Tasks to drop the excel worksheet and create worksheet and then use insert command to insert the data in excel.DROP TABLE `DailyAllCases`CREATE TABLE `DailyAllCases` (`Case ID` decimal NULL, `Case Type` VarChar (100) NULL, `Case Subtype` VarChar (100) NULL, `Provider Group` VarChar (100) NULL, `Status` VarChar (100) NULL,`Priority` VarChar (100) NULL,`Anon?` VarChar (10) NULL, `Creation Date` DateTime NULL,`Created By` VarChar (100) NULL, `Last Changed Date` DateTime NULL,`Last Changed By` VarChar (100) NULL, `Case Summary` VarChar (100) NULL, `Case Description` text NULL)Everything works fine... data gets inserted to the excel file but somehow it start inserting data from the row 2500 instead of the row 2nd.I'm not sure what is wrong with the excel?Can anyone help please? Any suggestion or idea will be greatly appreciated!Thanks,KD |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-10 : 16:55:10
|
How do you specify where to drop the data in the excel file?You can explicitly "select" or activate the cell A1 in the code to designate the drop.How you are referencing the excel object? you don;t need to drop and add the sheet (i imagine something in that code is where the problem lies)..you can just clear all the data on the target sheet.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
BobFoutFX4
Starting Member
9 Posts |
Posted - 2008-03-14 : 13:39:53
|
| How do you clear out the Excel sheet you are sending the data to? |
|
|
|
|
|
|
|
|
Export SQL to Excel