SLK database question

Topics: Developer Forum, Project Management Forum
Nov 14, 2007 at 5:17 PM
Edited Nov 14, 2007 at 5:18 PM
This question is regarding Solitaire example.
When I hit 'submit assignment' button (on the left side of screen) all learner response stored in 'InteractionItem' table of 'SharePointLearningKit' database (I kept default db name during slk installation).
But If I don't hit 'submit assignment' button and just hit check answer button it stores data somewhere other than 'interactionitem' table.
Could any one let me know what table these learner responses are stored when only 'check answers' button is hit.
Thanks
Coordinator
Nov 14, 2007 at 8:45 PM
Edited Nov 14, 2007 at 8:55 PM
I don't know off the top of my head, but when I want to spy on the SQL behavior of an application like SLK I use SQL Server Profiler. What you do is:

  • Install SQL Server 2005 Tools on your development machine.
  • Run SQL Server Profiler from the Start menu.
  • File -> New Trace. Set the Server Name to the name of the machine that is running SQL Server / SharePointLearningKit DB. Make sure you're logged onto a Windows account on your development machine that has administrator rights on the SLK/SQL Server machine.
  • Connect. Run.

Now you'll get a window open that is tracing out all calls to the SQL Server. It will look a little wierd, but when you do something on SLK you'll see a row that looks like:
RPC:Completed exec spexecutesql N'SET XACTABORT...

If you click on that row, in the lower window pane you'll see the actual SQL statement that was executed, it will look something like:

exec spexecutesql N'SET XACTABORT ON
DECLARE @LastRowCount int
DECLARE @LastError int
IF dbo.LearnerAssignmentListForLearners$Security(@UserKey) = 0
BEGIN
RAISERROR(''LSERROR'',16,3)
RETURN
END
IF @@ERROR <> 0
RETURN
SELECT QueryTable.IsFinal
FROM LearnerAssignmentListForLearners(@UserKey) QueryTable
WHERE (QueryTable.AssignmentSPWebGuid = @V1)
AND (QueryTable.AssignmentStartDate <= @V2)
AND (QueryTable.IsFinal <> @V3 OR QueryTable.IsFinal IS NULL)
AND (QueryTable.LearnerAssignmentState < @V4)
IF @@ERROR <> 0
BEGIN
RETURN
END
IF dbo.LearnerAssignmentListForLearners$Security(@UserKey) = 0

And if you're a T-SQL whiz, you can use this SQL code to reverse engineer what table was touched and what the row values were set to...

Ah, one interesting twist with SLK -- it turns out that when SLK was developed, they didn't submit a lot of "raw SQL" to the database. They also didn't use Stored Procedures. Instead they use a lot of custom Functions. This means that when you see LearnerAssignmentListForLearners above, you won't find a corresponding database table of that name, but if you use SQL Server Management Studio to look under SharePointLearningKit -> Programmability -> Functions -> Scalar-Valued Functions you'll find a function named LearnerAssignmentListForLearners$Security. If you look at the contents of that Function, you'll find the actual SQL called -- this is just another layer of indirection that was used to add row-level security to the SLK database (I may be over-simplifying here but...)

OK. That was complex. Another simpler way to answer your question that involves a little more effort is to use manual database table diffing. Frankly, this is what I've been using while debugging some SLK database work lately. To do this, install SQL Server 2005 Tools as above but start SQL Server Management Studio instead of SQL Server Profiler and attach it to the database. Use Management Studio to browse all the tables. Select a table you think contains the data (the names are pretty good so you can generally guess quickly), right click on the table and select "Open Table". This will give you a grid view of all the rows in that table. Perform your action (check answers) and then right click on the grid and choose "Execute SQL" to refresh the grid contents. If you guessed the right table, you'll see the Table Rows data change. Note that you can also edit the row contents from this view. I frequently do this to reset the SLK state as I run through tests & the debugger.