James Wiseman

In the bad old days, when I wanted to shut down a remote Windows machine, I would log on through terminal services and then hit the ‘Turn Off’ button.

This carried the overhead of logging onto the computer manually, waiting for the settings to load and then clicking on the start men, etc, etc, etc.

Now we can just fire up cmd.exe and enter the following command:

shutdown -r -t 1 -m \\[computername]

In this instance we are specitying a restart of a remote machine, hence the -r and -m flags respectively.

For info, the spec for the command is below:

Usage: shutdown [-i | -l | -s | -r | -a] [-f] [-m \\computername] [-t xx]
                [-c "comment"] [-d up:xx:yy]

No args             Display this message (same as -?)
-i                  Display GUI interface, must be the first option
-l                  Log off (cannot be used with -m option)
-s                  Shutdown the computer
-r                  Shutdown and restart the computer
-a                  Abort a system shutdown
-m \\computername   Remote computer to shutdown/restart/abort
-t xx               Set timeout for shutdown to xx seconds
-c "comment"        Shutdown comment (maximum of 127 characters)
-f                  Forces running applications to close without warning
-d [u][p]:xx:yy     The reason code for the shutdown
                    u is the user code
                    p is a planned shutdown code
                    xx is the major reason code (positive integer less than 256)
                    yy is the minor reason code (positive integer less than 65536)

You could, of course type in shutdown /?

Does that title make any sense to you?

If not, can you bear with me? I’ll make it worth your while, I promise!

I’ve been looking at a bit of code that threw an error for the first time in nearly a year. This ‘bit’ of code is present on four different environments, and is run under some particularly strenuous and varied conditions.

Here is the gist of the code:

SELECT @var = ISNULL((SELECT col1 FROM SomeTable WHERE col2='SomeValue'), 0)

Those gurus (and some lesser mortals) among us will spot the potential problem immediately. For those, this sort of issue sticks out like sore thumb. But, for the rest of us it isn’t so inherently obvious.

Let me demonstrate. Imagine our ‘SomeTable’ has the following data:

We are assuming that statement we are passing to our ISNULL function will only ever return one result. However, in the above example it does not. You can try it yourself; simply paste the SQL below into your query window:

