March 4, 2009 at 5:51 am
DECLARE
@LastRun2 datetime,
@UploadJob varchar(50),
@ID int,
@CashDesk int
set @LastRun2 = getdate()
set @UploadJob = 'BLOBBLOB'
set @ID = 10300
set @CashDesk = 232
Update [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus]
SET [Last run] = @LastRun2
Where [Upload job] = @UploadJob
AND Store = @ID
AND [CashDesk] = @CashDesk
I get the error message:
Server: Msg 8180, Level 16, State 1, Line 14
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 14
Line 1: Incorrect syntax near 'run'.
NOTICE!! The Script is run on another server, than the one it has to run the UPDATE statement on.. So it is a linked server!!
It is the "SET [Last run] = @LastRun2" that gives me trouble..
If I change it to
SET [Last run] = getdate()
It will work ...
Why?? I need to get it to work with the variable!! Can you help?
Kind regards,
Anders
March 4, 2009 at 6:29 am
Use this
DECLARE
@LastRun2 datetime,
@UploadJob varchar(50),
@ID int,
@CashDesk int
set @LastRun2 = getdate()
set @UploadJob = 'BLOBBLOB'
set @ID = 10300
set @CashDesk = 232
DECLARE @Query NVARCHAR(4000)
SET @Query = '
Update [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus]
SET [Last run] = ' + CAST(@LastRun2 AS NVARCHAR) + '
Where [Upload job] = ' + CAST(@UploadJob AS NVARCHAR) + '
AND Store = ' + CAST(@ID AS NVARCHAR) + '
AND [CashDesk] = ' + CAST(@CashDesk AS NVARCHAR)
EXEC @Query
Regards,
Nitin
March 4, 2009 at 7:27 am
Thanks for the reply,
It did not work...
Server: Msg 203, Level 16, State 2, Line 23
The name 'Update [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus]
SET [Last run] = mar 4 2009 3:17PM
Where [Upload job] = BLOBBLOB
AND Store = 10300
AND [CashDesk] = 232' is not a valid identifier.
Any clue??
March 4, 2009 at 7:30 am
Ohh... I got this error because of:
EXEC @Query
I changed it to
EXEC (@Query)
But then I got
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'run'.
March 4, 2009 at 7:43 am
Modify this line to add single quote (')
SET [Last run] = ' + CAST(@LastRun2 AS NVARCHAR) + '
change above to
SET [Last run] = ''' + CAST(@LastRun2 AS NVARCHAR) + '''
Regards,
Nitin
March 4, 2009 at 7:44 am
Same thing will be application to
Where [Upload job] = ' + CAST(@UploadJob AS NVARCHAR) + '
chnage above to
Where [Upload job] = ''' + CAST(@UploadJob AS NVARCHAR) + '''
Regards,
Nitin
March 5, 2009 at 3:08 am
Thanks, but sorry, it did not help:
SET @Query = 'Update [SQLSERVER\SQLINSTANCE].[Databasename].dbo.[Tablename]
SET [Last run] = ''' + CAST(@LastRun2 AS NVARCHAR) + '''
Where [Upload job] = ''' + CAST(@UploadJob AS NVARCHAR) + '''
AND Store = ' + CAST(@ID AS NVARCHAR) + '
AND [CashDesk] = ' + CAST(@CashDesk AS NVARCHAR)
EXEC (@Query)
This returns:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'run'.
March 5, 2009 at 3:41 am
Try to write whole query in single line (one last hope).
SET @Query = 'Update [SQLSERVER\SQLINSTANCE].[Databasename].dbo.[Tablename] SET [Last run] = ''' + CAST(@LastRun2 AS NVARCHAR) + ''' Where [Upload job] = ''' + CAST(@UploadJob AS NVARCHAR) + ''' AND Store = ' + CAST(@ID AS NVARCHAR) + ' AND [CashDesk] = ' + CAST(@CashDesk AS NVARCHAR)
Regards,
Nitin
March 5, 2009 at 3:58 am
Do you get a similar error if you run the corresponding simple select?
SELECT [Last run]
FROM [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus]
WHERE [Upload job] = @UploadJob
AND Store = @ID
AND [CashDesk] = @CashDesk
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2009 at 4:40 am
No, here it returns the date as it should.
2009-03-04 20:58:41.083
March 5, 2009 at 4:56 am
agh100 (3/5/2009)
No, here it returns the date as it should.2009-03-04 20:58:41.083
The usual suspects here are the backslash in the server name and the setting of quoted_identifiers... what does this return?
DECLARE
@LastRun2 datetime,
@UploadJob varchar(50),
@ID int,
@CashDesk int
SELECT @LastRun2 = GETDATE(), @UploadJob = 'BLOBBLOB', @ID = 10300, @CashDesk = 232
SELECT [Last run], @LastRun2, GETDATE()
FROM [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus]
WHERE [Upload job] = @UploadJob
AND Store = @ID
AND [CashDesk] = @CashDesk
BTW copy and paste this code, then change the server etc to your values, rather than modifying your existing code.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2009 at 5:11 am
Hi,
And as a reply to Nitin, no, this returned the same error.
I found another way to solve it.. But this is a bit stupid... The data from the @Lastrun2 is the value returned from a SELECT statement into another table on another server... If I instead of doing this:
SET [Last run] = @LastRun2
do it like this:
SET [Last run] = (SELECT LastRunField FROM Table WHERE CashDesk = @CashDesk and UploadJob = @UploadJob and Store = @ID)
It seems to be working... But the problem is, the @LastRun2 is data from a cursor, that contains the exact same data I get from the above mentioned statement. 🙁 And the performance will be reduced drastic if I have to make another select inside the cursor.
March 5, 2009 at 5:22 am
Interesting...have a look at this...
SELECT u.[Last run], t.LastRunField
FROM [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus] u
INNER JOIN [Table] t ON t.CashDesk = u.CashDesk and t.UploadJob = u.UploadJob and t.Store = u.Store
WHERE u.[Upload job] = @UploadJob
AND u.Store = @ID
AND u.[CashDesk] = @CashDesk
which can be quickly converted to an UPDATE...
UPDATE u SET [Last run] = t.LastRunField
FROM [SQLSERVER\SQLINSTANCE].[Database].dbo.[UploadStatus] u
INNER JOIN [Table] t ON t.CashDesk = u.CashDesk and t.UploadJob = u.UploadJob and t.Store = u.Store
WHERE u.[Upload job] = @UploadJob
AND u.Store = @ID
AND u.[CashDesk] = @CashDesk
Reckon you're ready to convert that dog-slow, unnecessarily complicated cursor-based operation to a fast, slick set-based operation?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply