Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Incorrect syntax near the keyword 'convert' in stored procedure using sql server 2005

Posted on 2008-06-25
3
Medium Priority
?
2,675 Views
Last Modified: 2008-06-30
Hi,

I have the following code that is talking to a sql stored procedure: -

USE [testdata]
GO

DECLARE      @return_value int,
            @NewTestID uniqueidentifier,
            @ErrorCode int,
            @ErrorDescription varchar(255)

EXEC      @return_value = [dbo].[AddNewTestData_v0141]
            @Operator = 13,
            @SNumber = 1689,
            @SerialNumber = N'A08171470380006',
            @DateTimeStart = convert(datetime,'2008-04-22 13:35:00',120),
            @DateTimeStop = convert(datetime,'2008-04-22 13:36:00',120),
            @TestResult = N'1',
            @Assembly = N'AH387775U001',
            @StageRefNum = 945,
            @TransactionNo = 2,
            @NewTestID = @NewTestID OUTPUT,
            @ErrorCode = @ErrorCode OUTPUT,
            @ErrorDescription = @ErrorDescription OUTPUT

SELECT      @NewTestID as N'@NewTestID',
            @ErrorCode as N'@ErrorCode',
            @ErrorDescription as N'@ErrorDescription'

SELECT      'Return Value' = @return_value

GO


It keeps on coming up: -

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'convert'.

The two date parameters are set to datetime.

What am I doing wrong please?

Many Thanks
Lee

0
Comment
Question by:ljhodgett
  • 2
3 Comments
 
LVL 2

Expert Comment

by:pierky
ID: 21864250
Try this:

DECLARE DateTimeStart datetime
DECLARE DateTimeStop datetime
SET DateTimeStart = convert(datetime,'2008-04-22 13:35:00',120)
SET DateTimeStop = convert(datetime,'2008-04-22 13:36:00',120)

EXEC      @return_value = [dbo].[AddNewTestData_v0141]
            @Operator = 13,
            @SNumber = 1689,
            @SerialNumber = N'A08171470380006',
            @DateTimeStart,
            @DateTimeStop,
            @TestResult = N'1',
            @Assembly = N'AH387775U001',
            @StageRefNum = 945,
            @TransactionNo = 2,
            @NewTestID = @NewTestID OUTPUT,
            @ErrorCode = @ErrorCode OUTPUT,
            @ErrorDescription = @ErrorDescription OUTPUT
0
 

Author Comment

by:ljhodgett
ID: 21864415
Hi,

It comes back with the error message: -

Msg 155, Level 15, State 2, Line 1
'datetime' is not a recognized CURSOR option.
Msg 155, Level 15, State 2, Line 2
'datetime' is not a recognized CURSOR option.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@return_value".

Best Regards
Lee
0
 
LVL 2

Accepted Solution

by:
pierky earned 2000 total points
ID: 21864445
Sorry sorry sorry... bad copy/paste, I forgot some "@"

DECLARE @DateTimeStart datetime
DECLARE @DateTimeStop datetime
SET @DateTimeStart = convert(datetime,'2008-04-22 13:35:00',120)
SET @DateTimeStop = convert(datetime,'2008-04-22 13:36:00',120)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question