Generating random strings with T-SQL

For testing scenarios you may need to generate random strings. You can use one of the following ways for this purpose.

declare @alphabet varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1);

This script will generate random string of length 5. If you need to increase or decrease number of characters in the generated string, you can add/remove substring statements in the above script.

A more easier approach can be to use NEWID() function, convert the result to varchar and then you can use LEFT(), RIGHT(), SUBSTRING() or any combination of string functions to generate string of desired length.

SELECT CONVERT(varchar(255), NEWID())

This will convert NEWID() output to varchar(255)

SELECT LEFT(CONVERT(varchar(255), NEWID()), 10)

This will display left 10 characters of the NEWID().

SELECT RIGHT(CONVERT(varchar(35), NEWID()), 10)

This will display 10 characters of the NEWID() from right.

SELECT SUBSTRING(CONVERT(varchar(35), NEWID()), 10, 25)

This will display substring of 15 characters starting from index 10 and ends at index 25.

Understanding DEFAULT constraints in MS SQL Server

If you have worked with multiple instances of a database, there is a high chance that you have faced issues with constraints default names. You generated the script for tables/schema changes and try to run on another instance of that database, where some constraint is being dropped from a table, and you get error message.

For example, you have an Items table, have a constraint defined on column Item_Code with default name, and a drop constraint statement like this:

 ALTER table [Items] drop DF__Items__Item_Code__07F6335B

It may worked on your current database instance, but on some other instances it may give you an error message like this:

 Msg 3728, Level 16, State 1, Line 1
 'DF__Items__Item_Code__07F6335B' is not a constraint.
 Msg 3727, Level 16, State 0, Line 1
 Could not drop constraint. See previous errors.

Why you get this error?

Because at the time you created this constraint, you have not provided a custom name, so SQL Server generated a default name. Since default name is auto-generated, there are no chances that this same name will be generated on each instance, no all other instances are having constraint with same name. When you try to run this drop constraint statement on other instances, you are getting this error.

Lets try to understand this with an example.

Create a new database.

Create a new table, lets say Items with this statement.

CREATE TABLE [dbo].[Items](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Item_Code] [nvarchar](255) NOT NULL,
 [Item_Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

At this point we have no constraints on any column of Items table. Lets defined a new one.

 ALTER TABLE [dbo].[Items] ADD  DEFAULT ('') FOR [Item_Code]
 GO

In this constraint, we are setting empty string as default value for column Item_Code. I did not provide any name, SQL Server has defined an auto-generated name, in my case it is DF__Items__Item_Code__07F6335A, and this is not a good approach.

Better is to define a custom name for constraints, which will remain static across all instances of this database.

Lets define a new constraint on Item_Name column, but this time with custom name:

 ALTER TABLE [dbo].[Items] ADD  CONSTRAINT [DF_Items_Item_Name]  DEFAULT ('') FOR [Item_Name]
 GO

Here constraint name is DF_Items_Item_Name, and will remain common on all nodes.

So far, we have created a new table and then defined constraints on existing columns.

The same problem will happen when you add new columns to this table. Lets add a new column CreatedBy with default name 'admin':

 ALTER TABLE [dbo].[Items] ADD [CreatedBy] [nvarchar](20) NOT NULL DEFAULT 'admin' 
 GO    

Here again, we did not provide a custom name, so SQL Server will place auto-generated. In my case it is DF__Items__CreatedBy__0DAF0CB0.

Good technique will be to provide custom name, lets create a new column CreatedOn, but this time with our specified name:

 ALTER TABLE [dbo].[Items] ADD [CreatedOn] [datetime] 
  CONSTRAINT [DF_Items_CreatedOn] DEFAULT (getdate()) NOT NULL 
 GO

Here constraint name is DF_Items_CreatedOn.

One thing to be aware that you can not change constraint names, you have to drop old one and create new constraint if you need to modify or rename.

I hope you found this post helpful. Share your thoughts in the comments below!

Crystal Report: could not load assembly

I moved crystal report files from ASP.Net website to Windows Forms Application, and run the application, then I got this error message on loading reports.

Could not load file or assembly 'file:///C:\Program Files\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet1\crdb_adoplus.dll' or one of its dependencies. The system cannot find the file specified.

By adding following settings in app.config file solved this issue for me.

 <startup useLegacyV2RuntimeActivationPolicy="true">
  <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
 </startup>

Read web.config file outside the application folder in C#

I have a web application running on my server. And I needed to develop a small utility application which has to do some work in database, and I don't want to write same database credentials or connection strings(and some other additional app-settings) in this new console application, I want it to read settings from same web.config file. But the problem I was facing when I tried this code:

 string filePath = @"D:\MyProject\Web.config";
 Configuration c1 = ConfigurationManager.OpenExeConfiguration(filePath);
 var value1 = c1.AppSettings.Settings["Key1"].Value;

It was not giving any error if it was failed to load the given file path, but also was not working as per my expectations. The Configuration object c1 was loaded fine but when I tried to read ApSettings, it was empty. There was no key/value pairs loaded in this collection object. So trying to read value for Key1 was giving me error as:

 Object reference not set to an instance of an object. 

After wasting much time thinking and searcing on this issue, I found this solution. It worked fine and allowed me read config file from another application.

 string filePath = @"D:\MyProject\Web.config";
 var fileMap = new ExeConfigurationFileMap { ExeConfigFilename = filePath };
 var configuration = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);
 var value = configuration.AppSettings.Settings["Key1"].Value;

Here I get proper value of Key1, in variable value.

Running PHP script from the command line

I was need to call my php script from command prompt in Windows. This is what I found:

Let's say, we have PHP file myscript.php in Test folder inside htdocs folder of XAMPP installation (this is not mandatory, you can place your php file anywhere on your local hard drive), and I want to call it from command prompt.

We need to write it this way:

 php "D:\xampp\htdocs\Test\myscript.php"

It is assumed that your php.exe file path is set in environment PATH variable. If it is not included in environment PATH variable then you may need to write full path for php.exe. In my case, it is like this:

 "D:\xampp\php\php.exe" "D:\xampp\htdocs\Test\myscript.php"