April 24, 2008 at 11:14 am
I am trying to export the result of a querry each night to an excel spreadsheet. I found this Stored Procedure which allows me to create the excel Workbook .
------------------------------------------
CREATE PROCEDURE spExecute_ADODB_SQL
@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
= 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=%DataSource;
Extended Properties=Excel 8.0'
AS
DECLARE
@objExcel INT,
@hr INT,
@command VARCHAR(255),
@strErrorMessage VARCHAR(255),
@objErrorObject INT,
@objConnection INT,
@bucket INT
SELECT @ConnectionString
=REPLACE (@ConnectionString, '%DataSource', @DataSource)
IF @Worksheet IS NOT NULL
SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet)
SELECT @strErrorMessage='Making ADODB connection ',
@objErrorObject=NULL
EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT
IF @hr=0
SELECT @strErrorMessage='Assigning ConnectionString property "'
+ @ConnectionString + '"',
@objErrorObject=@objconnection
IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection,
'ConnectionString', @ConnectionString
IF @hr=0 SELECT @strErrorMessage
='Opening Connection to XLS, for file Create or Append'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open'
IF @hr=0 SELECT @strErrorMessage
='Executing DDL "'+@DDL+'"'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute',
@Bucket out , @DDL
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,
@Description output,@Helpfile output,@HelpID output
SELECT @strErrorMessage='Error whilst '
+COALESCE(@strErrorMessage,'doing something')+', '
+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC @hr=sp_OADestroy @objconnection
GO
-------------------------
Each time I want to add the information to the Excel I do a Create Table, but I have to change the "Name" manually. I would like to automate this and have the "New_Name" be the Date that the Create Table was run. I have tried different methods to of doing a GetDate(), but it always comes up with a syntax error. Here is what I run to create the spreadsheet and table
--This uses the Stored Procedure to create a table in the excel spread sheet
--------------------------------------------------------------------------
spExecute_ADODB_SQL @DDL='Create Table NEW_NAME
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
@DataSource ='C:\Reports\NightlyReport.xls'
--This Exports the Data to Excel
-------------------------------------------------------------------------
INSERT INTO NightlyReportDatabase... NEW_NAME
(dbid, id, login_name, rleasenumber, releasestate)
SELECT T1.dbid,T1.id,T4.login_name,T2.rleasenumber,T2.releasestate from ( ( ( ( T1.Defect T1 INNER JOIN T1.users T4 ON T1.owner = T4.dbid ) LEFT OUTER JOIN T1.parent_child_links T2mm ON T1.dbid = T2mm.parent_dbid and 16781849 = T2mm.parent_fielddef_id ) LEFT OUTER JOIN T1.relrec T2 ON T2mm.child_dbid = T2.dbid ) LEFT OUTER JOIN T1.history T3 ON T2.dbid = T3.entity_dbid and 16781811 = T3.entitydef_id ) where T1.dbid <> 0 and ((T2.rleasenumber like '%7.0%' and T2.releasestate like '%Required%' and T3.action_timestamp > {ts '2007-08-01 23:59:59'}))
My question is can you use a Date variable for "Create Table"?
Thank you
April 24, 2008 at 11:49 am
this is untested but:
CREATE PROCEDURE spExecute_ADODB_SQL
@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
= 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=%DataSource;
Extended Properties=Excel 8.0'
AS
DECLARE
@objExcel INT,
...
@filename varchar(100)
...
set @filename = 'C:\Reports\NightlyReport_' + GETDATE() + '.xls'
...
--This uses the Stored Procedure to create a table in the excel spread sheet
--------------------------------------------------------------------------
spExecute_ADODB_SQL @DDL='Create Table NEW_NAME
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
@DataSource =@filename
...
-- Cory
April 24, 2008 at 11:58 am
Cory Ellingson (4/24/2008)
this is untested but:
CREATE PROCEDURE spExecute_ADODB_SQL@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
= 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=%DataSource;
Extended Properties=Excel 8.0'
AS
DECLARE
@objExcel INT,
...
@filename varchar(100)
...
set @filename = 'C:\Reports\NightlyReport_' + GETDATE() + '.xls'
...
--This uses the Stored Procedure to create a table in the excel spread sheet
--------------------------------------------------------------------------
spExecute_ADODB_SQL @DDL='Create Table NEW_NAME
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
@DataSource =@filename
...
Thank you,
But that would only name the XLS file itself, I am trying to rename the Table Name, which turns out to be the worksheet name within the XLS. Renaming the table name adds a new worksheet eachtime. If the table name is the same it over rights the information.
Thank you for the suggestion.
April 24, 2008 at 12:10 pm
My bad - I guess I did not read it all - however, the same idea should work:
CREATE PROCEDURE spExecute_ADODB_SQL
@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
= 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=%DataSource;
Extended Properties=Excel 8.0'
AS
DECLARE
@objExcel INT,
...
@ddlname varchar(100)
...
set @ddlname = 'Create Table Table_' + getdate() + '
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
...
--This uses the Stored Procedure to create a table in the excel spread sheet
--------------------------------------------------------------------------
spExecute_ADODB_SQL @DDL=@DDLName,
@DataSource ='C:\Reports\NightlyReport.xls'
...
-- Cory
April 24, 2008 at 1:53 pm
Thank you,
I added into the SP the:
DECLARE
@ddlname varchar(100)
And also added:
set @ddlname = 'Create Table Table_' + getdate() + '
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)'
And then excuted:
spExecute_ADODB_SQL @DDL=@ddlname,
@DataSource ='C:\Reports\NightlyReport.xls'
And got the result:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@ddlname'.
I did try a few varitions, I know if I manauly entered a name like NewTable then it works
spExecute_ADODB_SQL @DDL='Create table NeWTable
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
@DataSource ='D:\Reports\NightlyReport.xls'
So I tried a simple SELECT getdate() and got "2008-04-24 15:32:29.430" for a result and tried entering that manually and it failed.
spExecute_ADODB_SQL @DDL='Create table 2008-04-24 15:32:29.430
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
@DataSource ='D:\Reports\NightlyReport.xls'
So I tried: SELECT CONVERT(VARCHAR(8),GetDate(),112) Which gave me the result 20080424 and plugged this into it and it did work.
spExecute_ADODB_SQL @DDL='Create table 20080424
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)',
@DataSource ='D:\Reports\NightlyReport.xls'
So I changed
set @ddlname = 'Create Table Table_' + getdate() + '
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)'
tO
set @ddlname = 'Create Table Table_' + CONVERT(VARCHAR(8),GetDate(),112) + '
(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)'
bUT i still recieved the same error.
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@ddlname'.
April 25, 2008 at 1:34 pm
It appears that the problem is that you cannot use an "@" in a Create Table.
So somehow I need to be able to generate the GetDate() result and then create another query.
Thank you for the help.
April 25, 2008 at 1:37 pm
You may need to create the SQL Statement and the execute it with EXEC sp_executesql
-- Cory
April 25, 2008 at 2:10 pm
junkmail (4/25/2008)
It appears that the problem is that you cannot use an "@" in a Create Table.So somehow I need to be able to generate the GetDate() result and then create another query.
Thank you for the help.
Seems to me this would work as you just need to create the DDL statement BEFORE calling the sp:
[font="Courier New"]DECLARE @ddlname VARCHAR(100),
@table_name VARCHAR(30)
SET @table_name = 'Table_' + CONVERT(VARCHAR(8),GETDATE(),112)
SET @ddlname = 'Create Table ' + @table_name + '(dbid Int, id Text, login_name Text, rleasenumber Text, releasestate Text)'
spExecute_ADODB_SQL @DDL=@ddlname,@DataSource ='C:\Reports\NightlyReport.xls'
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 2:34 pm
I believe DTS can do most of this work for you, if you want it to. You might want to look into using that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 8:10 am
GSquared (4/25/2008)
I believe DTS can do most of this work for you, if you want it to. You might want to look into using that.
Yes, I tried a DTS but I could not figure out how to export to the same Spreadsheet and naming the new worksheet to the date each time the DTS is run.
April 29, 2008 at 9:41 am
Cory Ellingson (4/25/2008)
You may need to create the SQL Statement and the execute it with EXEC sp_executesql
Thank you for the help. Let me try to work out how to do a sp_executesql and if I need help I will post.
April 29, 2008 at 11:03 am
I created this "exec sp_executesql" as you sugested and it looks good. but how do you get a Apostrophe in? I tried ''' ? I need one before the word 'Create Table ' so it looks like 'Create Table
Thank you
declare
@cmd nvarchar(2000),
@StoreProcedureName varchar(30),
@theDDL varchar(10),
@ParameterName varchar(30),
@tablename varchar(30),
@therows varchar(60),
@thesource varchar(30),
@filename varchar(30)
set @StoreProcedureName = 'spExecute_ADODB_SQL '
set @theDDL = '@DDL'
set @ParameterName = 'Create Table '
set @tablename = CONVERT(VARCHAR(8),GetDate(),112)
set @therows = '(dbid Text, id Text, login_name Text, rleasenumber Text, releasestate Test)'
set @thesource = '@DataSource'
set @filename = 'C:\Reports\NightlyReport.xls'
set @cmd = @StoreProcedureName + @theDDL + '=' + @ParameterName + @tablename + @therows + ', ' + @thesource + '=' + @filename
print @cmd
April 29, 2008 at 12:10 pm
declare
@cmd nvarchar(2000),
@StoreProcedureName varchar(30),
@theDDL varchar(10),
@ParameterName varchar(30),
@tablename varchar(50),
@therows varchar(160),
@thesource varchar(30),
@filename varchar(30)
set @StoreProcedureName = 'spExecute_ADODB_SQL '
set @theDDL = '@DDL'
set @ParameterName = '''Create Table '
set @tablename = CONVERT(VARCHAR(8),GetDate(),112)
set @therows = '(dbid Text, id Text, login_name Text, rleasenumber Text, releasestate Test)'''
set @thesource = '@DataSource'
set @filename = 'C:\Reports\NightlyReport.xls'
set @cmd = @StoreProcedureName + @theDDL + '=' + @ParameterName + @tablename + @therows + ', ' + @thesource + '=' + @filename
print @cmd
NOTE, I had to change the size of some fields, the sizes I just randomly picked, and have no real importance, other than they used to be to small to fit
-- Cory
May 1, 2008 at 9:26 am
Thank you,
Mr or Mrs. 500
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply