How do you Create Table using the current Date for the name of the Tabe

  • 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

  • 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

  • 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.

  • 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

  • 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'.

  • 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.

  • You may need to create the SQL Statement and the execute it with EXEC sp_executesql

    -- Cory

  • 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]

  • 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

  • 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.

  • 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.

  • 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

  • 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

  • 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