Showing posts with label keyword. Show all posts
Showing posts with label keyword. Show all posts

Wednesday, March 28, 2012

Incorrect syntax near the keyword 'WHERE'.

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0

Below is the code I am using for an update to a MS Sql Database.

<%@. Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez -->
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->

<center>
<%

Dim oRS
Dim Conn

Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql

Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open

sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id

set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display -->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>

</form>

</center>
</body>
</html>sql=sql & "PhoneNumber='" & PhoneNumber & "',"

It looks like the syntax error is due to the extraneous comma after the last
column.

I strongly suggest you google 'SQL injection'. Your current code will allow
a hacker can execute any arbitrary SQL statement. The best protection
against injection is to use parameterized SQL statements, stored procedures
and validate user input. Never build a SQL Statement string by
concatenating user input values. The example below uses a parameterized
UPDATE statement via OLEDB:

Const adParamInput = 1
Const adInteger = 3
Const adVarChar = 200

Set Conn = CreateObject("ADODB.Connection")
Set Command = CreateObject("ADODB.Command")

Conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=FriendsContactInfo;" & _
"App=AspRunner Professional Application"

Command.ActiveConnection = Conn

Command.CommandText = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=?," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"

Set parameter = Command.CreateParameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"StreetAddress", _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"PhoneNumber", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Parameters.Append parameter

Command.Execute

Conn.Close

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DaveF" <jeacdf@.excite.comwrote in message
news:1173540573.073247.128620@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0
>
Below is the code I am using for an update to a MS Sql Database.
>
<%@. Language=VBScript %>
<% Option Explicit %>
>
<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez -->
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
>
<center>
<%
>
Dim oRS
Dim Conn
>
Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql
>
Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")
>
>
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open
>
>
sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id
>
>
>
set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display -->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>
>
</form>
>
</center>
</body>
</html>
>

Posted by abundantxcvr at 2:22 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END


My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

sql
Posted by abundantxcvr at 2:21 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END


My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

Posted by abundantxcvr at 2:21 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END


My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

Posted by abundantxcvr at 2:20 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Monday, March 26, 2012

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

Posted by abundantxcvr at 5:33 AM 0 comments
Labels: , , , , , , , , , , , ,

Incorrect syntax near the keyword 'UNION'. ??

Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
> DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
You can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
http://www.aspfaq.com/
(Reverse address to reply.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> By the way, that will totally distroy your TOP 1 logic... but here's a
very
> simple solution:
> SELECT *
> FROM
> (SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC) x
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
> AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> (SendDate
> the
> yet
>
Posted by abundantxcvr at 5:33 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Incorrect syntax near the keyword 'UNION'. ??

Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
> DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
PieterYou can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
http://www.aspfaq.com/
(Reverse address to reply.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> By the way, that will totally distroy your TOP 1 logic... but here's a
very
> simple solution:
> SELECT *
> FROM
> (SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC) x
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
> AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> (SendDate
> the
> yet
>sql
Posted by abundantxcvr at 5:32 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Incorrect syntax near the keyword 'UNION'. ??

Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign = tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign = tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
> DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
PieterYou can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign = tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> By the way, that will totally distroy your TOP 1 logic... but here's a
very
> simple solution:
> SELECT *
> FROM
> (SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC) x
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
> AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Why does this query give this error?
> > SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> > FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => > tblCampaigns.Campaign
> > WHERE (Number = '+32479990284') OR (Number = '0479990284')
> > ORDER BY SendDate DESC
> > UNION
> > SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> > FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => > tblCampaigns.Campaign
> > WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
> (SendDate
> > > DATEADD(hh, 48 ,GETDATE() ))
> >
> > Error:
> > Server: Msg 156, Level 15, State 1, Line 5
> > Incorrect syntax near the keyword 'UNION'.
> >
> > What I need to do is Select all the records (tblSMS) with SendDate from
> the
> > last 48 hours, and I have also to select the Last one (in case it isn't
> yet
> > in the last 48 hours).
> >
> > Does anybody knwos what goes wrong?
> >
> > If I try the two query's separetly they work fine...
> >
> > Thanks a lot,
> >
> > Pieter
> >
> >
>
Posted by abundantxcvr at 5:31 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

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!
Posted by abundantxcvr at 5:31 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Incorrect syntax near the keyword THEN

Hi Everyone,
I really tried to not post this question but I gave up. I tried brackets,
parenth...etc but nothing worked. I get this error message: Incorrect syntax
near the keyword 'THEN'. Please help, I am learning SQL Server.
thanks in advance.
Ismail

use mis
select CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET, CLAIM_HMASTERS_VS.

CLAIMNO,
'AMOUNT' =
CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSE
CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)
END,

FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO =
CLAIM_DETAILS_HCVW.CLAIMNO

where CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1Try putting the ELSE part out of the brackets
i.e
CASE
WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' )
ELSE CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET
END
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________

"ielmrani via SQLMonster.com" <u21259@.uwewrote in message
news:65279dafaa057@.uwe...

Quote:

Originally Posted by

Hi Everyone,
I really tried to not post this question but I gave up. I tried brackets,
parenth...etc but nothing worked. I get this error message: Incorrect


syntax

Quote:

Originally Posted by

near the keyword 'THEN'. Please help, I am learning SQL Server.
thanks in advance.
Ismail
>
use mis
select CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET,


CLAIM_HMASTERS_VS.

Quote:

Originally Posted by

>
CLAIMNO,
'AMOUNT' =
CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSE
CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)
END,
>
FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO


=

Quote:

Originally Posted by

CLAIM_DETAILS_HCVW.CLAIMNO
>
where CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1
>
>

|||That's it. Thank you so much.
Jack Vamvas wrote:

Quote:

Originally Posted by

>Try putting the ELSE part out of the brackets
>i.e
>CASE
>WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' )
>ELSE CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET
>END
>--
>--
>Jack Vamvas
>___________________________________
>Receive free SQL tips - www.ciquery.com/sqlserver.htm
>Make SQL Server faster - www.quicksqlserver.com
>___________________________________
>

Quote:

Originally Posted by

>Hi Everyone,
>I really tried to not post this question but I gave up. I tried brackets,


>[quoted text clipped - 20 lines]

Quote:

Originally Posted by

>Message posted via SQLMonster.com
>http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1
Posted by abundantxcvr at 5:30 AM 0 comments
Labels: , , , , , , , , , , , ,

Incorrect syntax near the keyword SELECT

Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"

----------------
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM
SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,
Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits
FROM Videos LEFT OUTER JOIN
(SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits
FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1
FROM Hits
GROUP BY VideoId, UserId) AS TB1
GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoId
ORDER BY TB2.hits DESC
) AS T1
WHERE rownum <= 5
-------------

If I run the query that is in BOLD as:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5

the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above?


Thank in advance,

Louis

Try this:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM(SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,
Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits
FROM Videos LEFT OUTER JOIN
(SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits
FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1
FROM Hits
GROUP BY VideoId, UserId) AS TB1
GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoId
ORDER BY TB2.hits DESC

)
) AS T1
WHERE rownum <= 5

|||

Thanks Limno, but I had tried that before and I get this error:

Incorrect syntax near ')'

I don't understand why

Louis

|||

Here is a working one:

SELECT*FROM(SELECT ROW_NUMBER()OVER(ORDERBY DateAdded)AS rownum,*FROM(SELECTTOP(100)PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,

Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits

FROM VideosLEFTOUTERJOIN

(SELECTTOP(100)PERCENT VideoId,COUNT(*)AS hits

FROM(SELECTTOP(100)PERCENT UserId, VideoId,COUNT(*)AS cnt1

FROM Hits

GROUPBY VideoId, UserId)AS TB1

GROUPBY VideoId)AS TB2ON Videos.VideoId= TB2.VideoId

ORDERBY TB2.hitsDESC

)AS T1) AS T2

WHERE rownum<= 5

|||

Thanks Limno, that worked

Louis

Posted by abundantxcvr at 5:30 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Incorrect syntax near the keyword 'OR'.

Hi,
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql
Posted by abundantxcvr at 5:29 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

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.
>
Posted by abundantxcvr at 5:28 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

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.
>
Posted by abundantxcvr at 5:27 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

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
Posted by abundantxcvr at 5:26 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Incorrect syntax near the keyword 'Insert'.

I'm trying to build a command that gets a linkservername but gets this error
msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
Thnaks.
set @.cmd = Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
run_date))),
case j.enabled
when 0 then 'N'
when 1 then 'Y'
End,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
End,
h.run_duration,
substring(h.message,1,300)
From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
[+@.linkservername+].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From [+@.linkservername+].[msdb].[dbo].
[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1
Message posted via http://www.webservertalk.comNaana,
I believe you'll need to use the EXEC statement and build your SQL statement
dynamically to do this. Google Dynamic SQL.
HTH
Jerry
"Naana via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:549AE24111A74@.webservertalk.com...
> I'm trying to build a command that gets a linkservername but gets this
> error
> msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
> Thnaks.
>
> set @.cmd = Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
> run_date))),
> case j.enabled
> when 0 then 'N'
> when 1 then 'Y'
> End,
> case h.run_status
> when 0 then 'Failed'
> when 1 then 'Successful'
> when 3 then 'Cancelled'
> when 4 then 'In Progress'
> End,
> h.run_duration,
> substring(h.message,1,300)
> From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
> [+@.linkservername+].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From [+@.linkservername+].[msdb].[dbo].
> [sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1
>
> --
> Message posted via http://www.webservertalk.com|||Hi
You are missing the many single quotes within the construction of @.cmd
Try:
set @.cmd = 'Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
case j.enabled
when 0 then ''N''
when 1 then ''Y''
End,
case h.run_status
when 0 then ''Failed''
when 1 then ''Successful''
when 3 then ''Cancelled''
when 4 then ''In Progress''
End,
h.run_duration,
substring(h.message,1,300)
From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From
['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1'
You may want to read:
http://www.sommarskog.se/dynamic_sql.html
John
"Naana via webservertalk.com" wrote:

> I'm trying to build a command that gets a linkservername but gets this err
or
> msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
> Thnaks.
>
> set @.cmd = Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
> run_date))),
> case j.enabled
> when 0 then 'N'
> when 1 then 'Y'
> End,
> case h.run_status
> when 0 then 'Failed'
> when 1 then 'Successful'
> when 3 then 'Cancelled'
> when 4 then 'In Progress'
> End,
> h.run_duration,
> substring(h.message,1,300)
> From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
> [+@.linkservername+].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From [+@.linkservername+].[msdb].[dbo].
> [sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1
>
> --
> Message posted via http://www.webservertalk.com
>|||John Bell wrote:
>Hi
>You are missing the many single quotes within the construction of @.cmd
>Try:
>set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
>['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>You may want to read:
>http://www.sommarskog.se/dynamic_sql.html
>John
>
>[quoted text clipped - 30 lines]
Hi John,
Thanks, the sub_query is working now but I get the following message when
runn the whole query:
1) Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ACCURATE'.
and
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ''.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.
The whole query:
Declare @.linkservername varchar(256)
Declare @.cmd varchar(8000)
Declare @.batchid bigint
-- Set the parms
exec pBatchInsert @.BatchID output
--select @.batchID
-- if exists drop then create table to hold error log results
If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
[Error_Log_Results_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Drop Table [dbo].[Error_Log_Results_Temp]
End
Create Table [dbo].[Error_Log_Results_Temp]
([vchMessage] [varchar] (255),
[ID] [int])
-- if exists drop the create table to hold a list of servers this proc is
accesing.
If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
[Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Drop Table [dbo].[Job_Status]
End
Create Table [dbo].[Job_Status]
(Status varchar(16),
Server varchar(64),
[TimeStamp] datetime)
-- cursur thru the sysservres table to get list of servers linked to this on
e.
Declare linkserver_cursor CURSOR
For Select srvname
From master..sysservers(nolock)
Where srvproduct = 'SQL Server'
and isremote = 1
and srvname not in ('DBPHQOVOM01\OVOPS')
-- and srvname = 'posrep'
Order by srvname
Open linkserver_cursor
Fetch Next From linkserver_cursor into @.linkservername
While ( @.@.fetch_status <> -1 )
Begin
If ( @.@.fetch_status <> -2 )
Begin
-- insert ststus
Insert into Job_Status
Select 'Started', @.linkservername, getdate()
Insert into Servers_Scaned
Select @.BatchID, @.linkservername
-- build command
set @.cmd = 'Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+',
'+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
case j.enabled
when 0 then ''N''
when 1 then ''Y''
End,
case h.run_status
when 0 then ''Failed''
when 1 then ''Successful''
when 3 then ''Cancelled''
when 4 then ''In Progress''
End,
h.run_duration,
substring(h.message,1,300)
From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From
['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1'
-- exec command
Exec (@.cmd)
--select @.cmd
-- Begin loading info from error logs.
Set @.cmd = ' '
Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
[master].[dbo].[xp_readerrorlog]'
--select @.cmd
Exec (@.cmd)
-- insert error log results into temp table and tag it with a server name.
Insert into Error_Log_Results
Select @.batchid, @.linkservername,'0', *
From Error_Log_Results_Temp
Where (vchMessage like '%error:%'
or vchMessage like '%failed%'
or vchMessage like '%invalid%'
or vchMessage like '%full%'
or vchMessage like '%hung%'
or vchMessage like '%kill%'
or vchMessage like '%violation%'
or vchMessage like '%SqlDumpExceptionHandler%'
or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
or vchMessage like '%Stack%'
or vchMessage like '%terminating%'
or vchMessage like '%shutdown%'
or vchMessage like '%fatal%')
-- truncate table for load from the second error log.
Truncate Table Error_Log_Results_Temp
-- clear then build the command
Set @.cmd = ' '
Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
[master].[dbo].[xp_readerrorlog] 1'
--select @.cmd
Exec (@.cmd)
-- Insert results into temp error log table
Insert into Error_Log_Results
Select @.batchid, @.linkservername,'1', *
From Error_Log_Results_Temp
Where (vchMessage like '%error:%'
or vchMessage like '%failed%'
or vchMessage like '%invalid%'
or vchMessage like '%full%'
or vchMessage like '%hung%'
or vchMessage like '%kill%'
or vchMessage like '%violation%'
or vchMessage like '%SqlDumpExceptionHandler%'
or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
or vchMessage like '%Stack%'
or vchMessage like '%terminating%'
or vchMessage like '%shutdown%'
or vchMessage like '%fatal%')
Truncate Table Error_Log_Results_Temp
Insert into Job_Status
Select 'Finished', @.linkservername, getdate()
End
FETCH NEXT From linkserver_cursor into @.linkservername
End
CLOSE linkserver_cursor
DEALLOCATE linkserver_cursor
-- Cleanup
Drop Table [dbo].[Error_Log_Results_Temp]
Drop Table [dbo].[Job_Status]
-- print ' '
-- print '***************************************
***'
-- print '** End of Report: '+convert(varchar(64),getdate())+' **'
-- print '***************************************
***'
-- Return (0)
GO
Message posted via http://www.webservertalk.com|||Hi
The query runs but now get's this message for my server names as invalid
column names.
) Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ACCURATE'.
Thanks.
John Bell wrote:
>Hi
>You are missing the many single quotes within the construction of @.cmd
>Try:
>set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
>['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>You may want to read:
>http://www.sommarskog.se/dynamic_sql.html
>John
>
>[quoted text clipped - 30 lines]
Message posted via http://www.webservertalk.com|||Hi
Accurate must be in your data, therefore you may want to try using PRINT
@.cmd before your EXEC statement to show what the SQL is that you are
executing. You could then 'debug' the output from the PRINT statement so tha
t
it provides what you require and then figure out how to make up the command
in dynamic SQL.
It could be that you are wanting to output a literal value rather than the
value of a column from your variables e.g.
If @.batchid had a value 101 and @.linkservername was DBPHQOVOM01\OVOPSVR
Select distinct '+convert(varchar(10),@.batchid)+','+@.lin
kservername+',...
Would be looking for a column called 101 and DBPHQOVOM01\OVOPSVR (which is
it if a valid column should be escaped with [] such as [DBPHQOVOM01\OVOPSVR]
)when you executed @.cmd.
Therefore:
Select distinct '+convert(varchar(10),@.batchid)+',['+@.li
nkservername+'],...
would give
Select distinct 101, [DBPHQOVOM01\OVOPSVR], ...
If you actually wanted a constants to be output then you need to enquote the
n
Select distinct
'''+convert(varchar(10),@.batchid)+''',''
'+@.linkservername+''',...
this would give
Select distinct '101', 'DBPHQOVOM01\OVOPSVR', ...
HTH
John
"Naana via webservertalk.com" wrote:

> John Bell wrote:
>
> Hi John,
> Thanks, the sub_query is working now but I get the following message when
> runn the whole query:
> 1) Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'ACCURATE'.
> and
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ''.
> Server: Msg 156, Level 15, State 1, Line 27
> Incorrect syntax near the keyword 'and'.
> The whole query:
> Declare @.linkservername varchar(256)
> Declare @.cmd varchar(8000)
> Declare @.batchid bigint
> -- Set the parms
> exec pBatchInsert @.BatchID output
> --select @.batchID
> -- if exists drop then create table to hold error log results
> If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
> [Error_Log_Results_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> Begin
> Drop Table [dbo].[Error_Log_Results_Temp]
> End
> Create Table [dbo].[Error_Log_Results_Temp]
> ([vchMessage] [varchar] (255),
> [ID] [int])
> -- if exists drop the create table to hold a list of servers this proc is
> accesing.
> If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
> [Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> Begin
> Drop Table [dbo].[Job_Status]
> End
> Create Table [dbo].[Job_Status]
> (Status varchar(16),
> Server varchar(64),
> [TimeStamp] datetime)
>
> -- cursur thru the sysservres table to get list of servers linked to this
one.
> Declare linkserver_cursor CURSOR
> For Select srvname
> From master..sysservers(nolock)
> Where srvproduct = 'SQL Server'
> and isremote = 1
> and srvname not in ('DBPHQOVOM01\OVOPS')
> -- and srvname = 'posrep'
> Order by srvname
> Open linkserver_cursor
> Fetch Next From linkserver_cursor into @.linkservername
> While ( @.@.fetch_status <> -1 )
> Begin
> If ( @.@.fetch_status <> -2 )
> Begin
> -- insert ststus
> Insert into Job_Status
> Select 'Started', @.linkservername, getdate()
> Insert into Servers_Scaned
> Select @.BatchID, @.linkservername
> -- build command
> set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+',
> '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
> ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>
> -- exec command
> Exec (@.cmd)
> --select @.cmd
> -- Begin loading info from error logs.
> Set @.cmd = ' '
> Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
> [master].[dbo].[xp_readerrorlog]'
> --select @.cmd
> Exec (@.cmd)
> -- insert error log results into temp table and tag it with a server name.
> Insert into Error_Log_Results
> Select @.batchid, @.linkservername,'0', *
> From Error_Log_Results_Temp
> Where (vchMessage like '%error:%'
> or vchMessage like '%failed%'
> or vchMessage like '%invalid%'
> or vchMessage like '%full%'
> or vchMessage like '%hung%'
> or vchMessage like '%kill%'
> or vchMessage like '%violation%'
> or vchMessage like '%SqlDumpExceptionHandler%'
> or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
> or vchMessage like '%Stack%'
> or vchMessage like '%terminating%'
> or vchMessage like '%shutdown%'
> or vchMessage like '%fatal%')
> -- truncate table for load from the second error log.
> Truncate Table Error_Log_Results_Temp
> -- clear then build the command
> Set @.cmd = ' '
> Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
> [master].[dbo].[xp_readerrorlog] 1'
> --select @.cmd
> Exec (@.cmd)
> -- Insert results into temp error log table
> Insert into Error_Log_Results
> Select @.batchid, @.linkservername,'1', *
> From Error_Log_Results_Temp
> Where (vchMessage like '%error:%'
> or vchMessage like '%failed%'
> or vchMessage like '%invalid%'
> or vchMessage like '%full%'
> or vchMessage like '%hung%'
> or vchMessage like '%kill%'
> or vchMessage like '%violation%'
> or vchMessage like '%SqlDumpExceptionHandler%'
> or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
> or vchMessage like '%Stack%'
> or vchMessage like '%terminating%'
> or vchMessage like '%shutdown%'
> or vchMessage like '%fatal%')
> Truncate Table Error_Log_Results_Temp
> Insert into Job_Status
> Select 'Finished', @.linkservername, getdate()
> End
> FETCH NEXT From linkserver_cursor into @.linkservername
> End
> CLOSE linkserver_cursor
> DEALLOCATE linkserver_cursor
>
> -- Cleanup
> Drop Table [dbo].[Error_Log_Results_Temp]
> Drop Table [dbo].[Job_Status]
> -- print ' '
> -- print '***************************************
***'
> -- print '** End of Report: '+convert(varchar(64),getdate())+' **'
> -- print '***************************************
***'
> -- Return (0)
>
> GO
>
> --
> Message posted via http://www.webservertalk.com
>
Posted by abundantxcvr at 5:26 AM 0 comments
Labels: , , , , , , , , , , , , ,

Incorrect syntax near the keyword in.

Hi

i want to copy tables from sql to access
i gave

SELECT Persons.* INTO Persons IN 'Backup.mdb'FROM Persons
 The error is Incorrect syntax near the keyword 'in'
any one reply me

I think we are going to need more information, but I am pretty sure you could use OPENDATASOURCE for this:
INSERT INTO
OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','DataSource="c:\test.mdb"; User ID=Admin;Password=' )...Persons(column1,column2)
SELECT * FROM Persons
(seehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9334 for reference)

Posted by abundantxcvr at 5:25 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

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!

Posted by abundantxcvr at 5:25 AM 0 comments
Labels: , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)