Help With ServerAgent Job Syntax
Apr 11, 2007
Hi,
Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.
I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.
I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.
The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:
USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)
How can I delete the records from the other four tables?
Thanks
View 6 Replies
ADVERTISEMENT
Jul 31, 2000
Iam trying to start SQL server agent but it gives the following error.
SERVICE CONTROL FAILURE
An error - 5 (Access is denied) occured while performing this service operation on SQLserveragent service.
Can anyone let me know what is the problem and how it could be solved.
Any help would be appreciated.
Thanks,
Kris.
View 1 Replies
View Related
May 13, 2006
I installed SQL2005 (first time). I have a couple of problems that perplex me.
I can connect to my instance when starting up SQL Management Studio no problem. After connecting, I can manually backup and restore DBs, no problem.
However, when I attempt to start SQL Server Agent - I get the follwoing message:
Unable to start service SQLAgent$GREATPLAINSV80 on server USIND-BAT3835N1.
===================================
The SQLAgent$GREATPLAINSV80 service on USIND-BAT3835N1 started and then stopped. (ObjectExplorer)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
The error log is as follows:
Date,Source,Severity,Message
05/13/2006 12:08:57,,Information,[098] SQLServerAgent terminated (normally)
05/13/2006 12:08:56,,Error,[382] Logon to server 'USIND-BAT3835N1GREATPLAINSV80' failed (DisableAgentXPs)
05/13/2006 12:08:56,,Error,[298] SQLServer Error: 772<c/> Cannot generate SSPI context [SQLSTATE HY000]
05/13/2006 12:08:56,,Error,[298] SQLServer Error: 772<c/> SQL Network Interfaces: The Local Security Authority cannot be contacted [SQLSTATE HY000]
05/13/2006 12:08:56,,Error,[000] Unable to connect to server 'USIND-BAT3835N1GREATPLAINSV80'; SQLServerAgent cannot start
05/13/2006 12:08:56,,Error,[298] SQLServer Error: 772<c/> Cannot generate SSPI context [SQLSTATE HY000]
05/13/2006 12:08:56,,Error,[298] SQLServer Error: 772<c/> SQL Network Interfaces: The Local Security Authority cannot be contacted [SQLSTATE HY000]
I researched using Microsoft SQL Home knowledge base for error 772, cannot generate SSPI context and [SQLSTATE HY000] and didn't find anything that seems to apply.
Why can I connect to the database engine but not start SQL ServerAgent?
View 4 Replies
View Related
Dec 22, 1999
I have found a huge prefomance degridation running something thru a Job on the server and running it through ISQL.
I have a sp the runs in about 24 min thru ISQL, but when I fire it off thru a job at night using the Agent, it take roughly 4.5 hours... Way slow...
Any thoughts?
View 1 Replies
View Related
Jun 15, 2007
After installing SQL Server 2005, a security scan was performed on the SQL server. Below are a few items that the scan identified within the Windows User Rights Assignment as potential vulnerabilities; it is worthy to note in Microsoft's defense that we lock things down pretty tightly in our IT shop. I suspect the SQL Server install assigns these OS privileges to the SQLServer and SQLServerAgent accounts by default. I have not heard of the 1st, 3rd and 4th below and suspect that they are not essential to the normal operation of SQL Server, but would like to know if anyone out there knows for sure. We are considering eliminating some or all of these privieleges for the SQLServer and SQLServerAgent system accounts at the OS level.
1) SQLServer and SQLServerAgent accounts have "Bypass Traverse Setting" privilege within Windows User Rights Assignment
2) SQLServer and SQLServerAgent accounts have "Log on as Batch Job" privilege within Windows User Rights Assignment. I realize I need this to schedule SQL Server jobs which run batch jobs and such, but any other reason to keep this privilege.
3) SQLServer and SQLServerAgent accounts have "Memory Quota" privilege within Windows User Rights Assignment
4) SQLServer and SQLServerAgent accounts have "Replace Process Token" privilege within Windows User Rights Assignment
Any guidance on this would be greatly appreciated.
Regards,
Jason
View 1 Replies
View Related
Sep 30, 2006
Hi,
I was looking at a previos thread in this very queston and the answers given to it, I tried a SSIS package that works fine on its own but on creating a new job and invoke it, the JOB fails ,it says its not able to locate the file specified,
I tried copying the package to the server machine wher am creating the job,but again the same error; and when i try to alter the protection level of the SSIS package to Server Storage its throwing an error like '' This protection level cant be applied to this destination,The system can't verify that the destination supports storage capacity. this error occurs when saving to XML."
I am using OLE DB Destination in the dataflow task of the SSIS package I ve created. Please guide whr am going wrong. Some detailed steps which has some screenshots depicting step by step procedure of creating a JOB that calls a SSIS package will be highly helpful
Thanks in Advance,
View 4 Replies
View Related
Dec 17, 2007
I have built a SSIS package which runs fine in BIDS. I went into SqlServer Management Studio and created a new job and job step. When Iselect the SQL Server Integration Services Package, I get the belowerror. There are no options on selecting a SSIS package.I searched this error for about 5 hours yesterday and the onlysolution I could find was a user who rebooted their server which fixedthis problem. I did restart the server, which did not fix theproblem.Can anyone help provide more information on this problem and thesolution to it? Thank you, Jason.The specified module could not be found. (Exception from HRESULT:0x8007007E) (SqlManagerUI)------------------------------Program Location:atMicrosoft.SqlServer.Management.SqlManagerUI.DTSJob SubSystemDefinition.Microsoft.SqlServer.Management .SqlManagerUI.IJobStepPropertiesControl.Load(JobSt epDatadata)atMicrosoft.SqlServer.Management.SqlManagerUI.JobSte pProperties.UpdateJobStep()atMicrosoft.SqlServer.Management.SqlManagerUI.JobSte pProperties.typeList_SelectedIndexChanged(Objectsender, EventArgs e)at System.Windows.Forms.ComboBox.OnSelectedIndexChang ed(EventArgse)at System.Windows.Forms.ComboBox.WmReflectCommand(Mes sage& m)at System.Windows.Forms.ComboBox.WndProc(Message& m)atSystem.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)atSystem.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32msg, IntPtr wparam, IntPtr lparam)
View 1 Replies
View Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
View 5 Replies
View Related
May 27, 2008
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
View 2 Replies
View Related
May 22, 2008
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
View 5 Replies
View Related
Apr 2, 2007
Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:
User ID
FirstName
LastName
Department
Site
i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results?
Any help would be appreciated. Thank you.
View 7 Replies
View Related
Aug 8, 2007
cmd.CommandText="Insert INTO personaldet(Firstname,Lastname,Username,password,dob,) values '('"+txtfname.Text.Replace("'", "''").ToString()+"','"+txtlname.Text+"','"+txtusername.Text+"','"+txtpassword.Text+"','"+txtdob.Text+"')'";
always says syntax error ')'.
check this plz
View 4 Replies
View Related
Nov 11, 2007
Hello, I have at sp that return a value:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getLastActivityDate]
(
@userid nvarchar(256)
)
AS
DECLARE @ret nvarchar(256)
SET @ret = (SELECT CAST(LastActivityDate AS nvarchar)
FROM aspnet_Users
WHERE UserName = @userid)
RETURN @ret
The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret?
// Tomas
View 2 Replies
View Related
Feb 24, 2004
I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)
Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent
Thank you for any help,
View 1 Replies
View Related
May 17, 2004
HI All,
I am writing some code where i want to get a list of all the company names that start with say, M...
here is what i have:
Dim M_company, companyID_M
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )
but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value???
thanks,
Leissa
View 4 Replies
View Related
Jan 6, 2005
I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.
ALTER PROCEDURE dbo.AddItemToCart
(
@Cart nvarchar(50),
@ItmID int,
@Qty int,
@ItmTyp char
)
As
Declare @AllItmCount Int
SELECT
@AllItmCount = Count(ItmID)
FROM
ShoppingCart
WHERE
Cart = @Cart
IF @AllItmCount = 0 /*new shoppong cart*/
INSERT INTO ShoppingCart
( Cart, Qty, ItmID, ItmTyp, Line )
VALUES
( @Cart, @Qty, @ItmID, @ItmTyp, 1)
ELSE /*some item entered */
DECLARE @CountItems int
SELECT
@CountItems = Count(ItmID)
FROM
ShoppingCart
WHERE
ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
IF @CountItems > 0 /* same item entered before then add together */
UPDATE ShoppingCart
SET Qty = (@Qty + ShoppingCart.Qty)
WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
ELSE /* Find the last line number */
Declare @LastLine Int
SELECT @LastLine = Max(Line)
FROM ShoppingCart
WHERE Cart = @Cart
/*Add a new line */
INSERT INTO ShoppingCart
(Cart, Qty, ItmID, ItmTyp, Line)
VALUES
(@Cart, @Qty, @ItmID, @ItmTyp, @LastLine + 1)
View 6 Replies
View Related
Apr 23, 2005
Hi all,
I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use:
SELECT ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies, (SELECT MAX(AddedDate) FROM (SELECT ForumID, AddedDate, Author FROM dbo.Forums_Topic UNION ALL SELECT ForumId, AddedDate, Author FROM dbo.Forum_Reply) AS dates WHERE (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM dbo.Forums
Any suggestion on how to show the author of the last message as well? Thanks in advance.
View 27 Replies
View Related
Dec 6, 2005
i want to find some repeatable data in a column,is there a sql syntax here?Thanks in advance.
View 1 Replies
View Related
Jan 7, 2002
Hello,
I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?
SELECT TimeZone.LongZone, SiteMain.SiteNumber,
SiteType.SiteTypeDescription
FROM SiteType INNER JOIN
TimeZone ON SiteType.id = TimeZone.id INNER JOIN
SiteMain ON TimeZone.id = SiteMain.TimeZone AND
SiteType.id = SiteMain.SiteType
View 1 Replies
View Related
Feb 5, 2002
Can someone help me with the syntax for an if else statement such as:
select (if column(a) > 1) then print '+' else print '-')
,column(b)
,column(c)
,etc.
from table...
BOL doesn't seem to be much help for this
Thanks in advance!
CP
View 1 Replies
View Related
May 15, 2002
I have some problem for construction Sql statement to execute in asp pages.
In the table the data is stored in such a way that
Worknote Id,IncidentId, SeqNo , Desc1,Desc2.
1111 2004 1 - Test1 Test2
2222 3000 1 - Test1 Test2
2222 3000 2 - Test3 Test3
I want to take the maximum of IncidentId (3000) and concatinate the description (Test1 TEst2 Test3 Test4)
I dono how to construct SQl to fetch records . I tried some way , it gives only the last row.
SELECT iWorkNoteID, SeqNum, vchWorkNote1, vchWorkNote2 from incidentworknote where iWorkNoteID = (Select max(iWorknoteID) from incidentworknote where iIncidentID = " & iIncidentId & ")"
Can anyone helop me to find out.
View 1 Replies
View Related
Jun 5, 2000
I have a sql database which is not a backed up copy.....just a copy of the dbf file. I have tried everything in the enterprise manager to get the file re-introduced to no avail. I was told that you could execute the DB in the query analyser with the following
execute sp_attach_db @filename
but it does not like the syntax....can anyone please help.....
View 1 Replies
View Related
Oct 25, 2000
is it posible to run one sql query to check 20 tables to find a value?
Let say i have 20 tables e.g. table1, table2, table3 e.t.c all have a column call import_instance_ID what sql statement do i run to check all tables where import_instance_ID = 2 ??
can someone help me with the syntax pls.
View 2 Replies
View Related
Nov 10, 1999
Excuse my ignorance. I have been reading and studying this stuff
but still some questions.
Table Datatest Table B
ID Q1 Q2 Output ID Field1 Field2
1 Y N
2 Y Y
3 N N
Everytime someone adds a record to Table Datatest I want that ID
automatically
added to Table B.
What is the best way to do that? Is this were I use a trigger and
an
update or is there an easier way? Also case expressions again.
How do I add the results from a case to the same table using a trigger?
Like: 100 for ID 1
223 for ID 2
SELECT ID,
CASE WHEN Q1 = 'Yes' THEN 100
ELSE 0 end +
CASE WHEN Q2 = 'Yes' THEN 123
ELSE
end as Output,
FROM Datatest
Thanks for your help
View 1 Replies
View Related
Dec 20, 1999
Hi !
If I have 2 tables:
(Table) Items(id int,Description varchar(23))
Table2 Customer(id int,name varchar(25),item1 int, item2 int, item 3,int)
Now !
If I want to see a description of a specific tiem I do this:
select description from items,customer where customer.id = 1 and customer.item1 = items.id
That works fine but how do I dom if I want to have the dexcription on all three items ?:
I tried
select description from items,customer where customer.id = 1 and customer.item1 = items.id or customer.item2 = items.id or customer.item3 = items.id
but it didnt work
View 1 Replies
View Related
Jun 22, 2003
Hello members, I have a trouble in retrieve correct query by SQL syntax. Ok I have use
SELECT * FROM tableOne WHERE UserID = 'mugun' AND First_Name LIKE '%tom%' OR Last_Name LIKE '%tom%' ORDER by First_Name ASC
Ok the probem is lets say, we have name TomCruise in our DB in First_NAme and Last_Name column. It return a correct value which is TomCruise. But if we change the syntax like this:
SELECT * FROM tableOne WHERE UserID = 'mugun' AND First_Name LIKE '%tomcruise%' OR Last_Name LIKE '%tomcruise%' ORDER by First_Name ASC
There is no result for the above statement. Why? Please help me . Thanx in advanced.
View 2 Replies
View Related
Apr 18, 2005
Here is the scenario:
Table A has customer info (name and address)
Table B is a phone log table...a new row is created for each new call. In the table is a Date (when the customer was called) and a column named 'stage' that will be filled out either with the word 'follow up', 'dead', or null.
So the relationship from Table A to B is 1 to many, respectively.
I want to ask the two tables this question: Give me name and address (from Table A) and LATEST date customer was called, but ONLY if the 'stage' column says 'follow up'.
Here is the problem...Dr. Jones has three rows in Table B, his latest call was today, but stage is set to 'dead'.
His second line in the table was called yesterday, with the designation of 'follow up'.
When I run my query, I pull out the row from yesterday, since it meets the criteria of 'follow up'....but this is wrong, as I only want the latest designation of 'follow up' IF its the lastest date for this customer.
Here is my syntax:
SELECT DISTINCT
MAX(tableB.date) AS latestdate, tableA.CONTACT, tableA.ADDRESS1,
FROM tableB INNER JOINTableA ON TableB.ACCOUNTNO = TableA.ACCOUNTNO
GROUP BY tableA.CONTACT, CONTACT1.ADDRESS1
HAVING (tableb.stage = 'follow-up')
Please help...thank you
View 3 Replies
View Related
Jul 21, 2005
Here is my situation:
Table A has three columns (aaa1, aaa2, aaa3)
Table B has three columns (bbb1, bbb2, bbb3)
Common field is aaa1 = bbb1
I write a query that retrieves 3 rows out of table a
(select aaa2, aaa3
from table a
where (aaa2 = green) and (aaa3 = yellow))
----------------------------------------------------------
When I now create a left outer join onto Table b, and add one more additional criteria to the where exisiting ones(aaa2=green,aaa3=yellow) which is bbb2='white', I expect to at least see my original three rows of data (since of my left outer join), but now only receive 2 rows of data, since the third row does not have bbb2 as white.
Im confused...I was expecting to see three rows of data, with the bbb2 column being null for the third row....but instead I only get two rows of data...how can I write my query that will give me what I expected ?
Thank you
View 2 Replies
View Related
Aug 9, 2005
I have a field in a MS SQL 2000 database table with data type of 'NAME (varchar)', with the length of 64.
I want to delete all values in this field that are 25 characters or longer.
What would my syntax be ?
Thank you !
View 5 Replies
View Related
May 9, 2002
Can someone tell me why the first example will work, but not the second ???
-------------------------------------------------------
SELECT RIGHT(name, CHARINDEX(',', name) +1)
FROM Sheet1
-------------------------------------------------------
SELECT RIGHT(name, CHARINDEX(',', name) -1)
FROM Sheet1
-------------------------------------------------------
I get an error message "Invalid length parameter passed to substring function"...
If I place additional ( ) around "CHARINDEX() -1", SQL Server parses them out...
View 1 Replies
View Related
