February 9, 2010 at 2:51 pm
Lynn Pettis (2/9/2010)
J-F Bergeron (2/9/2010)
Lynn Pettis (2/9/2010)
I guess it's time for me to start a personal black-listEhh, I wish I would've seen that comment before I posted the solution,
Why be so rude anyway, I mean, people are just trying to help!
Well, we could see what happens to his future requests for help if the "gurus" stay away. 😉
Won't matter to him. This is a guy so oblivious that he has actually recommended using cursors instead of set-based solutions in more than one thread. He's proud of the fact that he uses lots of cursors in his SQL code. He is now and always has been a lost cause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2010 at 2:53 pm
CirquedeSQLeil (2/9/2010)
I think the only method to exclude those two tables (sysdiagrams and dtproperties) is to hard code them into the query. Anybody else have any other ideas?
That would certainly be the simplest.
I suggested to him that he contact Microsoft and find out how they actually defined the tree view in the object explorer. That's the only guaranteed way to get the results he's looking for.
Never mind that they probably won't answer. Never mind that he's almost certainly solving a non-existent problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2010 at 2:59 pm
GSquared (2/9/2010)
CirquedeSQLeil (2/9/2010)
I think the only method to exclude those two tables (sysdiagrams and dtproperties) is to hard code them into the query. Anybody else have any other ideas?That would certainly be the simplest.
I suggested to him that he contact Microsoft and find out how they actually defined the tree view in the object explorer. That's the only guaranteed way to get the results he's looking for.
Never mind that they probably won't answer. Never mind that he's almost certainly solving a non-existent problem.
They use SMO and the extended property microsoft_database_tools_support. That is fine for SMO, but they have no tSQL equivalent. My sys.extended_properties tables don't all have this extended property - thus I continue to get the tables in my queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 3:12 pm
Not sure I want to post this in that thread.
From http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic13791.aspx, Kalen Delaney brings up the stored proc that can changed the ObjectProperty 'IsMSShipped' to a value of 1. This is only another hack, but you could simply change the objectproperty of those two tables to a 1. Then they would be excluded when/ if you use objectproperty(object_id,'ismsshipped') = 0 in your script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 3:14 pm
CirquedeSQLeil (2/9/2010)
Not sure I want to post this in that thread.From http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic13791.aspx, Kalen Delaney brings up the stored proc that can changed the ObjectProperty 'IsMSShipped' to a value of 1. This is only another hack, but you could simply change the objectproperty of those two tables to a 1. Then they would be excluded when/ if you use
objectproperty(object_id,'ismsshipped') = 0in your script.
I don't like that option.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 9, 2010 at 3:15 pm
Roy,
Apologies on behalf of the community. I have removed the comment, which was inappropriate. I did not mail the guy since I don't think it would do any good. He strikes me as someone that won't get a clue.
February 9, 2010 at 3:19 pm
Alvin Ramard (2/9/2010)
CirquedeSQLeil (2/9/2010)
Not sure I want to post this in that thread.From http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic13791.aspx, Kalen Delaney brings up the stored proc that can changed the ObjectProperty 'IsMSShipped' to a value of 1. This is only another hack, but you could simply change the objectproperty of those two tables to a 1. Then they would be excluded when/ if you use
objectproperty(object_id,'ismsshipped') = 0in your script.I don't like that option.
I should clarify - She didn't suggest using that method either. She merely mentioned that there is a proc that could do that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 3:24 pm
Steve Jones - Editor (2/9/2010)
Roy,Apologies on behalf of the community. I have removed the comment, which was inappropriate. I did not mail the guy since I don't think it would do any good. He strikes me as someone that won't get a clue.
Would you like me to remove it from my quoted response?
With as many people who tend to go back and delete their comments, I felt it necessary should he do that as well.
February 9, 2010 at 3:28 pm
Didn't see that, but please do.
I was tempted to leave it. I'm still waiting for someone to go for an interview and have one of these comments come up. Especially if the interviewer is someone that saw the thread. However I think it goes against the grain of keeping a civil, professional site.
February 9, 2010 at 3:35 pm
Steve Jones - Editor (2/9/2010)
Didn't see that, but please do.I was tempted to leave it. I'm still waiting for someone to go for an interview and have one of these comments come up. Especially if the interviewer is someone that saw the thread. However I think it goes against the grain of keeping a civil, professional site.
Done. Agree with keeping things civil and professional.
Just to be sure, did I ever cross the line? Don't mind being admonished if I did.
February 9, 2010 at 3:44 pm
Lynn - no.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 3:47 pm
CirquedeSQLeil (2/9/2010)
Lynn - no.
I second that. As far as I'm concerened, you did not even come close to that line.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 9, 2010 at 3:50 pm
Nope, no crossing the line. Polite statement and I agreed with it.
February 9, 2010 at 4:35 pm
CirquedeSQLeil (2/9/2010)
Roy Ernest (2/9/2010)
If you use is_ms_shipped = 0, does it not exclude those tables?Those two tables, no. They are tweener tables. They were technically shipped with SQL Server, but not truly activated until a user creates a diagram. They are considered User tables by MS.
Whether they show up in a database is also a bit inconsistent. I have them showing up in those queries whether I have a diagram or not, and sometimes they don't show up at all. In this scenario it has to do with the extended properties - but I see nothing that correlates.
Those tables do not show up until you expand the "Database Diagrams" and select to install the necessary components (or some such dialog). This installs those tables, not the presence of any diagrams.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 9, 2010 at 4:36 pm
CirquedeSQLeil (2/9/2010)[hrI found a few databases that still show dtproperties and sysdiagrams.
Still looking for an alternative answer.
probably because those tables were scripted out without the extended properties, then when run it built them without the properties...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 11,281 through 11,295 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply