July 12, 2010 at 3:45 pm
I'm running the following script:
-- SQL Server 2005
DECLARE @vUnused_Index_Uptime_Threshold AS INT
DECLARE @vOnline_Since AS NVARCHAR (19)
DECLARE @vUptime_Days AS INT
DECLARE @vDate_24_Hours_Ago AS DATETIME
DECLARE @vDate_Now AS DATETIME
DECLARE @vSubject AS NVARCHAR (255)
DECLARE @vFixed_Drives_Free_Space_Table AS TABLE (drive_letter VARCHAR (5), free_space_mb BIGINT)
DECLARE @vDatabase_Name AS NVARCHAR (500)
DECLARE @vXML_String AS NVARCHAR (MAX)
DECLARE @vBody AS NVARCHAR (MAX)
DECLARE @vSQL_String AS NVARCHAR (MAX)
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT netbios_name, server_name, edition, version, level, online_since, uptime_days, reads, writes FROM [Sheet1$]')
SELECT
SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS netbios_name
,@@SERVERNAME AS server_name
,REPLACE (CONVERT (NVARCHAR (128), SERVERPROPERTY ('Edition')),' Edition','') AS edition
,SERVERPROPERTY ('ProductVersion') AS version
,SERVERPROPERTY ('ProductLevel') AS [level]
,@vOnline_Since AS online_since
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, @vUptime_Days), 1)), 4, 15)) AS uptime_days
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, @@TOTAL_READ), 1)), 4, 15)) AS reads
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, @@TOTAL_WRITE), 1)), 4, 15)) AS writes
and getting the following error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect.".
Msg 7321, Level 16, State 2, Line 18
An error occurred while preparing the query "SELECT netbios_name, server_name, edition, version, level, online_since, uptime_days, reads, writes FROM [Sheet1$]" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
if I change it to Excel 2003 I get the following error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 18
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
July 12, 2010 at 4:25 pm
Ok, a couple things:
Why would you not use a SSIS package, to run the query, and then pump the data out to any file type that you like?
second:
Why are you using NVARCHAR(MAX) data type? What in the world are you storing? That should hold about a million copies of the encyclopedia. Use the correct data types.
Then if you do get this to work correctly, how do you plan on firing it off? I would suggest that you use a SSIS package and then create a SQL job to schedule it to fire off when you need it. You can even create a web page to fire off the SQL job.
Andrew SQLDBA
July 12, 2010 at 5:47 pm
Thank you Andrew;
I am not a developer and I am new. I don't understand the best ways of doing things yet. I am doing this on my own workstation for right now just trying to learn. I will eventually try this in SSIS as well but for now I'm just really learning how to code in T-SQL (unfortunately on my own) and am attempting to try and accomplish tasks in different ways.
BTW I also have SQL 2000 installed on my workstation and attempted to accomplish this in a DTS package but still ran into problems when it came to sending it to an Excel spreadsheet.
Except for the Excel part I did first make this all work in Query Analyser (getting rid of MAX and modifying other parts of the code for SQL 2000) in both versions of SQL and got the results I wanted from the query.
i do appreciate any help I can get understanding what I don't know.
July 12, 2010 at 7:11 pm
You might want to check out this link[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 13, 2010 at 9:59 am
OK, you posted this into a SQL 2005 area, so I assumed this was for SQL 2005
I created a simple DTS package in about 2 minutes by using the wizard, and saving the package. I then opened the packaged and placed a different query into query editor. I actually created a stored procedure and used that. I was able to pump all the data out for an excel format, from one table.
I would suggest that.
Andrew SQLDBA
July 13, 2010 at 10:31 am
I would do what Andrew has done. That's worked well for me.
One thing to be aware of is that you want the create table statements in the DTS (along with a DROP) if you are running this over and over. That way you won't be appending to your XLS, which isn't what most people want.
July 13, 2010 at 10:36 am
You guys are great! I'm looking over the article that Wayne suggested right now and I will try the other method as well.
Thank you everyone 🙂
July 16, 2010 at 3:46 pm
Hi,
Small clarifications please...
Using DTS package I am able to export data to text file even even destination file does not exists (Package itself creating at destination point).. I have problem while exporting to Excel.. It is not creating excel file.
Is there any mistake doing from my end...
🙂
July 16, 2010 at 4:16 pm
Yes, you need to select "Excel File" when you select the Destination. You must be selecting "Flat File" or something. There is a long list of different connectors
Andrew SQLDBA
July 16, 2010 at 4:45 pm
I have done it fir the first time successfuly. The I deleted the file which I generated previously and tried It is giving error. Again I need to create table in 'Transform Task Properties> Destination '
Attaching Error screen shot
Note: As you said.. I have selected Microsoft Excel 97-2000 as destination
🙂
July 16, 2010 at 7:27 pm
How are you receiving a JET error when you are using an OLEDB Connection?
Simply just delete the package and start over.
This stuff is really simple.
Right click on the table and select "All Tasks" --> "Export Data" And allow the wizard to create a new package for you. This really takes all of two minutes to perform.
Andrew SQLDBA
July 17, 2010 at 9:52 am
The first time you ran through this, it usually asks you to create a table. It does that, but to repeat it, you'd need that table in the sheet or you need a T-SQL task to run the CREATE table script against the Excel file.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply