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.
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
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 ?????)
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
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
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??
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]....
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.
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!
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedWhy 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.
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.
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!
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
i want to check.. Report Server Windows service has Send As permissions on the SMTP server.
how can i do this..
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedHi
I want to build a class in ASP.Net 2.0 which will lookup if SQL server is up & running & whther connection is getting establish to the Database. if it fails it will try 3 time to reconnect. if no sucess then it will shoot a mail to Admin.
Please help me to achive this.
Thanks
Vishal.
Hello!
I want to read an image from the database. I've this function but she returns me an error that suggest me that the image might be damaged or corrupted.
I would like to know if it's possible and how to check an image in the DB.
Thank you!
Hi there,
I am quite new to SQL Server and am having a bit of trouble.
I have created a linked server with the datasource an excel spreadsheet.
I am trying to write a bit of T-SQL that basically checks if the linked server exists. If it does do X, if not do Y.
Can anyone help, its driving me bananas!
How do I check if SQL Server is set to Mixed mode or not?
Also, what other modes are possible (in addition to Mixed Mode) and how do I check these?
Last: What really do we mean by "Mixed Mode".
Thanks.
Hi,
can anybody help me that how can we see sql server Health check with t-sql.Because i am trying to make script for this
Basically i need these info..
1.Current Date
2.Current time
3.Session,id
4.Session name
5.all locks
6.cpu utilization
7.how much disk space is used inside the database
8.how much space is free inside the database
9.Date read
10 Date write
11.how can we see process information with t-sql or with stored procedure
and i have solved 6 problems from this list i made queries for this .
will u plz help me
thaxxx
jagpal singh
I support numerous cluster SQL boxes and as part of my daily DBA Check regime I need to know whether the instances have faulted and switched the to other side of the cluster.
Since not all fault's which are recorded in the event logs result in the instance failing over to the other side of the cluster, and the SQL instance thinks it exists on the virtual name, this can make it difficult to indentify which server SQL resides on using just SSMS.
Does anyone know a T-SQL command to discover the BIOS name -alternatively and recommendations how I check this parameter?
Hi,
I am skilled Oracle DBA and recently started to support MS SQL Server 2000
.
For all my Oracle DBs I run "Watch Dog" script that test if database ( server for MS SQL Server) is up and running and send me message on pager or by E-mail if it is down.
MY QUESTION IS:
How to do similar actions for MS SQL SERVER 2000?
Thanks in advance,
Michael Fradkov
Hello all, I am hoping this is the correct place for this. I have an application that depends on an active connection to Sql Server Express 2005. I am looking for a way is any to check for the existance ofthe server I defined and wether it is online or not. I know I can just catch the error on connection but it takes a bit and I have customers trying to cancel it before it's finished because they think it's locked up. I would like a quick way to check without trying to create a connection if it's online, then if it is, proceed, if not, let the customer know. I was hoping for something like a database server ping of sorts.
Thank you,
Jim
is there a way to get the edition(e.g. sqlexpress,enterprise etc...) of an installed sql server instance in your local machine programatically?
View 6 Replies View RelatedQuery :
select * from Item1 a (NOLOCK, Index(ItemIn1))
join Item2 b (NOLOCK, Index(ItemData1))
Please explain the above nolock etc after the alias in the given query. What it does ?
Sam.
Hi..
I have a web application(ASP.NET 2.0 + C#). I have sql server 2000 as well as sql server 2005.
Several queries in my application work well with the sql server 2005, and the same query fails in the sql server 2000.
How to check if the database is connected to sql server 2000 or sql server 2005 and then call the queries accordingly through the c# code?
Thanks
I want to set up a simple check constraint on a column limiting to values "Yes", "No" and ""I'm trying to use:CONSTRAINT IsAccessToItRestricted_ckcheck (IsAccessToItRestricted in('Yes,'No','');but this is not the right syntax.............. help!
View 1 Replies View RelatedHey anyone,
I got a problem to check a connection whether it is established or not from my C# Application to SQL Server.
I am developing my application which can run in 2 modes:
1. Online: There must be a connection to SQL Server to get new data updates.
2. Offline: I dont need a connection to SQL Server. I set a timer to tick every 5 seconds and it will check the connection with SQL Server and my problem here: (this is my code)1 public static bool checkConnection()
2 {
3 SqlConnection conn = new SqlConnection("mydatasource");
4 try
5 {
6 conn.Open();
7 return true;
8 }
9 catch (Exception ex) { return false; }
10 }
Yes, it works but my application seems to wait about >3 seconds to get the return "False"; I tried to add "Connect TimeOut=1" in my Connection String, but the problem is still there.
I dont know whether there is a faster way to check the connection to SQL server which is established or not.
Thank you very much,
Joesy
Hi all,
When I detach a SQL Server database, I see the following information:
Connections using this database: 5
Where are the connections from ? How can I check ?
Please help me !
Thanks
I have a stored procedure that references another stored procedure on a linked server. I would like to check if the linked server is running prior to accessing the stored procedure. Everything I have tried returns either message 11 or 17 and terminates the stored procedure. I would like to do some cleanup before the termination. Is there any way to check this?
ben
Hi,
I have my SQL server installed and running on a windows 2003 server. Sometimes, the server stops unexpectedly. I want to know if there is any mechanism to alert as and when the SQL server or SQL server agent goes down.
I guess everything is a windows service. Can we have a mechanism to test this service from a script and send an alert mail when the server goes down? How can this be done from a script?
Thanks in advance
-Sudhakar
How does one check which port SQL Server is listening to? How does one know if it is 1433 or 1434?
View 8 Replies View Related