• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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

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
printmedia
Asked:
printmedia
  • 6
  • 5
1 Solution
 
James MurrellProduct SpecialistCommented:
could you attache file excel?
0
 
printmediaAuthor Commented:
Attached is one of the excel files.
ContactAuditList-00-0001010-2008.xls
0
 
ZberteocCommented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
ZberteocCommented:
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
 
printmediaAuthor Commented:
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
 
ZberteocCommented:
Did you get any error?

0
 
printmediaAuthor Commented:
When I ran this I did not, but it did not create the excel files.
0
 
ZberteocCommented:
Of course it didn't as the EXEC statements are both commented. Uncomment them and try again.
0
 
printmediaAuthor Commented:
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
 
ZberteocCommented:
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
 
printmediaAuthor Commented:
Where is the Messages tab?

I'm in the DTS and simply right clicking the SQL Task and selecting Execute Step
0
 
ZberteocCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now