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

Need SQL Performance Advice (Scenario Given)

Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

Any help is greatly appreciated.indexes on the join columns?|||yes indexes are in place, i dont really thing that they are the problem,

i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

thanks for your help|||depends

without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible|||Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

-PatP

need SP for iterating through all Databases

Hi guys,
i'm not much familier to the SQL Database so that i need your expert
advice on one problem.
the problem is,
i've 50 databases and i need to truncate one perticular table from all
database.
all i know is there is one undocumented SP for "for each table". can
anyone tell if there is some SP for databases also? so that i can
write one short script for my problems. otherwise i dont know how would
i do this.
all i need is:
1. need to find out whether the table exists,
2. if table exists, clear all rows into the table.
please, guys, help me out. otherwise i'll endup clicking on all DBs and
truncating table.> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
undocumented stored procedure:
sp_MSforeachdb
ex.:
EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
dbo.Table'
Tom
http://sqlserverbuilds.blogspot.com/|||thanks pal,
u solved my big problem. as i'm a software developer, i dont have much
knowledge of DB. i can only write some queries but this is something i
dont do it regularly.
Lucky
<@.> wrote:
> > all i need is:
> > 1. need to find out whether the table exists,
> > 2. if table exists, clear all rows into the table.
> undocumented stored procedure:
> sp_MSforeachdb
> ex.:
> EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
> dbo.Table'
>
> Tom
> http://sqlserverbuilds.blogspot.com/|||Use master
GO
Declare @.SQL nvarchar(1000)
Declare @.DBID int, @.DBName varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT dbid, [Name]
FROM sysdatabases
-- WHERE dbid > 4
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @.DBID, @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Start of Cursor
Set @.SQL = '
USE ' + @.DBName + '
GO
EXEC sp_spaceused @.updateusage = N''TRUE'';
GO'
Print @.SQL
exec sp_executeSQL @.SQL
-- Movenext of cursor
FETCH NEXT FROM db_cursor
INTO @.dbid, @.dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||Here ya go!
set nocount on
declare @.databasename as varchar(200)
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')
open curs3
fetch next from curs3 into @.databasename
while @.@.fetch_status = 0
begin
exec ('use ' + @.databasename + '
IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ''mytable'')
truncate table mytable
')
fetch next from curs3 into @.databasename
end
close curs3
deallocate curs3
lucky wrote:
> Hi guys,
> i'm not much familier to the SQL Database so that i need your expert
> advice on one problem.
> the problem is,
> i've 50 databases and i need to truncate one perticular table from all
> database.
> all i know is there is one undocumented SP for "for each table". can
> anyone tell if there is some SP for databases also? so that i can
> write one short script for my problems. otherwise i dont know how would
> i do this.
> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
> please, guys, help me out. otherwise i'll endup clicking on all DBs and
> truncating table.sql

need SP for iterating through all Databases

Hi guys,
i'm not much familier to the SQL Database so that i need your expert
advice on one problem.
the problem is,
i've 50 databases and i need to truncate one perticular table from all
database.
all i know is there is one undocumented SP for "for each table". can
anyone tell if there is some SP for databases also? so that i can
write one short script for my problems. otherwise i dont know how would
i do this.
all i need is:
1. need to find out whether the table exists,
2. if table exists, clear all rows into the table.
please, guys, help me out. otherwise i'll endup clicking on all DBs and
truncating table.Here ya go!
set nocount on
declare @.databasename as varchar(200)
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')
open curs3
fetch next from curs3 into @.databasename
while @.@.fetch_status = 0
begin
exec ('use ' + @.databasename + '
IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ''mytable'')
truncate table mytable
')
fetch next from curs3 into @.databasename
end
close curs3
deallocate curs3
lucky wrote:
> Hi guys,
> i'm not much familier to the SQL Database so that i need your expert
> advice on one problem.
> the problem is,
> i've 50 databases and i need to truncate one perticular table from all
> database.
> all i know is there is one undocumented SP for "for each table". can
> anyone tell if there is some SP for databases also? so that i can
> write one short script for my problems. otherwise i dont know how would
> i do this.
> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
> please, guys, help me out. otherwise i'll endup clicking on all DBs and
> truncating table.|||> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
undocumented stored procedure:
sp_MSforeachdb
ex.:
EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELE
TE
dbo.Table'
Tom
http://sqlserverbuilds.blogspot.com/|||thanks pal,
u solved my big problem. as i'm a software developer, i dont have much
knowledge of DB. i can only write some queries but this is something i
dont do it regularly.
Lucky
<@.> wrote:
> undocumented stored procedure:
> sp_MSforeachdb
> ex.:
> EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DE
LETE
> dbo.Table'
>
> Tom
> http://sqlserverbuilds.blogspot.com/|||Use master
GO
Declare @.SQL nvarchar(1000)
Declare @.DBID int, @.DBName varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT dbid, [Name]
FROM sysdatabases
-- WHERE dbid > 4
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @.DBID, @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Start of Cursor
Set @.SQL = '
USE ' + @.DBName + '
GO
EXEC sp_spaceused @.updateusage = N''TRUE'';
GO'
Print @.SQL
exec sp_executeSQL @.SQL
-- Movenext of cursor
FETCH NEXT FROM db_cursor
INTO @.dbid, @.dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

need SP for iterating through all Databases

Hi guys,
i'm not much familier to the SQL Database so that i need your expert
advice on one problem.
the problem is,
i've 50 databases and i need to truncate one perticular table from all
database.
all i know is there is one undocumented SP for "for each table". can
anyone tell if there is some SP for databases also? so that i can
write one short script for my problems. otherwise i dont know how would
i do this.
all i need is:
1. need to find out whether the table exists,
2. if table exists, clear all rows into the table.
please, guys, help me out. otherwise i'll endup clicking on all DBs and
truncating table.
Here ya go!
set nocount on
declare @.databasename as varchar(200)
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')
open curs3
fetch next from curs3 into @.databasename
while @.@.fetch_status = 0
begin
exec ('use ' + @.databasename + '
IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ''mytable'')
truncate table mytable
')
fetch next from curs3 into @.databasename
end
close curs3
deallocate curs3
lucky wrote:
> Hi guys,
> i'm not much familier to the SQL Database so that i need your expert
> advice on one problem.
> the problem is,
> i've 50 databases and i need to truncate one perticular table from all
> database.
> all i know is there is one undocumented SP for "for each table". can
> anyone tell if there is some SP for databases also? so that i can
> write one short script for my problems. otherwise i dont know how would
> i do this.
> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
> please, guys, help me out. otherwise i'll endup clicking on all DBs and
> truncating table.

need SP for iterating through all Databases

Hi guys,
i'm not much familier to the SQL Database so that i need your expert
advice on one problem.
the problem is,
i've 50 databases and i need to truncate one perticular table from all
database.
all i know is there is one undocumented SP for "for each table". can
anyone tell if there is some SP for databases also? so that i can
write one short script for my problems. otherwise i dont know how would
i do this.
all i need is:
1. need to find out whether the table exists,
2. if table exists, clear all rows into the table.
please, guys, help me out. otherwise i'll endup clicking on all DBs and
truncating table.
Here ya go!
set nocount on
declare @.databasename as varchar(200)
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')
open curs3
fetch next from curs3 into @.databasename
while @.@.fetch_status = 0
begin
exec ('use ' + @.databasename + '
IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ''mytable'')
truncate table mytable
')
fetch next from curs3 into @.databasename
end
close curs3
deallocate curs3
lucky wrote:
> Hi guys,
> i'm not much familier to the SQL Database so that i need your expert
> advice on one problem.
> the problem is,
> i've 50 databases and i need to truncate one perticular table from all
> database.
> all i know is there is one undocumented SP for "for each table". can
> anyone tell if there is some SP for databases also? so that i can
> write one short script for my problems. otherwise i dont know how would
> i do this.
> all i need is:
> 1. need to find out whether the table exists,
> 2. if table exists, clear all rows into the table.
> please, guys, help me out. otherwise i'll endup clicking on all DBs and
> truncating table.
|||thanks pal,
u solved my big problem. as i'm a software developer, i dont have much
knowledge of DB. i can only write some queries but this is something i
dont do it regularly.
Lucky
<@.> wrote:
> undocumented stored procedure:
> sp_MSforeachdb
> ex.:
> EXEC sp_MSforeachdb 'USE [?] IF OBJECT_ID(''dbo.Table'')IS NOT NULL DELETE
> dbo.Table'
>
> Tom
> http://sqlserverbuilds.blogspot.com/
|||Use master
GO
Declare @.SQL nvarchar(1000)
Declare @.DBID int, @.DBName varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT dbid, [Name]
FROM sysdatabases
-- WHERE dbid > 4
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @.DBID, @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Start of Cursor
Set @.SQL = '
USE ' + @.DBName + '
GO
EXEC sp_spaceused @.updateusage = N''TRUE'';
GO'
Print @.SQL
exec sp_executeSQL @.SQL
-- Movenext of cursor
FETCH NEXT FROM db_cursor
INTO @.dbid, @.dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Need some help with query

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Try to use joins instead of alias names
sample:
3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME from Project b
left join WORKSON c on C.Pnumber=b.Pnumber
left join EMPLOYEE a on a.SSN=c.SSN
WHERE b.PLOCATION !=Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

select distinct Employee.Name from Project
left join Workson on Workson.PNumber=Project.PNumber
left join Employee on Employee.SSN=Workson.SSN
where Project.PLOcation='Houston'

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

Select Name from Employee where SSN=(Select SSN from
(Select SSN,count(SSN) as cnt from
(select distinct SSN,Workson.PNumber from Project
left join Workson on Workson.Pnumber= Project.PNumber
where Project.PLocation='Houston'
group by SSN,Workson.PNumber) as A
group by SSN) as B
where cnt=(select Count(Pnumber) from Project Where Plocation='Houston'))|||

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Hi
Different way for different queries exist.
if u still have problem in 3rd query u can use the following.

SELECT a.NAME
FROM
EMPLOYEE AS a, WORKSON AS b
WHERE a.SSN=b.SSN and b.PNUMBER IN
(SELECT PNUMBER
FROM PROJECT
WHERE PLOCATION NOT IN('HOUSTON'));

IF U STILL HAVE ANY PROBLEM IN ANY OTHER QUERY PLZ TELL I WILL TRY MY BEST TO SOLVE THAT ONE.sql

Need script to add days to a date, and polulate another table

Hi guys n gals,
I'll confess now that I only know enough sql to get me by when web
developing, so I really need a hand with this one...
I've basically setup a Scheduling system within our office where people
chuck in projects and they get scheduled...
Having been asked to setup some recurring functionality, I've created
the site and the fields in a seperate table.
What I capture is the 'StartDay' and 'EndDay' (assuming a wly
recurring job) and all the other details so it can be populated into
the live system.
What I'm trying to acheive is to find next Mondays date (which I've got
from this: SELECT DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6) think
it actually gets Sunday ) , then read the recurring table, pull
out the startday and endday, add them to next Mondays date and popluate
the live table fields StartDate and EndDate (as well as all the other
fields)
Hope this makes sense and I hope you kids can help me as I'm really
struggling
Thanks in advance
AdamAdam
You can use DATEPART(dw ...) to figure out what day of the w a date is
and math will take you to the next certain day of the w that you want.
You can use sp_helplanguage to figure out which day of the w is day 1 for
you, should that be necessary.
And so forth.
RLF
"tsgadam" <adamc@.databasegroup.co.uk> wrote in message
news:1138807651.032044.111320@.g14g2000cwa.googlegroups.com...
> Hi guys n gals,
> I'll confess now that I only know enough sql to get me by when web
> developing, so I really need a hand with this one...
> I've basically setup a Scheduling system within our office where people
> chuck in projects and they get scheduled...
> Having been asked to setup some recurring functionality, I've created
> the site and the fields in a seperate table.
> What I capture is the 'StartDay' and 'EndDay' (assuming a wly
> recurring job) and all the other details so it can be populated into
> the live system.
>
> What I'm trying to acheive is to find next Mondays date (which I've got
> from this: SELECT DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6) think
> it actually gets Sunday ) , then read the recurring table, pull
> out the startday and endday, add them to next Mondays date and popluate
> the live table fields StartDate and EndDate (as well as all the other
> fields)
>
> Hope this makes sense and I hope you kids can help me as I'm really
> struggling
> Thanks in advance
> Adam
>|||Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
A bit a reading, a bit of work to set up.
But once you do, these types of queries will be easy.
"tsgadam" <adamc@.databasegroup.co.uk> wrote in message
news:1138807651.032044.111320@.g14g2000cwa.googlegroups.com...
> Hi guys n gals,
> I'll confess now that I only know enough sql to get me by when web
> developing, so I really need a hand with this one...
> I've basically setup a Scheduling system within our office where people
> chuck in projects and they get scheduled...
> Having been asked to setup some recurring functionality, I've created
> the site and the fields in a seperate table.
> What I capture is the 'StartDay' and 'EndDay' (assuming a wly
> recurring job) and all the other details so it can be populated into
> the live system.
>
> What I'm trying to acheive is to find next Mondays date (which I've got
> from this: SELECT DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6) think
> it actually gets Sunday ) , then read the recurring table, pull
> out the startday and endday, add them to next Mondays date and popluate
> the live table fields StartDate and EndDate (as well as all the other
> fields)
>
> Hope this makes sense and I hope you kids can help me as I'm really
> struggling
> Thanks in advance
> Adam
>

Need HotFix for BUG#124525

Hi Guys,

Iam at the completion of my project and iam facing a crash from the sql server 2005 .....Microsoft have agreed it to be their bug in the following article

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124525

I have searched for any fix in the site but unable to find that..........So please give me the fix Iam in emergency and also client is on my neck....Please help me out

Thanks in advance,

Phani

hi,

I'm also facing the same problem.Can any one help me out?

Thanks

Need Help, DTS is Running extremely Slow!

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

GilTo maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the most advantageous time to execute jobs.

For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods.

Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Server Profiler or Windows NT Performance Monitor to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage.

Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule.

But test it before deploying on production.|||Thnx for replying Satya,

I am afraid this ahs nothing to do with CPU resources. Even when i start the job manualy when there is no activity at all an the server the DTS runs Very Very Slow.

I am prety sure that there is somthing else to be done , allthow i will try your advise.

Any suggestions?|||The are a number of thing you can do. It's specific to your job. First of all who has control. AS400 or the database(SQL). I don't know of a DTS package in AS400. So start there, next, you might want to look at the parameters manually vs job. Are there any environmental issues to look at. Such as: numbers of records to commit, indexes, maintenance and backups, buffers size, Look it up on books online SQL Server.|||AS400?

You mean DB2, no?

Have the DB2 dba set up an unload then copy the data to your box...

Then bcp the data in

I know that doesn't address your problem...

What's the execution step in the job look like?

One difference in your methods is the context of which ID is being used.

When you execute it from EM, it's under the context of your id, AND your location.

From the job, it's under the context of the agent AND the server drive mappings..

I'm guessing it's a network issue...

Did the job ever finish?|||Thnx alll 4 replying,

But i am afriad that that is not the problem...

I think i have solved it though...

The Client Access has an option to translate data by a DLL.
it was using one...dont know its name.

removing this dll solved the problem and the job is executing fine now.

thank you all for helping

Gil

need help with web developer

hey everyone...guys i'll be honest i am a noob to web developer but i want to learn this real bad..i have been given a task from my teacher to make a table in SQL...i am really confused how to create a table? i have used the sql+ an old version before but i dont find any sort of an editor excep for the visual web developer itself..i can see a table designer but it is not accessible...i am really sorry for my noobness but i really want to learn...awaiting replies

You need to install SQL Server Express with Management Studio if you have not installed it and use the videos in the links below to guide you to create tables and other objects in SQL Server. Hope this helps.

http://www.asp.net/learn/videos/

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2036523&SiteID=1

|||

In order to create objects in SQL using VWD you will need to create a database first. You can create a database in VWD using the normal 'Create...' dialog box, just choose SQL Databasee when presented with the option. Once you've done that you should be able to pull up the database in VWD and do things such as create new tables.

You can also use Management Studio as has also be suggested, but you should check with your teacher to see if there is a prefered path you should follow. Creating a database and table using Management Studio has different ramifications than using VWD. If this is part of a larger class project about creating a program in VWD that uses a database, then you should use the VWD tools to create the database so that your ultimate VWD project can find the database easily.

Mike