locked
DBPro - Incorrect syntax near "object_name". RRS feed

  • Question

  • I am trying to use Database Project 2010 to pull in all of our databases and I am getting an error with one of them.

    The database level setting for QUOTED_IDENTIFIER is set to OFF. However at the individual trigger session level, the QUOTED_IDENTIFIER is set to ON. When DBPro script the triggers from the database, it looks like this:

    CREATE TRIGGER "XXX_UTrig" ON tblXXX FOR UPDATE AS
    SET NOCOUNT ON

    IF UPDATE(XXX_ID)....

     

    The error I am getting is "Incorrect syntax near "XXX_UTrig" due to the double quote. If I set QUOTED_IDENTIFIER to ON in Database.sqlsettings, all the double quote errors went away.

    However I would not prefer to change the QUOTED_IDENTIFIER setting on the physical database if I can resolve it in database project. So how can I make DBPro retrieve each and every database settings at the object level and safe it in DBPro? I am still new to DBPro so I hope it is just my lack of profess with the tool. Thanks in advance for your help.

     

     

    Friday, October 22, 2010 1:27 AM

Answers

  • You can set the quoted_identifer option on individual objects. If you select the trigger in solution explorer, right click it and select properties. You should then see a property called quoted identifiers. You can set this to project default, on or off. Setting it to on for this object should solve your problem

     

    Anthony

    Friday, October 22, 2010 11:41 AM
  • Hello lkean,

    Thanks for your post.

    When you save objects or load a database that contains quoted identifiers if the SET QUOTED_IDENTIFER check box is cleared in the database properties, errors will appear.

    To work around this issue, besides Anthony’s great advice, you also have two options.

    1). First, you can change “XXX_UTrig” to [XXX_UTrig].

    2). Second, just as you mentioned, you can select the SET QUOTED_INDENTIFIER check box in the database property file.

    Hopeful it helps you. Let me know if you have any further concerns.

    Thanks,

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Monday, October 25, 2010 8:01 AM
    Moderator
  • It does make me wonder why DBPro does not import all the database settings for each object by default when we reverse engineer the DB into DBPro. It makes using DBPro quite laborious having to crosscheck each imported objects, am I just missing a setting while using the tool?

    The Import Database Schema and Settings has an explicit option to import these settings, by default this option is turned off.

     

    Another concern of mine is other database setting does not seem to be saved, only ANSI_NULLS and QUOTED_IDENTIFIER are available. Say ANSI_PADDING is set differently at the object level compared to the database. That setting is not reflected in DBPro. I understand ANSI_PADDING is going to be phased out per BOL, but it is still being used and we need a way to save that information.  Is there a way to save that information into DBPro at each individual object level?

    ASNI_NULLS and QUOTED_IDENTIFIER are the only two SET options that get persisted by SQL Server inside the meta data, all other SET options are session level settings, hence the reason they do not show up as such, but are options on the deployment engine.

     


    GertD @ www.DBProj.com
    Monday, October 25, 2010 11:55 PM

All replies

  • You can set the quoted_identifer option on individual objects. If you select the trigger in solution explorer, right click it and select properties. You should then see a property called quoted identifiers. You can set this to project default, on or off. Setting it to on for this object should solve your problem

     

    Anthony

    Friday, October 22, 2010 11:41 AM
  • Hello lkean,

    Thanks for your post.

    When you save objects or load a database that contains quoted identifiers if the SET QUOTED_IDENTIFER check box is cleared in the database properties, errors will appear.

    To work around this issue, besides Anthony’s great advice, you also have two options.

    1). First, you can change “XXX_UTrig” to [XXX_UTrig].

    2). Second, just as you mentioned, you can select the SET QUOTED_INDENTIFIER check box in the database property file.

    Hopeful it helps you. Let me know if you have any further concerns.

    Thanks,

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Monday, October 25, 2010 8:01 AM
    Moderator
  • Thank you both Anthony and Vicky for the post, the workaround allows me to build the project.

    I opted to set the QUOTED_IDENTIFIER check box at the individual object level in DBPro. Long story short, we inherited this DB so we do not want to change the QUOTED_IDENTIFIER setting at the database level to avoid extensive system testings. 

    It does make me wonder why DBPro does not import all the database settings for each object by default when we reverse engineer the DB into DBPro. It makes using DBPro quite laborious having to crosscheck each imported objects, am I just missing a setting while using the tool?

    Another concern of mine is other database setting does not seem to be saved, only ANSI_NULLS and QUOTED_IDENTIFIER are available. Say ANSI_PADDING is set differently at the object level compared to the database. That setting is not reflected in DBPro. I understand ANSI_PADDING is going to be phased out per BOL, but it is still being used and we need a way to save that information.  Is there a way to save that information into DBPro at each individual object level?

    It has been a great learning experience, thanks very much in advance for your help.

    Monday, October 25, 2010 6:03 PM
  • It does make me wonder why DBPro does not import all the database settings for each object by default when we reverse engineer the DB into DBPro. It makes using DBPro quite laborious having to crosscheck each imported objects, am I just missing a setting while using the tool?

    The Import Database Schema and Settings has an explicit option to import these settings, by default this option is turned off.

     

    Another concern of mine is other database setting does not seem to be saved, only ANSI_NULLS and QUOTED_IDENTIFIER are available. Say ANSI_PADDING is set differently at the object level compared to the database. That setting is not reflected in DBPro. I understand ANSI_PADDING is going to be phased out per BOL, but it is still being used and we need a way to save that information.  Is there a way to save that information into DBPro at each individual object level?

    ASNI_NULLS and QUOTED_IDENTIFIER are the only two SET options that get persisted by SQL Server inside the meta data, all other SET options are session level settings, hence the reason they do not show up as such, but are options on the deployment engine.

     


    GertD @ www.DBProj.com
    Monday, October 25, 2010 11:55 PM
  • Got it, thanks for the reply. I will have to test more about the other session settings to make sure there are not any impact with not saving the other options on table creation etc-etc, thanks again.
    Wednesday, October 27, 2010 9:22 PM