Check SQL Syntax

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 ?????)

View Replies


ADVERTISEMENT

Check The Syntax

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

View Replies View Related

Check Syntax Of All SP

When I want to remove a table or a column in a table, I need to change all the SP where that table/column is beign called. Is there a way to "recompile" or check the syntaxis of all the existing SP to make sure that I made all the correct changes?.

Thanks

View Replies View Related

Check The Syntax

default is tha constraint?
if i want to add default value to my column this is corect?

alter table tab1 alter column a add default 0

View Replies View Related

Please Check This SP Syntax - Need More Eyes!

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

@MyResult = 0
end

View Replies View Related

Dynamic Sql Syntax Check

CREATE TABLE #XYZ(
NTUser varchar(20),
FullNTName varchar(50),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(40),
Rolename varchar(30))

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??

View Replies View Related

How SQL Server Do Syntax Check?

declare @b nvarchar(1)
set @b = '1'


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.

View Replies View Related

T-SQL Syntax To Check For Nulls And Empty In One Statement

What is the T-SQL command to check for NULL or '' in a field in one statement?   I would like to change the following code to be more readable (without the OR).
 
IF @agent IS NULL OR @agent = ''

 
 
Thanks!

View Replies View Related

Syntax Check Needed On Case Statement

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.

View Replies View Related

CHECK CONSTRAINT And WITH CHECK Option: SQL 2000 Vs SQL 2005

Hi

I have got this script and I was comparing the execution plan query at the end on both SQL 2000 and SQL 2005.

 

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VW_PTN')

    DROP VIEW VW_PTN

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TB_PTN_1')

    DROP TABLE TB_PTN_1

GO

-- CREATE PARTITION TABLE 1

create table [dbo].[TB_PTN_1]

(

    [PTN_ID] int IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL ,

    [DATE_TIME] DATETIME NOT NULL,

    [PTN_NUMBER] int NOT NULL

) ON [PRIMARY]

GO

-- ADD PRIMARY KEY

ALTER TABLE [dbo].[TB_PTN_1] WITH CHECK

    ADD CONSTRAINT [PK_TB_PTN_1] PRIMARY KEY CLUSTERED

    ([PTN_ID], [PTN_NUMBER])

ON [PRIMARY]

GO

-- ADD CHECK CONSTRAINT ON PTN_NUMBER

ALTER TABLE [dbo].[TB_PTN_1] WITH CHECK

    ADD CONSTRAINT [CK_TB_PTN_1] CHECK (PTN_NUMBER=1)

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TB_PTN_2')

    DROP TABLE TB_PTN_2

GO

-- CREATE PARTITION TABLE 2

create table [dbo].[TB_PTN_2]

(

    [PTN_ID] int IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL ,

    [DATE_TIME] DATETIME NOT NULL,

    [PTN_NUMBER] int NOT NULL

) ON [PRIMARY]

GO

-- ADD PRIMARY KEY

ALTER TABLE [dbo].[TB_PTN_2] WITH CHECK

    ADD CONSTRAINT [PK_TB_PTN_2] PRIMARY KEY CLUSTERED

    ([PTN_ID], [PTN_NUMBER])

ON [PRIMARY]

GO

-- ADD CHECK CONSTRAINT ON PTN_NUMBER

ALTER TABLE [dbo].[TB_PTN_2] WITH CHECK

    ADD CONSTRAINT [CK_TB_PTN_2] CHECK (PTN_NUMBER=2)

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VW_PTN')

    DROP VIEW VW_PTN

GO

-- CREATE PARTITIONED VIEW

CREATE VIEW VW_PTN

AS

    SELECT * FROM TB_PTN_1

    UNION ALL

    SELECT * FROM TB_PTN_2

GO

set showplan_text off

go

set showplan_all on

go

SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2

go

set showplan_all off

go

 

This is what I see in SQL server 2000

 

SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2
  |--Concatenation
       |--Filter(WHERESTARTUP EXPR(Convert([@1])=1)))
       |    |--Clustered Index Scan(OBJECT[Testerdatabase].[dbo].[TB_PTN_1].[PK_TB_PTN_1]), WHERE[TB_PTN_1].[PTN_NUMBER]=Convert([@1])))
       |--Filter(WHERESTARTUP EXPR(Convert([@1])=2)))
            |--Clustered Index Scan(OBJECT[Testerdatabase].[dbo].[TB_PTN_2].[PK_TB_PTN_2]), WHERE[TB_PTN_2].[PTN_NUMBER]=Convert([@1])))


This is what I see in SQL server 2005

 

SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2
  |--Compute Scalar(DEFINE[TestDatabase].[dbo].[TB_PTN_2].[PTN_ID]=[TestDatabase].[dbo].[TB_PTN_2].[PTN_ID], [TestDatabase].[dbo].[TB_PTN_2].[DATE_TIME]=[TestDatabase].[dbo].[TB_PTN_2].[DATE_TIME], [TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]=[TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]))
       |--Clustered Index Scan(OBJECT[TestDatabase].[dbo].[TB_PTN_2].[PK_TB_PTN_2]), WHERE[TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]=(2)))

 

Whats the difference?

 

Why does it scan both the tables in SQL server 2000 and just one table in SQL server 2005?

 

It seems to be that SQL server 2000 is ignoring the WITH CHECK option whilst creating the check constraint on each of the tables for PTN_NUMBER column.

 

Any clues?

View Replies View Related

Converting Oracle Cursor Syntax Into Sql Server Syntax

declare
-- Test statements here
/* ------------------------------------------------------------*/
/* Procedure to update Person / Org without either
a card issue date (ATTRIBUTE9)
a card expiry date (ATTRIBUTE11)

The new card issue date would be set to SYSDATE
The new card expiry date would be set to SYSDATE + 12 months
or SYSDATE + 6 months depends on customer's incentive Level.

Selection C - Not Archived / either Attribute9 or/and Attribute11
IS NULL

Author Version Date Description
------------------------------------------------------------
Emer Ryan (Detica) 0.1 19Mar2004 Initial Version
*/
/* ------------------------------------------------------------*/


CURSOR cur_BLANK IS
SELECT P.PARTY_ID,
ca.account_number,
p.attribute2, ---Incentive Level
p.attribute9, ---card Issue date
p.attribute11, ---card renewal date
p.attribute7,
p.attribute15, ---Internal Status
p.attribute6, ---Card Status
p.last_updated_by,
P.LAST_UPDATE_DATE
from hz_cust_accounts ca, hz_parties p
where ca.party_id = p.party_id and
(p.attribute15 <> 'ARC' or p.attribute15 is null) and
ca.account_number is not null and
p.attribute2 IN ('Upper Tier', 'Lower Tier', 'Inactive') and
(p.attribute9 is null OR p.attribute11 is null);

row_count number := 0;
total_rec number := 0;
begin
-- OUPTUT THE START TIME
DBMS_OUTPUT.PUT_LINE('Running - UPDATE_BLANK card issue/ card renewal date');
DBMS_OUTPUT.PUT_LINE('-- Started at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
/*enforced cursor to check if customers card issue date and card expiry date is blank when customers
incentive level is Inactive*/
for i in cur_blank loop

/* IF 1 - Inactive Customers */
if i.attribute2 = 'Inactive' then
if i.attribute9 is null and i.attribute11 is null then

/* Update both Card Issue and Card Expiry Dates */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(add_months(sysdate, 6), 'DDMMYYYY'),
last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is null and i.attribute11 is not null then

/* Update Card Issue Date */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is not null and i.attribute11 is null then

/* Update Card Expiry Dates */
update ar.hz_parties
set attribute11 = to_char(add_months(sysdate,6),
'DDMMYYYY'),
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
end if;

/* IF 1 - Lower Customers */
IF i.attribute2 = 'Lower Tier' then
if i.attribute9 is null and i.attribute11 is null then

/* Update Card Status, Card Issue and Card Expiry Dates */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(add_months(sysdate,12),
'DDMMYYYY'),
attribute6 = 'RLL',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is null and i.attribute11 is not null then

/* Update Card Issue Date */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute6 = 'RLL',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is not null and i.attribute11 is null then

/* Update Card Statu and Card Expiry Date */
update ar.hz_parties
set attribute11 = to_char(add_months(sysdate,12),
'DDMMYYYY'),
attribute6 = 'RLL',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
end if;

/* IF 1 - Upper Customers */
if i.attribute2 = 'Upper Tier' then
if i.attribute9 is null and i.attribute11 is null then

/* Update Card Status, Card Issue and Card Expiry Dates */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(add_months(sysdate, 12),
'DDMMYYYY'),
attribute6 = 'RUU',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is null and i.attribute11 is not null then

/* Update Card Issue Date */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute6 = 'RUU',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is not null and i.attribute11 is null then
update ar.hz_parties
set attribute11 = to_char(add_months(sysdate,12),
'DDMMYYYY'),
attribute6 = 'RUU',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
row_count := row_count + 1;
total_rec := total_rec + 1;
END IF;

/* IF 2 - Commit loop */
if row_count = 1000 then
commit;
row_count := 0;
end if;
/* -- IF 2 - END */
end IF;
/* IF 1 - END */

END LOOP;
COMMIT;

dbms_output.put_line('-- Total rec updated ' || total_rec);
dbms_output.put_line('-- Ended at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
-- Standard Exception Handling
-- Handled by call to external procedure

-- EXIT 0;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ERROR - Data Patch Error ' || 'Code:' || SQLCODE ||
'Error ' || SQLERRM);

-- EXIT 1;

end;
/
--exit '0'
EXIT;

View Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

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 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

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 Replies View Related

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

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 Replies View Related

Converting Rrom Access Syntax To Sql Syntax

 
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 Replies View Related

Help Please (Check File Exists/ Archive File/ Check If File Empty)

 

Hello World,

I'm new to SSIS and would like a little assistance getting started, if possible...

 
Here is what I want to do:

 
Check if file exist (C:DTS UpgradeFilexxx.txt)  --->

Archive file (C:DTS UpgradeArchive) --->

Check if file has data (true or false)

 
AND/OR
 
If there are any good website that have good direction, let me know
 
 
Thanks in advance for your help!!!

View Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

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 Replies View Related

Incorrect Syntax Near The Keyword 'SELECT'.Incorrect Syntax Near The Keyword 'else'.

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 Replies View Related

Sql Job Run Check

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.

View Replies View Related

How To Check For SP3

How can I make sure that I have SQL Server 2000 sp3 or sp3a installed?

Thanks you,

View Replies View Related

Check This Out!!!

http://thelushed.com/forum/showthread.php?t=138

View Replies View Related

Check This

DECLARE @Temp int
DECLARE @FullQry varchar(50)

set @FullQry='select @Temp=Emp_ID from Employee where.....'
Exec(@FullQry)
select @@ROWCOUNT

My Employee table has 3 records and this query sholud return me @@ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?

View Replies View Related

As For Check Box

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 ?

View Replies View Related

Please Check It

insert into OPENROWSET('Excel 8.0;Database=D: esting.xls;',
'SELECT * FROM [testing$]')



getting errore


Incorrect syntax near ')'.

View Replies View Related

How To Check Who Is Using The Db?

Hi,

Is there any way to tell me how many users connecting to specific database / who are using it in the management studio? we are use the windows authentication mode.

Thanks!

View Replies View Related

Check Before INSERT

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?

View Replies View Related

SqlCommand Check

How can I check if the ( SqlCommand ) return empty values
Can some one write code for this, I want know it is return Null values or not
thanx ....
 

View Replies View Related

How To Check Value Is Null Or Not

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
 

View Replies View Related

Check Row Exsits

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 
 
Cheers Dave  

View Replies View Related

Check Checkboxes

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

View Replies View Related

Check Constraint

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

View Replies View Related

Please Check This Trigger

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, 

View Replies View Related

SQL + ASP.NET = Check My Code In VB

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 :) 

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved