Stuck In Trigger
Oct 13, 2007
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
Test(TestNo,TestName,maxpoint)
TestEvent(TestNo,TestEventNo,TestDate)
TestEventEmployee(TestEventNo,EmployeeID)
TestEvent
TestEventNo
TestNo
Date
TE001
T001
7/7/2007 12:00:00 AM
TE002
T004
7/7/2007 12:00:00 AM
TE003
T003
7/7/2007 12:00:00 AM
TE004
T003
7/8/2007 12:00:00 AM
TE005
T002
7/9/2007 12:00:00 AM
TE006
T001
7/9/2007 12:00:00 AM
NULL
NULL
NULL
TestEventEmployee
TE001
EMP001
TE002
EMP001
TE003
EMP001
TE003
EMP005
TE002
EMP002
thus only EMP001 should get a increment..thanks in advance
View 10 Replies
ADVERTISEMENT
May 5, 2008
Everything works except for the select portion, I cant put my finger
on what is wrong with this.. I included the trigger and error..
If one can show what I did wrong that would be great..Thanx..
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[arcit] ON [dbo].[active] AFTER UPDATE
as
begin
begin tran
SET IDENTITY_INSERT Archive ON
INSERT INTO Archive()
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'archive' order by ordinal_position
fromInserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1
DELETE FROM a
fromInserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1
commit tran
end
Error Message
Msg 156, Level 15, State 1, Procedure arcit, Line 10
Incorrect syntax near the keyword 'from'.
View 6 Replies
View Related
Oct 16, 2007
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so
created a new database with text indexing enabled and the following table
create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL)
next
create fulltext catalog remoteSupportCatalog
create unique index ui_remotesupport ON support (ftid)
then
create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog
--------
I then populated some rows and issues a quesry
Select * from support where freetext(problemBody, 'test database')
it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"
SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters>
<asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button
</SelectParameters>
and it doesnt give me back an error or data it does nothing. What am I missing?????
View 7 Replies
View Related
Aug 4, 2006
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is:
SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
The tables are
tblUsers
_______
lUserID
tblDLHistory
__________
lDownloadID
lUserID
lVersionID
What on earth am I doing wrong?
View 3 Replies
View Related
Mar 6, 2008
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company
From dbo.contact1 as a,
(
Select DISTINCT accountno
From dbo.contsupp
Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
) as b
Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
------------------------
|dbo.contact1 |
|------------------------|
|accountno | company |
|------------------------|
|5123 | Alpha |
|4158 | Beta |
|2121 | Gamma |
|5555 | Omega |
------------------------
--------------------------------------------
|dbo.contsupp |
|--------------------------------------------|
|accountno | contact | contsupref |
|--------------------------------------------|
|5123 |Product Inventory|device 01 |
|2121 |product note |the note |
|2121 |Product Inventory|device 02 |
|5123 |Email Address |a@b.com |
|4158 |ladkafndkaldkfna |device stopped|
--------------------------------------------
**note: i am using MSSQL 2000
View 2 Replies
View Related
Feb 27, 2004
Hello,
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
View 3 Replies
View Related
Jun 15, 2008
Hi
I am completely stuck trying to make a query
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader:
Sales Order Ref, Order Date,
1, 01/05/2008
2, 01/05/2008
3, 02/05/2008
4, 02/05/2008
OrderLines:
Sales Order Ref, Part Number
1, 222
1, 234
1, 333
2, 222
2, 555
2, 444
3, 333
3, 111
4, 222
StockCategory
Stock Category, Part Number,
A, 222
B, 234
C, 333
D, 444
E, 111
Stock Group:
Stock Group, Stock Category, Priority
Berr, A, 1
Berr, B, 1
OGD, C, 2
OGD, D, 2
DFID , E, 3
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
I am using the following query:
DECLARE @Period DATETIME
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
SET @Period =’2008-05-01’
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT
COUNT(oh.[Sales Order Reference])
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])
GROUP BY oh.[Sales Order Reference]
Is this possible to do?
Thanking you in advance!!!
View 6 Replies
View Related
Jun 23, 2008
Hi,
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>
Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE
View 6 Replies
View Related
Jun 25, 2007
Hello, Well actually I am beginner to ASP.Net, I am working on Microsoft Virtual PC with VS 2005 and SQL Server 2005 installed. Now when Create web application in Studio all works fine with databases but when i try to write some code for accessing SQL Databases I keep getting this error PLZ heLP.Following is my code<% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><html><script language="C#" runat="server" Debug="false" >SqlConnection sqlcon;protected void Page_Load(Object Src, EventArgs e){ sqlcon=new SqlConnection("Data Source=VS02005;Initial Catalog=Gaurav;Integrated Security=True"); SqlDataAdapter sqlcom = new SqlDataAdapter("select * from Employee", sqlcon); DataSet ds = new DataSet(); sqlcom.Fill(ds,"Employee"); DataGrid1.DataSource = ds.Tables["Employee"].DefaultView; DataGrid1.DataBind();}</script><form runtat="server"> <asp:datagrid id="DataGrid1" runat="server" /></form></html>This is the error i am gettingServer Error in '/' Application.--------------------------------------------------------------------------------SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. I am stuck dont know what to do, I hve checked all permissions for user gaurav, I also gave administrator rights to gaurav but nothing is working PLEASE help me.
View 4 Replies
View Related
Jan 31, 2008
Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
View 2 Replies
View Related
Nov 29, 2001
Hi All
I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.
Any idea why it is happening ?
If any body ahs any solution you can call me at 1 860 520 7454.
Sujit
View 1 Replies
View Related
Oct 25, 2006
I have a table with 4 relevant fields (blank lines added for clarity).
State, City, Name, Primary_Contact
IL, Springfield, Bill, n
IL, Springfield, Frank, n
IL, Springfield, Larry, n
IL, Bloomington, Steve, n
IL, Bloomington, Chris, y
IL, Chicago, Betty, n
IL, Chicago, Linda, n
IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a
Primary_Contact='y'
So the results would be:
IL, Springfield
IL, Chicago
That's it. Any help is greatly appreciated.
View 6 Replies
View Related
Nov 11, 2004
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT
PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME,
CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4,
CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4,
Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN
Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN
CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN
CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3
CROSS JOIN
CFGMain
Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT *
FROM LD
WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
Thanks.
View 4 Replies
View Related
Aug 25, 2006
Hi,
I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:
Case 1:
PK | FK | Type | Text
--------------------------
1 | 226 | 0 | some text goes here
2 | 226 | 1 | NULL
Case 2:
PK | FK | Type | Text
--------------------------
3 | 334 | 0 | some text goes here
4 | 334 | 1 | actual text I want to select is in this cell
I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.
I had started writing it as
select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )
but this returns both rows. What I what is something that I think is more akin to
case a || case b
expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.
I hope you can understand what I'm trying to get and any suggestions would be much appreciated.
Thanks in advance,
Peter
View 2 Replies
View Related
Apr 2, 2008
I'ma newbie so please bare with me.
I have two tables, they are tomcat and eagle. With column's named Id, speed, ceiling, range, and data in all the fields.
This is my query:
SELECT id
FROM tomcat INNER JOIN eagle
ON tomcat.id = eagle.id;
my error message:
Msg 209, Level 16, Stae 1, Line 1
Ambiguous column name 'id'
I'm using the book "Head First SQL" as a ref, without any luck. I'm using SQL 2005 express.
Any thoughts, I'm sure its a simple issue.
Thanks
US Navy - We are fueled, armed, and go for launch.
View 2 Replies
View Related
May 7, 2006
I am attempting to write a function to Check the price of every disk and return the number of titles ont he disk which has the highest price. I do not even think that I am in the ballpark:
CREATE FUNCTION highest_price ( )
BEGIN
SELECT count(*) AS
FROM Disk
WHERE price = (SELECT max(price) FROM Disk);
END
/
MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)
Thank you in advance!
View 2 Replies
View Related
Mar 5, 2007
Hi I have the following tables:
document_area: doc_area_id(int) and doc_area_name(string).
document_area_access: doc_area_id(int) and username(string).
I am trying to do a select statement in an sqldatasource in .net that will select all the document_area.doc_area_name's where the current users username is in the document_area_access using the doc_area_id to link the tables.
Any suggestions?
Cheers, Mark
View 1 Replies
View Related
Oct 23, 2006
I'm trying to load in the code to a tutorial that requires SQL Server. Step number one on the install instructions says:
Set Up The Database
You may choose to set up your own Northwind database or restore the included Northwind.bak. Regardless of which approach you take you will need to grant your local [MachineName]ASPNET account access to Northwind and then execute GenerateStoredProcedures.sql against it to create the stored procedures. If you receive a message "There is no such user or group 'aspnet'." you may need to search and replace "ASPNET" with "[your machine name]ASPNET"
I'm using SQL Server Express and the SQL Server Management Studio Express, and well...I'm already stuck. I'm trying to create a new user named ASPNET from the management Studio. I don't see any UI that mentions creating user accounts, and the closest thing is LOGIN accounts. So I created a ASPNET login account, and the disconnected from SQL Express and tried to login as ASPNET. When I do this, I get an error that reads:
Login failed for user 'aspnet'. The user is not associated with a trusted SQL server connection. ERROR: 18452
suprisingly, every SQL Express tutorial that I could find never seems to mention anything about creating a user account. Can someone clue me in to what I am doing wrong? Or point me to a SQL Express tutorial that explains user accounts or logins so I can move on to step #2.
Thanks, -=Me=-
View 2 Replies
View Related
Apr 18, 2008
most my fields that I am interested in getting percentage values for have to be counted first.... I am having problems converting the counted values to percentages. I don't think I am doing it right...
I would greatly appreciate an example of a very simple matrix table with a counted value that displays percentages. I have found examples online but are part of a much more complex table and I can't seem to get thier formating to work on mine.
Kind regards,
Rich
View 6 Replies
View Related
Jul 2, 2007
Hi
I have got a query being executed on a view having half a million records each on 3 tables used in the view.
It takes 11 mins to execute it. However if I cancel the query in between at any time it shows me the first
381805 records always. I doubt if the query gets stuck somewhere because if I select top 381805 from table
the query is as fast as 23 seconds. I am using sql 2000 server.
Thanks
View 3 Replies
View Related
Jan 4, 2007
I know that the answer to my problem is something that lies right infront my eyes but I got totally stuck. I cant understand why, when I deploy a database application I've made, when the application is run for the second time, the database doesn't have the data I have entered the first time... Is it something I have to tell my application?
It must be something trivial but I just can't put my finger on it.
View 5 Replies
View Related
Dec 21, 2007
So, the main objective for this database/web application is reporting.
The issue/situation is:1. The customer has 10 goals2. Each goal has at least some detail shown in bullet points via a word document...this is how we'll get the data at first. we're going to develop the UI of the web application after the customer has more specs...main objective, get web reporting!3. Each detail could be broken down into a further / 2nd level of detail4. The 2nd level of detail could be further detailed by a 3rd level
...and, not knowing how in depth they'll provide the detail, there will be activities associated to points 2, 3, or 4...depending on how deep/specific the detail.
So, you might have one goal of "build a fence". And this goal only goes to the first detail level of say, "make it stucco" and that's it...no other detail.
A second goal might be, "landscape the front yard", and it's broken down into further levels of 2nd and 3rd:1. Make stepping stones**Three 2nd levels of:--1. Make path curvy--2. Use flagstone--3. Lead up to front door2. Plant trees**Three 2nd levels of:--1. Use 5 Aspens--2. Use 2 dogwoods--3. Use 1 maple3. Fill in bare spots**Two 2nd levels of:--1. Use bermuda*****example of 3rd level--2. Ensure dirt is fertilized----1. Use Miracle grow----2. Must be via hose, not granular
Now the tricky thing I'm stuck on is that be it the 1st level of detail, the 2nd, or the 3rd level, each could have activities. So, if the detail stops at the 1st level, activities will be tied to the 1st level. If detail stops at the 2nd level, activities will be tied to the 2nd level...and so on.
I was wondering about a detail table coming off the 10 goal/master table which would have columns of:1. Goal_ID2. Detail_Level_ID3. Detail_Level_Desc4. 2ndLvlDetail_ID5. 2ndLvlDetail_Desc6. 3rdLvlDetail_ID7. 3rdLvlDetail_Desc
What I'm wondering is how do I link the activities? Should I just have a 3rd table, "activities", and it would have:1. ActivityID2. ActivityDesc3. LinkID...and LinkID could be either tied to Detail_Level_ID, 2ndLvlDetail_ID, or 3rdLvlDetail_ID
This way I'm not too sure about referential integrity via the DB diagram to cascade deletes & updates because Detail_Lvl_ID could be non-unique.
Another option would be to have 4 tables:1. 10 goal main table2. 1st level of detail table3. 2nd level of detail table (links to 1st level)4. 3rd level of detail table (links to 2nd level)
..and the activites table would now have to be split into three different, i.e. DetailAct, 2ndDetailAct, 3rdDetailAct.
Right? Any suggestions are welcome.
Like I said, reporting is the main concern at first, and the customer wants to be able to either show/hide activities, so sub-reporting to the detail, 2nd lvl, 3rd level for the activities shouldn't be too bad.
Thanks!
View 1 Replies
View Related
Feb 23, 2006
Hello everyone..... First of all i have to say that a network engineer not a programmer. So here is my question..... A contractor built a web application in visual studio 2005 and sql server 2005 expess. It is on a windows xp pro developement box.... and the site runs in VS2005 but when it is put into IIS i get SQL errors and nothing runs. Can anyone help me sort out this issue because it has been dumped in my lap and i'm clueless....... when you goto the site you are supposed to be able to loging to a server page ( not windows Authentication) however, when you enter a name and password to log in it returns a sql error. i'm really getting tired of this issue so any help would be appreciated. I have included the SQLexpress error below.... thanks in advance!
Server Error in '/' Application.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734931
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
System.Web.UI.WebControls.Login.AttemptLogin() +105
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +510
View 2 Replies
View Related
Apr 29, 2002
I have log shipping set up between 2 servers. It works fine for 4 db's but will not work for the 5th. I have removed and recreated the log shipping again for this DB. (THe DB is actually not being used in production yet) What happens is the complete setup goes fine and the process starts. I come back after a few hours and notice the job is failing. WHen I go to the monitor side it shows the copy and restore work fine. The problem is that it keeps doing the file named db_name_initial_file.trn over an over again. Even though new files are being copied over, they are never restored, it just gets stuck on this one. Anyone seen this before?
View 3 Replies
View Related
Jun 22, 2005
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
TableName: Employees
EmployeeID............Date.............PayRate
-------------------------------------------
....1001...............1/24/03............12.50
....1002...............2/28/03............12.75
....1003...............5/14/03............10.50
....1002...............3/15/04............15.00
....1001...............6/22/04............14.00
....1002...............3/16/05............14.00
The result set should look like:
EmployeeID...........Date.............PayRate
-----------------------------------------
...1003................5/14/03............10.50
...1001................6/22/04............14.00
...1002................3/16/05............14.00
View 1 Replies
View Related
Mar 7, 2007
Hey guys, I am stuck on a homework question and I was hoping someone could help point me in the right direction.
the question is:
"find the book code and book title for each book whose price is greater than the book price of every book that has the type HOR"
now, I know how to find the book price of every book that has the type HOR but how do i minimize the results to show all books that have greater prices that the HOR books?
View 1 Replies
View Related
Feb 19, 2007
Hi
This would be easier if i show a table of data then try to explain what i need to do!
id - fac_id
1234 - 1
1234 - 2
2345 - 1
2346 - 1
2347 - 1
2347 - 3
Basically i need to change all fac_id which = 1 and change them to fac_id = 2, this is simple enough:
UPDATE SIC SET SIC_id = 2 WHERE SIC_id = 1
How ever, i need an exception where the 'id' is the same so i don't get duplicate entries.
So from the table above i need to change 2347 fac_id = 1 to fac_id = 2. I would NOT how ever change 1234 fac_id = 1 to 1234 fac_id =2 because there is already an id number with the same fac_id.
Hope this errrr makes sense!!
thanks
View 2 Replies
View Related
Jan 5, 2004
i'm using query analyzer,
simply trying to use smallmoney as
In my table I have declared as:
payrate smallmoney,
Inserting values into table like this:
'258'
Error recieved is:
Disallowed implicit conversion from data type varchar to data type smallmoney,
What am I doing wrong? Also, ideally i'd like the 258 to be expressed as £258 (english pounds) if anyone can help with that also.
Thanks in advance
View 4 Replies
View Related
May 13, 2008
This is my query:
SELECT aircraft_id, maximum_speed_mph
FROM us INNER JOIN rus
ON us.aircraft_id = rus.aircraft_id
WHERE maximum_speed_mph > ‘1000’
This is my error:
Msg 209, level 16, State 1, Line 4
Ambiguous column name ‘maximum_speed_mph’
Msg 209, level 16, State 1, Line 4
Ambiguous column name ‘Aircraft_id’
Msg 209, level 16, State 1, Line 4
Ambiguous column name ‘maximum_speed_mph’
Thanks for the help
US Navy - We are fueled, armed, and go for launch.
View 8 Replies
View Related
Jul 25, 2006
I've been working on this off and on for several weeks and I'm to a point in which I'm stuck again. I'm trying to figure out the code in which if the count is greater than 1 for the ogt_code 'Golf Course' then add 1 to the final count.
CREATE FUNCTION [dbo].[ngcoa_get_multi_golf_count] (
@org_cst_key_ext uniqueidentifier,
@todays_date av_date)
RETURNS int AS
BEGIN
declare @ret_value int
declare @ogt_code nvarchar(30)
select @ret_value = 0
select @ret_value = count(*)
FROM
co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
WHERE cxc_cst_key_1= @org_cst_key_ext
and cst_type='Organization'
and (cxc_end_date is null or cxc_end_date>@todays_date)
and cxc_rlt_code = 'Multi-Golf Parent'
and cxc_delete_flag=0
and cst_delete_flag=0
if @ret_value = 0
select @ret_value = 1
select @ogt_code = org_ogt_code
from co_organization (nolock)
where org_cst_key = @org_cst_key_ext
if @ogt_code = 'Supplier'
select @ret_value = 0
if @ogt_code = 'Allied'
select @ret_value = 0
if @ogt_code = 'Hotel/Conference Facility'
select @ret_value = 0
if @ogt_code = 'NGCOA Internal Vendor'
select @ret_value = 0
if @ogt_code = 'USGA Program'
select @ret_value = 0
return @ret_value
END
View 2 Replies
View Related
Aug 7, 2007
Hi there guys I am hitting my head against a brick wall trying to install SQL 2000 and SQL 2005 side by side and wonder if someone has a suggestion that may help please.
My operating system is win xp 32 bit, I have recently purchased this machine and it came with office 2007 already pre installed which comes with a named instance version of sql 2005 business contact manager, I then installed a default instance of sql 2000 (with SP 4)client tools and the server with no problem , I then purchased a cd of Microsoft SQL Server 2005 Standard Edition (32-Bit)for me to install to run alongside the 2000 version that is currently running on the same machine when all hell broke loose.
I selected the option saying install server components, tools , books online etc etc.
I agree to the licence agreement
Setup then installs Microsoft SQL Server 2005 Setup support Files succesfully, I press next...
Welcome to the Microsoft SQL Server installation wizard then comes up, I press next
System Configuration check brings back 14 successsful status messages and one warning message namely Edition Change Check warning
saying :
quote:Edition Change Check (Warning)
Messages
Edition Change Check
To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
I ignore above and press Next..
It then comes up with registration information which i succesfully complete, pressing Next
I then select all the components to install and press next
I then set it up to be a named instance 'MSSQL2005DATA', fine >> pressed next
Then I set it up to run under the local system account , fine. >> pressed next.
Then using windows authentication mode pressed next >>
Left collation settings as per default pressed next >>
Install the default configuration in the report server installation options message box, I pressed next >> which then comes up with the following message :
(SQL Server Setup failed to obtain system account information for the ASPNET account.To proceed, reinstall the .net framework and then run SQL server Setup again)
i don't have a copy of the .net framework so I select the install but do not configure the server option.
Setup then comes up with Ready to install message box which i then press the install button, which shows the following:
Setup Progress Files Status:
SQL SETUP Support Setup Finished
OWC11 Setup failed, Refer to log
coming up with a error:
ERROR 1706 Setup cannot find the required files,Check your connection to the network, or cd rom, For other potential solutions to this problem, see c:Program FilesMicrosoft OfficeOffice111033Setup.chm
I then press ok
and the setup progress fails to install :
OWC11
SQL SErver Database Services
Analysis Services
Reporting Services
Notification Services
Integration Services
Workstation Components
but I can't view any of these log files the only one that is available to view is sqlsetup0014_PCName_OWC11_1.log which comes up with the same message
MSI (s) (78:48) [12:44:04:581]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.
MSI (s) (78:48) [12:44:04:581]: Note: 1: 1706 2: -2147483647 3: OWC11.MSI
MSI (s) (78:48) [12:44:04:581]: SOURCEMGMT: Processing URL source list.
MSI (s) (78:48) [12:44:04:581]: Note: 1: 1402 2: UNKNOWNURL 3: 2
MSI (s) (78:48) [12:44:04:581]: Note: 1: 1706 2: -2147483647 3: OWC11.MSI
MSI (s) (78:48) [12:44:04:581]: Note: 1: 1706 2: 3: OWC11.MSI
MSI (s) (78:48) [12:44:04:581]: SOURCEMGMT: Failed to resolve source
MSI (s) (78:48) [12:49:43:192]: Product: Microsoft Office 2003 Web Components -- Error 1706. Setup cannot find the required files. Check your connection to the network, or CD-ROM drive. For other potential solutions to this problem, see C:Program FilesMicrosoft OfficeOFFICE111033SETUP.CHM.
Error 1706. Setup cannot find the required files. Check your connection to the network, or CD-ROM drive. For other potential solutions to this problem, see C:Program FilesMicrosoft OfficeOFFICE111033SETUP.CHM.
I have tried installing office 2003 to get the 0wc11.dll but to no avail it still gives me the same error..
Sorry this is a long winded post but can anyone shed a ray of light here.
Thank you.
View 8 Replies
View Related
Feb 17, 2008
Alright, I'll write this again. The first try didn't go so well. I am learning SQL Server from an online tutorial. Maybe there are a lot who do that, maybe not, but it's a good tutorial. There are projects to work on at the end of each section, and they involve more than just regurgitating everything I just read. They even make me figure out stuff that wasn't covered in the section (which is probably a mistake, but it makes the learning more interesting and I like it). The problem is that I've hit one of those places and I can't figure it out. The last project for this section is to write a function that calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder. Now I had to do the same thing as a query for the previous section, and hunting down all the formulae involved was more time-consuming than writing the thing, but I'm stumped here. Every example and work-along project was, for lack of a better description, "one function, one result". So how do I get a function to return multiple results? If I've posted this in the wrong place, someone please let me know where I might find the information I need. For those of you who may see this and be stunned that I haven't figured out the obvious, remember you were new once too. Thanks in advance.
Now that I've seen some of the other posts, maybe I should clarify. Here's what I had from the last project.
declare @Radius float(10),
@BaseDiameter float(10),
@BaseCircumference float(10),
@BaseArea float(10),
@Height float(10),
@SurfaceArea float(10),
@TotalArea float(10),
@Volume float(10)
set @Radius = 19.1
set @BaseDiameter = @Radius * 2
set @BaseCircumference = @BaseDiameter * PI()
set @BaseArea = power(@Radius,2) * PI()
set @Height = 16.27
set @SurfaceArea = 2 * (pi() * @Radius * @Height)
set @TotalArea = 2 * (pi() * @Radius * @Height) + 2 * (power(@Radius,2) * PI())
set @Volume = (power(@Radius,2) * pi()) * @Height
select @Radius as [Base Radius],
@BaseDiameter as [Base Diameter],
@BaseArea as [Base Area],
@Height as Height,
@SurfaceArea as [Side Area],
@TotalArea as [Total Area],
@Volume as Volume
go
So how do I make a function that does the same thing?
Schiz
View 3 Replies
View Related
Jul 23, 2005
HiI am trying to copy a 20 GB database from server 1 to 2. I took a fullbackup, copied to server2 and restored ( ready for use) using EM.After the message restore completed, I see the database's status as LOADING.We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv Servers.I ran dbcc on the source DB and it returned no errors. I tried the command'restore db with recovery ' which didnot help. I get an message saying thatthe data file is only partially restored.. It seems the last resort is tochange the status in system tables.Any ideas?ThanksRagu
View 13 Replies
View Related
