October 7, 2009 at 2:09 am
Hi,
I have 1 excel in C drive which is i am writing into it, and sending as attachment using SQL server procedure,
Now when my procedure loops through the Cursor again for 2nd mail to send;
First it should delete all the data from Excel and then again Write the data to Excel and send as attachment to the user.
I am not able to delete the data.
Thanks
Parth
October 7, 2009 at 6:14 pm
can you provide the code that you are using to delete from the excel spreadsheet? Then we can help you figure out what's going wrong.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 8, 2009 at 12:42 am
Delete openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\MOMA.xls;','Select * from [sheet1$]')
above code we are using for deleting data.
October 9, 2009 at 11:53 am
parth83.rawal (10/8/2009)
Delete openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\MOMA.xls;','Select * from [sheet1$]')above code we are using for deleting data.
I'm sorry i cannot test (my crappy Vista machine isn't allowing OPENROWSET today...) but, don't you need a 'FROM' keyword in there between DELETE and OPENROWSET?
Or perhaps you can change the inner statement to 'DELETE FROM [sheet1$]'?
Good Luck,
Mark
Just a cog in the wheel.
October 9, 2009 at 12:39 pm
Are you getting an error? If so, what is it?
A potential workflow alternative may be to:
-delete the file
-copy from a template to make a new file
-write your data
October 11, 2009 at 2:19 am
If I delete the file, and create a new one how am i gonna write my data as my Excel which i am sending is containing some macro and Columns with some names so there are some dependencies so how to avoid such things ..!!?
October 12, 2009 at 12:58 pm
Copy from a template file where your template has all the formatting and macros you need.
Untested code
DECLARE @cmd VARCHAR(1000)
DECLARE @TemplateFile VARCHAR(200)
SET @TemplateFile = 'Template.xls '
DECLARE @DestinationFile VARCHAR(200)
SET @DestinationFile = 'MyFile.xls '
DECLARE @FilePath VARCHAR(160)
SET @FilePath = '\\Server1\MyFolder\'
SET @cmd = 'COPY ' + @FilePath ++@TemplateFile + @FilePath + @DestinationFile
print @cmd
--Then write to the file you just created using openrowset...
September 23, 2010 at 4:49 pm
Great idea, I was having and exact issue. I will try it. 🙂
Billy Le
September 23, 2010 at 5:15 pm
Great idea emily-1119612!!! Kudos to you.
It work it work!!! for what it is that I am doing :).
I was setting up a SSIS package where it import data from an Excel spreadsheet into Table A then run a query to join 2 table A and B together then export to Excel Result_Output.xls, but I need my Result_Output.xls to be empty for the next run or it will just append the data, I was trying all different syntax to delete the record from the Result_Output.xls spreadsheet but to avail, you gave me the idea of creating an extra step in SSIS to copy over the template :).
thank you thank you,
Billy Le
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply