Edwin Daniel's profileMicrosoft technologies a...BlogLists Tools Help

Blog


    September 20

    ASP.NET 2.0 Poofiles and Login Controls - Introduction

    I will be doing a presentation for MSDN Day for Microsoft Singapore on ASP.NET 2.0 Profiles and Login Controls. For this, I will be writing a series of articles that focuses more on these technologies in this blog.
     
    Every web developer will face the fact that enterprise web applications need a means to manage users and sometimes visitors.  The solution should be able to track user preferences across multiple site visits. These web applications could be forums, e-commerce sites, portals, etc.  Creating a means to manage users is a very challenging task for any web developer.  I have had my shares of experiences that trace back from ASP 3.0 (classic), ASP.NET 1.0 and ASP.NET 1.1. You need a persistent storage media that is easy to manage, a data access layer that you use to interact with this persistent storage, and means to integrate the two to allow developers to incorporate this feature in their web applications.  Developers think that this is just about the technology on how to manage user preferences and profiles.  But from a business perspective, it provides site visitors a more personal experience which will enhance their interactivity with the application, thereby generating more traffic which sometimes equate to revenue. ASP.NET 2.0  introduces a new feature called Profiles that makes this task a lot easy to accomplish for web developers, making them more productive in building enterprise web applications.
    September 13

    Restoring database backups on the wrong database platform

    Somebody sent me a backup which I need to test.  There was only one SQL Server 2005 instance in our environment and the rest are all running SQL Server 2000.  I was doing a restore on my notebook (which has both SQL Server 2000 and SQL Server 2005 instances running) to test the backup and to my surprise, an error was thrown
     
    Microsoft SQL-DMO (ODBC SQLState: 42000)
    Error 3169: The backed-up database has an on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
    RESTORE FILELIST is terminating abnormally
     This just tells me that I am trying to restore a backup created by SQL Server 2005 on a SQL Server 2000 instance.  Which concludes that SQL Server 2005 backups cannot be restored on SQL Server 2000 instances. The reverse - restoring a SQL Server 2000 backup on a SQL Server 2005 instance - works as SQL Server 2005 upgrades the binaries required to run this database.

    Scripting Objects in SQL Server 2005

    I needed to have a copy of the database schema between the development server and the production server.  The problem is, I do not simply want to do a backup-restore option as it would include critical data stored in the database.  Good thing there is this option to generate a script in SQL Server 2005.  You use the Generate Scripts Wizard in SQL Server 2005 to generate scripts that will re-create the database objects inside the database.  The problem with this tool is that, unlike in SQL Server 2000,  you do not have the DROP and CREATE statements in a single script.  This approach by SQL Server 2000 checks whether the object exists and if it does, it will first drop the object before creating it.  Check out the scripts below to demonstrate my point.
     
    In SQL Server 2000,
     
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Orders]
    GO
    CREATE TABLE [dbo].[Orders] (
     [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
     [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...
     
    IN SQL Server 2005
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Orders](
     [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
     [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...
     
    I was looking for a way to resolve this when I chanced upon Scriptio from SQLTeam.com. It's a Windows Forms-based application created in .NET 2.0 using SQL SMO.  Although it is still under development, it looks at scripting the CREATE and DROP statements at the same time.  You can install the application from http://www.sqlteam.com/publish/scriptio/ which is a ClickOnce application. You can find more details about the application from http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx as even the source code was made available
    September 11

    Assign permissions to tables - the LazyDBA way

    A friend of mine asked me to create a SQL Server account that will be used to monitor user and system tables on all databases in a SQL Server instance.  He said that the account only needs SELECT permissions on user and system tables as they will evaluate what their monitoring software can do for their systems.  Future permissions will be given to this account once they see how beneficial it is to support their infrastructure. Being a generous guy as I am, I volunteered to do it without realizing how big the task is.  Besides, it's just a matter of a few mouse clicks in SQL Server Enterprise Manager.  To my surprise, they will have to do it on an instance with 80+ databases, most of which have more than 200+ user tables.  So much for being a nice guy.  But since I already said yes, I have no choice.  Thanks to Alex Turner of SQLServerCentral.com, I found a script that will do the trick.  Of course, I have to modify the script to suit my friend's requirement.
     
    Here's the script I created to 1) create a SQL Server account 2) give database-level permission to the account and 3) give SELECT permissions to all user and system tables in each database in the instance. Again, I just modified the script from Alex Turner so most of the credit still goes to him.
     
     
    USE master
    GO
    --creates the user account used for monitoring using the master database as default database
    if not exists (select * from master.dbo.syslogins where loginname = N'account')
     EXEC sp_addlogin 'account',
    'password', 'master'
    PRINT 'Account created'
    --=========================================================================
    SET NOCOUNT ON
    -- Get the name of all databases - do not include Northwind and Pubs
    DECLARE @strSQL NVARCHAR(100)  -- used for generating dynamic SQL statements
    DECLARE @databaseName NVARCHAR(128) -- used as variable to store database names
    DECLARE dbCursor CURSOR FOR  -- used for cursor allocation
     SELECT name FROM master.dbo.sysdatabases where NOT dbid IN (5,6) --does not include the Northwind and Pubs databases
    OPEN dbCursor
    FETCH NEXT FROM dbCursor INTO @databaseName
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    PRINT 'Setting up permissions for
    '+ @databaseName 
      DECLARE
      @tableViewName varchar(200),
      @cursor nvarchar(255)
      
      --Grant user access to database
      SET @strSQL ='USE ' + @databaseName + ' EXEC sp_grantdbaccess ''' + 'itmsql''' + ',''' + 'itmsql''' -- change this value should you decide to change the account used for monitoring
      EXEC sp_executesql @strSQL
      
      -- Tables/Views - uses the sysobjects table to extract views, user and system tables
      
      select @cursor=('DECLARE TabCursor CURSOR FAST_FORWARD GLOBAL FOR ' +
      'SELECT name FROM
    '+@databaseName+'.dbo.sysobjects WHERE xtype=''S'' OR xtype=''U''')
      EXEC sp_executesql @cursor
      OPEN TabCursor
      FETCH NEXT FROM TabCursor INTO @tableViewName
      WHILE @@FETCH_STATUS = 0
       BEGIN
        EXEC ('USE
    '+@databaseName+' GRANT SELECT ON [' + @tableViewName + '] TO itmsql') -- change this value should you decide to change the account used for monitoring
        PRINT 'SELECT permission granted to itmsql on table/view ' + @tableViewName
        FETCH NEXT FROM TabCursor INTO @tableViewName
       END
      CLOSE TabCursor
      DEALLOCATE TabCursor
     PRINT 'Assigning permissions done for database: ' + @databaseName
       PRINT '================================================='
     FETCH NEXT FROM dbCursor INTO @databaseName
    END
       CLOSE dbCursor
       DEALLOCATE dbCursor
    GO
    PRINT 'Granting permission tasks done'
    September 06

    TASKLIST for Windows XP Professional/Server 2003

    I was looking at ways to identify which SQL Server instance was taking much of my server memory resource.  My server is running on Windows Server 2003 (and good thing I am using Windows XP Professional as I am running SQL Server instances on my notebook for simulations)  and I normally use the Task Manager to identify the sqlservr.exe process.  The problem is that this will not show you the instance name of the SQL Server service running.  This is where the TASKLIST command becomes veyr helpful.  This command only ships with Windows XP Professional and Windows Server 2003.  It is a command-line tool which displays a list of application(s) and associated tasks/processescurrently running on either or remote.  Similar to any command-line tool, you can use the /? switch for the list of switches and options to use.  In my case, I use the /svc (displays services in each process) and /fi (displays a set of tasks that match a given criteria specified by the filter) switches.  An example of a command I run is similar to this:
     
    tasklist /svc /fi "IMAGENAME eq sqlservr.exe"
    This will list all services where IMAGENAME is equal to sqlservr.exe, the runtime for SQL Server.  This returns the instance name together with the process ID (PID).  Of course, this is not enough as it only returns these information.  If you want to know the memory usage for a given instance, you can simply run the same command without the /svc switch.  This will return the image name, process ID, Session name, session number and memory usage.  The process ID value will be your basis for identifying which SQL Server instance is using more memory resource. 
     
    I just wish you can do a join between the results of the first command and the second command so the results will be a bit user-friendly.
    September 04

    TrueType font error in SQLProfiler 2005...duh?

    I was running some scripts that our vendors gave in one of the test servers that is running Windows Server 2003 and SQL Server 2005 Enterprise Edition.  As always, I would like to know what is happening in the background while the scripts are running so I decided to run SQL Profiler. I always run SQL Profiler anytime I do some checks while processes are running in the background.  To my surprise, I get this "TrueType font error" when I run SQL Profiler.  This is the first time I will be running SQL Profiler in this machine as this was newly configured, "fresh from the oven" of those IBM warehouses.  I thought this has nothing to do with SQL Server 2005 so I did a quick search and found out that all I needed to do was to go to SQL Profiler's Tools > Options and modify the currently selected font.  When I run SQL Profiler again, everything was fine.
     
    The Windows Server 2003 is using Chinese (PRC) as its Regional Options with China as Location.
    September 03

    Look, ma, no codes - Deploying ASP.NET 2.0

    I was deploying classic ASP and SQL Server 2005 in our client's test servers a few days ago.  In the process, I was asked to look at the codes in their default.asp page to do troubleshooting of setup. As I was looking at the codes, I can't help but imagine how these codes are vulnerable to people who have access to the server (like me) and simply copy these codes for their own use.  This is where ASP.NET 2.0 have a very big advantage.  In ASP.NET 1.0/1.1, you can deploy your web application which contains the assemblies in DLL format.  But the problem is, your .ASPX pages still contain the HTML markup.  Anybody can still look atthe .ASPX files from the server and open it in any text editor.  With ASP.NET 2.0, you have the option to precompile your web application.  A lot of people think that precompilation is just so that the application loads a lot faster during the initial requests to the application.  This is just part of what precompilation does to your ASP.NET 2.0 applications.  What's more, you can deploy your applications without any code at all -HTML nor .NET.  The .NET Framework 2.0 provides options to precompile your ASP.NET 2.0 applications so as not to have any code during deployment.  One of them is the aspnet_compiler.exe, a command-line tool that comes with the SDK.  A complete description of this tool can be found on this MSDN website. Another is by using the Publish Website option in Visual Studio 2005/Visual Web Developer Express. It is as easy as unchecking the Allow this precompiled site to be updateable check box.  This is also described in the MSDN website. This will produce a single default.aspx with no code at all - HTML or .NET.  The web.config file also does not contain anything except a tag that identifies that the code was precompiled - no ConnectionStrings, no security settins, nothing at all. Which makes your code totally hidden to anybody who might just want to copy them from the server. This is one of the great reasons to migrate classic ASP or ASP.NET 1.0/1.1 to ASP.NET 2.0