SQLTeam.com | Weblogs | Forums

Automate a UPDATE statement (*.vbs or *.sql and SQLCMD)


#1

I can use the following query in SSMS fine:

use Database
Update Table
SET LRDT ='12/03/2035 14:00:00'

I need to automate this; either in a *.vbs or a *.sql which get's called via SQLCMD. SQLCMD errors if I try and run this saying the syntax for the SET is incorrect:

Msg 1934, Level 16, State 1, Server XXXXXXXX, Line 2
UPDATE failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or filtered indexes and/or query notificatio
ns and/or XML data type methods and/or spatial index operations.


#2

why don't you use sqlagent to automate this?


#3

Because it's part of a bigger deployment script which get's run at an ad-hoc time...

I have sorted the above issue now using SET QUOTED_IDENTIFIER ON


#4

Pls note that your date format is ambiguous and d/m/y will depend on Server locale and the settings (such as LANGUAGE) of the currently connected user.

'yyyymmdd' will be treated as unambiguous regardless of environment.