Please Check This SP Syntax - Need More Eyes!

Sep 13, 2007

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 3 Replies


ADVERTISEMENT

Check The Syntax

Oct 12, 2004

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

Check SQL Syntax

Oct 27, 2004

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

Check The Syntax

Jan 10, 2008

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

Dynamic Sql Syntax Check

May 14, 2008

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

How SQL Server Do Syntax Check?

Aug 23, 2007

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

A Second Set Of Eyes

Apr 10, 2007

This is more of a "does anyone see something I'm missing" post versus a real problem.

What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.

I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON

DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @DBNAME VARCHAR(64)
DECLARE @tablename varchar(128)
DECLARE @execstr varchar(255)
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @maxextfrag decimal

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0
SELECT @maxextfrag = 40.0

DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')

---- Declare a cursor.
--DECLARE tables CURSOR FOR
-- SELECT TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'


-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)


OPEN db

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @DBNAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'USE ' + @dbname + ';' + char(13)
PRINT @execstr
EXEC (@execstr)



-- Open the cursor.
OPEN tables

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @tablename
END



-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables


SELECT @SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @SQLSTRING = @SQLSTRING + @DBNAME
SELECT @SQLSTRING = @SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
OR ExtentFrag >= @maxextfrag'

PRINT @SQLSTRING

EXEC(@SQLSTRING)




FETCH NEXT
FROM db
INTO @DBNAME
END

CLOSE db
DEALLOCATE db


-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
OR ExtentFrag >= @maxextfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
--
---- Delete the temporary table.
DROP TABLE #fraglist

Again, thanks!!

View 4 Replies View Related

Transact SQL :: Syntax Check For Archiving A Table

May 12, 2015

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 =

[code]....

View 8 Replies View Related

Syntax Check Needed On Case Statement

Feb 26, 2008

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

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

May 5, 2008

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

Need Another Pair Of Eyes Sql 'Order By'

Feb 9, 2006

my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.

the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/Newindex/trip_types.asp, line 53

line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53---> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.

any comments gratefully received thanks.

View 6 Replies View Related

I Need Fresh Eyes On A Simple Sql Query

Apr 20, 2006

greets, im coding a few queries to a table. im storing sets of records into the table, each set of records will haev a different batch id. so basically 2 sets of records can occupy this table at the same time, and their batch id is the main key (with 2 other fields also being PKs). i want to compare the 2 sets in the same table and get the differences:
1. records that were added
2. records that were updated
3. records that were deleted

ive written queries for the added records, and the updated records but i cant get the query for finding deleted records. the logic looks good to me but im obviously missing something so i could use a fresh pair of eyes.
here is the table def:

Code:


CREATE TABLE [dbo].[UPCXREF_BATCH] (
[BATCH_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHAIN_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPC] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITM_CODE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITM_CATEGORY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UPCXREF_BATCH] WITH NOCHECK ADD
CONSTRAINT [PK_UPCXREF_BATCH] PRIMARY KEY CLUSTERED
(
[BATCH_ID],
[CHAIN_CODE],
[UPC]
) ON [PRIMARY]
GO



and here are the queries ive gotten so far

Code:


/* This section retrieves all UPDATED records */
SELECT 'U' as FLAG, b2.CHAIN_CODE as CHAIN_CODE, b2.UPC, b2.ITM_CODE, b2.ITM_CATEGORY
FROM UPCXREF_BATCH b2 INNER JOIN UPCXREF_BATCH b1
ON b2.CHAIN_CODE=b1.CHAIN_CODE
AND b2.UPC=b1.UPC
AND (b2.ITM_CODE<>b1.ITM_CODE OR b2.ITM_CATEGORY <> b1.ITM_CATEGORY )
WHERE b2.BATCH_ID='BTC0002' AND b1.BATCH_ID='BTC0001'



/* This section retrieves all NEW records */

SELECT 'A' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0002'
AND (CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001')
OR UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001'))




here was my attempt to get deleted records which looks like it makes sense but isnt working

Code:


SELECT 'D' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0001'
AND CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
AND UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')



here batch 'BTC0001' is the older set of records that already existed and batch 'BTC0002' is the new set we just inserted into the table. am i missing something else?

View 11 Replies View Related

Incorrect Query Results, Could Use Fresh Eyes

Jun 4, 2007

I have three tables:

Category: category id, category name, moreā€¦
Topic: topic id, topic name, category id, moreā€¦
Post: post id, post text, topic id, moreā€¦

I need help with a query to display the following:

Category name, # of topics, # of posts

Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562


Itā€™s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7


Overview: use category id to get count of topics then use the topic id to get the count of posts.


SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName

Thanks in advance

View 6 Replies View Related

Data Access :: How To Check All Connection Automatically During Routine Check By Using Batch File

May 20, 2015

I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.

View 5 Replies View Related

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

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

Dec 14, 2003

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'


Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()


End Sub

Thankyou in advance.

View 3 Replies View Related

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

Mar 31, 2008

Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:

INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName

OR

WHERE f.Name = @FacilityName


My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?

Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?

Thanks!

View 4 Replies View Related

Converting Rrom Access Syntax To Sql Syntax

Sep 23, 2007


Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end




Case Statement might be the solution but i could not do it.






Your input will be appreciated

Thank you

View 5 Replies View Related

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

Mar 10, 2008



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

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

May 27, 2008

This is the error it gives me for my code and then it calls out line 102.  Line 102 is my  buildDD(sql, ddlPernames)  When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one.  Could it not like something in my sql select statement.  thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
 
End Sub

View 2 Replies View Related

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

May 22, 2008

What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.

View 5 Replies View Related

Sql Job Run Check

Jun 6, 2008

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

How To Check For SP3

Jan 23, 2004

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

Thanks you,

View 4 Replies View Related

Check This Out!!!

Aug 9, 2004

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

View 1 Replies View Related

Check This

May 4, 2004

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

Please Check It

Jun 24, 2008

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



getting errore


Incorrect syntax near ')'.

View 2 Replies View Related

How To Check Who Is Using The Db?

Feb 1, 2007

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

As For Check Box

Jan 30, 2008

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

SQL + ASP.NET = Check My Code In VB

Sep 1, 2006

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

Please Check This Trigger

Nov 25, 2006

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

Check Constraint

Mar 28, 2007

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

Check Checkboxes

Jul 6, 2007

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

Check Before INSERT

Aug 6, 2007

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







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