Search code examples
t-sqlsybasesap-ase

Sybase: why does quoting the owner raise an error number 4701?

I have noticed that quoting the owner/schema in SQL such as the below will raise an error (4701)

SET QUOTED_IDENTIFIER ON Truncate Table "dbo".SYB_CO_COLOUR4 --Fails with: Error (4701) Cannot truncate table 'dbo".SYB_CO_COLOUR4', because this table does not exist in database 'CSCR1839'. 
SET QUOTED_IDENTIFIER ON Truncate Table "dbo"."SYB_CO_COLOUR4" --Fails as above
SET QUOTED_IDENTIFIER ON Truncate Table dbo."SYB_CO_COLOUR4" --Works
SET QUOTED_IDENTIFIER ON Truncate Table dbo.SYB_CO_COLOUR4 --Works
SET QUOTED_IDENTIFIER ON Truncate Table dbo."SYB CO COLOUR 5" --Works

By convention our app's SQL quotes the schema/owner and table (in case they have spaces in them) but I cannot get either of the top two statements to execute due to the owner/schema being quoted.

Is there a way round this?

Thanks

Solution

  • User names cannot be quoted identifiers in Sybase ASE, but table/column names can. In any case, best do not use quotes around identifiers, but square brackets, i.e. [dbo].[yourtablename]. This always works and does not require any options to be set.