August 10, 2010 at 9:20 am
i am creating a proc that exports data to excel then emails it using database mail but have stumbled on a couple of errors.
first when i COPY a template file i can only use 24 characters it seems, is this true or is there anyway around it, the code is below:
SET @FILENAME = 'COPY /Y D:\TEST.xls /B D:\'+ @XPRACTICE +'.XLS'
so if @xpractice = SQLSERVERCENTRALFORUM
then i get
SQLSERVERCENTRALFORUM.xl
as a filename
Secondly how can i make the following OPENROWSET use the above filename once it is created, i have tried:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\'+ @XPRACTICE +'.XLS;','Select * from [DATA$]')
but get an error at '+'
cheers for any help
August 10, 2010 at 9:33 am
andyc209 (8/10/2010)
i am creating a proc that exports data to excel then emails it using database mail but have stumbled on a couple of errors.first when i COPY a template file i can only use 24 characters it seems, is this true or is there anyway around it, the code is below:
SET @FILENAME = 'COPY /Y D:\TEST.xls /B D:\'+ @XPRACTICE +'.XLS'
so if @xpractice = SQLSERVERCENTRALFORUM
then i get
SQLSERVERCENTRALFORUM.xl
as a filename
Secondly how can i make the following OPENROWSET use the above filename once it is created, i have tried:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\'+ @XPRACTICE +'.XLS;','Select * from [DATA$]')
but get an error at '+'
cheers for any help
1. How you declared @FILENAME? I believe you declared it to small.
2. You can't perform string conacatination inside of OPENROWSET. Declare separate string variable for your datasource:
DECLARE @datasource VARCHAR(4000)
SET @datasource = 'Excel 8.0;Database=D:\'+ @XPRACTICE +'.XLS;'
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', @datasource ,'Select * from [DATA$]')
August 10, 2010 at 9:44 am
i declared @filename:
DECLARE @FILENAME AS VARCHAR(50)
August 10, 2010 at 9:52 am
DECLARE @datasource as VARCHAR(4000)
SET @datasource = 'Excel 8.0;Database=D:\'+ @XPRACTICE +'.XLS;'
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', @datasource ,'Select * from [DATA$]')
i get an Incorrect syntax near '@datasource'
August 10, 2010 at 10:20 am
The length of "'COPY /Y D:\TEST.xls /B D:\'+ @XPRACTICE +'.XLS'" when @XPRACTICE = 'SQLSERVERCENTRALFORUM' will be 51.
Therefore you have cut-off in result. Don't be greedy :-D, declare @FILENAME as VARCHAR(255) for example.
Yes, OPENROWSET expects string literals only. You will need to use dynamic SQL to do what you want:
DECLARE @sql NVARCHAR(4000)
SET @sql = N'INSERT INTO OPENROWSET (''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=D:\'+ @XPRACTICE + '.XLS;'',''Select * from [DATA$]'')'
EXEC (@Sql)
August 11, 2010 at 2:28 am
still getting an error - altered your code slightly, here is all the code below for this bit - @pracid is declared in code above it (works fine)
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
DECLARE @FILEDAY AS VARCHAR(50)
DECLARE @FILEMONTH AS VARCHAR(50)
DECLARE @FILEYEAR AS VARCHAR(50)
DECLARE @FILENAME AS VARCHAR(255)
DECLARE @ATTACHMENT AS VARCHAR(500)
SET @FILEDAY = DATEPART("DD",GETDATE())
SET @FILEMONTH = DATEPART("MM",GETDATE())
SET @FILEYEAR = DATEPART("YYYY",GETDATE())
SET @FILENAME = 'COPY /Y D:\TEST.xls /B D:\'+ @XPRACTICE +'.XLS'
SET @ATTACHMENT= 'D:\'+ @XPRACTICE +'.XLS'
PRINT @ATTACHMENT
DECLARE @xlFileName nvarchar(255)
SET @xlFileName=N'D:\'+ @XPRACTICE +'.XLS'
DECLARE @sql nvarchar(2000)
SET @sql='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@xlFileName+''',''Select * from [DATA$]'')'
PRINT @sql
EXEC(@SQL)
SELECT COMPANY,LOANREF,AGREEMENTNUMBER,SOLREF,FIRSTNAME,SURNAME,ADD1,ADD2,ADD3,POSTCODE,CAPITAL,INTEREST,FEES,BALANCE,STARTDATE,DUEDATE,LOANTYPE FROM TBL_STATEMENTS where PRACID = @PRACID
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC master..xp_cmdshell @FILENAME
i keep getting the file created but no data in it with the error line
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=D:\Eatons.XLS','Select * from [DATA$]')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
August 11, 2010 at 3:16 am
Your sql statement is incomplete:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=D:\Eatons.XLS','Select * from [DATA$]')
Insert what? Have you looked into syntax of statement you are using?
Google usually helps...
It should look something like:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from [SQLServerTable]
So, your error tells you that SQL expects something more after ')'.
August 11, 2010 at 4:05 am
thanks for the help - these are my first go a procs.
tried bringing the select statement in but it now says i Must declare the scalar variable "@PRACID".
i have listed the whole code as i have declared it
DECLARE @PRACID AS INT
DECLARE @PRACTICE AS nvarchar(100)
DECLARE GET_PRAC SCROLL CURSOR FOR
select PRACID, SHORTNAME from TBL_PRACTICE WHERE STATEMENT = 1
OPEN GET_PRAC
FETCH NEXT FROM GET_PRAC
INTO
@PRACID,
@PRACTICE
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @XPRACTICE AS VARCHAR(100)
SET @XPRACTICE = REPLACE(@PRACTICE,' ','_')
-- **************************************************
-- WRITE THE FILE TO BE MAILED
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
DECLARE @FILEDAY AS VARCHAR(50)
DECLARE @FILEMONTH AS VARCHAR(50)
DECLARE @FILEYEAR AS VARCHAR(50)
DECLARE @FILENAME AS VARCHAR(255)
DECLARE @ATTACHMENT AS VARCHAR(500)
SET @FILEDAY = DATEPART("DD",GETDATE())
SET @FILEMONTH = DATEPART("MM",GETDATE())
SET @FILEYEAR = DATEPART("YYYY",GETDATE())
SET @FILENAME = 'COPY /Y D:\TEST.xls /B D:\'+ @XPRACTICE +'.XLS'
SET @ATTACHMENT= 'D:\'+ @XPRACTICE +'.XLS'
PRINT @ATTACHMENT
DECLARE @xlFileName nvarchar(255)
SET @xlFileName=N'D:\'+ @XPRACTICE +'.XLS'
--EXEC ('
--insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
--''Excel 8.0;Database=' + @xlFileName + ';'',
--''SELECT * FROM [DATA$]'')
--SELECT COMPANY,LOANREF,AGREEMENTNUMBER,SOLREF,FIRSTNAME,SURNAME,ADD1,ADD2,ADD3,POSTCODE,CAPITAL,INTEREST,FEES,BALANCE,STARTDATE,DUEDATE,LOANTYPE FROM TBL_STATEMENTS where PRACID = 1
--')
DECLARE @sql nvarchar(2000)
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@xlFileName+''',''Select * from [DATA$]'')SELECT COMPANY,LOANREF,AGREEMENTNUMBER,SOLREF,FIRSTNAME,SURNAME,ADD1,ADD2,ADD3,POSTCODE,CAPITAL,INTEREST,FEES,BALANCE,STARTDATE,DUEDATE,LOANTYPE FROM TBL_STATEMENTS where PRACID = @PRACID;'
PRINT @sql
EXEC(@SQL)
--SELECT COMPANY,LOANREF,AGREEMENTNUMBER,SOLREF,FIRSTNAME,SURNAME,ADD1,ADD2,ADD3,POSTCODE,CAPITAL,INTEREST,FEES,BALANCE,STARTDATE,DUEDATE,LOANTYPE FROM TBL_STATEMENTS where PRACID = @PRACID
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC master..xp_cmdshell @FILENAME
--- END LOOP THROUGH ALL THE PRACTICES ---
FETCH NEXT FROM GET_PRAC
INTO
@PRACID,
@PRACTICE
END
CLOSE GET_PRAC
DEALLOCATE GET_PRAC
August 11, 2010 at 4:25 am
Change to:
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@xlFileName+''',''Select * from [DATA$]'')SELECT COMPANY,LOANREF,AGREEMENTNUMBER,SOLREF,FIRSTNAME,SURNAME,ADD1,ADD2,ADD3,POSTCODE,CAPITAL,INTEREST,FEES,BALANCE,STARTDATE,DUEDATE,LOANTYPE FROM TBL_STATEMENTS where PRACID = ' + CAST(@PRACID as varchar(11)) + ';'
As you had it before, @PRACID would be local variable in the SQL string you was trying to execute, and it is not declared there...
BTW, there is another, better and affer way to execute dynamic SQL - using SP_EXECUTESQL (it allows passing parameters and stops SQL injection), however in your case, I guess, EXEC (@SQL) will do just fine.
August 11, 2010 at 4:43 am
hopefully nearly there
i have changed where you suggested
DECLARE @xlFileName nvarchar(255)
SET @xlFileName=N'D:\'+ @XPRACTICE +'.XLS'
PRINT @pracid
DECLARE @sql as nvarchar(2000)
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@xlFileName+''',''Select * from [DATA$]'')SELECT COMPANY,LOANREF,AGREEMENTNUMBER,SOLREF,FIRSTNAME,SURNAME,ADD1,ADD2,ADD3,POSTCODE,CAPITAL,INTEREST,FEES,BALANCE,STARTDATE,DUEDATE,LOANTYPE FROM TBL_STATEMENTS where PRACID = ' + CAST(@PRACID as varchar(11)) + ';'
PRINT @sql
EXEC(@SQL)
but now i the Copy bit does not work
SET @FILENAME = 'COPY /Y D:\TEST.xls /B D:\'+ @XPRACTICE +'.XLS'
SET @ATTACHMENT= 'D:\'+ @XPRACTICE +'.XLS'
so it crashes with the following error
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'DATA$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "Select * from [DATA$]" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
which i assume is because the files do not exist.
what is strange is that if i remove all the bit with openrowset in the first block of code the COPY bit works fine.
If i run it with the files already there ino data gets posted yet if i check the select query in query analyzer it pulls the data i require so i know this is right.
sorry about this
August 11, 2010 at 4:54 am
What crashes with the mentioned error? Why you think that it's related to COPY?
The error clearly says that it cannot find object 'DATA$' - there is nothing bout COPY. Do you have the sheet named as DATA in your destination XLS file?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply