June 11, 2018

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!

May 18, 2018

Error in Crystal Report: could not load file or 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>

April 16, 2018

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 wokring 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.

March 31, 2018

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"

March 17, 2018

XAMPP - PHP - Connect with MS SQL Server 2008 R2 in Laravel

Few days back I got stuck in this situation, I was developing PHP web application with Laravel 5.6 framework, the problems comes when I tried to connect with MS SQL Server rather than MySQL or MariaDB (that comes as default in XAMPP package), It started giving me error like driver not found. Here is how I solved this problem.

First you need to download Microsoft Drivers 4.3 for PHP for SQL Server, then extract the files to some folder. Copy the file php_pdo_sqlsrv_71_ts_x86.dll to php/ext folder (from XAMPP installation's root folder). Now we have to instruct PHP to use this extension.

Open php/php.ini file and move the cursor to extensions section. Add following new line of extension which directs PHP to use SQL Server driver's dll.

extension=php_pdo_sqlsrv_71_ts_x86.dll

We have successfully set-up our PHP environment with MS SQL Server Driver.

Don't forget to restart your server afterwards.

Now you should be able to successfully connect with SQL Server from PHP.

Here comes the Laravel part. You have to specify connection string parameters in .env file of Laravel root folder.

DB_HOST=MY-PC\MYSQL2008R2
DB_PORT=1433
DB_DATABASE=ITEMMASTER
DB_USERNAME=myuser
DB_PASSWORD=mypassword

Or alternative way is to define connection parameters in config/database.php file.

'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => 'MY-PC\MYSQL2008R2',
            'port' => '1433',
            'database' => 'ITEMMASTER',
            'username' => 'myuser',
            'password' => 'mypassword',
            'charset' => 'utf8',
            'prefix' => '',
        ],

Now Laravel website should be able to connect with SQL Server database.

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