February 8, 2013 at 3:04 pm
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
- MS Office 2003
- 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
February 8, 2013 at 4:00 pm
It would have helped if you were to translate the German to English and you were using Access. I have taken the liberty to do the translation below.
Failed to create the Access database F: \ \ 08-02-2013_22-31 \ 863D2122-986A-4D8D-A401-B9EE78DF7EEF.MDB: Class not registered
Error accessing Access database F: \ \ 08-02-2013_22-31 \ 863D2122-986A-4D8D-A401-B9EE78DF7EEF.MDB: Type mismatch.
Export Error: (SELECT TOP 65535 * INTO [Excel 8.0; Database = C: \ \ 08-02-2013_22-31 \ Total.xls] [dbo_Buch] FROM [ODBC; Driver = SQL Server; Database = total; server. = NB; Trusted_Connection = Yes;] [dbo.Buch]): syntax sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @ returnval <any> OUT [, additional IN, OUT, or BOTH parameter.]].
Error when closing the connection to the Access database: Syntax sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @ returnval <any> OUT [, additional IN, OUT, or BOTH parameter]].
Error while destroying the ADO Connection object: Syntax sp_OADestroy: ObjPointerToBeDestroyed int IN.
Please read Microsoft support article:
February 8, 2013 at 4:08 pm
Additionally, was the source server (F:\) office updated as well? There were drastic changes in Office 2003 to 2007. You will need to be on the same office versions between client and server.
February 8, 2013 at 4:36 pm
Hi SQLSeTTeR
Thanks for your answer. It must be a problem of different Office-Versions. The above code worked fine with MS Office 2003; but I guess the connection can not be set up for Office 2007. Probably I need to change only some parameters in the above code; but due to my very limited T-SQL knowledge I fail to identify where to change what..
I have not written the above code myself. Thus I am not able to fully understand the logic behind it. But the process is the following
1. Java Application generates an SQL Code based on user selections / inputs (not part of the above code)
2. The code is run automatically with sqlcmd.exe and generates a database with tables in respect to the user inputs from # 1. The names of the database as well as of the included tables are dynamic (not alwallys the same) (not part of the above code)
3. The generated database with the included tables shall be stored in an excel file. The sql code to do this also part of the SQL code generated via java application in #1 - that is the code I pasted in in my first posting. This code
- first generates a MS Access Database with a random name
- then generates an Excel Sheet in which all tables of the database get stored
- then the MS Access Database is destroyed
Any ideas how to modify the above code?
Thanks
February 8, 2013 at 4:38 pm
Its not a coding issue. You either need to rollback or roll forward your office builds.
February 8, 2013 at 4:44 pm
what do you mean exactly? Is it not possible to adapt the code working with MS Office 2007? I really have to 'downgrade' my Office to Version 2003?
February 8, 2013 at 5:01 pm
Is this being run locally or you making a remote call ?
February 8, 2013 at 5:05 pm
it's only locally
February 8, 2013 at 5:17 pm
replace this change to your access connection string and test.
-- Zufälliges Erzeugen eines Datenbank-Namens
SET @dbname = @pfad + CAST(newid() AS varchar(100)) + '.MDB'
-- Erstellen der Verbindungszeichenfolge
SET @conString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + @dbname
/*
updated to reflect Access 2007
*/
February 8, 2013 at 5:23 pm
replace this coding change to your Excel connection string and test. Leave the previous Access coding change in place.
-- Ö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 12.0;Database=' + @pfad + db_name() + '.xlsx].[' + @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
/*
updated to reflect Excel 2007
*/
February 8, 2013 at 5:36 pm
Hi sqlsetter
Thanks again for your answer. Unfortunately I get the same error massage as initially. Do I have to install something special?
February 8, 2013 at 6:00 pm
Did you make both coding changes for Excel and Access? They both need to be in place.
February 8, 2013 at 6:33 pm
Yes, I copy'n'pasted both text blocks you sent me; unfortunately without success.
Is there an easy way to test, wheter I can connect with Access DB or not? Because my code is already rather complex....
Thanks for your help.
February 8, 2013 at 6:38 pm
You replace what you had in there previously with what I gave you, correct?
February 17, 2013 at 5:49 am
I found the reason for the problem. I work with
- Win 7 64bit
- MS SQL Server 2008 R2 64bit
- MS Office 2007 32bit; there is no Office 2007 64bit
I downloaded the 64bit MS Office 2010 trial version, and then the following code worked
DECLARE @pfad varchar(255)
SET @pfad = 'F:\\17-02-2013_11-12\'
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)) + '.ACCDB'
-- Erstellen der Verbindungszeichenfolge
SET @conString = 'Provider=Microsoft.ACE.OLEDB.12.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
-- Ö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
Only one thing does not properly work: The temporary Access DB will not be destroyed.
Now I have the following options
1. Uninstall Office 2007 and replace it with an Office 2010 64bit version
2. Install Win7 / SQL Server 32bit and hope that this works
3. Try to find a solution to make Win 7 / MS SQL Server 2008 R2 64bit AND MS Office 2007 32bit work.
Questions:
A. Do you see any possibilities to make option 3 work?
B. Do you know why the temporary Access Database is not removed?
Thanks again
Angeline
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply