SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
MSSQL Objects query currently excludes CLR based objects

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
MSSQL Objects query currently excludes CLR based objects
Author Message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post MSSQL Objects query currently excludes CLR based objects Reply with quote
The objects query currently excludes objects based on the CLR like procedures and UDF's. The xtype's are as follows:

PC - CLR Stored Procedure
FT - CLR Table-Valued function
FS - CLR Scalar-Valued function

Can we get this added to the default query? Yes, I can add it to my own.


Current Objects query:

select
name, xtype, id
from
/*db.*/dbo.sysobjects
where
xtype in ('U','S','V','P','X','RF','FN','TF','IF')
and uid = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and xtype = 'P')

Suggested Objects Query:

select
name, xtype, id
from
/*db.*/dbo.sysobjects
where
xtype in ('U','S','V','P','X','RF','FN','TF','IF', 'PC', 'FT', 'FS')
and uid = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and xtype = 'P')
Fri Nov 30, 2007 4:28 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
It seems as though you are translating xtypes to types for internal handling. Changing the query didn't cause those new items to show in the proper place.

"EXEC " didn't cause the xtype PC items to show.
"Select dbo." didn't cause the xtype FS to show
"Select * dbo." didn't cause the xtype FT to show.
Fri Nov 30, 2007 4:36 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
Build: 3.0.24
Using: SSMS on SQL 2005.
Fri Nov 30, 2007 4:37 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6506

Post Reply with quote
Thanks. I will add it as an enhancement request
Fri Nov 30, 2007 4:48 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6506

Post Reply with quote
In the output, you need to map these types to type 'P' so that SQL Assistant can treat them as regular stored procedures. Right now, it simply doesn't know what to do with them and what kind of objects they are.
Fri Nov 30, 2007 4:52 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
Here is the query if it is of use to anyone. BTW, SSMS crashed after changing to this query and clicking ok.

QUERY:
select
name, xtype = Case xtype
when 'PC' then 'P'
when 'FT' then 'TF'
when 'FS' then 'FN'
else xtype
end, id
from
/*db.*/dbo.sysobjects
where
xtype in ('U','S','V','P','X','RF','FN','TF','IF', 'PC', 'FT', 'FS')
and uid = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and xtype = 'P')

ERROR:
SqlWb.exe - Application Error : The instruction at "0x081b6078" referenced memory at "0x081b6078". The memory could not be "read".
Fri Nov 30, 2007 5:01 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
Updated for 3.5.11 (support for synonyms)

select
name, xtype = Case xtype
when 'PC' then 'P'
when 'FT' then 'TF'
when 'FS' then 'FN'
else xtype
end, id
from
/*db.*/dbo.sysobjects
where
xtype in ('U','S','V','P','X','RF','FN','TF','IF', 'SN', 'PC', 'FT', 'FS')
and uid = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and xtype = 'P')
Wed May 21, 2008 9:33 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6506

Post Reply with quote
Thank you. I just tried it and it works fine for me.
Wed May 21, 2008 10:33 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6506

Post Reply with quote
Similar change for supporting CLR based objects will appear in the next SQL Assistant build.
Tue May 27, 2008 8:25 pm View user's profile Send private message
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.