Incorrect Syntax using IF statement

Hi,

I'm new to SQL Server Programming, I work with ASP a lot, but lately
I've been trying to create Stored Procedures, etc. I'm having a
problem writing a simple IF statement.. I don't seem to understand why
it's giving me this error. I've search around on Google Groups, but I
still don't get it.

=================
USE msdb

IF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')

END
=================

My error is:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'END'.

Thanks for any help.you need a BEGIN for every END
example

DECLARE @.v BIT
SELECT @.v = 1

IF @.v = 1
BEGIN
select 'yes'
END
ELSE
BEGIN
select 'No'
END

Or without begin...end
IF @.v = 1
select 'yes'
ELSE
select 'No'

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I played with it a bit and I got the result I was looking for.

==============
USE msdb
DECLARE @.JobName varchar(255)
SELECT @.JobName = name FROM sysjobs WHERE name = 'Scheduled Nightfax'

IF @.JobName = 'Scheduled Nightfax'
PRINT 'YES'
ELSE
PRINT 'NO'
==============

Incorrect syntax near 'use '

I am trying to run the following three line statement and I get an error
stating: "Line 3: Incorrect syntax near 'use '."
The database name is correct, "cms user messaging"
declare @.dbname sysname
set @.dbname = 'cms user messaging'
'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
Message posted via http://www.webservertalk.comTry,
declare @.dbname sysname
set @.dbname = 'cms user messaging'
exec('use [' + @.dbname + '] DBCC SHOWFILESTATS with no_infomsgs')
go
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to run the following three line statement and I get an error
> stating: "Line 3: Incorrect syntax near 'use '."
> The database name is correct, "cms user messaging"
> declare @.dbname sysname
> set @.dbname = 'cms user messaging'
> 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
> --
> Message posted via http://www.webservertalk.com
>|||The first message was incorrect. This is the correct statement I am trying
to run:
declare @.dbname sysname
set @.dbname = 'cms user messaging'
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (7, 1),
UsedExtents dec (7, 1),
[Name] varchar(50),
[FileName] sysname )
declare @.string sysname
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents ,
UsedExtents , [Name] , [FileName]) exec (@.string)
In running this statement I get the following error message:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'cms'. No entry found
with that name. Make sure that the name is entered correctly.
Message posted via http://www.webservertalk.com|||Try,
declare @.dbname sysname
set @.dbname = 'cms user messaging'
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (7, 1),
UsedExtents dec (7, 1),
[Name] varchar(50),
[FileName] sysname )
declare @.string sysname
set @.string = 'use [' + @.dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents
, [Name] , [FileName])
exec (@.string)
select * from #datafilestats
...
AMB
"Robert Richards via webservertalk.com" wrote:

> The first message was incorrect. This is the correct statement I am trying
> to run:
> declare @.dbname sysname
> set @.dbname = 'cms user messaging'
> create table #datafilestats
> ( dbname varchar(25),
> flag bit default 0,
> Fileid tinyint,
> [FileGroup] tinyint,
> TotalExtents dec (7, 1),
> UsedExtents dec (7, 1),
> [Name] varchar(50),
> [FileName] sysname )
> declare @.string sysname
> set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
> insert into #datafilestats (Fileid, [FileGroup] , TotalExtents ,
> UsedExtents , [Name] , [FileName]) exec (@.string)
> In running this statement I get the following error message:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'cms'. No entry found
> with that name. Make sure that the name is entered correctly.
> --
> Message posted via http://www.webservertalk.com
>

Incorrect syntax near the keyword WHERE

Can someone help me? I am trying to script an SQL statement that would allow someone to INSERT a new username into a database where it is not a duplicate entry. The table is like this:
UserID - int, 4, identity(1,1)
UserName - nvarchar(50)
UserPass - nvarchar(50)

The code to execute this where i am getting the errors is this:

Function ChooseUName()
 If Page.IsValid Then
 Dim objCon As New SqlConnection(con)
 Dim sqlInsert As String = "INSERT INTO tblUser (UserName) " & _
 "VALUES (@.Username) WHERE NOT EXISTS (SELECT UserName FROM tblUser)"
 Dim cmd As New SqlCommand(sqlInsert, objCon)
 cmd.Parameters.Add("@.Username", SqlDbType.NVarChar, 50)
 cmd.Parameters("@.Username").Value = txtUsername.Text
 Dim id As Integer
 Try
 objCon.Open()
 id = cmd.ExecuteScalar()
 Finally
 If objCon.State = ConnectionState.Open Then
 objCon.Close()
 End If
 End Try
 Response.Write("Your User ID is: " & id.ToString())
 Response.End()
 End If
 End Function

This is the error:

Incorrect syntax near the keyword 'WHERE'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.
Source Error:
Line 73: Try
Line 74: objCon.Open()
Line 75: id = cmd.ExecuteScalar()
Line 76: Finally
Line 77: If objCon.State = ConnectionState.Open Then

PLEASE HELP!! I'M ON A STRICT DEADLINE!!! :o THANKS IN ADVANCE!You need something more like this:

Dim sqlInsert As String = "IF NOT EXISTS (SELECT UserName FROM tblUser WHERE UserName=@.UserName) INSERT INTO tblUser (UserName) VALUES (@.Username)"

Terri|||THANK YOU SO VERY MUCH!!!

That worked like a charm. Do you or anyone else know of any resources online where i can learn more about using the SQL Syntax in my web apps? I've never seen such a string and now it's like a whole new world has opened up!

thanks again.|||You definitely need to haveSQL Server 2000 Books Online. This will help you tremendously with syntax issues. It's large but well worth the free download.

Another place you could look isMicrosoft ASP.NET Quickstarts Tutorial -- Server-Side Data Access. The section on "Inserting Data in a SQL Database" has coding to handle your situation, although they just do the INSERT and then test to see if it violated the primary key.

There are others out there, but those 2 resources come to mind first.

Terri

Incorrect syntax near the keyword 'THEN'.

Hi All,
I'm trying to put an if statement in my SP, which incidently is comparing
two dates, thats by the by, because I cant even get this simple if
statement
to run with out error:
IF (9 > 8) THEN
PRINT '9 is greater than 8'
END IF
Also tried:
IF (9 > 8)
PRINT '9 is greater than 8'
END IF
And:
IF (9 > 8)
PRINT '9 is greater than 8'
END
Help! Whats wrong here?
Simon.
---
I am using the free version of SPAMfighter for private users.
It has removed 2976 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Forgot to add the error messages for the 2nd and 3rd attempts:

> IF (9 > 8)
> PRINT '9 is greater than 8'
> END IF
Incorrect syntax near the keyword 'END'.

> IF (9 > 8)
> PRINT '9 is greater than 8'
> END
Incorrect syntax near the keyword 'END'.|||do you have SQL Server Books Online? The help on IF is pretty clear -
it's not like VB [if you don't have it, you need to get it]
Simon Harris wrote:
> Hi All,
> I'm trying to put an if statement in my SP, which incidently is comparing
> two dates, thats by the by, because I cant even get this simple if
> statement
> to run with out error:
> IF (9 > 8) THEN
> PRINT '9 is greater than 8'
> END IF
> Also tried:
> IF (9 > 8)
> PRINT '9 is greater than 8'
> END IF
> And:
> IF (9 > 8)
> PRINT '9 is greater than 8'
> END
> Help! Whats wrong here?
> Simon.
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2976 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>|||Thanks Trey, I do have SQL Books online (Forgot about it...Too used to
Googling!) after reading that it makes sense, and works.
Thank you!

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:
> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.
Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:

> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>
|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:

> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online
to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING i
nstead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>sql

Incorrect syntax near the keyword 'IF'

After I added a second IF statement into the area of the JOIN operations, it started to complain. When I had only the first IF statement to determine whether to include the INNER JOIN for @.TermID, all was fine. So I am not sure why after adding that second IF check for the second JOIN operation it's complaining now:

SELECT @.sql =
'SELECT DISTINCT a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[ppStaffID],
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[ppCountryId]) AS PrimaryAddress,
a.[GAddress1] + '' '' + a.[GAddress2] + '' '' + a.[GCity] + '' '' + a.[GState] + '' '' + a.[GZip] + '' '' + dbo.aaGetCountryDescription([GadCountryId]) AS ShippingAddress
FROM [aaStaff] a
INNER JOIN cmydb.dbo.[ppStaff] s ON s.[ppStaffId] = a.[ppStaffId]
INNER JOIN Mydb2.dbo.TtUsers u ON u.ppStaffId = s.ppStaffID
LEFT JOIN mydb2.dbo.ttUserRoles ur ON ur.ttUserId = u.ttUserId AND ur.ttRoleId = @.xttRoleId'

-- Check Whether to include Term Join
IF @.TermID IS NOT NULL
SELECT @.sql = @.sql + ' INNER JOIN cmydb.dbo.AdClassSchedTerm st ON st.AdTermID = @.xTermID ' + CHAR(13) +
-- Check Whether to include Course Taught Join
IF @.TaughtCourseID IS NOT NULL
SELECT @.sql = @.sql + ' INNER JOIN cmydb.dbo.adClassSched sc ON sc.AdCourseID = @.xTaughtCourseID AND convert(varchar,EndDate,101) < GetDate()' + CHAR(13) +
'WHERE 1 = 1
AND s.[Active] = 1
AND u.[Active] = 1
AND u.[ppCampusID] = @.xppCampusID' + CHAR(13)
IF @.FirstName IS NOT NULL
SELECT @.sql = @.sql + ' AND (a.[FirstName] LIKE @.xFirstName OR @.xFirstName = ''%%'' OR @.xFirstName IS NULL)' + CHAR(13)
IF @.LastName IS NOT NULL
SELECT @.sql = @.sql + ' AND (a.[LastName] LIKE @.xLastName OR @.xLastName = ''%%'' OR @.xLastName IS NULL)'

Incorrect syntax near the keyword 'IF'.

You have a dangling '+' operator; try removing the trailing '+' from this line:

IF @.TermID IS NOT NULL
SELECT @.sql = @.sql + ' INNER JOIN cmydb.dbo.AdClassSchedTerm st ON st.AdTermID = @.xTermID ' + CHAR(13) +

|||

errr...yea. I was thinking I had to leave those in to concatenate it if it was not null...but I only need that trailing + on my last if statement, not the first inner join.

Thanks!

Incorrect syntax near the keyword "From"

Hi, I have Error when I write this SQL statement retrieving the non selected facilitator for a specific course:

The SQL statement:

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = 2)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

Every thing goes right until I change this:

WHERE [transaction_ facilitators].trans_Course_id = 2

To

WHERE [transaction_ facilitators].trans_Course_id = @.course_id

It gives me this error:

Incorrect syntax near the keyword "From"

Any suggestion ??

hi, could it be that the @.course_id variable being passed is having the wrong value? coz, I think you're query is valid enough..|||

Will it give the error before it takes the value.

:( I don't know what to do..

Its not working its sth with the passing parameter.

HELP…

|||in that case then would you mind posting your code here? aside from the one you gave already, especially the one you are assigning value to your variable.|||

The " Incorrect syntax near the keyword "From" error is appear to me while im using the query builder .

The error while using the code appears to be :

Invalid index 0 for this SqlParameterCollection with Count=0.

And the cose id:

IfNot Page.IsPostBackThen

Me.SqlConn.Open()

Me.SqlDataAdapter_Get_FaciliInfo.SelectCommand.Parameters(0).Value = 1'Request.QueryString.Get("courseid")

Me.SqlDataAdapter_Get_FaciliInfo.Fill(Me.Dataset_Get_PopUpFaciliInfo)

Me.DataGrid_Get_Info.DataBind()

Me.SqlConn.Close()

EndIf

...

|||ah ic so could be that you no value is being passed on the parameter, so in effect in your WHERE clause doesn't have any value: i.e.g 'SELECT * FROM column=' <-- no value that's why there is a syntax error in FROM.. maybe nothing no value is passed to the courseid in the query string ? i.e.mypage.aspx?courseid= instead ofmypage.aspx?courseid=2?


|||

no its not that if you've noticed that i make it comment

Me.SqlDataAdapter_Get_FaciliInfo.SelectCommand.Parameters(0).Value = 1'Request.QueryString.Get("courseid")

And pass the parameter a value 1 and I still get the same error

|||ok let's try one more time is the following your query string for your commandtext?:

SELECT facilitators.facilitator_id,facilitators.facilitator_name

FROM facilitatorsINNER JOIN

[transaction_facilitators] ON facilitators.facilitator_id <> [transaction_facilitators].trans_Facilitator_id

WHERE (NOT(facilitators.facilitator_id IN

(SELECT [transaction_facilitators].trans_Facilitator_id

FROM [transaction_facilitators]

WHERE [transaction_facilitators].trans_Course_id = @.code_id)))

GROUP BY facilitators.facilitator_id,facilitators.facilitator_name

if so, change the @.code_id to ?(question mark) so that the resulting command text will be:

SELECT facilitators.facilitator_id,facilitators.facilitator_name

FROM facilitatorsINNER JOIN

[transaction_facilitators] ON facilitators.facilitator_id <> [transaction_facilitators].trans_Facilitator_id

WHERE (NOT(facilitators.facilitator_id IN

(SELECT [transaction_facilitators].trans_Facilitator_id

FROM [transaction_facilitators]

WHERE [transaction_facilitators].trans_Course_id = ?)))

GROUP BY facilitators.facilitator_id,facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

the reason being the @.<var> is a sql server reserved naming convention for its variable, it(Sql) assumed then that you declare a variable of that name, however since it isn't declared(coz it is being passed) it doesn't know how to generate a plan and gives you a cryptic error in FROM..

|||See your original thread for answer.|||

:( That's unbelievable… its not working what is this… look I try this:

SELECT DISTINCT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = @.course_id)))

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

And I try this:

SELECT DISTINCT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = @.course_id)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

And I try this:

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = ?)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

And I try this:

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = ?)))

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

Nothing work what is this… is it a curse ?

