Export Tables to Excel - Error

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

General SQL Server Forums

New to SQL Server Programming

Export Tables to Excel - Error

Author

Topic

Angeline87
Starting Member

7 Posts

Posted - 2013-02-08 : 16:53:03

Hi to everybody

I used to use an application, which creates an SQL Query that exports all Tables of a Database to an Excelsheet. It worked very good for a long time (I worked with MS SQL Server 2005 and MS SQL Server 2008 R2). As an Office Suite I worked with MS Office 2003 and I had Windows XP.

Now, I work on a new computer with
- MS SQL Server 2008 R2 and
- MS Office 2007
- Windows 7

And the skript does not work anymore. I am not able to find the root cause of the problem (I am not the developer person but the user and therefore I only have basic know how in SQL and T-SQL).

Can anybody help me?

This is the SQL-Code:
-------Excel erzeugen

DECLARE @pfad varchar(255)
SET @pfad = 'F:\\08-02-2013_22-31\'
DECLARE @hr int -- Returncode der sp_OA... Aufrufe
DECLARE @katalog int -- Objektvariable für ADOX.Catalog
DECLARE @verbindung int -- Objektvariable für ADO.Connection
DECLARE @dbname varchar(255) -- Name der temporären Access-Datenbank
DECLARE @conString varchar(512) -- Verbindungszeichenfolge für Access-Datenbank
DECLARE @quelle varchar(255) -- Hilfsfeld für Fehlerbehandlung
DECLARE @beschreibung varchar(255) -- Hilfsfeld für Fehlerbehandlung
DECLARE @dummy int -- Dummy Output Parameter
DECLARE @Excel_Mappe varchar(255) -- Vollständiger Name der Ziel Excelmappe
DECLARE @tabelle varchar(255) -- Name der zu exportierenden Tabelle
DECLARE @besitzer varchar(255) -- Besitzer der zu exportierenden Tabelle
DECLARE @exec varchar(4000) -- Hilfsvariable für dynamische Ausführungen

-- Cursor zum Ermitteln aller Tabellen ohne Text- und Image-Spalten
DECLARE curTabellen CURSOR FAST_FORWARD FOR
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
AND NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text', 'ntext', 'image')
AND TABLE_CATALOG = t.TABLE_CATALOG AND
TABLE_SCHEMA = t.TABLE_SCHEMA AND
TABLE_NAME = t.TABLE_NAME)
ORDER BY TABLE_NAME

-- Erzeugen eines ADOX-Katalog Objekts
EXEC @hr = master.dbo.sp_OACreate 'ADOX.Catalog', @katalog OUTPUT
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @katalog, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Erstellen des ADOX.Catalog Objekts: %s', 10 , -1, @beschreibung)
END

-- Zufälliges Erzeugen eines Datenbank-Namens
SET @dbname = @pfad + CAST(newid() AS varchar(100)) + '.MDB'
-- Erstellen der Verbindungszeichenfolge
SET @conString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @dbname

-- Erstellen einer neuen (leeren) Access-Datenbank
-- Dieser Schritt ist notwendig, da nur über eine Verbindung zu einer Access-Datenbank
-- die benötigte Jet-Funktionalität zur Verfügung steht
EXEC @hr = master.dbo.sp_OAMethod @katalog, 'Create',
@dummy output,
@conString
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @katalog, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Erstellen der Access-Datenbank %s: %s', 10 , -1, @dbname, @beschreibung)
END

-- Variable @verbindung auf die Eigenschaft "ActiveConnection" des Katalog-Objekts setzen
EXEC @hr = master.dbo.sp_OAGetProperty @katalog, 'ActiveConnection', @verbindung OUTPUT
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Zugriff auf Access-Datenbank %s: %s', 10 , -1, @dbname, @beschreibung)
END

-- Öffnen des Cursors und Export für alle gefundenen Tabellen durchlaufen
OPEN curTabellen
FETCH NEXT FROM curTabellen INTO @besitzer, @tabelle
WHILE @@FETCH_STATUS = 0
BEGIN
-- Dynamisches Erzeugen der SELECT INTO Anweisung
SET @exec = 'SELECT TOP 65535 * INTO [Excel 8.0;Database=' + @pfad + db_name() + '.xls].[' + @besitzer + '_' + @tabelle + '] FROM [ODBC;Driver=SQL Server;Database=' + DB_NAME() + ';Server=' + @@SERVERNAME + ';Trusted_Connection=Yes;].[' + @besitzer + '.' + @tabelle + ']'
EXEC @hr = master.dbo.sp_OAMethod @verbindung, 'Execute', @dummy output, @exec
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Export: (%s): %s', 10 , -1, @exec, @beschreibung)
END
FETCH NEXT FROM curTabellen INTO @besitzer, @tabelle
END

