Exporting a Temp Table with Code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Exporting a Temp Table with Code

Author

Topic

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-17 : 17:37:18

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

52326 Posts

Posted - 2012-09-17 : 17:49:29

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/

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-17 : 17:58:15

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 18:02:18

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/

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-17 : 18:56:37

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 19:40:43

excel file is in local machine or server? also is local machine also 64 bit?

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

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-18 : 11:52:51

Excel file on local machine and local is a 32bit Win 7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:11:09

did you install ACE driver in server?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources