Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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
How To Check Other DB's Table
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???

View Replies !   View Related
Check Diskspace UNC Via T-SQL
Hello,I have an EM Job script that backups up my databases using SQLsafe to atarget UNC.Sometimes, the target server that will store the backups is off line orit's disk is full.I am wondering how I can check to see if the machine is up via the joband secondly, check the disk space.If either check fails, I would then check another machine machine.Any ideas appreciated,ThanksRobSQL 2000 Server and Enterprise, Windows 2003SQL 2005 Server and Etnerprise, Windows 2003Target storage is a Windows 2003 and I connect via UNC

View Replies !   View Related
Check Integrity
Hi,What methods can we use to check the integrity problems? Some records in mydatabase are having Foreign Keys but the database doesn't have any relatedrecords with required Primary Keys. I need to scan the whole database anddelete (maybe with backup, maybe not) all wrong records.Who can I do that? Is it need to write my own application to do that or wehave some standard way to fix these problems? I'm not a databaseadministrator and don't know these ways (yet). Can somebody help me withadvice?Thanks.Dmitri Shvetsov

View Replies !   View Related
Check Constraints
Below is a check constaint that I tried implementing by using trigger because SQL Server doesn't support subquery in check constraints but I don't know if the trigger will implement the same integrity as the check constraint. Here's it


--- Check Constraint ---
check((Derived_Val=0) or ((select count(*) from Stage_One where Task=U_Id and Quantity is null)=0))

--- Trigger ---
create trigger tr_Ins_Upd_Process
on Process
for insert, update as
if (select count(*) from Stage_One, inserted
where ((Task=U_Id and Quantity is null)or(inserted.Derived_Val=0))) = 0
begin
rollback transaction
end

j2dizzo

View Replies !   View Related
Check The Indexes
Hello, everyone:

How to check the index is clustered or non-clustered? Thanks.

ZYT

View Replies !   View Related
Dbcc Check
When i have to abort the DBCC CHECK because of too long runtime, is it making a full rollback ?
I did it and it stopped immediately.

dajm

View Replies !   View Related
How To Check Two Tables?
Hello, everyone:

I imported a table from a database to another one in same server. How to check if two tables have the same data?

Thanks

ZYT

View Replies !   View Related
Check If Cast Is Possible
I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.

View Replies !   View Related
How To Check DB Sizes?
Hi there

I hope this isn't a totally stupid question, but I was wondering how to check the physical size the DBs take up on disks...

Does MSSQL save DBs as a specific file-extension? Is there an SQL command I can run that will give extensive info on DB sizes?

Any help welcome :)


Thanks!

View Replies !   View Related
Check If Exist
Hi guys help please..is there a function in MS SQL that check if a particular value exist in a row and would return a boolean value base from what found, Return True if it found something and False if it does not found one. I've try the EXISTS function but I cant get the rigth syntax..Any help will be greatly appreciated!

OR Maybe you can help me directly with my problem. I want to check first in my Table 1 with 3 columns if value X exists in column 1 and if X exists UPDATE that column with value Y and if value X does not exists INSERT something in the Table 1. Any suggestion or Comments will be greatly appreciated!

View Replies !   View Related
How To Check For NULL Value In VB.NET?
How do I check whether a DATETIME field in SQL Server is NULL or not using VB.NET?

I have attempted this:

Public Function GetDate(ByVal FullDate As DateTime) As String

Dim MyNull As System.DBNull

If FullDate.ToString Is MyNull Then
GetDate = ""
Else
GetDate = FullDate.Date
End If

End Function

but it gives an error

TIA.

View Replies !   View Related
Check Priv's
Hi all,

I have an webapplication and I want to let the app check the privileges of the user logged in. with that info the app must show some functions of the system and hide others.

I want to know if somenthing like this is possible and how I can do It

PS.
MySQL has a table with users, tables and the privileges those users had on that table and you could just use a select statement to shwo them.
(Is something like this also possible in SQL server 2k)

thnx in advance

View Replies !   View Related
Check Constraint?
In SQL Server 2000, I want to apply a check constraint on a column type varchar that it is not duplicated. Could someone help me out with this?

Also, should I just make this column the primary key instead of an identity field being the primary key?

What is the performance difference by applying the constraint rather then just making it the primary key?

Mike B

View Replies !   View Related
Check Constraint
I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.


What did I do wrong. Thanks for your help.

View Replies !   View Related
T-sql: Returning A Row With Check
Hello all,

i am writing a SP and want to do the next thing:

1. check whether a row exists by key field.
2. if not - raiserror
3. if does exist - return it.

the thing is that i dont want to scan the table twice with 2 select statements, and dont know how to do it....

any sugestions ??

thanks a lot, Ran.

View Replies !   View Related
How Can I Check When Something Is Written In A DB?
Hi,

i have following problem:

I have a DB with 4 tables in MSSQL6.5. In those tables write an NT service (4 threads for each table) 24h/day. I want to know somehow when i have no write in one of those tables for more than 1-2 hours (i dont want to querry those tables everytimes). MSSQL6.5 offer this kind of facilities? Or I can do this using stored procedures or something else?

Thank you very much,
Sebastian Bologescu

View Replies !   View Related
Check Alloc
When running the check alloc we get the following error message


Table Corrupt: object id does not match between extent in allocation page and Sysindexes; check the following extent: alloc pg#=5632 extent#=5664 object id on extent=-5 (object name = -5) object id in Sysindexes=5 (object name = sysprocedures)

Table Corrupt: Object id wrong; tables: alloc page 5632 extent id=5776 l page#=5783 objid in ext=-5 (name = -5) objid in page=5 (name = sysprocedures)objid in sysindexes=5 (name = sysprocedures)

The corruption exists on a system table 'sysprocedures'. Technet fix : To restore from clean backup but our backup also has the problem. Is it possible to
1) copy the data
2) drop the table
3) recreate the table and repopulate

with system tables, or is there an alternative ?


regards

Sanjeev

View Replies !   View Related
Check Constraints?
Hi,
I am developing a database for my company in SQL server 2000 and I have some
few problems.
Firstly.

I have a customers table and orders table in my DB:

Customers Orders
--------- ------
CustID (primary key) ----------------< CustID
. ^ ProductID
. | Quantity
. | .
. | .
etc. | etc.
|
relationship
(one to many)

What I want to do is:
1) to be able to delete a Customer and automaticaly SQL server delete all the
orders that this customer done from the Orders table.
2) If for some reason the CustomerID changes, SQL should be able to
automaticaly update the necessary fields with the new values in the Orders
table.
Finally, 3) I want to be able to insert a new customer that has an order
and update both the Customers table and Orders table automaticaly. e.g

CustID Name Address ProductID Quantity etc.
------ ---- ------- --------- -------- ----
10-003 John London 33-25 2 ...

Such a kind of insert should add automaticaly the following entries in the
two tables:

Customers Orders
--------- ------
CustID (10-003) CustID (10-003)
Name (John) ProductID (33-25)
Address(London) Quantity (2)
. .
. .
etc. etc.

A friend of mine told me that this can be done using Foreign Check constraints
in SQL server. But I do not know what to do.

Can anybody help me please?

Thank you very much.

Efthymios Kalyviotis
ekalyviotis@comerclub.gr

View Replies !   View Related
Check This Procedure
Hi all

can any one check this procedure and let me know how to make smalll
because i have total 28 codes to check ....v001,m006,m007...
it is working fine but in the report i have to insert all the of the
each district,site for all the code..so if i go on writing like
this i get 28*4 ..output paramters and also it is not easy to
debug the procedure ..so let me know any other way for this

i want the data like this to show in the report

district site code1 code2
year02 year01 ix per year02 year01 ix per
1000200 1020
100 20 30

total 2 30 50

2000 500 45 45
400 50 50

total 2 30 50

=======================================
CREATE PROCEDURE dbo.cdh_spDynamicReport (@intYear1 smallint,@intYear2

smallint, @intMonth TinyInt) AS
BEGIN

select t_V001_1.district_type, t_V001_1.sun_site,
t_V001_1.Amount as 'V001 2002', t_V001_2.Amount 'V001 2001',

t_V001_1.Amount - t_V001_2.Amount as 'Improve V001', t_v001_1.Amount

/(t_V001_1.Amount - t_V001_2.Amount) * 100 as 'Percentage1',
t_m006_1.Amount as 'M006 2002', t_m006_2.Amount 'M006 2001',

t_m006_1.Amount - t_m006_2.Amount as 'Improve M006', t_m006_1.Amount

/(t_m006_1.Amount - t_m006_2.Amount) * 100 as 'Percentage2'

from

(
select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear1
and oper_sundata.map_code = 'v001'
group by district_type,sun_site, oper_sundata.map_code
) as t_V001_1

left join

(select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear2
and oper_sundata.map_code = 'v001'
group by district_type,sun_site, oper_sundata.map_code
) as t_V001_2

on t_V001_1.district_type = t_V001_2.district_type and
t_V001_1.sun_site = t_V001_2.sun_site and
t_V001_1.map_code = t_V001_2.map_code

left join


(
select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear1
and oper_sundata.map_code = 'm006'
group by district_type,sun_site, oper_sundata.map_code
) as t_m006_1

on t_V001_1.district_type = t_m006_1.district_type and
t_V001_1.sun_site = t_m006_1.sun_site

left join


(
select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear2
and oper_sundata.map_code = 'm006'
group by district_type,sun_site, oper_sundata.map_code
) as t_m006_2

on

t_m006_1.district_type = t_m006_2.district_type and
t_m006_1.sun_site = t_m006_2.sun_site and
t_m006_1.map_code = t_m006_2.map_code


order by t_V001_1.district_type, t_V001_1.sun_site


END
GO
================================================== =============

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved