Mike Diehl's WebLog

If you use a JET data provider, this method throws an exception saying that the underlying provider does not support this method.

Anyone got any ideas for a workaround? I suppose I could use good ol' DAO through Interop.

Some have wondered aloud if Rory is gay.

I wonder if he has ever worked at Starbucks? Although I have never met Rory, this guy reminds me of him (language warning for the cartoon, requires Flash and audio). Or perhaps this is what Rory would come up with if he were to start doing Flash animation on his Tablet PC.

Now I'm not so happy for people calling me “tall-brained”!

Great article here - the premise is that all the “Teach yourself [something] in 21 days“ books which have proliferated in the past 10 years are all bogus. In fact, most skills (not just programming) take about 10 years to master.

This new article by Paul Sheriff discusses a framework that allows both internal Intranet users to be authenticated to a web site using Windows Authentication, and external users (clients or customers or B2B or whatever) to be authenticated using Forms authentication.

Ever try searching support.microsoft.com or msdn.microsoft.com for something and not get what you want? I've usually ended up using Google to find kb articles or whatever, then getting a ton of non-MS related hits in the results.

Here is something I happened across today while reading Kent Sharkey's blog, a Microsoft-specify Google page: http://www.google.com/microsoft.html

Nice. In my Links toolbar now.

Mike

I was Joel's 'question monkey' today on his second webcast this week. We had some fun today, there were far less questions than Wednesday's presentation.

We covered buffer overruns, integer overflows, cross-site scripting, SQL injection attacks, and some Unicode issues.

It appeared to go over well again. We'll do it again in March.

Mike

I am the rabbit, with “nasty, big, pointy teeth!”

I'm not an ASP.NET expert by any means, so I post this question to the Blogsphere...

I have a student who is a Lotus Notes developer, who commonly uses javascript to open a “popup window” from the browser as a dialog box, have the user select some values, and post those results (somehow) back to the calling page. This is easily enough done in Windows Forms, but is there a corresponding paradigm in ASP.NET?

We've been talking about postbacks and linking to different pages, but never any parent/child or dialog type page to page relationships. I think the paradigm for ASP.NET applications is just different.

Is that right? Or can it be done (easily)?

Mike

As of SQL Server 2000, we have had the table variable to use in T-SQL for temporary tables or user defined functions. I like to use table variables wherever I can because I have observed performance problems with CURSORS and #TEMPTable objects.

Stephen Livingstone recently posted some T-SQL that used CURSOR objects to iterate through rows and call another stored procedure. Here is his code:

DECLARE MYCURSOR CURSOR FOR
  (SELECT p1, p2
   FROM  tbl_MyTable
   WHERE p4=123)

 DECLARE @p1 INT
 DECLARE @p2 INT
 DECLARE @p3 INT

 OPEN MYCURSOR
  FETCH NEXT FROM MYCURSOR INTO @p1, @p2, @p3

  WHILE @@FETCH_STATUS = 0
   BEGIN

    -call stored procedure
    exec UpdateTable @p1, @p2, @p3

    FETCH NEXT FROM MYCURSOR

   END

    CLOSE MYCURSOR
    DEALLOCATE MYCURSOR

Here is how I would prefer to write this, assuming that I had to call the internal stored procedure iteratively:

-- set up my in-memory table
declare @myTable table
   (rowId int identity(1,1),
   p1 int,
   p2 int,
   p3 int)

-- set up row counters and “cursor“ variables
declare @rowId int, @maxRowId int, @p1 int, @p2 int, @p3 int

-- insert the rows into the work table
insert into @myTable
   (p1, p2, p3)
select
   p1, p2, p3
from
   tbl_myTable
where
   p4 = 123

-- determine the start and end rows (RowId is an Identity column, auto-incrementing starting at 1)
-- note, I should check for Null here.
select
   @rowId = min(RowId),
   @MaxRowId = max(RowId)
from @myTable

-- here is the loop
while @RowId <= @MaxRowId begin
   -- fetch the values from the work table
   -- for the current row
   select
      @p1 = p1,
      @p2 = p2,
      @p3 = p3
   from
      @myTable
   where 
      RowId = @RowId

   -- call the stored proc for this row
   exec UpdateTable @p1, @p2, @p3
   -- increment the row counter
   set @RowId = @RowId + 1
end

-- end of procedure

I'm not exactly sure what SQL Server is doing behind the scenes, but here is my theory - @Table variables use a lot less of tempdb (or maybe none at all) than do #TEMPTable and CURSOR objects. I know that #tempTable objects are created in tempdb, and I think that CURSOR objects are created there too. In some performance issues I was troubleshooting with stored procedures, they were deadlocking on tempdb..sysobjects and tempdb..syscolumns, and when I re-wrote them to use @table variables instead of CURSORS, those problems went away.

There are some limitations of @table variables - they are completely locally scoped, they cannot be passed as parameters, and they cannot use INSERT INTO @table EXEC StoredProc. See here and here for some earlier discussion about @Table variable scope.

Mike

PS: My first preference, if this code is being called regularly, is to revisit the stored proc UpdateTable, and either create another stored proc that does the same thing but operates on a set of rows (passing in the criteria for P4, in the example the value for P4 was 123).

CREATE PROC dbo.UpdateTableSet @filter int
as

update tbl_myOthertable
  set ...
from tbl_MyOtherTable mot
join tbl_myTable mt on mot.MotId = mt.ModId
where pt.P4 = @filter

Update: I fixed a boundary bug in the While statement, as pointed out by  

Jon Blake Cusack, self-confessed geek, convinced his wife to name his son Jon Black Cusack Version 2.0