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.
Author |
Topic |
Angeline87
Starting Member
7 Posts |
Posted - 2013-02-08 : 16:53:03
|
Hi to everybodyI 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 7And 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 erzeugenDECLARE @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_OUTPUTGO 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 registriertFehler 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 lotAngeline |
|
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 JamesThanks 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 codeDECLARE @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 StatementFehler 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 registeredI 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 64bitMS 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 |
|
|
|
|
|
|
|