dbo.sysobjects shows as a comment in Microsoft SQL Server Management Studio Express

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

There is a significant difference in the way a particular stored procedure works on my machine vs. my boss's machine (works on mine, fails on his).

In the stored procedure is the following line:

IF exists(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

In Microsoft SQL Server Management Studio Express on my boss's machine, sysobjects is displayed in green (that seems to be the color used for comments) and on my machine sysobjects is displayed in black. Another difference is that on my machine the N in N'[dbo].[t]' and N'IsUserTable' is black while on his it is red. I assume the color relates to a characteristic of what is being displayed but have no idea why the 2 procedures should be different.

Q: Any idea what might be going on? Why the difference in how the procedure is displayed?

Thank you.

Posted 11-Mar-11 6:48am

tom_delorenzo

Add a Solution

Comments

MarqW 12-Mar-11 10:39am

Sounds like he's got the default SQL Service Management Studio default colour scheme to me (that's what I get)

Are you sure you've not just changed the "Colours" option in Tools->Options?

There's a good case against reliance on syntax highlighting here
http://www.linusakesson.net/programming/syntaxhighlighting/index.php

Regardless of colour, does it say why his fails?

tom_delorenzo 15-Mar-11 14:20pm

Sorry for the long delay. To start, let me admit that I have nearly zero knowledge of SQL. Our SQL person left rather abruptly for personal reasons and I'm floundering a bit. I have discovered that I am using SQL 2005 while my boss has SQL 2008. The application goes something like this: the user starts typing and as each character is keyed in the list of returned data from the DataBase gets ever more refined. However, what we have been testing is simply a single character. Key in a zero and all entries with a zero in them get listed. Key in a 1 and nothing gets displayed although there are many entries with a 1 in the key. 2 thru 5 also produce no results while 6 thru 9 work fine. The letters 'B, C, F, L, P and U" work fine all other letters do nothing. I am completely mystified by this behavior.

walterhevedeich 22-Mar-11 13:15pm

Where is the user typing? in the application? Probably the application is designed to pull all data that begins with the character, as opposed to containing it. You may need to verify though on your tables because I may be wrong.

tom_delorenzo 22-Mar-11 13:42pm

In the application. Whatever the user keys in is sandwiched between 2 percent signs (mdstring like '%x%'). Theoretically, this should result in a match if the keyed character 'x' appears anywhere within the key. There is enough varied data that there should be matches for whatever single character gets keyed in. Of course, that is not what is happening and there is a good reason. Unfortunately, I happen to have no idea what that could possibly be. However, I keep returning to the "comments" in the stored procedure as the only noticible difference between a procedure that works and one that doesn't.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

joeller · Answer 1 · 2012-04-27T06:00:00

To answer you original question, green is the color of what used to be called system tables and are now called system views. In fact, the dbo.sysobjects table no longer exists and has been replaced by the sys.sysobjects table. (Though SQL Server still responds to this query. Still haven't figured out why. That is what I wasa looking for when I found this question.) Therefore in SQL Server 2005 this formula is showing up as black because it is not recognized as a system table or view. However in SQL Server 2008, it is again showing up as a green for system table. Why; only Microsoft knows.

Red is the color for all string literals that fall withing the single quotes. The N prefixing the string literal indicates that it should be interpreted as a unicode literal.

That sort of reddish violet is the color for functions that should also be the same. The string literals in your T-SQL script shown here are being used as parameters to the functions object_id() and OBJECTPROPERTY(). (Which paarameters are required to be passed as unicode) What this code is saying is Do a query to show me everything in the sysobjects tables whose objectid (represented as "id" in sysobjects), is equal to the object id of the object whose name is "t" and belongs to the schema "dbo" and whose property of "IsUserTable" = 1 (which means that the object you are looking for is a user designed table.) The Function keyword phrase If EXISTS() means that if the query returns one or more values (And it can't return more than one because id is a primary key), then do some action described beginning with the next line.
Personally I would find it shocking if you had a table whose name was "t", but I've learned to never underestimate the power of bad database design.

I really can't answer anything about your app without seeing the code and I can't believe that you haven't found a solution by now. But for anyone else reading this, remember string wildcards like the "%" sign are only valid when the query uses "LIKE" as the comparision operator in the "WHERE" clause.