June 9, 2015 at 5:15 am
Hi Team,
Using below statement to export a table from sql server 2008 to EXCEL 2010
Insert into Openrowset
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Export\XLS.xlsx;' ,
'SELECT * FROM [employees$]')
SELECT name,id,group,agency FROM dbo.employees
getting below ERROR
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
below changes also done.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Please help.
June 9, 2015 at 9:38 am
Minnu (6/9/2015)
Hi Team,Using below statement to export a table from sql server 2008 to EXCEL 2010
Insert into Openrowset
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Export\XLS.xlsx;' ,
'SELECT * FROM [employees$]')
SELECT name,id,group,agency FROM dbo.employees
getting below ERROR
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
below changes also done.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Please help.
That's not quite the right idea. You're trying to INSERT into an OPENROWSET, which isn't designed to handle an insert. Is this a one-time thing or something that needs to occur on a regular basis? I ask because it would be much easier to set this up as an SSIS package. As a one-time thing, you can use the Import/Export Wizard within SSMS to do it, and it can even create an SSIS package for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 9, 2015 at 3:34 pm
That's not quite the right idea. You're trying to INSERT into an OPENROWSET, which isn't designed to handle an insert. Is this a one-time thing or something that needs to occur on a regular basis? I ask because it would be much easier to set this up as an SSIS package. As a one-time thing, you can use the Import/Export Wizard within SSMS to do it, and it can even create an SSIS package for you.
+1
Joie Andrew
"Since 1982"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply