September 9, 2010 at 8:48 am
(And yes, I have ad hoc query enabled.)
I'm trying to export some data into an excel spreadsheet. This is actually being run through an SSIS package. Because the column names are dynamic, I resorted to using:
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="\\My UNC PATH (with IP Address)\MySpreadsheet.XLS";Extended Properties=Excel 8.0')...[MyExcelWorksheetTabName]
SELECT * FROM <dynamically generated table with the data to be exported>
Just before this statement, the package runs a CREATE TABLE against the Excel Destination, and it does create both the spreadsheet and the table (and the named range). Note that this also creates the column names in the first row of the spreadsheet tab. Note also that when creating the tab this way, that a named range of the same name as the tab name is created (without the $), so you can insert the data by either the named range, or the tab name. I've tried both.
When the INSERT runs, I'm getting this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
If I try to run it as just a select statement (replace "INSERT INTO" with "SELECT * FROM") from SSMS, I get the same error message.
BTW, this package runs just fine on my laptop that I developed it on.
SSIS and the SQL database engine services are both running under the same domain account on the production server.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 10, 2010 at 3:09 pm
WayneS (9/9/2010)
(And yes, I have ad hoc query enabled.)I'm trying to export some data into an excel spreadsheet. This is actually being run through an SSIS package. Because the column names are dynamic, I resorted to using:
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="\\My UNC PATH (with IP Address)\MySpreadsheet.XLS";Extended Properties=Excel 8.0')...[MyExcelWorksheetTabName]SELECT * FROM <dynamically generated table with the data to be exported>
Just before this statement, the package runs a CREATE TABLE against the Excel Destination, and it does create both the spreadsheet and the table (and the named range). Note that this also creates the column names in the first row of the spreadsheet tab. Note also that when creating the tab this way, that a named range of the same name as the tab name is created (without the $), so you can insert the data by either the named range, or the tab name. I've tried both.
When the INSERT runs, I'm getting this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
If I try to run it as just a select statement (replace "INSERT INTO" with "SELECT * FROM") from SSMS, I get the same error message.
BTW, this package runs just fine on my laptop that I developed it on.
SSIS and the SQL database engine services are both running under the same domain account on the production server.
Does your ssis service account have permissions to "\\My UNC PATH (with IP Address)\MySpreadsheet.XLS"?
September 13, 2010 at 7:38 am
AlexSQLForums (9/10/2010)
Does your ssis service account have permissions to "\\My UNC PATH (with IP Address)\MySpreadsheet.XLS"?
Yes. It first makes the directory (if not already there), deletes the spreadsheet (if already present), creates the spreadsheet, and then exports the data into the spreadsheet. The export is performed via OpenDataSource; all the other steps are performed with a file system task, or (to create the spreadsheet) an execute sql task via the Excel connection manager. Everything is occurring except for the actual export of the data, which is generating the error specified above.
I have tried running both OpenDataSource and OpenRowSet against a local spreadsheet; both generate the same error, even if just selecting data from the spreadsheet.
Other details that may be helpful (or may not...)
Windows Server 2003, 32-bit.
SQL Server 2005, Enterprise Edition, 32-bit.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 13, 2010 at 7:52 am
Wayne,
Not a solution so much as a troubleshooting tip.
Right before your Insert, but after the dynamically generated table & stuff, insert a Script Task to pop up a MsgBox with the Excel connection info. The command is really easy.
MsgBox("My Excel Destination is: " + CStr(DTS.Variables("Myvarname").Value) )
Do the same with your INSERT statement to see the dynamically generated table name. You'll have to hit okay after each message box pops up before the pkg continues, but it's a nice way of seeing how these values / variables are really populating, or if they are.
September 13, 2010 at 9:11 am
Brandie,
Thanks for the tip.
I don't think this is the issue. If I open up a query window in SSMS and directly enter :
select * from OpenRowSet (...)
I get the same error message, regardless if pointing to a local file or a file on the network file share.
Using the same command on the network file share that works on my laptop doesn't work on the server.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 13, 2010 at 9:44 am
WayneS (9/9/2010)
When the INSERT runs, I'm getting this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
If I try to run it as just a select statement (replace "INSERT INTO" with "SELECT * FROM") from SSMS, I get the same error message.
BTW, this package runs just fine on my laptop that I developed it on.
SSIS and the SQL database engine services are both running under the same domain account on the production server.
So if you only do the select you are still getting the Excel error? I assume that you are using an Execute SQL Task, right?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 13, 2010 at 10:06 am
Jack Corbett (9/13/2010)
So if you only do the select you are still getting the Excel error? I assume that you are using an Execute SQL Task, right?
Yes, doing just the select against the Excel spreadsheet gets the OLE DB error (not excel specific):
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Yes, I'm utilizing an Execute SQL Task to perform this export.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 14, 2010 at 9:21 am
FYI, I found these KB articles that might deal with this (although they mention pertaining only to SQL 7/2000, this is a JET issue and that is outside of SQL). Will keep you informed, but it does sound possible!
http://support.microsoft.com/kb/296711/EN-US/
http://support.microsoft.com/kb/814398
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 14, 2010 at 9:56 am
You've gotta love solutions that include logging in as a service account.
Also, I was under the impression that your Select was from SQL Server and Into an Excel Spreadsheet. So I guess I was misinterpreting you when you said that the you converted the INSERT INTO into a SELECT. I thought you meant you were just trying the select, but you meant you were attempting a select from the spreadsheet/workbook.
So it does look like you might have found the problem in those KB articles.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 27, 2010 at 11:16 am
Update: that didn't resolve it... back to square 1 :crying::angry:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 27, 2010 at 11:39 am
Wait. I'm confused. Are you selecting from an Excel spreadsheet or from a SQL Server table (dynamically generated)?
I know you're inserting INTO an Excel spreadsheet. I just don't understand the data source part.
September 27, 2010 at 2:13 pm
Brandie Tarvin (9/27/2010)
Wait. I'm confused. Are you selecting from an Excel spreadsheet or from a SQL Server table (dynamically generated)?I know you're inserting INTO an Excel spreadsheet. I just don't understand the data source part.
Step 1: select from tables into a dynamically generated table. (works fine)
Step 2: select from dynamically generated table into Excel spreadsheet.
Edit: I've also noticed that I get the same error when just selecting from an excel spreadsheet, so I've been using that method to speed up overall testing of this situation.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 28, 2010 at 4:42 am
Okay, so is the dynamically generated table a temp table or a "real" table?
You shouldn't have to use OpenDataSource in SSIS. I've been able to direct "traffic" to specific worksheets in Excel just by using a OLEDB Connection manager.
OLEDB Connection manager -> Use Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. Database file name = Excel workbook location. I don't use a login / password.
In Data Flow Task, use OLE DB Destination object. Use your Excel workbook Connection manager. Data access mode: Table or View. Name of the table or view: SheetName.
OLE DB Source is the SQL Server connection manager and uses Data Access Mode: Table or View and the name of the table I'm pulling from. Though I could just as easily use SQL Command (stored procedure or directly inputted code) or one of the variable options.
For this particular project, I do have a template excel spreadsheet that has all my tabs preconfigured and I make a renamed copy of the template so the template never gets overridden. But I don't think you have to do that.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply