SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Exporting a Temp Table with Code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

txgeekgirl
Starting Member

18 Posts

Posted - 09/17/2012 :  17:37:18  Show Profile  Reply with Quote
I would like to export a tempTable to an Excel spreadsheet via code so I can drop the table later.

The only thing that seems to work is to use the SSIS Wizard - which I do not want to do as it means this task cannot me automated.

I am looking for a simple export solution but this one seems to not be working (syntax error like it's missing something) Any other suggestions?


INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;', 'SELECT * FROM dbo.Test') 
	

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/17/2012 :  17:49:29  Show Profile  Reply with Quote

see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

also reg. SSIS it can automated so long as you save it as a package and execute it from SQL agent job created on a predefined schedule

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

txgeekgirl
Starting Member

18 Posts

Posted - 09/17/2012 :  17:58:15  Show Profile  Reply with Quote
I actually was just reading that artice having found it in a previous post.

I modified my code to:

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;', 'SELECT * FROM [Test]') SELECT * FROM dbo.Test	


Error: Msg 7308, Level 16, State 1, Line 1
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/17/2012 :  18:02:18  Show Profile  Reply with Quote
see

http://www.sqlservergeeks.com/forums/microsoft-data-platform/sql-server-bi/37/ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-configured-to-run-in-single-threaded-apartment-mode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

txgeekgirl
Starting Member

18 Posts

Posted - 09/17/2012 :  18:56:37  Show Profile  Reply with Quote
OK - So if I changed the connection from a Jet to an ACE and the version types and got the:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Then downloaded the Microsoft Access Driver to get the driver to run that code - but that did not rectify the error

Is there anything else I can do?

I am running Microsoft 2010 Excel on a Win 7 32 bit O/S with SQL on a 64bit server but running the code from my local machine through SSMS
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/17/2012 :  19:40:43  Show Profile  Reply with Quote
excel file is in local machine or server? also is local machine also 64 bit?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

txgeekgirl
Starting Member

18 Posts

Posted - 09/18/2012 :  11:52:51  Show Profile  Reply with Quote
Excel file on local machine and local is a 32bit Win 7
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/18/2012 :  13:11:09  Show Profile  Reply with Quote
did you install ACE driver in server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000