Grouper Users - Open Discussion List

["Roy, Nicholas S" <>]

Thanks Chris, that worked for me and I have the tables, indexes and views 
created now.

I'll try to get the API and UI working with this and let people know how it 
goes.

Nick

-----Original Message-----
From: Chris Hyzer 
[mailto:]
 
Sent: Thursday, February 25, 2010 1:36 PM
To: Roy, Nicholas S; 

Subject: RE: Grouper and Microsoft SQL Server/DDL

Try this one... (attached).

Btw, the reason the tests hang is because sql server handles transactions 
unlike any other DB we support.  This is unfortunate.

If you open a transaction, do work, then open another transaction, then do 
work, then rollback.  It rolls back both transactions.  Weird.

This article explains it

http://www.codeproject.com/KB/database/sqlservertransactions.aspx

I guess a solution would be to start a savepoint each time I open a 
transaction, and instead of rollback, rollback to that savepoint.  Maybe this 
is only for sql server.  Seems like a lot of work, we should discuss to see 
if we want to give it a try...  note that for normal operations we don't have 
a lot of nested transactions so it should be fine.  :)

Here is a jira about this:
https://bugs.internet2.edu/jira/browse/GRP-384


Chris

-----Original Message-----
From: Roy, Nicholas S 
[mailto:]
 
Sent: Thursday, February 25, 2010 9:34 AM
To: Chris Hyzer; 

Subject: RE: Grouper and Microsoft SQL Server/DDL

Thanks again Chris,

Should I break out the CREATE VIEW statements and execute them separately?  
When I run the DDL, I get a lot of this kind of error message:

Msg 111, Level 15, State 1, Line 968
'CREATE VIEW' must be the first statement in a query batch.

Nick

-----Original Message-----
From: Chris Hyzer 
[mailto:]
 
Sent: Thursday, February 25, 2010 2:27 AM
To: Roy, Nicholas S; 

Subject: RE: Grouper and Microsoft SQL Server/DDL

We've discussed supporting sql server, and now is a good time to try it.  :)

> like '||' for concatenation instead of '+', 

I believe I fixed that

> ORDER BY in views, 

Those weren't needed so I removed them

> and index keys that are larger than 900 bytes.

This one sounds very limiting...  If you are using mssql, I put the name and 
display name of indexed cols to 900 instead of 1024.  Also, for mssql I don't 
index audit or change log cols (will notice on UI screen to find audit logs).

>   The first two are somewhat overcome-able as far as I can tell, but the 
> last one is fairly tricky to fix and the only way I can think to make it 
> happy would be to rewrite the indexes > with included columns.

I did that with one of them, but the others have only one col, so not sure 
how they can be included, unless you have a way.

I tried to run the unit tests, and it made it to 300/1500, and it hung 
(waiting on locked query).  I tried to run queries against the DB, and they 
hung.  This is mssql express.  Anyways, if anyone can help out with this, 
that would be great.  Otherwise, if it doesn't pass the unit tests, its hard 
to say it is ok to use other than at your own risk... :)

Attached is my DDL, we can put the changes in 1.5.2

Regards,
Chris