Syntax Error

Sep 14, 2004

I get the following errors with the script below:

I am novice with SQL and have been unable to figure out the syntax just yet. The line for the errors are marked blue

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'user'.
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'auto_increment'.
Server: Msg 170, Level 15, State 1, Line 44
Line 44: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 53
Line 53: Incorrect syntax near '='.

-----------------------------------------------------------

CREATE TABLE admin (
user varchar(100) NOT NULL default,
pass varchar(100) NOT NULL default ''
) TYPE=MyISAM;

INSERT INTO admin VALUES ( 'admin', 'pass456');

CREATE TABLE affiliates (
refid varchar(30) NOT NULL default '',
pass varchar(20) NOT NULL default '',
company varchar(100) NOT NULL default '',
title varchar(5) NOT NULL default '',
firstname varchar(40) NOT NULL default '',
lastname varchar(40) NOT NULL default '',
website varchar(100) NOT NULL default '',
email varchar(100) NOT NULL default '',
payableto varchar(100) NOT NULL default '',
street varchar(100) NOT NULL default '',
town varchar(100) NOT NULL default '',
county varchar(100) NOT NULL default '',
postcode varchar(20) NOT NULL default '',
country varchar(100) NOT NULL default '',
phone varchar(30) NOT NULL default '',
fax varchar(30) NOT NULL default '',
date varchar(40) NOT NULL default ''
) TYPE=MyISAM;

CREATE TABLE banners (
number int(3) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
image varchar(60) NOT NULL default '',
description varchar(200) NOT NULL default '',
PRIMARY KEY (number)
) TYPE=MyISAM;

CREATE TABLE clickthroughs (
refid varchar(20) default 'none',
date date NOT NULL default '0000-00-00',
time time NOT NULL default '00:00:00',
browser varchar(200) default 'Could Not Find This Data',
ipaddress varchar(50) default 'Could Not Find This Data',
refferalurl varchar(200) default 'none detected (maybe a direct link)',
buy varchar(10) default 'NO'
) TYPE=MyISAM;

CREATE TABLE sales (
refid varchar(20) NOT NULL default '',
date date NOT NULL default '0000-00-00',
time time NOT NULL default '00:00:00',
browser varchar(100) NOT NULL default '',
ipaddress varchar(20) NOT NULL default '',
payment varchar(10) NOT NULL default ''
) TYPE=MyISAM;

HELP!

View 1 Replies


ADVERTISEMENT

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Getting Server Error Syntax Error Converting The Nvarchar Value 'Sonoma' To A Column Of Data Type Int.

Apr 20, 2007

Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
 And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
 1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24

 
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07

View 2 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Error 241: Syntax Error Converting Datetime From Character String

Jan 7, 2004

Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:


CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO



Thanx in front
Cya

View 14 Replies View Related

Snapshot Agent Gives Error: Line38: Syntax Error Near 'tabl'

Oct 1, 2005

Hi all,

View 4 Replies View Related

Alter Table Syntax Error - Anyone Sees The Error?

Nov 24, 2004

Hello,

the following alter table statement:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED

is answered with:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.

which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.

Note that:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL

DOES get executed, and

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY

produces the same error.

Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.

So - anyone an idea?

View 4 Replies View Related

Error 0: Syntax Error Or Access Violation

Aug 1, 2004

Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.


CREATE PROC CabsSchedule_Insert
{
@JulianDatesmallint,
@SiteCodesmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvd varchar(30),
@Remarksvarchar(50)
}
AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)


Thanks,

View 2 Replies View Related

SQL Syntax Error

Apr 7, 2007

Hi Guys, I'm hoping somebody can help me with this really frustrating problem that I'm having.......
 I'm developing a peer to peer file sharing application (final year degree project) in which I use a web service & sql database as the management server. For some strange reason, I'm getting an SQL syntax error on some machines but not on others when I call the method to submit a file list to the server (see below for code for the method). Another strange thing is that on different machines, I'm getting a different error. I've seen "incorrect syntax near 'd' ". and also "incorrect syntax near 've' ", while on two other machines it works just fine - It appears that the connection to the webservice and to the database is working just fine on all machines as before this method I have a login which works perfectly and the data is represented in the database.
Does anybody have any pointers or even the slighest idea what can cause an error like this or have seen anything like this before. Hoping to get this sorted pretty soon as the deadline is nearing. All and any help is very much appreciated!!!!
Kevin
public void submitFiles(FileObject[] files, string peerID)
{
foreach (FileObject fo in files)
{ System.Text.StringBuilder submit = new System.Text.StringBuilder("INSERT INTO SharedFiles (FileID, FileName, FileType, FileSize, PeerID) VALUES ('" + fo.guid.ToString() + "', '" + fo.name + "' ,'" + fo.name + "', '" + fo.size + "', '" + peerID + "')");
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(submit.ToString(), con);
try { con.Open();
cmd.ExecuteNonQuery();
 } finally { con.Close(); } 
}
 }

View 2 Replies View Related

SQL Syntax Error

Aug 8, 2007

Hello, I have this sql quiery: sqlcommand2.CommandText = "Select Count(UserIP) From InboundTraffic Where InboundURL Contains('" & SiteDomain(i).ToString & "') and DateTimeReceived > " & Last30Days

 
SiteDomain is placing a string variable such as website.com and Last30Days is a date variable which = now - 30days
Im getting this error "Syntax error (missing operator) in query expression 'InboundURL Contains('website.com') and DateTimeReceived > 7/9/2007 8:20:30 PM'"
 
What am I mising?  THANKS!

View 5 Replies View Related

Syntax Error

Jan 17, 2008

Could someone help me with this error message:
Syntax error converting the varchar value '180 Ways to Walk the Leadership Talk by John Baldoni' to a column of data type int.
Getting error on the Titles.title column.  Tried casting it but it still does not work.  What am I missing? 
CREATE procedure GetRequestInfo@Requestorid int
ASselect distinct requestors.Requestorid, CAST(Titles.title AS VARCHAR(255)), requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip,LibraryRequest.ShipDate,LibraryRequest.DueDate,LibraryRequest.ReturnDate,
Cast(DATEPART(m, requestors.requestDate) as Varchar(5)) + '/' +Cast(DATEPART(d, requestors.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, requestors.RequestDate) as Varchar(5)) as 'RequestDate'
from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.Titleid = LibraryRequest.Titleidwhere Requestors.requestorid = requestors.requestoridGO

View 2 Replies View Related

Syntax Error In IF ELSE

Apr 17, 2008

Can anyone tell me why I get a syntax error on the THEN and the ELSE?
@Start datetime,@End datetime,@EmailAck bit,@SelectedProcess nvarchar(25)ASBEGINIF @SelectedProcess = 'Monthly' THENUPDATE tblReminderSchedule SETPrintedDate=GETDATE(),[Status]=1FROM  tblReminderSchedule INNER JOIN  tbllOAN ON tblReminderSchedule.lOAN_ID = tbllOAN.lOAN_IDWHERE (tblReminderSchedule.ReminderDate BETWEEN @Start AND @End) AND (dbo.tblReminderSchedule.ReceivedDate IS NULL) AND   (tbllOAN.ReminderByEmail = @EmailAck) AND (tbllOAN.Frequency = 'Monthly')ELSEUPDATE tblReminderSchedule SETPrintedDate=GETDATE(),[Status]=1FROM  tblReminderSchedule INNER JOIN  tbllOAN ON tblReminderSchedule.lOAN_ID = tbllOAN.lOAN_IDWHERE (tblReminderSchedule.ReminderDate BETWEEN @Start AND @End) AND (dbo.tblReminderSchedule.ReceivedDate IS NULL) AND   (tbllOAN.ReminderByEmail = @EmailAck) AND (tbllOAN.Frequency <> 'Monthly')END 

View 4 Replies View Related

Syntax Error

Mar 21, 2004

I am trying to open a table in my DB to check for login ids:

The code I used is below> I will appreciate any help on this matter

<CODE>
Public Function Authorize(ByVal Username As String, ByVal Password As String) As Integer
Dim sql As String
Dim con As New SqlConnection("data source=localhost; initial catalog=Jasist; Integrated Security = SSPI")
sql = "Select * from USER Where User_Name = '" & Username & "' and User_Passwd = '" & Password & "'"
con.Open()
Dim cmd As New SqlCommand(sql, con)
Dim Id1 As Integer
Dim dr As SqlDataReader = cmd.ExecuteReader
If dr.Read Then
Id1 = 1
con.Close()
dr.Close()
Return Id1
Else
con.Close()
dr.Close()
Return 0
End If
End Function
<CODE>



THE ERROR GENERATED WAS->

Server Error in '/Jasist' Application.
--------------------------------------------------------------------------------

Incorrect syntax near the keyword 'USER'.
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 'USER'.

Source Error:


Line 19: con.Open()
Line 20: Dim cmd As New SqlCommand(sql, con)
Line 21: Dim Id1 As Integer
Line 22: Dim dr As SqlDataReader = cmd.ExecuteReader
Line 23:


Source File: C:InetpubwwwrootJasistClass1.vb Line: 21



--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

View 2 Replies View Related

Syntax Error

Mar 27, 2001

Hi, hope someone can spend a minute checking out my script error. The following is part of my SQL statement. It has got syntax error near "="
I was hoping the script could run 100 times and print number 1 to 100.

DECLARE @ID int
SET @id = 1
EXEC (' WHILE ' + @id + ' <= 100 ' + ' BEGIN SELECT ' + @id +
' SET ' + @id + ' = ' + @id + ' + 1 ' + ' END ' )

Any input is most welcome.


Richard

View 4 Replies View Related

Need Help With Syntax Error.

Oct 17, 2000

Can anyone tell me what is wrong with the script below?

INSERT INTO #SUBSCRIBERLIST(EMEMBER_GUID,EHBSID)SELECT (M.EMEMBER_GUID,@EHBSID)
FROM EMEMBER M WHERE M.EMEMBER_GUID = @EM_ID1 AND M.MEMBERTYPE <>1

This is the error I am getting.

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ','.


Thanks in advance.

Matt

View 1 Replies View Related

Syntax Error Near '='

Aug 31, 2004

hi
i am getting this error while running SQL query : syntax error near '='

Query is :

SELECT People.People, People.Name,
Sum(([ProjectStatusReport].[Week]), 0, ([ProjectStatusReport].[Week] = #8/27/2004#, 1, 0)) AS Created,
Sum((ProjectStatusReport.Week), 0, (ProjectStatusReport.Week = #8/27/2004#, (ProjectStatusReport.Accomplishments) Or (ProjectStatusReport.Plans), 0, 1, 0)) AS Complete,
People.email FROM Register INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status
LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register INNER JOIN People ON StakeHolders.People = People.People
WHERE SR_Status.Status='Active' AND StakeHolders.Status = 'Yes' GROUP BY People.People, People.Name, People.email
HAVING (Sum(ProjectStatusReport.Week), 0, ((ProjectStatusReport.Week) = #8/27/2004#, 1, 0) > 0))
AND Sum(ProjectStatusReport.Week),0,((ProjectStatusRep ort.Week) = #8/27/2004#,(ProjectStatusReport.Accomplishments) Or (ProjectStatusReport.Plans),0,1,0)>0
ORDER BY People.People

Any help

Regards

View 2 Replies View Related

Syntax Error

Feb 6, 2007

Hi All,

When I run the following portion of my stored procedure, I get the following error message:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Here is the script:

set @dttimestr = 'select @dttime = substring(convert(varchar(11),getdate(),120),1,4)+
substring(convert(varchar(11),getdate(),120),5,3)'

EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output
print @dttime

Can you please tell me how to fix it?

View 3 Replies View Related

Syntax Error...

Jul 1, 2004

Quick question..

SELECT * FROM Table_1
INTERSECT
SELECT * FROM Table_2
INTERSECT
SELECT * FROM Table_3;

Syntax error in FROM clause, and it highlights the second select.. whats wrong with this statement? does it not like the *'s?

Thx

View 2 Replies View Related

Where's The Syntax Error????

Jun 30, 2004

Code:


str_IntroducerInfo = "UPDATE tblPolicy SET " & _ "Saluation = '" & saluation & "', " & _
"ClientName = '" & tempName & "', " & _
"ClientNRIC = '" & nric & "', " & _
"Submitted = '" & submitted & "', " & _
"Inforced = '" & inforced & "', " & _
"PolicyNo = '" & policyno & "', " & _
"ProductName = '" & productname & "', " & _
"ProductProvider = '" & productprovider & "', " & _
"ModalPremium = '" & modalpremium & "', " & _
"Freq = '" & freq & "', " & _
"Method = '" & method & "', " & _
"NextBirth = '" & nextbirth & "', " & _
"PremTerm = '" & premiumterm & "', " & _
"PolicyTerm = '" & policyterm & "', " & _
"Status = '" & pstatus & "', " & _
"ApplType = '" & appltype & "', " & _
"ApplType(A&H) = '" & appltypeah & "', " & _
"SupervisorAgree = '" & supervisoragree & "', " & _
"SupervisorRemarks = '" & superremarks & "', " & _
"Remarks = '" & remarks & "', " & _
"DateSig = '" & datesig & "', " & _
"AuthPerson = '" & authPerson & "', " & _
"CorFax = '" & corfax & "', " & _
"Contact = '" & number1 & "', " & _
"Contact1 = '" & number2 & "', " & _
"CorAddress = '" & address & "', " & _
"CorEmail = '" & email & "', " & _
"CorNature = '" & nature & "', " & _
" WHERE ID = '" & introducerID & "';"

response.Write(str_IntroducerInfo)
set rs_IntroducerInfo=conn.Execute(str_IntroducerInfo)



The output from the line


Code:


UPDATE tblPolicy SET Saluation = 'Miss', ClientName = 'Karlam Pte Ltd', ClientNRIC = 'S7908121X', Submitted = '24 February 2003', Inforced = '25 February 2003', PolicyNo = '30', ProductName = 'Shop Care', ProductProvider = 'Liberty Insurance', ModalPremium = '50000', Freq = 'Yearly', Method = 'Cheque', NextBirth = ' ', PremTerm = '5', PolicyTerm = '2', Status = 'EE', ApplType = 'AWS', ApplType(A&H) = 'A', SupervisorAgree = 'Yes', SupervisorRemarks = 'Good', Remarks = 'Bad', DateSig = '21 March 2004', AuthPerson = 'Ho LiLi', CorFax = '68525687', Contact = '68523652', Contact1 = '65825588', CorAddress = '20 Robotson Road, Robot Tower B, #03-98, S(263673)', CorEmail = 'karlam@kar.com', CorNature = 'Trading', WHERE ID = '80017-S7908121X';



The error

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/Ivan-Test/ops/comm.asp, line 1851

My Access table fields
ID
PolicyNo
Submitted
Inforced
Saluation
ClientName
ClientNRIC
FACode
Type
ProductProvider
ProductName
ModalPremium
Freq
Method
NextBirth
PremTerm
PolicyTerm
Status
ApplType
ApplType(A&H)
SupervisorAgree
DateSig
SupervisorRemarks
Remarks
AuthPerson
Contact
Contact1
CorAddress
CorFax
CorEmail
CorNature

View 3 Replies View Related

Bcp Syntax Error Help?

Jun 6, 2004

I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @returnDay int
DECLARE @query varchar(8000)
--Looking at current date,
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 8
BEGIN
SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c: est.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @query

EXEC master.dbo.xp_sendmail @recipients=test@test.com',
@copy_recipients = test@test.com',
@message='Submitting BloodBank Results for the previous month.
@subject='BloodBank results for the previous month',@attachments = '\testc$ est.txt'

SELECT @@ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy......here...
Thanks

View 1 Replies View Related

Syntax Error /

Jan 19, 2007

I have a syntax error of


Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '<'.

When running the following.
I have highlighted the offending character.


DECLARE @Todayvarchar(30)SET @Today = GetDate()
DECLARE @Aprilvarchar(30)SET @April = '01/04/'
DECLARE @Augustvarchar(30)SET @August = '01/08/'
DECLARE @LastYear varchar(30)SET @LastYear = YEAR(DATEADD(yy,-1,GetDate()))
DECLARE @ThisYear varchar(30)SET @ThisYear = YEAR(GetDate())

SELECTe.employee_numberAS 'Employee Number',
e.known_as_and_surnameAS 'Name',
b.benefit_codeAS 'Benefit Code'

FROMpwa_master.employeee,
pwa_master.benefitb

WHEREe.unique_identifier = b.parent_identifier
ANDb.benefit_code = 'BONUS'
ANDe.company_reference IN ('NGB','NGBO','BMS')
ANDe.payment_method = 'MONTHLY'
ANDb.benefit_start_date >=

(
CASE @Today < @April + @ThisYear
THEN
@LastYear
ELSE
(
CASE @Today > @April + @ThisYear
THEN
(
CASE e.company_reference WHEN 'NGB'
THEN
@April + @ThisYear
ELSE
@April + @LastYear
END
)
ELSE
(
CASE @Today > @August + @ThisYear
THEN
@April + @ThisYear
END
)
END
)
END
)


GROUP BY employee_number, known_as_and_surname, benefit_code, e.company_reference

Can anyone tell me why this happens?
I swear I have made case statements like this before (usign variables and greater-than signs etc).

It is hopefully me just being a dumba** but I can't see what's wrong with it.

-GeorgeV

View 14 Replies View Related

Syntax Error

Feb 7, 2007

Hi All,

When I run the following portion of my stored procedure, I get the following error message:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Here is the script:

set @dttimestr = 'select @dttime = substring(convert(varchar(11),getdate(),120),1,4)+
substring(convert(varchar(11),getdate(),120),5,3)'

EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output
print @dttime

Can you please tell me how to fix it?

View 8 Replies View Related

Syntax Error

Apr 2, 2008

Does anyone know why this blows up with a syntax error?

declare @dbccpage table (
ParentObject sysname,
Object sysname,
Field sysname,
VALUE sysname
)
insert into @dbccpage
exec ('dbcc page (<dbid>, <file-id>, <page-in-file>) with TableResults')
select Field, VALUE
from @dbccpage
where Field like 'Metadata:%'

View 5 Replies View Related

Syntax Error

Apr 17, 2008

Syntax error converting the varchar value '1,876.43 ' to a column of data type int.

i got this error im not really sure what its all about.

View 2 Replies View Related

Syntax Error UGH!

Jun 24, 2008

This is a simple error but for some reason i've been staring at it for 30 mins and still can't seem to find whats wrong!! If someone can help that would be greatly appreciated. i'm getting the error:

Msg 102, Level 15, State 1, Procedure LibraryAddAdult, Line 12
Incorrect syntax near '('.
THERE IS NO ( NEAR LINE 12!

USE Library
GO

CREATE PROC LibraryAddAdult
@firstname shortstring,
@lastname shortstring,
@street shortstring,
@city shortstring,
@state statecode,
@zip zipcode,
@member_no member_no,
@middleinitial letter = ' ',
@phone_no phonenumber = ' ',
@expr_date datetime = dateadd(year, 1, getdate())

OUTPUT
AS
IF (
(@firstname = ' ') OR
(@lastname = ' ') OR
(@street = ' ') OR
(@city = ' ') OR
(@state = ' ') OR
(@zip = ' ') OR
(@member_no = ' ')
)
RETURN SELECT 'You must insert appropriate name, street, city, state, zip'


INSERT INTO member(firstname, lastname, middleinitial)
VALUES (@firstname, @lastname, @middleinitial)

INSERT INTO adult(street, city, state, zip, phone_no, expr_date)
VALUES (@street, @city, @state, @zip, @phone_no, @expr_date)

SET @member_no = SCOPE_IDENTITY()

RETURN
SELECT 'Member has been successfully added to the database'
SELECT (lastname + ' ' + middleinitial + ' ' + lastname) AS Name, street, city, state, zip, phone, expr_date
FROM member
INNER JOIN adult
ON member.member_no = adult.member_no

View 1 Replies View Related

Syntax Error

Feb 15, 2007

Hello,

Why is it giving me this error?

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

on this line of code??

where substring (compl_cde_ojb, 1, 3) = CODE (+)

Thanks,

Kurt

View 3 Replies View Related

Syntax Error

Mar 9, 2007

What's wrong with this sp ? I get the error 'Incorrect syntax near the keyword where.' If I use between instead like this it works
--MaxDateOut between @strStartDate and @strEndDate
but I want to include the start and end dates. MaxDateOut finds the latest date in a view which groups the data.

I have also tried taking the 'and' out before the 'where' and same error.

CREATE PROCEDURE spRMU_GetChaseFiles

@strStartDate datetime,
@strEndDate datetime,
@strDepartment nvarchar(100),
@strDivision nvarchar(100)

AS

SELECT *
FROM vweChaseFilesOut

WHERE

Dept like '%'+@strDepartment+'%' and
Division like '%'+@strDivision+'%' and
where MaxDateOut >= @strStartDate and <= @strEndDate
order by MaxDateOut asc
GO

View 6 Replies View Related

Syntax Error

Oct 4, 2007

i'm getting syntax error.....
please guide where it will be?

it is saying near case.

SELECT
CASE Column5
WHEN 1 THEN EXEC INSERT INTO '+#tbl+' SELECT Column2 FROM TABLE13
WHEN 2 THEN 'Item Type' -- TABLE11
WHEN 3 THEN 'Item Group' -- TABLE117
END
FROM TABLE90 WHERE Column3 = 1 AND Column4 = 1


Vinod
Even you learn 1%, Learn it with 100% confidence.

View 2 Replies View Related

Sql Syntax Error

Nov 12, 2007



Hi there. I'm using the query builder on my table adapter in vb.net 2005 to build a select statement. it's not working quite the way i want and i'm also have trouble understanding why my sql statement is getting "rewritten" by the query builder.
I think my problem is with the way I'm specifying an OR clause. for example, I'd like to select records where the field ARB is either null or set to true. when i run the query, i don't get any errors, but the results aren't correct and the sql has changed.

What I would like is:

SELECT field names
FROM HOUSEHOLDER
WHERE (PostalCode LIKE @PCode) AND ( (ARB = 'True') OR (ISNULL(ARB, '') = '') )
AND (ISNULL(TerritoryID, '') = '') AND ( (ISNULL(DoNotCall, '') = '') OR (DoNotCall = 'False') ) AND ((ISNULL(ActiveRecord, '')= '') OR (ActiveRecord = 'True'))

Once I save the query, it gets modified to:

SELECT ID, FName, LName, StreetNum, StreetID, CityID, PostalCode, PhoneNumber, ApartmentNumber, Moved, ARB, DoNotCall, TerritoryID, ActiveRecord,
Comments, LastUpdated
FROM HouseHolder
WHERE (PostalCode LIKE @PCode) AND (ARB= 'True') OR
(PostalCode LIKE @PCode) AND (ISNULL(ARB, '') = '') AND (ISNULL(TerritoryID, '') = '') AND (ISNULL(DoNotCall, '') = '') OR
(PostalCode LIKE @PCode) AND (ISNULL(ARB, '') = '') AND (ISNULL(TerritoryID, '') = '') AND (DoNotCall = 'False') AND (ISNULL(ActiveRecord, '')
= '') OR
(PostalCode LIKE @PCode) AND (ISNULL(ARB, '') = '') AND (ISNULL(TerritoryID, '') = '') AND (DoNotCall = 'False') AND (ActiveRecord = 'True')


Can you tell me where my syntax is incorrect?
Please and thanks.

View 3 Replies View Related

Syntax Error

Sep 13, 2007



Hello all!
Getting a sysntax on this..

@FName nvarchar(50),

@LName nvarchar(50),

@DOB nvarchar(50),

@Acct nvarchar(50),

@Login nvarchar(50),

@Status nvarchar(50),

@Rmark nvarchar(255),

@Room nvarchar (50),

@Age nvarchar(50),

@Type nvarchar(50),

@Misc bit

AS

BEGIN

DECLARE @Variable as int

set @variable = 1

--DECLARE Variable = 1

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

IF LEN(@FName)>0

@Variable ++ 1

IF LEN(@LName)>0

Variable = Variable + 1

IF LEN(@DOB)>0

Variable = Variable + 1

IF LEN(@Acct)>0

Variable = Variable + 1

IF LEN(@Status)>0

Variable = Variable + 1

IF LEN (@Rmark)>0

Variable = Variable + 1

IF LEN (@Room)>0

Variable = Variable + 1

IF LEN (@Age)>0

Variable = Variable + 1

IF LEN (@Type)>0

Variable = Variable + 1

IF LEN (@Login)>0

Variable = Variable + 1

IF LEN (@Misc)>0

Variable = Variable + 1

/* Insert statements for procedure here*/

IF Variable =>1 THEN

SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,

Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,

Active_Orders.Order_ID, Active_Orders.Login

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Active_Orders.First_Name = @FName) OR

(Active_Orders.Last_Name = @Lname) OR

(Active_Orders.DOB = @DOB) OR

(Active_Orders.Account_Number = @Acct) OR

(Order_Status.Status = @Status) OR

(Active_Orders.Remarks = @Rmark) OR

(Active_Orders.Rm_Desc = @Room) OR

(Active_Orders.Age = @Age) OR

(Active_Orders.Type = @Type) OR

(Active_Orders.Login = @Login) OR

(Active_Orders.Misc = @Misc)

ELSE

SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,

Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,

Active_Orders.Order_ID, Active_Orders.Login

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Active_Orders.First_Name = @FName) AND

(Active_Orders.Last_Name = @Lname) AND

(Active_Orders.DOB = @DOB) OR

(Active_Orders.Account_Number = @Acct) AND

(Order_Status.Status = @Status) AND

(Active_Orders.Remarks = @Rmark) AND

(Active_Orders.Rm_Desc = @Room) AND

(Active_Orders.Age = @Age) AND

(Active_Orders.Type = @Type) AND

(Active_Orders.Login = @Login) AND

(Active_Orders.Misc = @Misc)

END

Msg 102, Level 15, State 1, Procedure Search4, Line 31

Incorrect syntax near '@Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 33

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 35

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 37

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 39

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 41

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 43

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 45

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 47

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 49

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 51

Incorrect syntax near 'Variable'.

Msg 102, Level 15, State 1, Procedure Search4, Line 53

Incorrect syntax near '>'.

Msg 156, Level 15, State 1, Procedure Search4, Line 72

Incorrect syntax near the keyword 'ELSE'.


Where am I going wrong?

Thanks!

Rudy

View 5 Replies View Related

Syntax Error

Sep 6, 2007

hi

below is my query

SELECT CAT.CAT_ID, CAT.CAT_NAME, CAT.CAT_ORDER, FORUM.*, TOPIC.*, REPLY.* FROM
(((FORUM_FORUM FORUM INNER JOIN FORUM_CATEGORY CAT ON FORUM.CAT_ID=CAT.CAT_ID))
INNER JOIN FORUM_REPLY REPLY ON REPLY.TOPIC_ID=TOPIC.TOPIC_ID)
INNER JOIN FORUM_TOPICS TOPIC ON FORUM.FORUM_ID=TOPIC.FORUM_ID)
WHERE ((F_PRIVATEFORUMS=0) OR (F_PRIVATEFORUMS=6 AND FORUM.FORUM_ID IN(0, 5, 7, 9, 15)))
AND (T_LAST_POST > '20070906005247') AND ((TOPIC.T_MESSAGE LIKE '%testing%') OR (REPLY.R_MESSAGE LIKE '%testing%'))
ORDER BY CAT.CAT_ORDER, CAT.CAT_ID, FORUM.F_ORDER, TOPIC.T_STICKY DESC, TOPIC.T_LAST_POST DESC

i m getting this error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

View 5 Replies View Related

A Syntax Error

Apr 18, 2007

Hi,

I write a test DMX as follows to expriment



cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, PredictStdev(BloodPressure) AS Stdev " +
"FROM PredictTimeSeries(BloodPressure, " + numTimePoints + ")" +
") " +
"FROM " + modelName;



However, after execute this command, a error show:



The syntax for 'Stdev' is incorrect. I have no idea about the reason to raise to error.



How can I fix it and why this error occur?



Any help would be welcome.



Ricky.

View 3 Replies View Related

UPDATE Syntax Error

Apr 10, 2007

Hello
I am trying to update a column containing URL's and include the "www." which had previously been omitted on many URL's in the column. But I get an error when trying to UPDATE
I have tried:
UPDATE table_nameSET URL = http://www.a2zdom.com/*WHERE URL = http://a2zdom.com/*
I have tried and left out the http: and also the /* but nothing works.  Is this type of update not possible?
Thanks

View 7 Replies View Related







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