UPDATE a datetime field with a datetime from another database

Question

Post reply

UPDATE a datetime field with a datetime from another database

agh100

Ten Centuries

Points: 1139
More actions
March 4, 2009 at 5:51 am

#206632

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

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

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

agh100 Ten Centuries Points: 1139 More actions · Answer 2

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??

agh100 Ten Centuries Points: 1139 More actions · Answer 3

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'.

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 4

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

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 5

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

agh100 Ten Centuries Points: 1139 More actions · Answer 6

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'.

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 7

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

agh100 Ten Centuries Points: 1139 More actions · Answer 9

No, here it returns the date as it should.

2009-03-04 20:58:41.083

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

agh100 Ten Centuries Points: 1139 More actions · Answer 11

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

UPDATE a datetime field with a datetime from another database

Cookies on SQLServerCentral