Solved

Error "File format is not Valid" when email excel file created in SQL Server 2000 in Dynamic SQL

Posted on 2008-10-23
12
244 Views
Last Modified: 2012-06-21
Hi all.

I have a DTS that creates excel files for records in a SQL Server 2000 table and then an ActiveXScript runs that emails the excel file as an attachment.

The problem is that whenever the end user receives the email and tries to open the excel file they get the following error: "File format is not Valid"

So I created a sample excel file on my desktop and pasted in the location where the other excel sheets that created by the Dynamic SQL provided below and had the DTS run and email it, and I was able to open the excel file. So I believe the error is in the Dynamic SQL, the box is SQL Server 2000 and the end users have Excel 2003.

Do I need to install something the sql server box so when the Dynamic SQL runs it creates the excel files with the right version?

Thank you in advance.
BEGIN

	DECLARE @OLEDBInitString varchar(1000),

		@FilePath varchar(1000),	

		@CopyCmd varchar(1000)

	SET @FilePath = @FolderPath + 'ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

	SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'

	EXEC master.dbo.xp_cmdshell @CopyCmd, no_output

	SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 8.0;'

	DECLARE @Insert varchar(1000)

	SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$] 

	SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM  dbo.[Contact Audit List 2008]  WHERE AccountID ='''+@LOC + ''''

	

EXEC (@Insert)

Open in new window

0
Comment
Question by:printmedia
  • 6
  • 5
12 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 22794943
could you attache file excel?
0
 

Author Comment

by:printmedia
ID: 22795823
Attached is one of the excel files.
ContactAuditList-00-0001010-2008.xls
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22796375
I think you're missing initialize some variables and the string you build are NULL because of that. I modified the code and tested with print to see the output. Initialize the variables with the values you need and always test with print first before EXEC:
DECLARE 

	@OLEDBInitString varchar(1000),

	@FilePath varchar(1000),

	@FolderPath as varchar(1000),        

	@CopyCmd varchar(1000),

	@Insert varchar(1000),

	@LOC varchar(1000),

	@TemplateFilePath aS VARCHAR(1000)

SELECT 

	@LOC = 'Location',

	@TemplateFilePath ='Template'
 

SET 

	@FilePath = isnull(@FolderPath,'FolderPath/') + 'ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

SET 

	@CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + isnull(@FilePath,'FilePath') + '"'
 

--EXEC master.dbo.xp_cmdshell @CopyCmd, no_output

print @CopyCmd
 

SET 

	@OLEDBInitString = 'Data Source="' + isnull(@FilePath,'FilePath') + '";Extended properties=Excel 8.0;'
 

