Hey guys. I've a few sp's. This sp's are doing some inserts and updates in a
table
I need to setup a job to run every4 hours. And at

at 12 am, it's supposed to insert in table1
at 4 am, it's supposed to insert in table2 and truncate table1.
at 8 am, it's supposed to insert in table1

Please help me on writing this script. I can get the table name from
sysobjects and the text from sysomments but haven't reached farther than
that. Can someone think of a way to do this? Thank you...

Show quote Hide quote Why not setup 3 separate jobs, each that run once a day.
Job 1 at 12 am
Job 2 at 4 am
Job 3 at 8 am

because the table being created is called by another stored proc. This stored
proc is called by a report. That's why.

I don't understand what you're doing here.

This is what's happening.

There is a report being accessed from a website. This report accesses a sp.
This sp has a select query inside it which goes to some tables and gets the
stuff. The table name I need to change is one of these tables. But as i said
in the first part, i need to update this table all the time and start from
scratch WITHOUT DOWN TIME. That's y i need to do wut i asked. Now, u think
there is a way where i can run a script to change these sp's so, that they
reflect different table name every 4 hours? Thank you.

Based on your narrative it is hard to understand what you are trying to
accomplish.

On a cursory inspection, you are attempting something which normal
developers in general circumstances would not do. Can you elaborate you
requirements perhaps with a repro? Please refer to www.aspfaq.com/5006 for
more details on how you can present more information for others to help you.

Allright, I'll paste part of the sp here. I really dont know what you guys
wnat me to paste here since my concern is with 5 sp's. instead of manually
going and replacing the names of a table, i want a script that would change
the names of a table.
Anyways, I'm pasting part of the script here... --------------------------------------------------------------------------------------------
ALTER                           Procedure
[dbo].[Report_PopulateTable_ITAssetComponent]
/* Input parameter to specify the user language ID */
    @inLanguageID int

AS

            /* Getting Fixed ITAsset Components*/
            SELECT  ta.itassetobjectid as ItAssetObjectID,
                ta.OrgUnitID as OrgUnitID,
                OU.ObjectName as theOrgUnitName,
                DO.ObjectName as theAssetName,
                DA.SerialNumber as theSerialNumber,
                DA.Manufacturer as theManufacturer,
                DA.ModelNumber as theModelNumber,
                DITT.ITAssetTypeName as theITAssetTypeName,
                DIA.OSServicePack as theOSServicePack
            into #FixedITAsset
            FROM datObjects OU    
                join    datObjects DO       (nolock)    on OU.ObjectID = DO.parentObjectID
                join    datAssets DA        (nolock)    on DO.ObjectID = DA.AssetObjectID
                join     datITAssets DIA     (nolock)    on DA.AssetObjectID= DIA.ITAssetObjectID
                join     ##tempassets ta        (nolock)    on DIA.ITAssetObjectID =
ta.itassetobjectid
                join     defITAssetTypes DITT    (nolock)    on DITT.ITAssetTypeID
=DIA.ITAssettypeID
            WHERE
                DO.parentObjectID =
                (SELECT ParentObjectid FROM datObjects WHERE ObjectID =
DIA.ITAssetObjectID)

/*Get OrgUnit Name as per the Language and insert in the work table*/
SELECT @theComponentName = string
FROM datStrings WHERE StringID=1226 AND LanguageID=@inLanguageID
INSERT INTO DatStdItAssetComponents
(OrgUnitID,itassetobjectid,AssetName,ColumnName,Value,LanguageID)
    select
OrgUnitID,ITAssetObjectID,theAssetName,'4271',theOrgUnitName,@inLanguageID
    from #FixedITAsset --------------------------------------------------------------------------------------------

That's an example of what's happening. This sp is about 7 pages long and
there are a few like these. Do you see where it says
'DatStdItAssetComponents'? I've to replace that with DatStdItAssetComponents1
after 4 hours from now and back to 'DatStdItAssetComponents' 8 hours from
now. Keep alternating between the two table names. I've pasted only one
insert statement here but there are a lot more.

My point is to rplace the table name with another table name every 4 hours
by a SCRIPT. Thank you.

Show quote Hide quote Strange requirements but what do I know...
Why not just create a job that alters the stored procedure?
Look up Alter Procedure in Books Online.
You would need 2 jobs.
Job 1 runs at 4, job 2 runs at 8, job 1 runs at 12...

Hello Tejas,
I am sure there is a better way ... but you could pass in the table name
as a variable into the stored procedure. You will then need to create all
your inserts etc. dynamically using that variable as your table name.
Hope that helps,
Sandeep


Show quoteHide quote

On Thu, 26 Jan 2006 15:17:02 -0800, Tejas Parikh wrote:

Hi Tejas,

Maybe it's just me, but I honestly don't understand why you can't make
two copies of the stored procedure, then set up a job to run the first
at for instance 4AM, noon, 8PM and the second at midnight, 8AM, 4PM.

Self-modifying code is maintenance hell.

Table design (search with ranking)
How does engine decide whether to scan or seek index
Nightmare Query
Problem with DateAdd & GetDate()
Multiple-step OLE DB operation generated errors.