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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy