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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 9, 2004
http://thelushed.com/forum/showthread.php?t=138
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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