May 24, 2010 at 12:44 pm
I need help bad. I have read a ton or articles and for whatever reason I can not export the results of the following query to Excel.
1. I would like to automatically create the spreadsheet but COULD start with a blank master copy and just make a copy of it and write to it if I have to.
2. I am running SQL 2008 on a 64 bit machine. Some of the things I have tried are giving me problems because of this fact
3. Using a query like this:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 CustomerID, CustomerName
FROM Customers
GO
Gives me results like this:
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.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
And I can't find how to fix that.
So the query I need to write to Excel is as follows:
SELECT coalesce(LastName,'') as LastName, coalesce(FirstName, '') as FirstName, coalesce(PreTitle, '') as PreTitle, coalesce(SufTitle, '') as Suffix, coalesce(OldPriNumb, '-') as OldStreetNumber,
coalesce(OldPreDirc, '') as OldPreDirectional, coalesce(OldPriName, '') as OldStreetName, coalesce(OldSuffix, '') as OldStreetSuffix, coalesce(OldPstDirc, '') as OldPostDirectional,
coalesce(OldCityNme, '') as OldCity, coalesce(OldState, '') as OldState, coalesce(OldZip, '') as OldZip, coalesce(OldZippl4, '') as Old4, coalesce(NewPriNumb, '') as NewStreetNumber,
coalesce(NewPreDirc, '') as NewPreDirectional, coalesce(NewPriName, '') as NewStreetName, coalesce(NewSuff, '') as NewSuffix, coalesce(NewPostDir, '') as NewPostalDirecitonal,
coalesce(NewCityNME, '') as NewCityName, coalesce(NewST, '') as NewState, coalesce(NewZip, '') as NewZip, coalesce(NEWPL4CODE, '') as NewPlus4
FROM FFUpdate
WHERE FFUpdate.Transmitted = 0
AND FFUpdate.CustomerID= 2
Can anyone help me here?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
May 24, 2010 at 7:13 pm
Jeffery Williams (5/24/2010)
2. I am running SQL 2008 on a 64 bit machine. Some of the things I have tried are giving me problems because of this factINSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 CustomerID, CustomerName
FROM Customers
GO
The Jet engine doesn't work on 64-bit machines.
Also, I think that to insert into Excel, you need to use the OpenDatasource function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 24, 2010 at 7:35 pm
I found a script that actually works. Works as in it gets the table data into Excel. NOW I need to midify it as it is designed to get the data in ALL tables within a DB, and to that end the column headings are the same as the Sql TABLE. In my case I need to change the name of the heading, pull for only a single client (on a particular run that is) and some other WHERE clauses; such as data not already sent, etc.
HOWEVER.... The basic function of taking table data, creating an XLS, giving it headers (Column names) and inserting the data is actually functioning. The puzzling part is that it is using a method that I have already tried (CMDShell)...
Here is the script that is working. If anyone wants to assist on limiting the scope of its functionality as detailed above it would be appreciated. I MAY be able to do it on my own, I am certainly going to attempt it this evening.
The script that accomplishes the basic task:
http://www.sqlservercentral.com/scripts/Replication/31668/
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply