Hello fellow programmers,

I need your help on a query a bit special.
I just started working as a ms sql dev, and I want to setup an t-sql unit testing bed.
So far, so good, I've found a framework that does the job correctly; tsqlunit [ http://tsqlunit.sourceforge.net/ ]

I need it to be ms-sql server 2000 compliant, which means that I cannot rely neither on try/catch blocks, not on the CLR functions or UT.

The direction is ok for adding UT to our db procedures (I was a bit surprised, I admit), but, as a lot of automation is already done on the DB schemas (extraction, analyze and commit into source safe) and several in-house tools are there to automate the extraction of the dev db to be put on a prod server, no tables nor stored procedures should be added into the application databases.

So, I've installed tsqlunit into a separated DB and modified it to take a named parameter @db to specify in which one he should do the tests.

The testing part in itself works ok.
What is causing me problem is the setup/teardown part.

When scanning the db to look after ut procedures, the framework does a

SET @hasSetup= (select count(*) from sysobjects where xtype='P' and name LIKE @setupLikeExpression )

which result in a query like this:

select count(*) from sysobjects where xtype='P' and name LIKE ut[_]testProc[_]SetUp

I have an @db variable (nvarchar(255)) that exists in that procedure, and I've tried several way to add it in that form, without success.

Then I reverted to a variable table

declare @r table(n int)
declare @chk nvarchar(4000)
set @chk=N'select count(*) from '+@db+'dbo.sysobjects where xtype=''P'' and name LIKE '+@setupLikeExpression
insert into @r
exec sp_executeSql @chk, N'@setupLikeExpression VARCHAR(255)', @setupLikeExpression
 set @hasSetup=(select n from @r)

But this result in

An INSERT EXEC statement cannot be nested.

because, as I understand it, this stored procedure is called from another stored procedure.
The flow is:

sp_runtests
    ¦--> sp_describe (where my error occurs)
    ¦--> sp_runTestSuite
... and some other...

The test case fetching causes no error, thanks to an static output cursor:

declare @cursor nvarchar(4000)
DECLARE @testcases_cursor CURSOR

set @cursor=N'SET @testcases_cursor = CURSOR STATIC FOR 
    select name from '+cast(@db as nvarchar)+'dbo.sysobjects 
        where xtype=''P'' and name LIKE  @testPrefix + ''%''
    OPEN @testcases_cursor'
exec sp_executeSql 
          @cursor, 
          N'@testPrefix varchar(10), 
          @testcases_cursor cursor output', @testPrefix, @testcases_cursor output

FETCH NEXT FROM @testcases_cursor INTO @testcase

I could always revert to something similar to check the setup/teardown, but this looks like a bit costly, and it made me curious about this. Is this even possible?

Does any sql server guru (John, Forrest ??) have an tip for me ?

I was able to resolve this after all.
I just had to use temp tables, rather than temp variables.

if(object_id('#r'))is not null
begin
    drop table #r  --Because this stored proc can be called recursively
end
create table #r (n int)

set @chk=N'
    truncate table #r
    insert into #r
        select count(*) 
        from '+cast(@db as nvarchar)+'dbo.sysobjects 
        where xtype=''P'' 
            and name LIKE @setupLikeExpression'
    exec    sp_executeSql @chk
            , N'@setupLikeExpression VARCHAR(255)'
            , @setupLikeExpression
    set @hasSetup=(select n from #r)

Why do you need to use a temp table? Your code is only trying to set a value into a variable?

SET @hasSetup= (select count(*) from sysobjects where xtype='P' and name LIKE @setupLikeExpression )

I forget which extended stored procedure lets you use variables this way. The following code doesn't work, but there's one that does - I'll see if I can find it.

Declare @t int, @sql nvarchar(max)
Set @sql = 'Select @t = Count(*) From master.dbo.sysProcesses'
Exec sp_executesql @sql
Select @t

PS - I tend not to use the internet much over the weekends. I update my blog if I have a good idea I need catharsis from, but otherwise I try to turn off the electronics and just relax. Party like it's 1799.

OK, got it. I thought it was sp_executesql, but I was using the wrong syntax.

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(id)
FROM master.dbo.sysobjects'
SET @ParmDefinition = N'@LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname


http://support.microsoft.com/kb/262499

Well, I ended up using a temp table, because I needed to make a count() into another database, but this database being passed as a parameter.
And as the strored procedures where nested, I received a message from SQL server that an "insert ... select" was not allowed in nested procedures int a temp variable.
So I tried a temp table, and as I've seen it working OK, I concentrated on the next job. Keeping in my mind that idea that it should be possible otherwise, but when job calls....

I did not thought of using an "select count()" into the sp_executesql with an OUTPUT parameter though.
Clever.. I'll modify th SP that way.

I see that you adresse master.dbo.sysProcesses in your 1st post.
Was it just pseudo code, or can you access every objects of other databases from the master db ?
I rather think not, but it just made me curious.

What made me curious too, was the "party like 1799" quote...
What was it like to party in 1799 ?? Or was it 1979 ?
And seriously, if you partied in 1799, you should drop the "newbie" of your nickname

One thing I know about parties in 1799 is they didn't have people sitting with their computers. There's a line in a Prince song where he says they're going to party like it's 1999, meaning New Year's Eve, but also the millinium. So I was joking about that, but saying I do a low tech versions on the weekends. I really like computer programming - it's a constant exorcise in logic, but it's also maddening. So putting the computer away, not having access to it like in the ancient times, helps me stay sane.

Anyway, on to the technical stuff.

I'd recommend sp_executesql with an output parameter rather than a temp table, because you'll be incurring less disc based IO that way, and also less memory. I'd probably suggest a table variable as #2 if necessary, but I think selecting the count should be enough. In fact, I think using a subquery with exists would do you better, but, that would be a slightly larger logic change.

Try running this query

Select * From master..sysprocesses

You can even run a dbcc inputbuffer(?) on each spid to see what it's doing. The sys processes virtual table has one row for every connection to the server. It will tell you how much Physical IO, CPU, and other resource usage per transaction. Good stuff to know, but just pseudo code for the example above.

Does SQL Server 2000 have sys.Tables? I've heard that querying sysObjects directly can in rare cases cause a spid to crash. I've never seen this myself, or heard a detailed description, but, that's what I'm told.

There's a line in a Prince song where he says they're going to party like it's 1999

Yeah, I remember it. It was on the "1999" album, and released in 1984, in fact.
D a m n (way to bypass the filter :/ ), that's old...

I really like computer programming - it's a constant exorcise in logic, but it's also maddening

So true... I've actually spent my sunday next to a lake, with my daughter. Was rather hot for the season, and she discovered mini boats. That was a lot of fun, she went totally made about it. It's a good way to let the dev stuff away.

Does SQL Server 2000 have sys.Tables?

Well, honestly, I don't know for sure, but I think that yes.
Our "global" dev server is an ms-sql 2000 instance, and the UT tests I ran on it went smoothly.
But it's true that I havent tried that "patch" fo tsqlunit.
For now, I've installed it on my office local instance and on my personal computer.

I've started sql server and t-sql 10 days ago, and so far so good, I can relate a lot to Oracle pl/sql or postgresql pl/pgsql.
I'm playing with the developer edition of sql server at home, and I have a sql 2005 enterprise edition running at my office workstation, but

Some oddities, like the "default" constraints stored apart of the tables, meaning that a drop table would leave default value objects in the db, but in overall, I'm doing good.
And I'm enjoying that new job in fact, a lot.
I work with funny peoples with good characters, and it's really a pleasure to be there every days.
It seems that I've impressed my "mentor" even, by the speed I grasp the concepts they have put in place.
Well see, I start the OLAP and datawarehouse next week. Never touched it until now...

Originally Posted by tripy

Some oddities, like the "default" constraints stored apart of the tables, meaning that a drop table would leave default value objects in the db, but in overall, I'm doing good.

Congratulations on what sounds like a new job?

Just to put your fears to rest, tho. Default constraints are stored apart from tables, as different objects of different types. Just as tables and indexes are treated differently. But if you drop a table, the indexes, constraints, and columns that belong to that table will all be removed for you.

Try running this code, which will prove it

Create Table DFTest ( T1 uniqueIdentifier Constraint TT1 Default ( NewID() ) )

GO

Select * From sysObjects Where Name In ( 'DFTest', 'TT1' )

GO

Drop Table DFTest

GO

Select * From sysObjects Where Name In ( 'DFTest', 'TT1' )

Yep, new job since 2 weeks.
And I do not need to test what you wrote, I trust you.

The fact is that the db is designed to be accessed through wrapper functions, and not directly from the .net application, and we need to know the name of indexes in the replication scripts.
Thus creating them with specific names.

At least, I believe it is the reason. I still not grasp everything, but I try hard.

OK, got it. I thought it was sp_executesql, but I was using the wrong syntax.

I've removed the temp table usage, and moved to an output parameter to sp_executeSql(), and it works like a charm...
I have to think more about that, it looks like a life savior.

    SET @setupLikeExpression=@testPrefix +  @suite + @LikeUnderscore  + 'SetUp'
    SET @chk=N'set @hasSetup=(select count(*) from '+cast(@db as nvarchar)+'dbo.sysobjects where xtype=''P'' and name LIKE @setupLikeExpression)'
    exec    sp_executeSql @chk
            , N'@setupLikeExpression VARCHAR(255), @hasSetup bit output'
            , @setupLikeExpression, @hasSetup output
    --print 'has setup?:'+cast(@hasSetup as varchar)

    SET @teardownLikeExpression=@testPrefix +  @suite + @LikeUnderscore  + 'TearDown'  
    SET @chk=N'set @hasTearDown=(select count(*) from '+cast(@db as nvarchar)+'dbo.sysobjects where xtype=''P'' and name LIKE @teardownLikeExpression)'
    exec    sp_executeSql @chk
            , N'@teardownLikeExpression VARCHAR(255), @hasTearDown bit output'
            , @teardownLikeExpression, @hasTearDown output
    --print 'has tear down?:'+cast(@hasTearDown as varchar)

Originally Posted by tripy

Yep, new job since 2 weeks.

Congratulations, Tripy! I hope it's challenging you, and that you're enjoying it?

Originally Posted by tripy

we need to know the name of indexes in the replication scripts.
Thus creating them with specific names.

If you say Create Table MyTable ( SomeID Int Default ( 12 ) ), you'll get a default constraint object there in your database. You can see it by querying sysObjects. The trouble is, it'll be given a name automatically, since all objects need a name. SQL will choose something horrible like DF_MyTable______AEC89D2 for the new constraint. You have no way of knowing beforehand, or afterwards unless you query the information schema.

You can't delete the column without first deleting the constraint, or the entire table the column lives in. And it's hard to delete the constraint without knowing it's name. This has always bothered me about Microsoft's SQL Server.

To get around this behavior, you have to use syntax like Create Table MyTable ( SomeID Int Constraint dfMyTable_SomeID Default ( 12 ) ), which is long hand and implementation specific, so hard to remember what order things come in. Or you can create the table, then add a constraint to it in a second batch.

Your company isn't the only one to be flummoxed by this behavior.

Congratulations, Tripy! I hope it's challenging you, and that you're enjoying it?

Thanks John.
I do enjoy that new job, a lot. I find it way more relaxing to work in a place where they just want it "to works well", after 1 1/2 year in an university where the level of abstraction was getting ridiculously overwhelming.
I really feel much better there.

Or you can create the table, then add a constraint to it in a second batch.

It's more or less what is done.
First, we design everything in Sybase PowerDesigner.
From there, the basic table script is generated, and pasted into a auto-generated template that will check that for each table defined, if it already exists, as long as the constraints and indexes.
This is done so because no direct access to the table is done, everything goes through a wrapper function (which is auto-generated too).

As such, a well determined constraint name was needed, to simplify the script generation. And it's the reason we give named constraints into the table creation definition.
In fact, the developers there have developed a couple of tools that inspect the systables of the