create table #temp (col1 int, col2 varchar(10));
insert into #temp values (1,'SomeValue');
insert into #temp values (2,'SomeValue');
select isnull((select col1 from #temp where col2 = 'SomeValue'),0)
drop table #temp

You should get the following message:

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Understanding the data is key to resolving this. You have assumed there will only be one value, so what are you prepared to accept? Possible solutions include:

select isnull((select max(col1) from #temp where col2 = 'SomeValue'),0)
select isnull((select min(col1) from #temp where col2 = 'SomeValue'),0)
select isnull((select top 1 col1 from #temp where col2 = 'SomeValue'),0)
select isnull((select distinct col1 from #temp where col2 = 'SomeValue'),0)

The first three solve the problem for you in all cases. In the fourth example, the use of the distinct clause will only work if your result set is identical. In fact, it will still break for our example.

But, by putting one of these fixes in place, we are maybe missing the whole point. Although defensive programming can help trap this sort of error, we are overlooking the fact that we have maybe stumbled upon some sort of data integrity error. I.e. do we have two records where we really shouldn’t?

Then the fix is something more fundamental, for example, are we missing a unique constraint somewhere? Here is our table definition with the constraint:

create table #temp (col1 int, col2 varchar(10), constraint UK_COL2 unique (COL2));

Running our original code block with this in place shows up the error when we try to insert a duplicate value in Col2:

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UK_COL2'.
Cannot insert duplicate key in object 'dbo.#temp'.

Sure, we still get the error, however, we get it earlier in our process, which is generally better. The is because we may not query these data for some time after it has been inserted, so it may be some time before the original error presented itself. In addition to this, the chances are that if you’ve coded a unique constraint then you’ll be more inclined to test for it.

So, a few things to think about. Principally, what do you want your subquery to return, and is your data correct?

I’ve been playing around with SSRS in SQL Server 2005 recently, and have been particularly encouraged by its potential as a central reporting tool. As part of this  I have been demonstrating how we can SSRS to produce reports from numerous different data sources and providers.

In this particular instance I have been testing its ability to produce reports from the output of an Oracle Package/Procedure and a SQL Server Stored Procedure.  Using the built-in .NET Client I was able to do this quite successfully with little fuss.

It was then brought to my attention that Microsoft are deprecating Oracle Client from ADO.NET 4. This means that, while the code may continue working for some considerable time, going forward it is a better strategy to use an alternative. So, I switched to using Oracle 11g Release 2 ODAC 11.2.0.1.2.

It’s worth noting that for those wanting to utilise an Oracle Stored procedure in SSRS, it must return its data in an OUT REF CURSOR. There are also a few other conditions which you must abide by.  For further info see: Tap into Oracle Databases with SQL Server Reporting Services.

Anyway, on to the problem…

So, as already intimated, all was fine and dandy, until I switched provider. I had set up the dataset on my report, entered the Stored Procedure name, and clicked ‘Ok’. Nothing happened; the IDE just hung.

At this point, I should probably reproduce the procedure in question:

PROCEDURE pck_mypackage.pr_myproc(L_CURSOR out SYS_REFCURSOR)
is
   BEGIN
     OPEN L_CURSOR FOR
       SELECT * FROM MyTable;
   END;

And here the connection string (missing the credentials).

Provider=OraOLEDB.Oracle.1;Data Source=DBSOURCE

Nothing shocking whatsoever, right?

For the first day or so, I took to killing Visual Studio, tweaking a few settings and then trying again. No-one in the websphere seemed to have encountered this; I was baffled. I even resorted to Window’s Process Monitor and trawling through the 1 million+ events it had logged.

So, I decided to confide in my old friend, the bullet-point list. I regaled it with the details of what was working, and what was not. Here it is, replete with the pertinent information:

What Works?

• The stored procedure executes correctly in PL/SQL Developer
• The ‘test connect’ in works fine in SSRS
• A query string of SELECT * FROM MyTable; with Command Type of ‘text’ produces the correct fields in the SSRS report.
• .NET Oracle Provider instead of Oracle OLE DB Provider

What Fails:

• If i change the Command Type to ‘Stored Procedure’ and enter ‘pr_myproc’, when I click ‘OK’ Visual Studio 2005 (service pack 2) simply hangs/crashes.

My attention was drawn to the last three words, “simply hangs/crashes.” It was then I noticed that Visual Studio was not actually hanging completely. I could still click the top-right window buttons; it just seemed to be waiting.

I confided in a friendly DBA, who suggested I looked at the Oracle sessions in an attempt to determine:

1. Whether or not a connection was actually being made, and,
2. What it was doing if the connection was successful

Something must have piqued his interest, because he decided to do it there for me himself.

“Ahhh,” he said, “you’re querying sys.obj$.” He then proceeded to explain to me why exactly this was an issue. The schema we were searching had substantially large data dictionary. This dictionary has to be queried in order to bring back the procedure parameters and the REFCURSOR output.  When looking up the information, the two providers (.NET and Oracle) used two different queries that had a dramatic relative difference in their performances.

Here is the one the Oracle Provider used, taking 10+ minutes:

select * from (select null  PROCEDURE_CATALOG
                      , owner PROCEDURE_SCHEMA
                      , object_name PROCEDURE_NAME
                      , decode (object_type, 'PROCEDURE', 2, 'FUNCTION',  3, 1) PROCEDURE_TYPE
                      , null PROCEDURE_DEFINITION
                      , null DESCRIPTION
                      , created DATE_CREATED
                      , last_ddl_time DATE_MODIFIED
              from all_objects where object_type in ('PROCEDURE','FUNCTION')
              union all
              select null PROCEDURE_CATALOG
                          , arg.owner PROCEDURE_SCHEMA
                          , arg.package_name||'.'||arg.object_name PROCEDURE_NAME
                          , decode(min(arg.position), 0, 3, 2) PROCEDURE_TYPE
                          , null PROCEDURE_DEFINITION
                          , decode(arg.overload, '', '', 'OVERLOAD') DESCRIPTION
                          , min(obj.created) DATE_CREATED
                          , max(obj.last_ddl_time) DATE_MODIFIED
              from all_objects obj, all_arguments arg
              where arg.package_name is not null
              and   arg.owner = obj.owner
              and   arg.object_id = obj.object_id
              group by arg.owner, arg.package_name, arg.object_name, arg.overload ) PROCEDURES
WHERE PROCEDURE_NAME = 'pck_my_package.pr_myproc' order by 2, 3

The second SQL Statement in the union is a particular killer. Here, it joins all_objects and all_arguments tables! I actually ran this query in PL/SQL developer, but gave up after it failed to finish in thirty minutes.

This a particular problem of those schemas with large data dictionaries. I tried this in a smaller one, further up our shipping chain, and it returned in an acceptable amount of time.

I stumbled across an article today regarding Google Code search and some of its amusing side effects.

http://www.kottke.org/06/10/google-code-search

This demonstrates why we should be careful of code and comments that we implement, especially if it is published externally, or available on some code search tool.

What I found most interesting from the above link was the employment of Google Code Search to search for specific programming errors. To this end, I have conjured an example, which can check for a specific sort of JavaScript error:

if (x=y) {

The issue being, of course, that this is an assignment, not a comparison. x will be assigned y’s value and the following block always being executed. The regex for identifying this looks something like:

if\s*\([\s\w]+\=[\s\w]+\)

For completeness, here is the tester code that I wrote. You’ll see from the commented out line, how I gradually built it up:

window.onload = function() {
    var arr = ["if(x=0)", "if (x=0)", "if (x =0)", "if (y = 0)", "if (z == 0)", "sif (z = 0)", "if (x >= 0)", "if (x)"];
    //var regexes = [/if /, /if\s*/, /if\s*\(/, /if\s*\(.+\=.+\)/, /if\s*\([\s\w]+\=[\s\w]+\)/];
    var regexes = [/if\s*\([\s\w]+\=[\s\w]+\)/];
    var i, j;

    for (i = 0; i < arr.length; i++) {
        for (j = 0; j < regexes.length; j++) {
            document.write("Find " + regexes[j] + "  in " + arr[i] + ":" + (arr[i].search(regexes[j])>0) + "
");
        }
    }
}

When this is fed into Google Code Search we can see some examples amongst the JS files that are already there:

http://www.google.com/codesearch?hl=en&lr=&q=if%5Cs*%5C%28%5B%5Cs%5Cw%5D%2B%5C%3D%5B%5Cs%5Cw%5D%2B%5C%29&sbtn=Search

It is, of course, slightly frivolous to do this in JavaScript in that there is ready-made tool for checking this (and other problems) in JSLint. The above, however could certainly be employed on C or C++ code. You can probably think of some others as well.