I am querying a SQL Server 2000 server remotely and have couple of small syntax problems using the WHERE Clause in a Select statement
1. I wish to extract all records where the first 2 characters of a Varchar field (postcode) do not start with 2 specific characters (BT)
2. I wish to extract all records where a Char field is not blank (ie has at least 1 character in it)
Many thanks
Mike
Hi, I have a simple problem that I need a quick solution for. I hope someone can help.I have a list of email addresses that contain many different email providers (hotmail, yahoo, gmail, etc)What I'm trying to do, it select all emails that are not from a select list of providers...Here is the code I have so far... (this isnt giving me what I want)SELECT EmailAddress as Email FROM TBL_EmailAddresses WHERE EmailAddress <> '%aim.com%' OR EmailAddress <> '%hotmail.com%'OR EmailAddress <> '%msn.com%'OR EmailAddress <> '%yahoo.com%'OR EmailAddress <> '%gmail.com%'OR EmailAddress <> '%aol.com%'I want to select all emails that are not of the above types. Thanks in advance.
View 5 Replies View RelatedI cannot work out what I need to do to fix the query attached. I am getting the error Line 105: Incorrect syntax near ')'.
Thank you for your helpCREATE PROCEDURE spMHCPall
(
@type int
)
AS
Select
fname, lname,
dtmenroll,
ysnenroll,
lngtype,
lngprimaryprovider ,
Type,
strreason,
provider,
lngenrollmentid,
encounterdate,
encounterdate2,
countencounter,
countlocus,
counttplan,
countmrpt,
counttplanCBT,
counttplanCBT16,
countgroupattenance,
dtmbaseline,
dtmenrollment,
satsurvey,
encountercount,
countcontact,
contactdate,
psymdconsults,
countQOLSurvey,
lngpatientid,
Case When
( Case When
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )>IsNull(Date3,'1900-01-01')
Then
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )
Else
IsNull(Date3,'1900-01-01')
End)>IsNull(Date4,'1900-01-01')
Then
( Case When
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )>IsNull(Date3,'1900-01-01')
Then
(
Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01')
then IsNull(Date1,'1900-01-01')
Else IsNull(Date2,'1900-01-01')
End )
Else
IsNull(Date3,'1900-01-01')
End)
Else
IsNull(Date4,'1900-01-01')
End MaxDate,
Case When COALESCE(Date1,Date2,Date3,Date4) > DATEADD(M,-1,CONVERT(CHAR(8),GETDATE(),112)) then 'false' When COALESCE(Date1,Date2,Date3,Date4) is null then 'false' else 'true' end ysncontact
From
( Select A.lngpatientid, ysnenroll,lngtype, lngprimaryprovider, strreason,dtmenroll, lngenrollmentid,
'Date2'=(Select max(dtmcontact) FROM tblMHCPcontact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ),
'Date4'=(Select max(B.dtmenroll) FROM tblMHCPEnrollment B where A.lngpatientid=B.lngpatientid and B.lngtype = @type),
'Date3'=(Select max(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ),
'psymdconsults'=(Select count(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and B.providerid = '1'),
'countlocus'=(Select case when count(dtmdate)= 0 then 'N' else 'Y' end FROM tblMHCPLOCUS B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmdate between dateadd(month, -6, getdate()) and getdate() ),
'counttplan'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPtreatmentplan B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmstart between dateadd(month, -6, getdate()) and getdate()),
'countmrpt'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -1, getdate()) and getdate()),
'counttplanCBT'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, -1, getdate()) and getdate()),
'counttplanCBT16'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, +3, getdate()) and getdate()),
'countgroupattenance'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and dtmgroup between dateadd(day, +7, getdate()) and getdate()),
'contactdate'=(Select max(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'dtmbaseline'=(Select max(dtmintake) FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and ysnbaseline = 1),
'dtmenrollment'=(Select max(dtmenroll) FROM tblMHCPenrollment B where A.lngpatientid=B.lngpatientid and A.lngtype=b.lngtype and ysndisenroll is null),
'contactcount'=(Select case when count(dtmcontact)= 0 then 0 else 1 end FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'countcontact'=(Select count(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()),
'satsurvey'=(Select case when count(dtmSurvey)= 0 then 'N' else 'Y' end FROM tblMHCPClientSat B where A.lngpatientid=B.lngpatientid and dtmSurvey between dateadd(day, -70, getdate()) and getdate()),
'encountercount'=(Select case when count(visitdate)= 0 then 0 else 1 end FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'encounterdate'=(Select Max(visitdate) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'countencounter'=(Select count(visitdate)FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()),
'countSatSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()),
'countQOLSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()),
'Date1'=(Select max(dtmgroup) FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and A.lngtype = @type),
'fname' = (Select strfname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) ,
'lname' = (Select strlname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) ,
'Type' = (Select strtype FROM tbllkpMHCPEnrollment as P where A.lngtype=P.lngtype ),
'Provider' = (Select fname + ' ' + lname as pName FROM tbllkpMHCPprovider as P where A.lngprimaryprovider=P.staffid)
FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()))
From tblMHCPEnrollment A where A.lngtype = @type and ysnDisEnroll IS NULL) AS lngpatientidDate
HiCan someone please tell me whats wrong with the last line of the querybelow. The first three lines work fine but when i add the fourth line i getan error message (see text at ERROR MESSAGE)sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint ='J'" & _"AND dathergestuurd Is Not Null " & _"AND PerBankKas Is Null " & _"AND " & HTdatumMinAantalDagen & " > " & dathergestuurdThe last line is a comparison between two dates.ERROR MESSAGE============================================Run-time error 3075Syntax error (missing operator) in query expression 'FaktuurGeprint ='J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND22-11-2005 >'========= end error message==============================The first date (22-11-2005) is visible in the error message but the seconddate is missing.As you can see there is nothing after the > but there should be dateinformation from a DB-cell named dathergestuurd.I tried a lot of things but i keep getting the same message.What is wrong with the syntax of the last line ???T.i.a.RegardsTino WintershovenThe Netherlands
View 4 Replies View RelatedI have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter". The Branch Parameter is a numeric field.
I need (Branch = %) wildcard to select all the records. Can someone tell me the proper syntax for this?
SELECT EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM dbo.vw_Compliance_Percentage_EmployeeWHERE (Branch = @branch)ORDER BY Branch
I receive the message failed to convert parameter value from string to an int32
Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As New DataSet() Dim sGetRooms As String = "SELECT RoomID, RoomType, " _ & "RoomName FROM Rooms2 " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomType" Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _ & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomPrice" Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString Using con As New OleDbConnection(sConnect) Dim da As New OleDbDataAdapter(sGetRooms, con) Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10) param.Value = Request.QueryString("RoomType") & "%" da.SelectCommand.Parameters.Add(param) Try da.Fill(ds, "Rooms2") da.SelectCommand.CommandText = sGetroomsizeandprice da.Fill(ds, "roomprices") Catch ex As Exception Label4.Text = "ERROR: " & ex.Message Exit Sub End Try End Using Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID") Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID") Dim dr As New DataRelation("MenuLink", pkcol, fkcol) ds.Relations.Add(dr) DataList1.DataSource = ds DataList1.DataMember = "Rooms2" DataList1.DataBind() End Sub
View 2 Replies View RelatedHi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
View 1 Replies View RelatedI am getting the following syntax error to my query below. This query is similar to many I currently use and I have not been able to track down the error. Can anyone spot the error?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'data'.
Select
e.account_number,
epi.Provider_Role = [1],
epi.Provider_Role = [2],
epi.Provider_Role = [3],
epi.Provider_Role = [4],
epi.Provider_Role = [5],
epi.Provider_Role = ,
epi.Provider_Role = [7],
epi.Provider_Role = ,
epi.Provider_Role = [9],
epi.Provider_Role = [10],
epi.Provider_Role = [11]
from (SELECT e.account_number,
row_number() over (partition by epi.PROVIDER_ROLE order by e.account_number asc) as rownum,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
CONVERT(int,pm.PatientAge),
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
pm.PrinOpPhys,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
) data
PIVOT
(max(epi.Provider_Role) for rownumber
in ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11] )) pvt
order by e.account_number
Thanks!
Hello experts!
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
Any help is appreciated.
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.
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.
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!
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
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
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.
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
View 3 Replies View RelatedHi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
View 10 Replies View RelatedHi,
For many to many relationship, what is the minimum number of tables required.
Hi plz read below is my scenarioI have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.Thanks in advance.bye bye.happy coding!!!
View 1 Replies View RelatedExperts,
I've few questions regarding SQL Server. Appreciated, If any one out there help me with it.
I would like to do T-SQL Programing and would like to know some good book. I'll mostly be doing Stored procedures,Triggers and views programming. Any advice?
On my sql server I've 10 logins. But, I cannot view these logins from the Enterprise GUI. But, When i run a query against the syslogin table, those all 10 logins shows up. Why is that?
any help would be greatly appreciated.
Access 97.................
I'm trying to construct the following SQL statement but am missing something somewhere.
I get a syntax error with the 1st FROM highlighted.
If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.
I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.
SELECT
Type,
Sum(Volume) AS Volume,
TotalVolume
FROM
>>>>>>>>>>this following bit works............
(SELECT TOP 5
VotingUniverse_TopFirmsVolumeByType.Type,
VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
WHERE
(((VotingUniverse_TopFirmsVolumeByType.Volume)
In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
ORDER BY Volume DESC)))
ORDER BY
VotingUniverse_TopFirmsVolumeByType.Volume;)
>>>>>>>>>>>end of bit that works
INNER JOIN
VotingUniverse_TotalVolumeByType
ON
Type = VotingUniverse_TotalVolumeByType.Type
GROUP BY
Type,
TotalVolume;
hope this makes sense to someone anyway
thanks
====
Paul
I have a xxx.exe running on SQL 2000 server as a sql job every hour. For some reason this job sometime just hung in the middle and never finish. But
if I stop it and rerun it, it will be down within 10 seconds. I am wondering
if we can set up something so that the job aborts automatically if it ran more than 10 minutes.
Hi everyone.
We are running Sql 6.5 sp5 on a Pentium II 350 Mhz with 512 Mb. RAM, 1 ScSi 4 Gb hd, one 8Gb ScSi hd and 2 IDE hd (13,6 Gb and 8Gb) computer running windows NT 4.0 sp 6 without any problems.
Now have bought a new computer. The new one is a Pentium III 733 Mhz 512 Mb Ram with 2 ScSi ultra wide 2 18Gb each and one 40 Gb Ide Hd. We have installed windows NT 4.0 sp 6 and Sql 6.5 sp 5. We have restored our database to make some tests to it. The database is 7 Gb big.
The results of our test are that the old computer is between 20% and 30% faster than the new one when doing sql instructions and using the database even knowing that the hd´s of the new computers are 4 times faster than the old ones.
Would you please give us any help or clue that would make the new computer go faster?
We know that upgrading to sql 7 will work but we need first to make this computer work better with the 6.5.
Thank you.
Hello,
When I try to run my asp file it gives me this error. :mad:
Technical Information (for support personnel)
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
/conOpen_inc.asp, line 10
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Page:
GET /Default.asp
Anyone can tell me whats happening ? thank you
I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”.
Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
Thanks
Dear All,
I have a client requirement. We are integrating two applications. So the problem is that, whenever some updates (meaning to say "create" , "delete" , "update") happens in the SQL DB Tables, i needed to track that data and send it to a different system.
What I have thought abt is that if SQL could generate a file with all the values deleted/updated/created in the DB, then I could take those values and do the needful. Please help me..
Thanks,
Sanjo
Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:-
This is my input table
TableA
ID A B C
------------------------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn
My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
-----------------------------------------------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl
Please help me on how to create my output.
Thanks in advance,
Sid.
Hi,
I have a table structure like this
TableName: Common
Columns
PartnerId: int
NativeId: int
FirstName: nvarchar(50)
LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId,
2) if duplicates exists in the above condition i should select top 1 (first record)
How can i do it??
TIA,
sudheer
Hi,
I have been SQL developer for past 2 yrs, want to get into Administration(DBA) can any one suggest me some good articles on net and good books for this.
TIA,
sudheer.
Hi experts,
I am upgrading my database from SQL Server 2000, SP4 to SQL server 2005, SP2.
1) Is it required to recreated my user defined functions? I mean drop them and recreate them again?
2) Do I need to recreate the views and Stored procedure?
3) Do we have a checklist from Micorosoft for upgradation?
Thanks in advance
Regards
Sachin
Don't sit back because of failure. It will come back to check if you still available. -- Binu
Hey all, I need some help to build some data bases and maybe some morestuff. Please reply only if you can do it volunteerly or very cheap please;but there is incentives in time..TIA
View 4 Replies View RelatedI have a tableCreate Table Payments {paymentid int,customerid int,amount int,date datetime}What I want is the sum of the amounts of the last payments of all customers.Now the last payment of a customer is not necessarily the one with thehighest paymentid for that customer BUT it is the one with the highestpaymentid on the MOST RECENT date. We dont keep the time part just the dateso if there are more than 1 payments of a customer on a date ( and there aremany such cases ) only then the paymentid decides which is the last payment.Further the last payment may be the last as of today but I may want to findthe sum of all the last payments upto say March 1, 2003or any date. My own solution is too slow even it is correct.SELECT SUM( AMOUNT )FROM PAYMENTS AS P1WHERE PAYMENTID =( SELECT MAX( PAYMENTID ) FROM PAYMENTS AS P2 WHERE P1.CUSTOMERID =P2.CUSTOMERID AND DATE =( SELECT MAX(DATE) FROM PAYMENS AS P3 WHERE P3.CUSTOMERID = P2.CUSTOMERIDAND DATE < #9/8/03# ))What would be the most efficient solution to this.Both in SQL Server and in Access 2000thx in advance
View 3 Replies View RelatedI am using the following T-SQL to pull records from a given table that have a start time between 6:59:59PM and 7:00:00AM. However, it's not working. The SQL I'm using is listed here. What am I doing wrong?
select RecNum, convert(varchar(8), StartTime, 8) as Start_Time from TableA
where convert(varchar(8), StartTime, 8) between '18:59:59' and '07:00:00'
By the way, there are a multitude of records that I'm looking for in the table.