SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Owner (schema) names and delimiters

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Owner (schema) names and delimiters
Author Message
mksql



Joined: 29 Dec 2006
Posts: 19
Country: United States

Post Owner (schema) names and delimiters Reply with quote
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?
Mon Feb 05, 2007 2:31 pm View user's profile Send private message Yahoo Messenger
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Mon Feb 05, 2007 4:46 pm View user's profile Send private message
mksql



Joined: 29 Dec 2006
Posts: 19
Country: United States

Post Reply with quote
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')
Mon Feb 05, 2007 4:57 pm View user's profile Send private message Yahoo Messenger
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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
Mon Feb 05, 2007 7:21 pm View user's profile Send private message
mksql



Joined: 29 Dec 2006
Posts: 19
Country: United States

Post Reply with quote
> 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')
Tue Feb 06, 2007 11:18 am View user's profile Send private message Yahoo Messenger
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.