I don't have more time :'(… HELP

|||this is really strange, even if you substitute the parameter to a literal value it will still give you the same error? Ok then how about isolating your sub query first, try running that one alone and pass a literal value.|||If you are literally copying and pasting your code, and are also literally copying and pasting your error message, then the error message and the code are not matching up.

This error message would have an uppercase FROM if it were being generated from the code you have supplied so far.

Incorrect syntax near the keyword "From"

I think you are looking in the wrong place for the code generating the error.
sql

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
--
BUTTONS:
OK
--Are you using a database in compatibility mode? Right-click on database,
properties, Options, compatability level drop-down at top of dialog.
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility
> level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||What compatibility level is the database? I assume you have a @.query parm for the email sending, and
it seems that the database you access data from need to be in compat level 90.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||Master is set to 9.0
Do I have to have all databases set to 9.0?
"Will Alber" wrote:
> Are you using a database in compatibility mode? Right-click on database,
> properties, Options, compatability level drop-down at top of dialog.
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
> > level
> > of the current database to a higher value to enable this feature. See help
> > for the stored procedure sp_dbcmptlevel.
> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
>
>|||You'll need the database that is issuing this error to be set to that
compatibility level - note however that this is not without it's (potential)
problems - some SQL Server 2000 features are broken in 2005 compat. level,
so tread carefully!
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
> Master is set to 9.0
> Do I have to have all databases set to 9.0?
> "Will Alber" wrote:
>> Are you using a database in compatibility mode? Right-click on database,
>> properties, Options, compatability level drop-down at top of dialog.
>> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in
>> message
>> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
>> > TITLE: Microsoft SQL Server Management Studio
>> > --
>> >
>> > An exception occurred while executing a Transact-SQL statement or
>> > batch.
>> > (Microsoft.SqlServer.ConnectionInfo)
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
>> > level
>> > of the current database to a higher value to enable this feature. See
>> > help
>> > for the stored procedure sp_dbcmptlevel.
>> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
>> >
>> > --
>> > BUTTONS:
>> >
>> > OK
>> > --
>> >
>>|||I don't know what database it is. I'm simply trying to setup Database Mail
and test. Not really working with a particular database. I just assumed
that it was with the Master. I have about 6 databases on this server. -
Thanks.
"Will Alber" wrote:
> You'll need the database that is issuing this error to be set to that
> compatibility level - note however that this is not without it's (potential)
> problems - some SQL Server 2000 features are broken in 2005 compat. level,
> so tread carefully!
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
> > Master is set to 9.0
> > Do I have to have all databases set to 9.0?
> >
> > "Will Alber" wrote:
> >
> >> Are you using a database in compatibility mode? Right-click on database,
> >> properties, Options, compatability level drop-down at top of dialog.
> >>
> >> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in
> >> message
> >> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> >> > TITLE: Microsoft SQL Server Management Studio
> >> > --
> >> >
> >> > An exception occurred while executing a Transact-SQL statement or
> >> > batch.
> >> > (Microsoft.SqlServer.ConnectionInfo)
> >> >
> >> > --
> >> > ADDITIONAL INFORMATION:
> >> >
> >> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
> >> > level
> >> > of the current database to a higher value to enable this feature. See
> >> > help
> >> > for the stored procedure sp_dbcmptlevel.
> >> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> >> >
> >> > For help, click:
> >> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> >> >
> >> > --
> >> > BUTTONS:
> >> >
> >> > OK
> >> > --
> >> >
> >>
> >>
> >>
>
>|||You might also try msdb, I believe this is where all the dbmail stuff lives.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>I don't know what database it is. I'm simply trying to setup Database Mail
> and test. Not really working with a particular database. I just assumed
> that it was with the Master. I have about 6 databases on this server. -
> Thanks.|||... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
works, lower one at a time and see which one caused the problems.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> You might also try msdb, I believe this is where all the dbmail stuff lives.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>>I don't know what database it is. I'm simply trying to setup Database Mail
>> and test. Not really working with a particular database. I just assumed
>> that it was with the Master. I have about 6 databases on this server. -
>> Thanks.
>|||Changing msdb to 9.0 worked.
Thanks.
Problem resolved.
"Tibor Karaszi" wrote:
> ... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
> works, lower one at a time and see which one caused the problems.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> > You might also try msdb, I believe this is where all the dbmail stuff lives.
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.sqlblog.com/
> > http://www.aspfaq.com/5006
> >
> >
> >
> > "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> > news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
> >>I don't know what database it is. I'm simply trying to setup Database Mail
> >> and test. Not really working with a particular database. I just assumed
> >> that it was with the Master. I have about 6 databases on this server. -
> >> Thanks.
> >
> >
>|||> Changing msdb to 9.0 worked.
> Thanks.
> Problem resolved.
Since I was able to reproduce this in Katmai I filed an issue on Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284340
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476[/url]
BUTTONS:
OK
What compatibility level is the database? I assume you have a @.query parm for the email sending, and
it seems that the database you access data from need to be in compat level 90.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
>
|||Master is set to 9.0
Do I have to have all databases set to 9.0?
"Will Alber" wrote:

> Are you using a database in compatibility mode? Right-click on database,
> properties, Options, compatability level drop-down at top of dialog.
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
>
>
|||I don't know what database it is. I'm simply trying to setup Database Mail
and test. Not really working with a particular database. I just assumed
that it was with the Master. I have about 6 databases on this server. -
Thanks.
"Will Alber" wrote:

> You'll need the database that is issuing this error to be set to that
> compatibility level - note however that this is not without it's (potential)
> problems - some SQL Server 2000 features are broken in 2005 compat. level,
> so tread carefully!
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
>
>
|||You might also try msdb, I believe this is where all the dbmail stuff lives.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>I don't know what database it is. I'm simply trying to setup Database Mail
> and test. Not really working with a particular database. I just assumed
> that it was with the Master. I have about 6 databases on this server. -
> Thanks.
|||... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
works, lower one at a time and see which one caused the problems.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> You might also try msdb, I believe this is where all the dbmail stuff lives.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>
|||Changing msdb to 9.0 worked.
Thanks.
Problem resolved.
"Tibor Karaszi" wrote:

> ... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
> works, lower one at a time and see which one caused the problems.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
>
|||> Changing msdb to 9.0 worked.
> Thanks.
> Problem resolved.
Since I was able to reproduce this in Katmai I filed an issue on Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284340
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
http://go.microsoft.com/fwlink?Prod...25&LinkId=20476
BUTTONS:
OK
--Are you using a database in compatibility mode? Right-click on database,
properties, Options, compatability level drop-down at top of dialog.
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility
> level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...25&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||What compatibility level is the database? I assume you have a @.query parm fo
r the email sending, and
it seems that the database you access data from need to be in compat level 9
0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility leve
l
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...25&LinkId=20476
> --
> BUTTONS:
> OK
> --
>

Incorrect syntax near '='

Hi,
I'm using multiple select statements in MS-Access with logical
expressions like (ID=10)*50 instead of an if statement. In this manner
i'm able to get the same results as with if statements (if ID=10 then
result=50) only faster (in MS-Access that is).
when trying to get the same sql statement running in SQL-server I
receive an Incorrect syntax near '=' error message.
Are there any solutions? I will try to avoid updating all queries
because MS-Access will also be used as backend next to a version for
SQL-server backend.
Maybe anyone can help me.You can use CASE statement
SELECT result =
CASE WHEN ID = 10 THEN 50
ELSE something END
"Niels Verkaart" wrote:

> Hi,
> I'm using multiple select statements in MS-Access with logical
> expressions like (ID=10)*50 instead of an if statement. In this manner
> i'm able to get the same results as with if statements (if ID=10 then
> result=50) only faster (in MS-Access that is).
> when trying to get the same sql statement running in SQL-server I
> receive an Incorrect syntax near '=' error message.
> Are there any solutions? I will try to avoid updating all queries
> because MS-Access will also be used as backend next to a version for
> SQL-server backend.
> Maybe anyone can help me.
>|||Thank you Jack,
now I only have to write a clever function to convert dynamic queries
like:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
That's a nice little problem for me. but i'll get there.
> You can use CASE statement
> SELECT result =
> CASE WHEN ID = 10 THEN 50
> ELSE something END
> "Niels Verkaart" wrote:
>|||Hi Niels,
This is a typical crosstab (FAQ). The solution is basically the same as
what Jack has posted:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
SELECT VerkoopsoortID
, SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
FROM CalcAfzetPeriode
WHERE CalcID=1528
AND VerkoopsoortID NOT IN (0)
GROUP BY VerkoopsoortID
HTH,
Gert-Jan
Niels Verkaart wrote:
> Thank you Jack,
> now I only have to write a clever function to convert dynamic queries
> like:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
> VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
> 007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
> 009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
> VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
> That's a nice little problem for me. but i'll get there.
>|||Great Gert-Jan,
thank you both Gert-Jan and Jack!
Gert-Jan Strik wrote:
> Hi Niels,
> This is a typical crosstab (FAQ). The solution is basically the same
> as what Jack has posted:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
> SELECT VerkoopsoortID
> , SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
> FROM CalcAfzetPeriode
> WHERE CalcID=1528
> AND VerkoopsoortID NOT IN (0)
> GROUP BY VerkoopsoortID
> HTH,
> Gert-Jan
>
> Niels Verkaart wrote:

Incorrect syntax near ,. Sql Statement

SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ToString());
SqlCommand myCommand = new SqlCommand("SELECT (Deadline, Description, Headline, AddressField, OrganizationField, NameField, FileField, EmailField, CommentField) FROM RegistrationFormDB_Info WHERE (UserName = @.UserName AND TournamentName = @.TournamentName)", myConnection);

SqlParameter myParam = new SqlParameter();
myParam.ParameterName = "@.UserName";
myParam.Value = User.Identity.Name;

myCommand.Parameters.Add(myParam);

myParam = new SqlParameter();
myParam.ParameterName = "@.TournamentName";
myParam.Value = Request.QueryString["TournamentName"];

myCommand.Parameters.Add(myParam);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader(); <======================

I continue to get this error and I can't figure out the problem! Anything helps. Thank you very much.

era86:

"SELECT (Deadline, Description, Headline, AddressField, OrganizationField, NameField, FileField, EmailField, CommentField) FROM RegistrationFormDB_Info WHERE (UserName = @.UserName AND TournamentName = @.TournamentName)"

Remove the ( ) from your select query. You final query would be:

"SELECT Deadline, Description, Headline, AddressField,OrganizationField, NameField, FileField, EmailField, CommentField FROMRegistrationFormDB_Info WHERE UserName = @.UserName AND TournamentName= @.TournamentName"

Incorrect Syantax

Hi,
I am trying to use the following statement inmy trigger; but I am getting
error "incorrect syntax near ',' .
IF (@.CompanyOrderNo,@.Status NOT IN ( SELECT COMPANY_OrderNO,Status_ID from
OrderStatus))
How to correct this?
Thanks
pmudIF ( SELECT count(*) from OrderStatus
where COMPANY_OrderNO = @.CompanyOrderNo and Status_ID=@.Status ) = 0
"pmud" wrote:

> Hi,
> I am trying to use the following statement inmy trigger; but I am getting
> error "incorrect syntax near ',' .
>
> IF (@.CompanyOrderNo,@.Status NOT IN ( SELECT COMPANY_OrderNO,Status_ID fr
om
> OrderStatus))
> How to correct this?
> Thanks
> --
> pmud|||This works... Thanks!
--
pmud
"tthrone" wrote:
> IF ( SELECT count(*) from OrderStatus
> where COMPANY_OrderNO = @.CompanyOrderNo and Status_ID=@.Status ) = 0
> "pmud" wrote:
>

Incorrect sql statement - not reading variable

In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.

However this value is not then working in the following line:

strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

This statement is not reading any value for ProjectID and so is selecting all from the table....

How do I write this statement to pick up the variable above???

Thanks in advance for your help!

Clare

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"Dim connStringAsString ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"

da =

New Data.SqlClient.SqlDataAdapter(strSQL, connString)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

ForEach tempRowAs Data.DataRowIn ds.Tables(0).Rows

tempRow.Item(

"imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))Next

ImgGrid3.DataSource = ds

ImgGrid3.DataBind()

EndSub

hi,

try this

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.projectID"

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

yourcommand.Pramaters.Add(p)

|||

Thanks for your quick response but this is not working. I do not have a sql command declared, I am using strSQL ( a string), and da (a sqldataadaptor).

Can you help??

Thanks very much

|||

hi cabby

use following

Dim da As Data.SqlClient.SqlDataAdapterDim strSQL As StringDim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.ProjectID" dim sqlcmdas new sqlcommand(strsql, connstring)sqlcmd.parameter.addwithvalue("ProjectID", request.querystring("projectid"));Dim connString As String ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(sqlcmd) da.Fill(ds)in case your project idis char/varchar field use followingas parametervaluesqlcmd.parameter.addwithvalue("ProjectID","'" + request.querystring("projectid") +"'" ) 'see its included in single quotes

HTH

satish.

|||Hiya,|||

Hiya,

Thanks for the response. Sorry if I am asking simple questions but I have tried your code and am getting the following error:

Dim

sqlcmdAsNew sqlcommand(strSQL, connstring) - - - type 'sqlcommand' is not defined... so I change this to

Dim

sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString) -- and then I get connString - value of type string cannot be converted to 'System.Data.SqlClient.SqlConnection' and I also get an error on the next line

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid")) -- parameter is not a member of 'System.Data.SqlClient.SqlConnection'

My code now is:

Dim

dsAsNew Data.DataSetDim daAs Data.SqlClient.SqlDataAdapterDim strSQLAsStringDim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString)

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid"))Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

Thanks for the help!

Clare

|||first of all make sure you've imported system.data.sqlclient.

DimdsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"

Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL,new sqlconnection(connString) )

sqlcmd.parameters.addwithvalue(

"ProjectID", ProjectID) //make sure variable projectid is not null

Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

hope it will help now.

thanks,

satish.

|||

Thanks again, really appreciate the help.

Nearly there now I think...

I am getting to the line:

da.Fill(ds)

and getting the error:

Invalid column name'@.ProjectID'.

Any ideas?

Thanks

Clare

|||

hi,

try this

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

sqlcmd.Pramaters.Add(p)

|||

errr sorry appologies for silly mistake of me change following line of my code

sqlcmd.parameters.addwithvalue("ProjectID", ProjectID) //make sure variable projectid is not null

to

sqlcmd.parameters.addwithvalue("@.ProjectID", ProjectID) //make sure variable projectid is not null

thanks,

satish.

|||

Hi,

I am still getting the same error. Do I need to declare in my sub function header that it is taking in a parameter? And if so how do I write this on page load?

Thanks

Clare

|||

After all this I can just use:

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] =" & Request.QueryString("ProjectID")

Thanks for all the help

|||

hi

try this, it works fine

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =Request.QueryString("ProjectID")

p.SqlDbType =Data .SqlDbType .String;

sqlcmd.Pramaters.Add(p)

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean
I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Incorrect Date Conversion - Help Please

Dear Group

Some but not all of the dates are incorrect converted (incremented by 1
day) with the following SQL statement.

SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
ORDER BY ReturnedByDate ASC

'ReturnedByDate' is of type smalldatetime in the table. I need the
result set to return me the ReturnByDate once as actual date values
(for the value of a dropdown) and once as text values (for the text of
the same dropdown). It all works fine, it's just that some actual date
values are incremented by one day. The third column in the result set
sample represents the actual database value as a reference to give you
an idea of the original value.

ReturnedByDate LabelReturnedByDate ReturnedByDate
-------- ------ ------
2004-11-05 00:00:00.000 04.11.2004 2004-11-04 15:22:00
2004-11-11 00:00:00.000 11.11.2004 2004-11-11 00:00:00
2004-11-12 00:00:00.000 12.11.2004 2004-11-12 00:29:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:23:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:24:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 21:10:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 14:50:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:02:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:03:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:09:00
2004-12-09 00:00:00.000 09.12.2004 2004-12-09 08:09:00
Any help is very appreciated!

Thanks for your time & efforts!

MartinDECLARE @.ReturnedByDate smalldatetime
SET @.ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@.ReturnedByDate AS real) AS Y,
cast(@.ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@.ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@.ReturnedByDate,104) AS
LabelReturnedByDate, @.ReturnedByDate
-- FROM i2b_keytransactionhistory ORDER BY ReturnedByDate ASC

select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)|||DECLARE @.ReturnedByDate smalldatetime
SET @.ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@.ReturnedByDate AS real) AS Y,
cast(@.ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@.ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@.ReturnedByDate,104) AS
LabelReturnedByDate, @.ReturnedByDate
-- FROM i2b_keytransactionhistory ORDER BY ReturnedByDate ASC

select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)

-- Happy Holidays!
-- Kurt|||(theintrepidfox@.hotmail.com) writes:
> Some but not all of the dates are incorrect converted (incremented by 1
> day) with the following SQL statement.
> SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
> ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
> LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
> ORDER BY ReturnedByDate ASC

Converting datetime to int is not a very bright idea. For some reason,
that comes with rounding, so hours in the afternoon gets round up to
the next day.

Stick to convering your datetime to character.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 28 Dec 2004 18:26:55 -0800, theintrepidfox@.hotmail.com wrote:

>Some but not all of the dates are incorrect converted (incremented by 1
>day) with the following SQL statement.
>SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
>ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
>LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
>ORDER BY ReturnedByDate ASC

Hi Martin,

What Erland says :-)

But if you somehow REALLY need the number of days since 19000101 in your
frontend, the following should prove more reliable:

SELECT DATEDIFF(day, '19000101', ReturnedByDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)