Dynamic SQL usage

One of the usefulness about dynamic SQL is for database maintenance. For example, you want to run the maintenance process in the selected databases.

Below is the flow of using dynamic SQL to ease the maintenance:

• Create a table that stores the database names for your system.
• Declare a cursor and loop through all your database names and match the process number (explain later).
• Generate the dynamic SQL statement to be executed against the selected databases.
• Finally, execute the dynamic SQL.

The reason to have a table to stores your database names:

• You are not interested in all databases stored in sys.databases. You just want to run the script against the selected databases.
• The database name did not follow any convention before you take over the maintenance. In you are handling the database server, then, you should follow a standard naming convention for the database name. This allows you to query the database name from sys.databases.
• You don't want to execute the SQL statement against all your application databases. Only certain databases are going to run script 1 and other databases are going to run script 2, etc.

The table design to stores the database name:

• db_name nvarchar(255) - this is the database name
• process_1 int - if '1' means run the maintenance stored procedure #1. '0' to ignore.
• process_2 int - if '1' means run the maintenance stored procedure #2.'0' to ignore.
• (other process flag).

Benefits of using stored procedure instead of writing codes in C#

Started from my favorite programming language, Visual Basic 6 (that was in 1997), I've been thinking where is the best place to implement the "business rule". After many years of moving the business rules from the the programming language to the database and vice versa, I've finally decided the best place for the business rule is in the stored procedure.

The simplest stored procedure is for SELECT, INSERT, UPDATE and DELETE. Indeed, you can do more than that. Benefits of using the stored procedure:

• Create audit log when doing any of the CRUD operation.
• Able to implement sophisticated control in what kind of data can be return to caller.
• Easier to enhance/process the records before returning the records.
• Stored procedure has been compiled and it runs faster than the ad-hoc query.
• Able to enhance the stored procedure at anytime.
• You don't have to recompile the application if there are any changes in the rule.
• You don't have to redistribute the application (at client workstation or web server) if there are any changes in the rule.

Well, I know that you might not 100% agree with my argument. But, as a customize solution builder like me, we can't ask all the users to stop for a while and wait for the latest update.

You need an application log for your systems

All systems requires a common area to keep track certain information. For example,

• The exception/error which was not handled properly - this eases the system maintenance.
• The user login and logout - you may use this log to keep track all security related activities. For example, append a "warning" log when someone is trying to hack the system especially the web base system.
• When certain system settings were missing and the system continues to work using default value - this is very important if you are distributing your system to more than one site. For example, the system administrator forgot to set the new sales email address.
• When user clicks on certain function which handles using stored procedure in the database - this is important because the user might already have clicked it multiple times and the system like no-effect. By creating log record into this table, the system administrator will be able to tell whether the stored procedure is running or not and carry out necessary recovery.

Table design:

CREATE TABLE tb_log (
    log_id uniqueidentifier NOT NULL,        --primary key
    log_type_id INT NOT NULL,                --type of log.
    workstation NVARCHAR(255) NOT NULL,        --the workstation name
    uid NVARCHAR(255) NOT NULL,                --the current user ID.
    msg NVARCHAR(max) NULL,                    --the details of the log
    remarks NVARCHAR(255) NULL,                --the remarks/procedure name.
    is_sent INT NOT NULL,          --if '1', means the log has been email to admin.
    app_id INT NOT NULL,                    --the application ID
    module_id INT NOT NULL,                    --the module ID
    modified_on DATETIME NOT NULL,            --the last updated date/time
    modified_by NVARCHAR(255) NOT NULL,    --the person who perform last update.
    created_on DATETIME NOT NULL,     --this is the current date/time when the log record is inserted.
    created_by NVARCHAR(255) NOT NULL        --the current user ID while creating this record.
);

The allowed values for the log_type_id field:

1. Error - this indicates that the system crashed at runtime and the value in the msg field is the exception details.
2. Information - this indicates that some processes have been kicked off and running smoothly.
3. Warning - this indicates that some certain system settings were missing or using default value but the system is still able to function normally.
4. Audit Log - this indicates that the log is security related or certain important data has been changed by the user.
5. Critical - this indicates the system crash is critical or someone is trying to figuring out the login ID or password and requires immediate attention.

Add-on module to be build on top of this log table:

• Email the log records to the system administrator (and mark "is_sent" with value of "1"). The email can be send in daily or weekly basis.
• If the log_type_id indicates that it is a "threat" or "hacking", the log record must be send immediately to the system administrator.

Note: for process logging, please refers to "Tracking the progress of a long run process" article.

Sync the SQL user ID

After moving the database from one SQL server to another, the user ID (that has been created in the database) is no longer working. This is mainly due to the "SQL login ID" does not match with the "database user ID". The value that links between these ID-s are call the SID.


Before executing the fixes, you may wonder what has happened to the user ID. Execute the following query:

   use [my_database]
   go
   select * from sysusers where name = 'my_user_name'
   select * from master..syslogins where loginname = 'my_login_name'
   go


From the result, you might notice that the "sid" value in both queries are different. And this is the cause of the login problem.


In MSSQL, there is a system stored procedure that will help you to sync the SID value and it is called "sp_change_users_login".

To get the list of the mismatch user ID, execute this command.

    exec sp_change_users_login 'report'

To sync the database user ID with the SQL login ID:

   exec sp_change_users_login 'update_one', 'my_user_name', 'my_login_name'

Note: you have to replace "my_user_name" and "my_login_name" with the actual values.