First of all, hello and good morning, my question is, you can check SQL syntax in SQL Server with secondary button mouse or "Check SQL" button in toolbar (Microsoft Management Console 1.2).
I´d like to know if there´s a way to use these Server tools from Visual Basic 6 SP6, something like APIs ...
If there´s no solution, can anybody give me an idea of how to check SQL syntax in VB.
The application wants the users to make their own SQL sentences, (they just can write whatever they want ?????)
I'm getting the following error messages: Incorrect syntax near the keyword 'in'. Server: Msg 156, Level 15, State 1, Line 38 Incorrect syntax near the keyword 'group'.
/* create temp tables */ select distinct d_vst_id as 'DRW_ID' ,d_vst_instid as 'DRW_INSTID' into temp_tb1 from dnr_vst_db_rec where d_vst_instid = '' and d_vst_dontyp = 'WB' and d_vst_status = 'DN' and d_vst_date between 20020301 and 20030228 order by d_vst_id
Select distinct n_per_id as 'ID1' ,n_per_gender as 'GENDER' ,n_per_birth as 'BIRTH1' ,d_bty_abo + d_bty_rhesus as 'ABO1' ,n_adr_city as 'CITY1' ,n_adr_zip as 'ZIP1' into temp_tb3 from temp_tb1 right outer join nat_per_db_rec on DRW_ID = n_per_id right outer join dnr_bty_db_rec on DRW_ID = d_bty_id right outer join nat_adr_db_rec on DRW_ID = n_adr_id where DRW_INSTID = '' order by n_per_id
select distinct getdate() ,d_aaa_insthdg , case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as 'AGE' ,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1' ,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL' from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a where a.d_vst_instid = '' and a.d_vst_instid = d_aaa_instid and a.d_vst_id = ID1 and a.d_vst_instid = n_per_instid and a.d_vst_id = n_per_id and n_per_gender = 'M' and a.d_vst_btcdte between 20020301 and 20030228 and a.d_vst_btcdte = (Select max(b.d_vst_btcdte) from dnr_vst_db_rec b where b.d_vst_instid = '' and b.d_vst_status = 'DN' and b.d_vst_dontyp = 'WB' and b.d_vst_id = a.d_vst_id and b.d_vst_btcdte between 20020301 and 20030228)
group by d_aaa_insthdg ,case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as
when I try to create this SP I get: "incorrect syntax near @MyResult" I have tried INT and different variable names, but get same error.
CREATE PROCEDURE sp_IsValidLogon @UserName varchar(16), @Password varchar(16) , @MyResult varchar(3) OUTPUT As if exists(Select * From User_Table Where UserName = @UserName And Password = @Password) begin @MyResult = 1 end else begin INSERT INTO FailedLogons(UserName, Password) values(@UserName, @Password)
declare @totalFails int Select @totalFails = Count(*) From FailedLogons Where UserName = @UserName And dtFailed > GetDate()-1
if (@totalFails > 5) UPDATE User_Table Set Active = 0 Where UserName = @UserName
DECLARE @NAME AS varchar(1000) DECLARE @ADDRESS AS varchar(1000) Declare CUR_C Cursor For Select Rolename From DCJ_SecurityRole Open CUR_C Fetch Next From CUR_C into @NAME While @@fetch_status =0 Begin IF @NAME not in('All','PUBLIC') Begin SET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com' INSERT INTO #UserDetail EXEC (' SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME, initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY (ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM ''''LDAP://DC=XYZ,DC=com'''' WHERE MemberOf=''''' + @ADDRESS +''''' '') ') END Fetch Next From CUR_C into @NAME END CLOSE CUR_C DEALLOCATE CUR_C
When I executes this code I am getting some syntax error like Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'DCJAppDev'.where APJPRD is one of the role, Can anyone suggest what dynamic sql part has error??
if(@b='1') begin select * into #example from example select * from #example drop table #example end else begin select * into #example from example select * from #example drop table #example end With syntax check, I always get "'#example already exist'" But why? Just because of two "select into temp table" operation? I am confused. Thank you.
I am trying to write a SQL Server query that archives x-days old data from [Archive].[TestToDelete] to [Working].[TestToDelete]table. I want to check that if the records on ArchiveTable do not exist then insert then deleted...which will be converted to a proc later.. archives x-days old data from [Working].[TestToDelete] to [Archive].[TestToDelete] table */Here is the table definition, it is the same for both working and archive tables. There are indexes on: IpAddress, Logdate, Server, User and Sysdate (clustered).
CREATE TABLE [Archive].[TestToDelete]([Server] [varchar](16) NOT NULL,[Logdate] [datetime] NOT NULL,[IpAddress] [varchar](16) NOT NULL,[Login] [varchar](64) NULL,[User] [varchar](64) NULL,[Sysdate] [datetime] NULL,[Request] [text] NULL,[Status] [varchar](64) NULL,[Length] [varchar](128) NULL,[Referer] [varchar](1024) NULL,[Agent] [varchar](1024) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]/* the syntax, which will be converted to proc is: */SET NOCOUNT ON;DECLARE @Time DATETIME,@R INT,@ErrMessage NVARCHAR(1024)SET @R = 1/* set @Time to one day old data */SET @Time =
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....
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.
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
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?
) ) ) ) ) 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
Case Statement might be the solution but i could not do it.
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)
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.
I need a job run page to fire a job on a sql server if the job is not already running. How do I check if the job is running on the MSSQL server. Can I use the sp_job_help as it does return 4 data sets with the first having the data I need, but as yet I have not mastered a multi data set return.
I want to built a table to a form.... I have some check boxes in this form.... what is the script line for this check box ? I know it is suppose to work 1 or 0 ? for false or true ..... How I suppose to do this ?
Hello,I Have a code:<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [shop_clients] ([ID], [IMIE]) VALUES (@ID, @IMIE)"> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="IMIE" Type="String" /> </InsertParameters> </asp:SqlDataSource> SO this code It will allow me insert to database SQL textbox - name ?If yes How I can:If I click the button My textbox - name, insert to databasePlease me help :)
Aperently I could not insert a text field to another table from INSERTED in a trigger.It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable which is the table that has this trigger INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText) SELECT i.myTrID, i.myFirstName, p.myBigText FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID WHERE (i.myTrType = 'In') Thanks,
Hi I was wodering how to add an OR statment right in the Check Constraint expression. This is what I am starting with in the database ([zip] like '[0-9][0-9][0-9][0-9][0-9]') and what I want well not exact but this would answer my question ([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]') Thanks for any help
Hi, I have two web pages in one web page i have 5 check boxes. For example if the user checks the Checkbox1, checkbox2 and clicks on button. On the button click I am storing the selected checkboxes value in database lke the following: Year Options xxx 1 xxx 2 in the above format( user selectes checbox1, check box 2). And in the Second Web page I am showing the 5 checkboxes but in this web page I need to check the first and second checkboxes on the page load because user selectes those two check boxes in the first web page. my select query returning the results like this: Options 1 2 based on options I have to check those corresponding check boxes in the second web page. How to achive this. Thanks in advance
I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist? Here's my insert code. string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection); string usercode = GenPassWithCap(9); String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@id, @firstname, @lastname, @office, @team)"; SqlCommand myCommand = new SqlCommand(insertCmd, myConnection); myCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 10)); myCommand.Parameters["@id"].Value = usercode; myCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50)); myCommand.Parameters["@firstname"].Value = txtFirstName.Text; myCommand.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar, 50)); myCommand.Parameters["@lastname"].Value = txtLastName.Text; myCommand.Parameters.Add(new SqlParameter("@office", SqlDbType.VarChar, 75)); myCommand.Parameters["@office"].Value = dwnOffice.SelectedValue; myCommand.Parameters.Add(new SqlParameter("@team", SqlDbType.VarChar, 20)); myCommand.Parameters["@team"].Value = dwnTeam.SelectedValue; myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); Do I run a completey different select command before hand and try to match that field?
Hi, I'm trying to populate a table in one database with details from a identical table in another database. How do I check to see if the row exsits before I insert the data because at the moment I'm getting a violation of a primary key error.my codes something like the below
insert into db2.table1(values) select * from db1.table1, db1.table2 where t1.id = t2.id
Hi I have two tables. one MasterDetail and second is countrydetail In master detail i have MasterDetailId (Primary) and countryId. In CountryDetail table I have Countrid (Primary),CountryName. I don't want to give relationship because i can insert null value in countryid in MasterDetail table. So i have wriiten query like this -> select c.CountryName,m.MasterDetailId from MasterDetail m,CountryDetail c where c.CountryId=m.CountryId and m.MasterDetailId= '2' If In MasterDetail table if CountryId is null then it will not show me any record. So I want that record and its value with this query and checking null values. Help me about solving this query. Thank You
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???