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
 General SQL Server Forums
 New to SQL Server Programming
 Export Tables to Excel - Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Angeline87
Starting Member

7 Posts

Posted - 02/08/2013 :  16:53:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3554 Posts

Posted - 02/08/2013 :  17:31:25  Show Profile  Reply with Quote
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.

Edited by - James K on 02/08/2013 17:33:47
Go to Top of Page

Angeline87
Starting Member

7 Posts

Posted - 02/08/2013 :  17:59:30  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3554 Posts

Posted - 02/08/2013 :  18:51:39  Show Profile  Reply with Quote
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?
Go to Top of Page

Angeline87
Starting Member

7 Posts

Posted - 02/08/2013 :  19:24:17  Show Profile  Reply with Quote
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.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3554 Posts

Posted - 02/08/2013 :  19:51:02  Show Profile  Reply with Quote
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.
Go to Top of Page

Angeline87
Starting Member

7 Posts

Posted - 02/08/2013 :  20:31:14  Show Profile  Reply with Quote
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...
Go to Top of Page

Angeline87
Starting Member

7 Posts

Posted - 02/17/2013 :  06:49:58  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3554 Posts

Posted - 02/17/2013 :  08:33:42  Show Profile  Reply with Quote
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.
Go to Top of Page

uapi100
Starting Member

Canada
5 Posts

Posted - 02/20/2013 :  08:41:04  Show Profile  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000