Owner (schema) names and delimiters

Unser SQL 2000, in a database where most objects are owned by DBO, but a few are under a different owner (RPT in this case), the non-DBO objects do not appear in the popup list.

If I alter the object selection query in Assistant to return owner.object or [owner].[object], an extra set of brackets is inserted, causing a syntax error on parse, as in [owner.object] or [[owner].[object]].

Is there a way to prevent Assistant from adding the brackets?

I think all you need is to comment out "and uid = ?" line in DB Options, in "Objects (MSSQL)" query. This will make it to return all objects, including objects from other schemas.

That works, but then objects are returned and inserted without the owner name.

Something like the following works well, but I must manually remove the leading/trailing brackets ('[dbo.table1]'). It appears that Assistant adds the brackets whenever any non-alphanumeric characters are present (the period in this case).

SELECT u.name+'.'+o.name, o.xtype, o.id
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u ON u.uid = o.uid
WHERE xtype in ('U','S','V','P','X','RF','FN','TF','IF')
AND NOT (o.name = 'dtproperties')
AND NOT (o.name like 'dt%' and xtype = 'P')

Please don't add schema name to the query. This makes SQL Assistant treat the returned value as an object name. Because that value contains non-alphanumeric characters, it encloses the entire value into brackets, which you don't want.

I will check with developers what can be done and get back to you with a solution

> it encloses the entire value into brackets, which you don't want.

A bit of a temporary solution (read "hack") is to insert brackets around the dot, so when Assistant adds the outer brackets, the syntax is OK. It makes for an ugly popup list however.

The easiest solution I can think of is an option to not automatically add bracket delimiters to object names. I would rather have to add them manually in the very few cases where they are required.

SELECT u.name+'].['+o.name, o.xtype, o.id
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u ON u.uid = o.uid
WHERE xtype in ('U','S','V','P','X','RF','FN','TF','IF')
AND NOT (o.name = 'dtproperties')
AND NOT (o.name like 'dt%' and xtype = 'P')