-- "Aufräumarbeiten"
CLOSE curTabellen
DEALLOCATE curTabellen

EXEC @hr = master.dbo.sp_OAMethod @verbindung, 'Close'
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Schliessen der Verbindung zur Access-Datenbank: %s', 10 , -1, @beschreibung)
END
EXEC @hr = master.dbo.sp_OADestroy @verbindung
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Zerstören des ADO-Connection Objekts: %s', 10 , -1, @beschreibung)
END
EXEC @hr = master.dbo.sp_OADestroy @katalog
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Zerstören des ADO-Connection Objekts: %s', 10 , -1, @beschreibung)
END
-- Temporäre Access-Datenbank löschen
SET @exec = 'DEL "' + @dbname + '"'
EXEC master.dbo.xp_cmdshell @exec ,NO_OUTPUT
GO

And this is the error message:

Fehler beim Erstellen der Access-Datenbank F:\\08-02-2013_22-31\863D2122-986A-4D8D-A401-B9EE78DF7EEF.MDB: Klasse nicht registriert
Fehler beim Zugriff auf Access-Datenbank F:\\08-02-2013_22-31\863D2122-986A-4D8D-A401-B9EE78DF7EEF.MDB: Typkonflikt.
Fehler beim Export: (SELECT TOP 65535 * INTO [Excel 8.0;Database=F:\\08-02-2013_22-31\Total.xls].[dbo_Buch] FROM [ODBC;Driver=SQL Server;Database=Total;Server=NB;Trusted_Connection=Yes;].[dbo.Buch]): Syntax für sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, zusätzliche IN-, OUT- oder BOTH-Parameter]].
Fehler beim Schliessen der Verbindung zur Access-Datenbank: Syntax für sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, zusätzliche IN-, OUT- oder BOTH-Parameter]].
Fehler beim Zerstören des ADO-Connection Objekts: Syntax für sp_OADestroy: ObjPointerToBeDestroyed int IN.

Any Ideas how to fix it?

Thanks a lot
Angeline

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 17:31:25

With Office 2007 the connection strings are different; you have to use Microsoft.ACE.OLEDB.12.0. See examples of connection strings here: http://connectionstrings.com/excel-2007

My skills at German are rather limited - I know just two words Einfahrt and Ausfahrt, and that too only because I see that on every highway. So the meaning of the error messages may have been lost to me in translation.

Angeline87
Starting Member

7 Posts

Posted - 2013-02-08 : 17:59:30

Hi James

Thanks for your answer. Einfahrt and Ausfahrt; two important german words ;-)

Yes, that will be the problem I am running in. Because I have not written the code myself and I don't know exactly how it works I am still not able to fix it.

The way it worked: MS SQL Server Database --> MS Access Database (.MDB) --> MS Excel.
The Access Database is been created with a random name and destroyed after creation of the Excel-File

-- Zufälliges Erzeugen eines Datenbank-Namens
SET @dbname = @pfad + CAST(newid() AS varchar(100)) + ' .MDB' --> What has to be here? .accdb? -- Erstellen der Verbindungszeichenfolge
SET @conString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @dbname --> What has to be here? Microsoft.ACE.OLEDB.12.0

If I change the code as shown above; it still does not work??

Any ideas?

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 18:51:39

That seems correct. Print out the @conString after the set statement and see what it is, and compare to the connection strings on this page (which is for Access 2007) http://connectionstrings.com/access-2007 Then, if it displays an error while trying to create the file, can you post the exact error message?

Angeline87
Starting Member

7 Posts

Posted - 2013-02-08 : 19:24:17

Now I use the following code

DECLARE @pfad varchar(255)
SET @pfad = 'F:\\09-02-2013_00-15\'
DECLARE @hr int -- Returncode der sp_OA... Aufrufe
DECLARE @katalog int -- Objektvariable für ADOX.Catalog
DECLARE @verbindung int -- Objektvariable für ADO.Connection
DECLARE @dbname varchar(255) -- Name der temporären Access-Datenbank
DECLARE @conString varchar(512) -- Verbindungszeichenfolge für Access-Datenbank
DECLARE @quelle varchar(255) -- Hilfsfeld für Fehlerbehandlung
DECLARE @beschreibung varchar(255) -- Hilfsfeld für Fehlerbehandlung
DECLARE @dummy int -- Dummy Output Parameter
DECLARE @Excel_Mappe varchar(255) -- Vollständiger Name der Ziel Excelmappe
DECLARE @tabelle varchar(255) -- Name der zu exportierenden Tabelle
DECLARE @besitzer varchar(255) -- Besitzer der zu exportierenden Tabelle
DECLARE @exec varchar(4000) -- Hilfsvariable für dynamische Ausführungen

-- Cursor zum Ermitteln aller Tabellen ohne Text- und Image-Spalten
DECLARE curTabellen CURSOR FAST_FORWARD FOR
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
AND NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text', 'ntext', 'image')
AND TABLE_CATALOG = t.TABLE_CATALOG AND
TABLE_SCHEMA = t.TABLE_SCHEMA AND
TABLE_NAME = t.TABLE_NAME)
ORDER BY TABLE_NAME

-- Generate ADOX-Katalog Object
EXEC @hr = master.dbo.sp_OACreate 'ADOX.Catalog', @katalog OUTPUT
IF @hr <> 0 -- Error Handling
BEGIN
EXEC sp_OAGetErrorInfo @katalog, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Erstellen des ADOX.Catalog Objekts: %s', 10 , -1, @beschreibung)
END

-- Zufälliges Erzeugen eines Datenbank-Namens
SET @dbname = @pfad + CAST(newid() AS varchar(100)) + '.accdb'
-- Erstellen der Verbindungszeichenfolge
SET @conString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + @dbname

Print @conString

-- Erstellen einer neuen (leeren) Access-Datenbank
-- Dieser Schritt ist notwendig, da nur über eine Verbindung zu einer Access-Datenbank
-- die benötigte Jet-Funktionalität zur Verfügung steht
EXEC @hr = master.dbo.sp_OAMethod @katalog, 'Create',
@dummy output,
@conString
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @katalog, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Erstellen der Access-Datenbank %s: %s', 10 , -1, @dbname, @beschreibung)
END

....

I get the following output

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\09-02-2013_00-15\FD792C59-B0EA-41A4-87BE-13E4C6AD1C17.accdb --> print Statement
Fehler beim Erstellen der Access-Datenbank F:\\09-02-2013_00-15\FD792C59-B0EA-41A4-87BE-13E4C6AD1C17.accdb: Klasse nicht registriert --> Error; Text of the error message is user defined; see code above and means in Englisch: Error in generating Access database ... Class not registered

I also tried with File-Path with only one \ not \\. Does not work.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 19:51:02

Are you on a 64-bit windows? Is SQL Server 32 or 64? I know there are issues related to this with ACE drivers, but not familiar enough with the details. Post whether you have 64 or 32, that would help others who may be more familiar with the issue to respond appropriately.

Angeline87
Starting Member

7 Posts

Posted - 2013-02-08 : 20:31:14

Windows 7 64bit
MS SQL Server 2008 R2 64bit...

!?

It's some kind of a mystery to bugfix such little things. It already takes me days...

Angeline87
Starting Member

7 Posts

Posted - 2013-02-17 : 06:49:58

Maybe the problem is here: I run an Office 2007 32bit Version and I have installed the Microsoft Access Database engine 2007 32bit Version.

Could that be the problem? If yes: how to solve it?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-17 : 08:33:42

Usually I have seen people running into problems when they use 64-bit versions rather than 32-bit, supposedly because some of the drivers are available only for 32 bit.

I don't have enough familiarity with the ACE drivers to be able to diagnose the cause of the problem or suggest anything useful. Let us hope that someone else who is more familiar will chime in.

uapi100
Starting Member

5 Posts

Posted - 2013-02-20 : 08:41:04

Hello guys..When people install the window XP and after that installation driver on the window XP is 64-bit then error is occurred.At this situation Excel sheet create problem.Just install the suitable driver with the window then error is automatically disappeared.

do good have good

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources