CodeBetter.Com
Sign in | Join | Help
in Search
CodeBetter.Com
RSS 2.0 via Feedburner
           Do you Twitter? Follow us @CodeBetter

Raymond Lewallen

Professional Learner

May 2005 - Posts

  • Work for a Microsoft Certified Partner? Get discounts on your MCP exams!

    So here is some good news if you work for a Microsoft Certified Partner company. You can now get discounts on your MCP exams.

    Microsoft Corporation is offering Microsoft Certified Professional, Microsoft Partner Competency and Microsoft Business Solutions exams at significant discounts worldwide to candidates who are affiliated with Microsoft Certified Partner companies.

    The offer is available in two tiers, based on partner level. For companies at the Certified Partner level, Microsoft is providing exams at 25 percent off; up to 25 vouchers can be redeemer per Certified Partner organization. Gold Certified Partners get an even better break: 30 percent off exams for up to 100 vouchers per company. Candidates can find out if they qualify by going to https://partner.microsoft.com/global/examsavings and filling out a form. Exams retail for $125US, but vary by region. Vouchers are issued through MeasureUp, a Microsoft Certified Testing partner.

    Source: Microsoft Certified Professional Magazine

    Posted May 26 2005, 02:30 PM by Raymond Lewallen with 1 comment(s)
    Filed under:

  • Congrats to Carrie Underwood and a short discussion on an internet voting system

    Always great when a fellow Oklahoman does something wonderful.  Big shout out and congratulations to Carrie Underwood for winning this year's American Idol.

    To make this post a bit technical, American Idol had over 500 million votes this season.  Really not to difficult to handle from a software point of view, but can you imagine the hardware requirements?  They had their problems during the year, with constant busy phone lines and some other things.  Why not allow people to vote on the internet?  It would be much cost effective.  Sure, people could vote more than once.  You could help that out by setting/checking cookies and IP address.  Nothing keeping people from calling in more than once on the phone system.

    Just think, if you were owner/operator of that internet voting system and charged just 1 penny for each your system had to handle.  Overhead, I am guessing, would be less than that of a phone system.  Pretty simple to write from a software point of view.  So why not do it?
    Posted May 25 2005, 06:57 PM by Raymond Lewallen with 6 comment(s)
    Filed under:

  • The last tool you'll ever need to profile your managed code - ANTS Profiler from Red-Gate

    As so many of us know, Red-Gate has some really awesome database tools.  The Compare and Data Compare tools are totally awesome and I don’t know how I’d do many of the things I do without those tools.  Well, I know how I’d do them.  The same way I used to.  But never again will I work with databases between servers without the help of these tools.

    But I’m here to tell you about my experience with ANTS (Advanced .Net Tool Suite) Profiler.  I've been using this tool for a very short while and am absolutely thrilled with it.

    Now I’m not going to provide screenshots and all that jazz for ya.  I’m going to let you go to Red-Gate’s website and see that stuff for yourself.  Instead, I want to point out a couple of very awesome items.

    Talking about the performance profiler:

    1.  On the summary page, right when you take a snapshot of the the performance profile, it tells you the slowest 10 lines of code that have executed to that point.  Click on one, and down in the bottom it shows you the source of that line of code, points you to the line, and has a little “time to execute” bar graph out to the side.

    2.  Right underneath the slowest lines of code are the slowest methods to execute with the same type of information.

    3.  Under the all methods tab, you can see each method that has executed, what namespace in executed in, the time to execute, hit count, source file and each of those columns can be sorted and the namespace column can be filtered and sorted.  Click on a method name and in the pane below it shows you the source code.

    Talking about the memory profiler:

    1. On the summary page it shows the top 10 largest objects and the top 10 classes with the most live instances at the time the snapshot was taken.

    2. Under the all classes tab, you see the namespace, classname, module name, live count, live size in bytes, total number created and the total size.  All columns, again, are sortable.

    For me, ANTS is the easiest tool out there to use for profiling code.  Its much easier to use and contains much more information than what CLRProfiler provides (granted, CLRProfiler is free).  Its also uses about half the memory, when running, than CLRProfiler.

    Posted May 25 2005, 12:35 PM by Raymond Lewallen with 8 comment(s)
    Filed under: , ,

  • Dale Michalk on BizTalk 2004 for Developers

    On Monday, June 6th, Dale Michalk will be in Oklahoma City presenting on BizTalk server 2004.

    BizTalk Server 2004 is a server product from Microsoft that helps customers integrate systems, employees, and partners through business processes that are orchestrated in a flexible and adaptable manner.   In this talk we will cover the significant enhancements to the latest version of the product:  tight integration with VS.NET 2003 for development tasks, web service support to access existing web services as well as expose business processes as web services, an integrated rules engine to encode complex business logic declaratively, Business Activity Monitoring facilities to allow end users to use Office to monitor their business processes, and InfoPath support for building UI forms to easily work with the XML data that BizTalk Server 2004 manages.

    Dale Michalk is a Developer Evangelist for Microsoft.  His team covers large enterprise accounts in the Retail, Financial Services, Healthcare, and State and Local Government industries.  Before joining MS, he worked for startups and as a contractor for several of large companies.  His writings include co-authoring a book on a deep ASP.NET topic, Building ASP.NET Server Controls, published by Apress.  He was a Captain in the US Army and graduated from West Point.
    Posted May 24 2005, 05:45 AM by Raymond Lewallen with no comments
    Filed under:

  • I will be at DevCon 2005 in Dallas

    If anybody is going to be at DevCon 2005 in Dallas on June 16th, let me know.  I'd love to put some faces to some names.
    Posted May 20 2005, 05:55 AM by Raymond Lewallen with 1 comment(s)
    Filed under:

  • This is funny and I had to share it

    I've been away most of this week, kinda "vacationing".  I've been working in the mornings and taking the afternoons off.  I went fishing on Tuesday, golfing on Wednesday, saw Star Wars yesterday and will be fishing this afternoon.  Lots of fun, very relaxing and not only well deserved time off, but also very needed!

    I saw this on a signature in a post on a newsgroup this morning and wanted to share it.  I thought it was both original and funny:

    "Colt 45 - the original point and click interface"
    Posted May 20 2005, 05:45 AM by Raymond Lewallen with 1 comment(s)
    Filed under:

  • Updated random password or string generator in T-Sql for Sql Server

    Here is an updated version of my random string or password generator for T-Sql, made into a stored procedure with some other modifications.

    The stored procedure

    /***************************************************************************
    * Created By: Raymond Lewallen
    * Date:  05/16/2005
    * Purpose: Generate a random string of given length
    *
    * Comments: Everything is self-explanatory.  Right now
    *  max length is set to 100. So anything between 1
    *  and 100 will work for a length.
    *
    *   If you specify a @charactersToUse,
    *  the bit flags get ignored.
    *
    *   All spaces are stripped from the
    *  @charactersToUse.
    *
    *   Characters can repeat. Will be
    *  handled in a future version.
    ***************************************************************************/
    CREATE procedure dbo.GenerateRandomString (
     @useNumbers bit,
     @useLowerCase bit,
     @useUpperCase bit,
     @charactersToUse as varchar(100),
     @passwordLength as smallint,
     @password varchar(100) OUT
    )
    As
    Begin

    if @passwordLength <= 0
     raiserror('Cannot generate a random string of zero length.',16,1)

    declare @characters varchar(100)
    declare @count int

    set @characters = ''

    if @useNumbers = 1
    begin
     -- load up numbers 0 - 9
     set @count = 48
     while @count <=57
     begin
         set @characters = @characters + Cast(CHAR(@count) as char(1))
         set @count = @count + 1
     end
    end

    if @useLowerCase = 1
    begin
     -- load up uppercase letters A - Z
     set @count = 65
     while @count <=90
     begin
         set @characters = @characters + Cast(CHAR(@count) as char(1))
         set @count = @count + 1
     end
    end

    if @useUpperCase = 1
    begin
     -- load up lowercase letters a - z
     set @count = 97
     while @count <=122
     begin
         set @characters = @characters + Cast(CHAR(@count) as char(1))
         set @count = @count + 1
     end
    end

    set @count = 0
    set @password = ''

    -- If you specify a character set to use, the bit flags get ignored.
    if Len(@charactersToUse) > 0
    begin
     while charindex(@charactersToUse,' ') > 0
     begin
      set @charactersToUse = replace(@charactersToUse,' ','')
     end

     if Len(@charactersToUse) = 0
      raiserror('Cannot use an empty character set.',16,1)

     while @count <= @passwordLength
     begin
         set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
         set @count = @count + 1
     end
    end
    else
    begin
     while @count <= @passwordLength
     begin
         set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
         set @count = @count + 1
     end
    end

    end
    GO

    And here are some examples of how to use it.

    Use the bit flags - numbers only

    declare @a varchar(20)
    exec dbo.GenerateRandomString 1,0,0,null,20,@a OUT
    print @a

    Output: 44509955862560034316


    Use the bit flags - entire character set

    declare @a varchar(30)
    exec dbo.GenerateRandomString 1,1,1,null,30,@a OUT
    print @a

    Output: bl4Dut6ACchq460u65j2fkYoalrAAN


    Use a simple custom character set

    declare @a varchar(50)
    exec dbo.GenerateRandomString 1,1,1,'Ab',50,@a OUT
    print @a

    Output: bbbAAAbbbbAbbbAbbAAAbAbbbbbAbAbAbbAAAbAAbAbAbbAbbb


    Use a more complex character setdeclare @a varchar(50)
    exec dbo.GenerateRandomString 1,1,1,'ManagedCodeRocksMyWorld',50,@a OUT
    print @a

    Output: nednsndgcdgdnrgolRWRsgooRsdkMoCMRWedyglaknooodrdRd

    Posted May 17 2005, 05:21 AM by Raymond Lewallen with 22 comment(s)
    Filed under: ,

  • Sql Server script to automate the removal of logins

    Here is another one of those files that just floats around in a SqlScripts directory on my computer. I've found this script useful many times. This was written by Clinton Herring many moons ago. This script will remove a login from Sql Server. What's so fancy about the script? Well, the script takes care of going through each database and removing this login's permissions and object ownership (at least as much as possible) so you don't have to spend time searching and searching for this owner throughout all the databases on a server. You'll want to make note of the comments Clinton has left in the header, as there is important information on the behavior and actions taken when a scenario is encountered. One such scenario is: when a login you want to remove owns a database, that database ownership is given to 'sa'.

    sp_RemoveLogin

    Use master
    if exists (select *
                 from dbo.sysobjects
                where id = object_id(N'[dbo].[sp_RemoveLogin]')
                  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
       drop procedure [dbo].[sp_RemoveLogin]
    Go


    Create procedure sp_RemoveLogin
           @name sysname = null
    As
     
    /***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
    --Name        : sp_RemoveLogin        for SQL 7.0 & 2K
    --
    --Description : Attempts to remove a login from a SQL Server whether STD or NT.
    --
    --Parameters  : @name - the login to be removed, ie.e, <login> or
    --                      <domain>\<login>
    --
    --Comments    : Removing login from a SQL server can be a tedious, manual
    --              process checking for database access in each database, object
    --              ownership in each database, granted permissions (the login is
    --              the grantor), jobs & packages owned by the login. This procedure
    --              automates the process as much as possible. The following rules
    --              are applied when issues are encountered:
    --              1) If the login owns databases (as will occur when a restore
    --                 is done manually) the ownership is changed to sa.
    --              2) If the login is a user in a particlar db and owns objects,
    --                 then the proc attempts to reassign ownership to dbo. If an
    --                 object by the same name is already owned by dbo a message is
    --                 displayed and manual intervention is required.
    --              3) If this login as a user in a db has granted permissions then
    --                 those permissions are removed.
    --              4) Once object ownership is taken care and grants are dropped
    --                 then the user can be removed from the db.
    --              5) If the user is aliased it is dropped.
    --              6) This process continues for each db. Once all dbs are
    --                 processed if there were any objects that could not be handle 
    --                 without manual intervention a message is displayed to that
    --                 effect.
    --              7) If the login owns jobs or packages in msdb those are changed
    --                 to sa.
    --              8) Any open connections the login has are killed and finally the
    --                 login is removed from the SQL Server.
    --              9) If a session could not be killed a message is displayed to that
    --                 effect.
    --
    --Date        : 07/02/2001
    --Author      : Clinton Herring
    --
    --History     : 07/10/2002 Added code to change the db owner to sa if the
    --                         login owns databases.
    --
    /***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/

    -- Create a temp holding tables
    If (Select object_id('tempdb.dbo.#Parm')) > 0
       Exec ('Drop table #Parm')
    Create table #Parm(value int null)

    -- Declare variables   
    Declare @sid varbinary(85),
            @dbname sysname,
            @cmd varchar(4096),
            @spid int

    -- Check for master db
    If db_name() <> 'master'
       Begin
          Print 'This stored procedure must be run from the master database.'
          Return
       End

    -- Check for a null parameter
    IF @name is null
       Begin
          Print 'This stored procedure requires a valid login as a parameter.'
          Return
       End

    -- Check for logins not allowed to be dropped using this procedure
    IF @name in ('BUILTIN\Administrators', 'distributor_admin', 'sa', 'repl_publisher', 'repl_subscriber')
       Begin
          Print 'You may not drop the following logins using this stored procedure:'
          Print '   BUILTIN\Administrators, distributor_admin, sa, repl_publisher, repl_subscriber'
          Return
       End

    -- Check to see if the login exists.
    If exists (select * from master.dbo.syslogins where loginname = @name)
       Begin

          -- Display a message
          Print 'Attempting to find and drop ''' + @name + ''' from each database...'

          -- retrieve the sid of the login
          Set @sid = suser_sid(@name)

          -- Does this login own any databases
          If exists(select * from sysdatabases where sid = @sid)
             Begin
                Select @cmd = 'use master declare @cmd varchar(512) Exec sp_configure ''allow updates'',1 ' +
                              'Reconfigure with override Waitfor delay ''00:00:01'' ' +
                              'Print ''   Fixing db owner issues in master...'' ' +
                              'Select @cmd = ''Update sysdatabases set sid = 0x01 where sid = suser_sid(''''' + @name + ''''')'' ' +
                              'Exec (@cmd) Exec sp_configure ''allow updates'',0 Reconfigure with override '
                Exec (@cmd)
             End           

          -- If the login exists begin checking each database for this login as a users in
          -- that database.
          Select @dbname = min(name) from master.dbo.sysdatabases

          -- Loop through each database.
          While @dbname is not null
             Begin

                -- Here dynamic sql is required to use the 'Use command'.
                -- This loop checks for db and msdb ownership issues & granted permissions.
                -- Build a command.
                Select @cmd  = 'use ' + @dbname + ' declare @uid int, @cmd varchar(512), @name sysname ' +
                               'If exists (select * from sysusers where sid = suser_sid(''' + @name + ''') and isaliased = 0) ' +
                               'Begin Print ''   Processing db ' + @dbname + '...'' Select @uid = uid, @name = name from ' +
                               'sysusers where sid = suser_sid(''' + @name + ''') If exists (select * from sysobjects ' +
                               'where uid = 1 and name in (select name from sysobjects where uid = @uid)) ' +
                               'Begin Print ''   The following objects are owned by the user in database ' + @dbname + '.'' ' +
                               'Print ''   Objects with the same name owned by dbo already exist. Please decide '' ' +
                               'Print ''   what to do with these objects before attempting to drop this user.'' Print '''' ' +
                               'Select convert(varchar(50), name) ''name'', type from sysobjects where uid = @uid ' +
                               'Insert into #parm values(1) End ' +
                               'Else Begin Exec sp_configure ''allow updates'', 1 Reconfigure with override ' +
                               'waitfor delay ''00:00:01'' select @cmd = ''update sysobjects set uid = 1 where uid = '' ' +
                               '+ convert(varchar(5),@uid) + ' +
                               ''' Delete from syspermissions where grantor = '' + convert(varchar(5),@uid) ' +
                               'Print ''   Fixing object ownership issues in '' + db_name() + ''...'' Exec (@cmd) ' +
                               'Exec sp_configure ''allow updates'', 0 Reconfigure with override ' +
                               'Exec sp_revokedbaccess @name End Print '''' End ' +
                               'If exists(select * from sysusers where sid = suser_sid(''' + @name + ''') and isaliased = 1) ' +
                               'Begin Exec sp_dropalias ''' + @name + ''' Print '''' End'                     
                -- Execute the command
                Exec (@cmd)

                -- If the database is msdb then fix any job or package onwership issues.
                If @dbname = 'msdb' and
                   (exists(select * from msdb.dbo.sysjobs where owner_sid = @sid) or
                    exists(select * from msdb.dbo.sysdtspackages where owner_sid = @sid))
                   Begin
                      Select @cmd = 'use msdb declare @cmd varchar(512) ' +
                                    'Exec sp_configure ''allow updates'', 1 Reconfigure with override ' +
                                    'waitfor delay ''00:00:01'' select @cmd = ' +
                                    '''update sysdtspackages set owner = ''''sa'''', owner_sid = ' +
                                    '0x01 where owner_sid = suser_sid(''''' + @name + ''''') ' +
                                    'update sysjobs set owner_sid = 0x01 where owner_sid = suser_sid(''''' + @name+ ''''')'' ' +
                                    'Print ''   Fixing job &/or package ownership issues in msdb.'' ' +
                                    'Exec (@cmd) Exec sp_configure ''allow updates'', 0 Reconfigure with override '
                      Exec (@cmd)
                   End

                Select @dbname = min(name) from master.dbo.sysdatabases where name > @dbname
             End
         
          -- Did we have any issues that could not be resolved?
          If exists(select * from #parm where value = 1)
             Print 'Cannot drop the login at this time.'
          Else
             Begin
                Truncate table #parm

                -- Check for any connection by this login and attempt to kill them.
                If exists (Select * from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 and sid is not null)
                   Begin
                      Insert into #parm Select spid from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 and sid is not null
                      Select @spid = min(value) from #parm
                      While @spid is not null
                         Begin
                            Select @cmd = 'Kill ' + convert(varchar(5),@spid)
                            Exec (@cmd)
                            Select @spid = min(value) from #parm where value > @spid
                         End
                   End

                -- Not all kill commands succeed; check again
                If exists (Select * from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 and sid is not null)
                   Begin
                      Print 'Could not kill all active sessions for this login.'
                      Print 'Cannot drop the login at this time.'
                   End
                Else
                   Begin
                      If charindex('\', @name) > 0
                         Exec sp_revokelogin @name
                      Else
                         Exec sp_droplogin @name
                   End
             End

       End
    Else
       Begin
          Print 'The login ''' + @name + ''' does not exist on SQL Server ''' + @@servername + '''.'
       End


    GO

    Posted May 13 2005, 05:42 AM by Raymond Lewallen with 18 comment(s)
    Filed under: ,

  • Visual Studio 2005 IDE Automatically Produces Code to Implement IDisposable

    Here’s a neat little thing about Visual Studio 2005 IDE for Visual Basic.Net.  When you implement the IDisposable interface, the following code is automatically created for you.  This is different from other interfaces in that only the code stubs/shell (empty methods) are produced for other interfaces.  The auto-cdoe for IDisposable actually produces the code needed for the entire implementation except for the actual cleaning up of resources.  The private fields, Public Dispose Sub and Finalize come complete with no needed modifications so that nothing gets left out.  However, if you forget to clean up a managed or unmanaged resource in the Private Dispose(bool) method, that's your own fault :). Update: As Blair mentioned in the comments, for C# IDE you can right click on the IDisposable term and pick if you want to explicitly or implictly fill in the stubs for the interface.

    VB.Net 2.0 in 2005 IDE IDisposable implementation

     Public Class MyClass
        Implements IDisposable

        Private disposed As Boolean = False

        ' IDisposable
        Private Overloads Sub Dispose(ByVal disposing As Boolean)
            If Not Me.disposed Then
                If disposing Then
                    ' TODO: put code to dispose managed resources
                End If

                ' TODO: put code to free unmanaged resources here
            End If
            Me.disposed = True
        End Sub

    #Region " IDisposable Support "
        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Overloads Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub

        Protected Overrides Sub Finalize()
            ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Dispose(False)
            MyBase.Finalize()
        End Sub
    #End Region

    End Class

    Posted May 11 2005, 08:17 AM by Raymond Lewallen with 6 comment(s)
    Filed under:

  • Sql Server 2005: xp_cmdshell is turned off? what?

    So if you have played around much with Sql Server 2005, you may have noticed something peculiar.  Ok, several things that are peculiar, but I’m only going to mention one.  Have you tried to execute xp_cmdshell?  When you do, you’ll get the following message from the server:

    Msg: I don't think so you fool

    Msg 15501, Level 16, State 1, Procedure xp_cmdshell, Line 1
    This module has been marked OFF. Turn on 'xp_cmdshell' in order to be able to access the module."

    Whoa...never seen that one before.  Interesting.  I suppose I’ll just turn it ON, but it took me a little bit of searching to figure that one out too.  To turn it on, there is a “Surface Area Configuration” tool (sounds geometryish) in Microsoft Sql Server programs group that will let you configure this.  You can also execute the following code, which is easier.  I didn’t use the GUI tool, so I don’t have any screenshots or anything, but I read that is where you can configure it if you’re not t-sql kinda person.

    Turn it ON

    EXECUTE sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE sp_configure 'xp_cmdshell', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE sp_configure 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE
    GO

    And that will enable xp_cmdshell.  So it appears in order to further their efforts in making their server products more secure, Microsoft has graciously turned off xp_cmdshell by default.  Not a bad idea.  If you never use it, then you’ve got a little bit of added security by default.  I guess if you can figure out how to turn it on, then you know enough about Sql Server to know how to secure it too.

    Posted May 10 2005, 05:38 AM by Raymond Lewallen with 4 comment(s)
    Filed under:

  • SQL Server 2005 Integration Services: Lessons from Project REAL

    If you want to learn more about SSIS, check out this article:SQL Server 2005 Integration Services: Lessons from Project REAL. SSIS (Sql Server Integration Services) is the replacement for DTS packages.

    In Project REAL we are using large volumes of real data and scenarios from real companies to implement business intelligence systems using early releases of Microsoft SQL Server 2005. In the process, best practices are being developed and potential problems uncovered. This article reports some of the lessons learned while working on the data extraction, transformation, and loading (ETL) portion of the first phase of Project REAL.

    Some of the topics covered in this article include:

    • Upgrading from Sql Server 2000 DTS
    • Issues when using the upgrade/migration wizard
    • Manually upgrading DTS to SSIS
    • Lesssons learned developing SSIS packages
    • Implementation best practices
    • Package execution
    • Tips and tricks
    • And much more!

    The article may look lengthy (61 printed pages), but it is full of great content with tons of screenshots too.

    Posted May 10 2005, 05:20 AM by Raymond Lewallen with no comments
    Filed under:

  • The difference in TRUNCATE and DELETE in Sql Server

    I’ve answered this question many times, and answered it again this weekend.  What is the difference when doing a DELETE TableA instead of TRUNCATE TableA?  A common misconception is that they do the same thing.  Not so.  In fact, there are many differences between the two.

    DELETE is a logged operation on a per row basis.  This means that the deletion of each row gets logged and physically deleted.

    You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

    TRUNCATE is also a logged operation, but in a different way.  TRUNCATE logs the deallocation of the data pages in which the data exists.  The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse.  This is what makes TRUNCATE a faster operation to perform over DELETE.

    You cannot TRUNCATE a table that has any foreign key constraints.  You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

    TRUNCATE will reset any identity columns to the default seed value.  This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns.  After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1.  DELETE will not do this.  In the same scenario, if you DELETEd your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

    Posted May 09 2005, 05:45 AM by Raymond Lewallen with 51 comment(s)
    Filed under: ,

  • Performance Monitoring - JIT compilations

    Alrighty, another topic about performance monitoring, this time about viewing JIT information.  The previous performance monitoring topic was on garbage collection.

    When you compile your application with Visual Studio or the command line, you are compiling your C# or VB.Net code into Microsoft Intermediate Language, aka MSIL.  The reason for this is so that the IL code can be compiled to native code by the Just In Time (JIT) compiler on the fly, based on the machine the code is executing on.  This allows the JIT to create native code that is optimal for the machine the code is running on.  Take Omea Reader for example.  The MSIL code is identical on my machine and your machine, but when the application is executed, the JIT may produce different native code for your machine than it does for my machine.  The JIT is a very in-depth topic, and I’m not going to get into it very much here, but there are plenty of resources out there if you want to know more, such as:

    David Notario's WebLog - CLR and JIT Compiler. Specifically, you may want to start with his article The CLR x86 JIT, an overview.

    So I’m going to open up my performance monitor and add a few counters that can be found under the Performance Object heading of .Net CLR Jit.  I open up my Omea Reader so that it shows up in the box on the right so I can select the Omea Reader instance as the application I want to gather information from.  _Global_ is usually never a good choice, as it captures information about every CLR hosted application running on your machine.  I select the following counters:

    # of Methods Jitted – This counter displays the total number of methods compiled Just-In-Time (JIT) by the CLR JIT compiler since the start of the application. This counter does not include the pre-jitted methods.

    % Time in Jit – This counter displays the percentage of elapsed time spent in JIT compilation since the last JIT compilation phase. This counter is updated at the end of every JIT compilation phase. A JIT compilation phase is the phase when a method and its dependencies are being compiled.

    Standard Jit Failures – This counter displays the peak number of methods the JIT compiler has failed to JIT since the start of the application. This failure can occur if the IL cannot be verified or if there was an internal error in the JIT compiler.

    Total # of IL Bytes Jitted – This counter displays the total IL bytes jitted since the start of the application. This counter is exactly equivalent to the "# of IL Bytes Jitted" counter.

    Now I have everything selected that I want.  I click OK and stop the performance monitor and close Omea Reader.  Now I start the performance monitor and open Omea Reader because I want to know what all is going on and startup, which is where the vast majority of jitting takes place, although, by design, not all of it as we will see below.

    Here is what the report and graph look like after Omea Reader has finished loading up.

    Report showing JIT information at startup
    Graph showing JIT information at startup

    As you can see, over 2400 methods were JITted at startup, as inidicated by the blue line.  This isn’t all too common due to the number of methods that run in order to startup an application, which is also evident by the yellow line that indicates the percentage of time we have spent in the JIT.  You’ll probaby wonder “Why does the max say 147%?”  This is because of how the calculations are done, which is percentage of elapsed time spent in JIT compilation since the last JIT compilation phase.

    So now that everything is loaded up, Omea Reader is just sitting there doing nothing.  Now lets click on the update feeds and see what happens.

    Graph showing JIT information during initial update of feeds

    You can see from this graph that 413 methods had to be jitted in order for the update my feeds process to occur.  That’s one of the great things about the JIT.  It didn’t just go in and compile everything possible in the application in anticipation that we might use it.  It waited until we actually needed it before it went ahead and did the compilations.  You may think this is not so good of a thing, but it greatly increases initial load time for an application running on the .Net framework.  It only compiles what it knows it has to use, not what it thinks it is going to use.

    You can’t tell, but I’ve actually waited 10 minutes and now am going to click the update feed button again.  Since I have over 200 subscriptions, something out there is likely to have been updated, even though it is 10:30 PM for me.  Let’s click the update feed button again.

    Clicking the update feeds button again

    Viola!  Nothing happened!  See how cool that is?  All the methods and code that is required for the process of updating my feeds has already been compiled into machine level assembly language, therefore nothing had to be done other than an execution.  Now, everytime I update my feeds, its going to happen lickity-split because everything is already in place and ready to go for that happen.  Execution of machine level code only is all that will be required from now on.

    So, that is a quick and dirty look at how the JIT works and how to view its measurements.  Usually when you do something in an application after startup for the first time, you will see the number of methods increase slightly.  Do the same task again, the likelyhood of seeing any increments to the # of methods jitted counter is unlikely.

    Posted May 05 2005, 07:00 AM by Raymond Lewallen with 2 comment(s)
    Filed under:

  • Generate an Excel XLS spreadsheet from T-Sql in Sql Server

    Sometimes you find these really old files floating around on your harddrive and you forget that you ever downloaded them. Here is one such example. I have no idea where I got this or who to credit for its creation, but I've had it for awhile and came across it and thought it would be something nice to share with you, as I’m sure it is something of great help to many of you, especially if you are limited in your experience on creating DTS packages, which is another way, and preferred way under most circumstances, to get data from Sql to Excel.  This is a T-SQL script that uses the system stored procedures sp_OA* for creating and handling OLE objects, ADO, Jet and a linked server to create and populate an XLS file from a select statement.  By default, if the XLS file already exists, the result of the query will get appended to the worksheet.  You'll have to add some code to check for and delete the file before creating if that is your desired behavior.  Oh, and I used this a long time ago with some minor code changes and it worked fine, but this is the original script using the pubs database, so there are changes you’ll have to make, and they should be fairly obvious to you.


    Note: DTS packages are the preferred way of handling this type of data transfer, especially when scheduled, so don't be hasty to implement this without looking at a DTS solution first.  That being said, I'm sure there are those of you out there who can find usefulness out of this script.

    Create and Excel spreadsheet via T-Sql

    -- Create XLS script DAL - 04/24/2003

    --

    -- Designed for Agent scheduling, turn on "Append output for step history"

    --

    -- Search for %%% to find adjustable constants and other options

    --

    -- Uses OLE for ADO and OLE DB to create the XLS file if it does not exist

    --   Linked server requires the XLS to exist before creation

    -- Uses OLE ADO to Create the XLS Worksheet for use as a table by T-SQL

    -- Uses Linked Server to allow T-SQL access to XLS table

    -- Uses T-SQL to populate te XLS worksheet, very fast

    --

    PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '

    PRINT ''

    GO

     

    SET NOCOUNT ON

    DECLARE @Conn int -- ADO Connection object to create XLS

          , @hr int -- OLE return value

          , @src varchar(255) -- OLE Error Source

          , @desc varchar(255) -- OLE Error Description

          , @Path varchar(255) -- Drive or UNC path for XLS

          , @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM

          , @WKS_Created bit -- Whether the XLS Worksheet exists

          , @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)

          , @ServerName nvarchar(128) -- Linked Server name for XLS

          , @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation

          , @SQL varchar(8000) -- INSERT INTO XLS T-SQL

          , @Recs int -- Number of records added to XLS

          , @Log bit -- Whether to log process detail

     

    -- Init variables

    SELECT @Recs = 0

          -- %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail

          , @Log = 1

    -- %%% assign the UNC or path and name for the XLS file, requires Read/Write access

    --   must be accessable from server via SQL Server service account

    --   & SQL Server Agent service account, if scheduled

    SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'

    -- assign the ADO connection string for the XLS creation

    SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'

    -- %%% assign the Linked Server name for the XLS population

    SET @ServerName = 'EXCEL_TEST'

    -- %%% Rename Table as required, this will also be the XLS Worksheet name

    SET @WKS_Name = 'People'

    -- %%% Table creation DDL, uses Jet4 syntax,

    --   Text data type = varchar(255) when accessed from T-SQL

    SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'

    -- %%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB

    --   INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported

    --   Linked Server does not support SELECT INTO types

    SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone) '

    SET @SQL = @SQL+'SELECT au_id AS SSN'

    SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name'

    SET @SQL = @SQL+', phone AS Phone '

    SET @SQL = @SQL+'FROM pubs.dbo.authors'

     

    IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'

    -- Create the Conn object

    EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT

    IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers

    BEGIN

          -- Return OLE error

          EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

          SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc

          RETURN

    END

     

    IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'

    -- Set a the Conn object's ConnectionString property

    --   Work-around for error using a variable parameter on the Open method

    EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect

    IF @hr <> 0

    BEGIN

          -- Return OLE error

          EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

          SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc

          RETURN

    END

     

    IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'

    -- Call the Open method to create the XLS if it does not exist, can't use parameters

    EXEC @hr = sp_OAMethod @Conn, 'Open'

    IF @hr <> 0

    BEGIN

          -- Return OLE error

          EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

          SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc

          RETURN

    END

     

    -- %%% This section could be repeated for multiple Worksheets (Tables)

    IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'

    -- Call the Execute method to Create the work sheet with the @WKS_Name caption,

    --   which is also used as a Table reference in T-SQL

    -- Neat way to define column data types in Excel worksheet

    --   Sometimes converting to text is the only work-around for Excel's General

    --   Cell formatting, even though the Cell contains Text, Excel tries to format

    --   it in a "Smart" way, I have even had to use the single quote appended as the

    --   1st character in T-SQL to force Excel to leave it alone

    EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords

    -- 0x80040E14 for table exists in ADO

    IF @hr = 0x80040E14

          -- kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7

          OR @hr = 0x80042732

    BEGIN

          -- Trap these OLE Errors

          IF @hr = 0x80040E14

          BEGIN

                PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'

                SET @WKS_Created = 0

          END

          SET @hr = 0 -- ignore these errors

    END

    IF @hr <> 0

    BEGIN

          -- Return OLE error

          EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

          SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc

          RETURN

    END

     

    IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'

    -- Destroy the Conn object, +++ important to not leak memory +++

    EXEC @hr = sp_OADestroy @Conn

    IF @hr <> 0

    BEGIN

          -- Return OLE error

          EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

          SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc

          RETURN

    END

     

    -- Linked Server allows T-SQL to access the XLS worksheet (Table)

    --   This must be performed after the ADO stuff as the XLS must exist

    --   and contain the schema for the table, or worksheet

    IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)

    BEGIN

          IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'

          EXEC sp_addlinkedserver @server = @ServerName

                , @srvproduct = 'Microsoft Excel Workbook'

                , @provider = 'Microsoft.Jet.OLEDB.4.0'

                , @datasrc = @Path

                , @provstr = 'Excel 8.0'

          -- no login name or password are required to connect to the Jet4 ISAM linked server

          EXEC sp_addlinkedsrvlogin @ServerName, 'false'

    END

     

    -- Have to EXEC the SQL, otherwise the SQL is evaluated

    --   for the linked server before it exists

    EXEC (@SQL)

    PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'

     

    -- %%% Optional you may leave the Linked Server for other XLS operations

    --   Remember that the Linked Server will not create the XLS, so remove it

    --   When you are done with it, especially if you delete or move the file

    IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)

    BEGIN

          IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'

          EXEC sp_dropserver @ServerName, 'droplogins'

    END

    GO

     

    SET NOCOUNT OFF

    PRINT ''

    PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '

    GO

    Posted May 04 2005, 06:41 AM by Raymond Lewallen with 7 comment(s)
    Filed under: ,

  • TestDriven.Net version 1.1 has been released

    TestDriven.Net (formally NUnitAddIn) version 1.1 is now available.

    From Jamie’s blog, “I've updated the 'QualityTools' test runner so that is works with the latest VSTS 'Test Project's.  I've also improved the plug-in model for test runners.  You'll need this version if you want to use the version of MbUnit on Tigris with TD.NET.  Everything is there to build your own compatible MbUnit installer (using MSBuild + Wix).  You can find the new build here.“

    Posted May 02 2005, 06:24 AM by Raymond Lewallen with 2 comment(s)
    Filed under: , ,
More Posts Next page »

Our Sponsors

Free Tech Publications