Skip to Content.
Sympa Menu

grouper-users - RE: Grouper and Microsoft SQL Server/DDL

Subject: Grouper Users - Open Discussion List

List archive

RE: Grouper and Microsoft SQL Server/DDL


Chronological Thread 
  • From: Chris Hyzer <>
  • To: "Roy, Nicholas S" <>, "" <>
  • Subject: RE: Grouper and Microsoft SQL Server/DDL
  • Date: Thu, 25 Feb 2010 14:35:44 -0500
  • Accept-language: en-US
  • Acceptlanguage: en-US

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

Attachment: grouperDdl_20100225_13_11_01_687.sql
Description: grouperDdl_20100225_13_11_01_687.sql




Archive powered by MHonArc 2.6.16.

Top of Page