SET 

	@Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + isnull(@OLEDBInitString,'OLEDB') + ''')...[Sheet1$] 

SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM  dbo.[Contact Audit List 2008]  WHERE AccountID ='''+@LOC + ''''

        

--EXEC (@Insert)

print @Insert

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22796416
Actually insted of ISNULLs better explicitly initialize all your variables before using them:
DECLARE 

	@OLEDBInitString varchar(1000),

	@FilePath varchar(1000),

	@FolderPath as varchar(1000),        

	@CopyCmd varchar(1000),

	@Insert varchar(1000),

	@LOC varchar(1000),

	@TemplateFilePath aS VARCHAR(1000)

SELECT 

	@OLEDBInitString	='OLDBstr',

	@FilePath			='FilePath',

	@FolderPath			='FolderPath/',        

	@LOC				='Location',

	@TemplateFilePath	='Template'
 

SET 

	@FilePath = @FolderPath + 'ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

SET 

	@CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'
 

--EXEC master.dbo.xp_cmdshell @CopyCmd, no_output

print @CopyCmd
 

SET 

	@OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 8.0;'
 

SET 

	@Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$] 

SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM  dbo.[Contact Audit List 2008]  WHERE AccountID ='''+@LOC + ''''

        

--EXEC (@Insert)

print @Insert

Open in new window

0
 

Author Comment

by:printmedia
ID: 22796753
Thanks for the reply Zberteoc.

I changed my dynamic sql to match yours (I think) I didn't move the Select to the top as you did because it gave me an error.

But when I run the script it completes successfully but doesn't create the excel files.
DECLARE

	@LOC varchar(80),

	@FolderPath varchar(255),

	@TemplateFilePath varchar(255),

	@SoftshareFolderPath varchar(255),

 @OLEDBInitString varchar(1000),

		@FilePath varchar(1000),	

		@CopyCmd varchar(1000),

 @Insert varchar(1000)
 
 

 

SET @TemplateFilePath = 'D:\Contact Audit Lists\2008\Contact Audit List Template.xls'

SET @FolderPath = 'D:\Contact Audit Lists\2008\'

SET @SoftshareFolderPath = '\\\\Win2k3-1\\Contact Audit Lists\\2008\\'

 

DECLARE c_MyCurs CURSOR LOCAL FAST_FORWARD FOR

SELECT DISTINCT AccountID

FROM      dbo.[Contact Audit List 2008]

OPEN c_MyCurs

FETCH NEXT FROM c_MyCurs INTO @LOC

WHILE @@FETCH_STATUS = 0

BEGIN
 

	SET @FilePath = @FolderPath + 'ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

	SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'

	--EXEC master.dbo.xp_cmdshell @CopyCmd, no_output

print @CopyCmd	

SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 8.0;'

	

	SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$] 

	SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM  dbo.[Contact Audit List 2008]  WHERE AccountID ='''+@LOC + ''''

	

--EXEC (@Insert)

	

	UPDATE 

       	 dbo.[Contact Audit List 2008]

	SET 

        	ATTACHMENT ='ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

	WHERE 

        ACCOUNTID= @LOC
 

	FETCH NEXT FROM c_MyCurs INTO @LOC

END

CLOSE c_MyCurs

DEALLOCATE c_MyCurs

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22796787
Did you get any error?

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:printmedia
ID: 22796808
When I ran this I did not, but it did not create the excel files.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22796815
Of course it didn't as the EXEC statements are both commented. Uncomment them and try again.
0
 

Author Comment

by:printmedia
ID: 22797135
Ok I uncommented both EXEC statements and it still gave me the "File format is not valid" when I open the attachment in the email sent via SQL Server. Below is what the code looks like:
DECLARE

	@LOC varchar(80),

	@FolderPath varchar(255),

	@TemplateFilePath varchar(255),

	@SoftshareFolderPath varchar(255),

 @OLEDBInitString varchar(1000),

		@FilePath varchar(1000),	

		@CopyCmd varchar(1000),

 @Insert varchar(1000)
 
 

 

SET @TemplateFilePath = 'D:\Contact Audit Lists\2008\Contact Audit List Template.xls'

SET @FolderPath = 'D:\Contact Audit Lists\2008\'

SET @SoftshareFolderPath = '\\\\Win2k3-1\\Contact Audit Lists\\2008\\'

 

DECLARE c_MyCurs CURSOR LOCAL FAST_FORWARD FOR

SELECT DISTINCT AccountID

FROM      dbo.[Contact Audit List 2008]

OPEN c_MyCurs

FETCH NEXT FROM c_MyCurs INTO @LOC

WHILE @@FETCH_STATUS = 0

BEGIN
 

	SET @FilePath = @FolderPath + 'ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

	SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'

	EXEC master.dbo.xp_cmdshell @CopyCmd, no_output

print @CopyCmd	

SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 8.0;'

	

	SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$] 

	SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM  dbo.[Contact Audit List 2008]  WHERE AccountID ='''+@LOC + ''''

	

EXEC (@Insert)

	

	UPDATE 

       	 dbo.[Contact Audit List 2008]

	SET 

        	ATTACHMENT ='ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

	WHERE 

        ACCOUNTID= @LOC
 

	FETCH NEXT FROM c_MyCurs INTO @LOC

END

CLOSE c_MyCurs

DEALLOCATE c_MyCurs

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22797354
comment back the exec statement and uncomment or leave uncommented the print statements. Run the script and then go to the Messages tab and check what was printed there. Copy a commend from there and try to run it in windows and see if you get the same error message. If you do is not a SQL code issue but there is something wrong to your files.
DECLARE

        @LOC varchar(80),

        @FolderPath varchar(255),

        @TemplateFilePath varchar(255),

        @SoftshareFolderPath varchar(255),

 @OLEDBInitString varchar(1000),

                @FilePath varchar(1000),        

                @CopyCmd varchar(1000),

 @Insert varchar(1000)

 

 

 

SET @TemplateFilePath = 'D:\Contact Audit Lists\2008\Contact Audit List Template.xls'

SET @FolderPath = 'D:\Contact Audit Lists\2008\'

SET @SoftshareFolderPath = '\\\\Win2k3-1\\Contact Audit Lists\\2008\\'

 

DECLARE c_MyCurs CURSOR LOCAL FAST_FORWARD FOR

SELECT DISTINCT AccountID

FROM      dbo.[Contact Audit List 2008]

OPEN c_MyCurs

FETCH NEXT FROM c_MyCurs INTO @LOC

WHILE @@FETCH_STATUS = 0

BEGIN

 

        SET @FilePath = @FolderPath + 'ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

        SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'
 

        --EXEC master.dbo.xp_cmdshell @CopyCmd, no_output

		print @CopyCmd  
 

		SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 8.0;'

        

        SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$] 

        SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM  dbo.[Contact Audit List 2008]  WHERE AccountID ='''+@LOC + ''''

        

		--EXEC (@Insert)

		print @Insert

        

        UPDATE 

         dbo.[Contact Audit List 2008]

        SET 

                ATTACHMENT ='ContactAuditList_' + @LOC  + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'  

        WHERE 

        ACCOUNTID= @LOC

 

        FETCH NEXT FROM c_MyCurs INTO @LOC

END

CLOSE c_MyCurs

DEALLOCATE c_MyCurs

Open in new window

0
 

Author Comment

by:printmedia
ID: 22797431
Where is the Messages tab?

I'm in the DTS and simply right clicking the SQL Task and selecting Execute Step
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 22797528
run the script in Query Analyzer firs to test it and you'll see the message tab. You never run a script directly in DTS without testing it in QA first.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now