Need to change a table name from all sp's

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..."Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:6B9DF2A1-0509-453E-9BC8-FD40A378DDF0@.microsoft.com...
> 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...
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 store
d
proc is called by a report. That's why.|||"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:BF02A629-DFB7-44DD-9341-80B0D458C3B8@.microsoft.com...
> 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.
Anith|||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,Col
umnName,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 DatStdItAssetComponents
1
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.|||"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:A07841F1-E18F-47E9-9E01-68FF441747AB@.microsoft.com...
> 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,Col
umnName,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.
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

> 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,Col
umnName,Value,LanguageID)
> select
> OrgUnitID,ITAssetObjectID,theAssetName,'
4271',theOrgUnitName,@.inLangua
> geID
> 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.
>|||On Thu, 26 Jan 2006 15:17:02 -0800, Tejas Parikh wrote:

>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.
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.
Hugo Kornelis, SQL Server MVP