exporting to excel using stored procedure and filename in a variable

Question

Post reply

exporting to excel using stored procedure and filename in a variable

luisalfonso70

SSC-Addicted

Points: 410
More actions
May 28, 2009 at 1:14 am

#131540

hi, I wish to make avery day an excel file with the information of a table, so I'll make a stored procedure for copy a excel file template to the same directory but adding at the end of the file the date,after do that I'll generate a query to populate the excel file.
here is my code:
DECLARE @rc int
DECLARE @dt varchar(8)
DECLARE @cmd nvarchar(1000)
declare @newfile nvarchar(100)
SELECT @dt = Convert(varchar(8),getdate(),112)
SET @cmd = 'copy i:\toluca\toluca.xls i:\toluca\toluca_'+@dt+'.xls'
EXEC @rc = master.dbo.xp_cmdshell @cmd
Exec master..xp_cmdshell @cmd
set @newfile='i:\toluca\toluca_'+@dt+'.xls'
SET QUOTED_IDENTIFIER On
insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database='+@newfile+';;HDR=YES',
'SELECT * FROM [ayunt$]')
select * from acci.dbo.mun125
after execute it I get the next error:
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '+'.
I've tried to use double quotes and still having the seame error, any ideas?
thsnks

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

OPENROWSET does not accept variables for its arguments.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

You need to build dynamic sql and then run the query using EXECUTE statement. For e.g.

DECLARE @SQL VARCHAR(MAX), @Table VARCHAR(100)

SET @Table = 'sys.objects'

SET @SQL = 'SELECT * FROM ' + @Table

EXECUTE( @SQL )

--Ramesh

Tom257 SSChampion Points: 11543 More actions · Answer 3

I tried this - changing your table names to match my system

DECLARE @dt varchar(8)

declare @newfile nvarchar(100)

SELECT @dt = Convert(varchar(8),getdate(),112)

set @newfile='C:\temp\XL_'+@dt+'.xls'

Declare @XLString nvarchar(4000);

SET @XLString='

insert into OPENROWSET(

''Microsoft.Jet.OLEDB.4.0'',

''Excel 8.0;Database=C:\temp\'+@newfile+''',

''SELECT * FROM [tab1$]'')

select * from sys.syslogins'

EXEC (@XLString);

But I'm still getting errors 7399

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

- though that could be due to something else like security - give it go - it might just work for you.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Tom Brown (5/28/2009)
I tried this - changing your table names to match my system
.....
But I'm still getting errors 7399
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
- though that could be due to something else like security - give it go - it might just work for you.

For this to work, you have to have an existing excel worksheet.

--Ramesh

Tom257 SSChampion Points: 11543 More actions · Answer 5

I have an existing spreadsheet, I'd changed permissions to everyone full control, made sure it was no longer open in excel - and its still failing. I must be missing something.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

Tom, I wonder why its not working on your system, it works fine on my machine without granting any explicit permissions on the folder to a normal db_owner user (though I needed to change the registry setting "DisallowAdhocAccess" for the provider to allow adhoc access).

I suspect there must be something missing here.;-)

Just for a thought, can you verify the sheet name used in query with that of the actual excel file?

--Ramesh

Tom257 SSChampion Points: 11543 More actions · Answer 7

Yes most Odd

even without the dynamic part its failing

insert into OPENROWSET(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Temp\XL_20090528.xls',

'SELECT * FROM [tab1$]')

select * from sys.syslogins

I've verified the sheet name matches, tried renaming with and without the '$' - but no change. I restarted the server and the error message changed very slightly. It now gives the first line in black, and omits the 'Message 7399' part in red - which was the first line.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I have other linked servers and OPENROWSETs working fine, I have the Ad Hoc setting, its just this writing into and XLS file that I can't get working.

Its not important to me, just annoying.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

I am just running out of ideas:w00t:, that's what you don't get when working late:-D. Well, one thing that I am sure of is that its definitely a file access/security issue. Since you mentioned other OPENROWSETs are working fine, I might just want to try copying the erroneous file into this directory where the OPENROWSETs are working fine.

--Ramesh

Tom257 SSChampion Points: 11543 More actions · Answer 9

OK - the other Openrowsets WERE working fine yesterday (I didn't check earlier), however today they don't work at all. Nothing using the Jet driver works now. I've tried rebooting, changing the TEMP directory used by the SQLServer account so I can view the goings on using this ref:

http://blogs.msdn.com/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

I had a theory that it was something to do with SharePoint / SharePoint Designer - but no - I've stopped all those services and restarted SQL Server - still the same error.

Now it has become a problem for me because I'm working with a linked Access database - and that link uses the Jet driver too :sick:

Alberto dbLearner Ten Centuries Points: 1246 More actions · Answer 10

Try using Linked Server instead of Openrowset, it's kind of slow but it worked for me

NOTE:

--I've tried this with Excel 2003, not 2007

--Its kind of tricky regardind data types

--CREATE LINKED SERVER

--You first need to create a spreadsheet with the headers you need

EXEC sp_addlinkedserver

@server = ExcelLink,

@srvproduct = 'Excel',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@datasrc = 'c:\MyDir\MySpreadSheet.xls',

@provstr = 'Excel 8.0'

Go

--CREATE SECURITY FOR LINKED SERVER

--NOte that theres no special security, no password

EXEC sp_addlinkedsrvlogin

@rmtsrvname = ExcelLink,

@useself = false,

@locallogin = Null,

@rmtuser = Admin,

@rmtpassword = Null

Go

--WITH THIS YOU GET THE SHEETS IN THE WORKBOOK, THEY ARE RECONGNIZED AS TABLES

EXECUTE SP_TABLES_EX Excellink

--WITH THESE YOU GET THE HEADERS IN THE SHETS, AS FIELDS

EXECUTE SP_COLUMNS_EX Excellink

--ASUMING THERE IS A SHEET CALLED Sheet1, YOU CAN BROWSE IT

select * from Excellink...Sheet1$

--or

select * from openquery(ExcelLink, 'Select * from [Sheet1$]')

--YOU CAN INSERT DATA IN TO THE SHEET AS IT WAS A TABLE:

Insert Into Excellink...Sheet1$ (

NAME,

ID,

DOC)

values (

'My Name',

'1',

'123')

--OR

Insert Into Excellink...Sheet1$ (

NAME,

ID,

DOC)

SELECT NAME, ID, DOC FROM MyTable

If you want to shut down the linked server, do this:

Exec sp_droplinkedsrvlogin Excellink, null

Exec sp_dropserver Excellink

I hope to get a lot of comments about this

Good Luck

Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader

Tom257 SSChampion Points: 11543 More actions · Answer 11

Thanks for the advice, but somethings gone seriously kaputt with my 'Microsoft.Jet.OLEDB.4.0' driver - linked server returns the same error as openrowset

I have temporarily got round the problem by using the import and export wizard for the tables I'm working with.