Some times when you execute a SQL query to insert or update data into a table, you may see the following error.

String or binary data would be truncated.
The statement has been terminated. 
Description: An unhandled exception occurred during the execution of the current web request. 
Please review the stack trace for more information about the error and where it 
originated in the code. 
Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

What this error means is that you are trying to insert more data into a field than the schema specified for. For example if you have a varchar field and limited its size to 100, if you try to insert a string with length more than 100, then you will end up with this exception. In older version of SQL, the server used to silently let this case go through and truncate the data and you would not know it. And then you will notice it when trying to display these fields in some form.

Recently I upgraded my development machine to Windows 7 and moved all daatabase files to it as well. When I tried to attach one of the database to SQL Server 2008, i got the following error.

Unable to open the physical file. 
Operating system error 5: "5(Access is denied.)". 
(Microsoft SQL Server, Error: 5120)

The error is very clear that it has everything to do with file system permissions on the database file. I searched around for answers to why SQL Management Studio is having issue although I am logged in as administrator on my machine. All the search results hinted that account under which SQL server is running does not have enough previlleges. So I was like so I just need to set appropriate permissions on MDF and LDF or any other database related files. The most dreaded suggestions that came across were people suggesting to give Full Control to the user account on the database folder(s). I am not going to go into discussion about this here why this is just bad. Lets just say that I am not a big fan of giving blanket full control permissions to user accounts to sensitive files without understanding what it means.

My SQL server instances were running under Network Service account. So I looked at the properties of the database folder and database files. And changed the permissions. Well that did not work. Since the error is very clear about the problem, it was time to do some old school debugging of problem. Since the error is related to file system, so it was time to fire up my good old Process Monitor tool. Now I was going to watch for Access Denied error when my database file was going to be accessed.

What user account is used to access the database file?

If you can find the answer to this question, then all your problems are solved. So this is where Process Monitor is going to help. Start Process Monitor and try to attach the database file. Now capture the results from Process Monitor. You definitely will want to filter the results to include the entry related to your database file only. See the following image where the last entry for my database file has STATUS code of Access Denied. This is what I was looking for. Now double click on this entry and it will bring up details about that I/O operation. The following image shows the account that is being Impersonated to access this file. Yes, impersonation. When you start SQL Management Studio, it asks what kind of authentication you want to use. If you select currently logged in user or Windows Authentication that means SQL Management Studio is going to take all action on your behalf when accessing operating system resources.

Fix It

Now you have two options to fix your problem.

• If you are going to use Windows Authentication for SQL Management Studio, then you need to fix permissions on the database files to include that user with appropriate rights.
• If you are going to use SQL Authentication, then make sure that account under which your SQL Server (and not SQL Management Studio) runs has correct set of permissions to access the physical files.

Making the above change should fix your problem and you will be able to attach the database without any errors.

I have been doing lot of database development for some years. Some time I got the design right and some time I made a mess that I had to clean up. Some time I will miss some requirements and some time I will get the normalization wrong. Like any other development process I went through iterations to finally get to the point where I am comfortable with database design.

Recently I got tasked with database rearchitecture of a large scale system. Since I had some time on hand (for a change) I decided to read up on some literature and books on database design and best practices. I asked a good friend of mine who is an excellent DB engineer about recommened books. He recommended only two book. One was by Joe Celko. After readin abstract and some reviews on the book, i decided that this book is not something I want to pick up. It is too abstract and theoraticak. Second book he recommended was Pro SQL Server 2008 Relational Database Design and Implementation. It definitely looked like something I may enjoy reading.

This book is definitely a very good resource on database design and architecture. It is just the right kind of book, not too abstract not to targtted towards just showing you sample scripts and things like that. Book starts you with basic concepts on database design and build from there. First four chapters are definitely worth reading if you are just starting in world of database design. I really liked how in Chapter 3 he goes through step by step process of requirement gathering and shaping the design and making chnages as you go along. I will definitely recommend this book to any one who wants to grasp some concepts about what is database design, process to follow and how to implement it effectively.

This is a very common problem after you do a new install of SQL Server on a server. Moment you try to access it over the network, following error pops up in one form or the other.

A network related or instance specific error occurred, while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name is correct and that
SQL server is configured to allow remote connections.(provider: SQL Network Inrerfaces, error:26 -
Error Locating Server/Insatance Specified)(Microsoft SQL Server)

In SQL 2005, there used to be surface area configuration manager that you could use to enable network access to your SQL server. In SQL Server 2008, the interface has been changed a little bit. There is no surface area configuration manager per se. Here is the procedure you will follow in SQL 2008.

• From start menu of SQL Server 2008, run SQL Server Configuration Manager
• From left side view of SQL Server Configuration Manager, expand SQL Server Network Configuration
• In the right view, you will find list of protocols for SQL Server. By default only Shared Memory is enabled. Others are disabled. Enable the other protocols to get your SQL server to accept connections over the network.

Connecting to named instance?

If you are trying to connect to named instance, make sure that you are specifying correct instance name and that instance name actually exists on target server and is running.

SQL Server Browser Service Running

Make sure that SQL Browser service is running on target server. Otherwise you will get the error that I have mentioned at the start.

While creating a new web application, as part of user management feature I was added bunch of tables and some stored procedures. When I executed my stored procedure sp_adduser I kept getting the error Procedure or function sp_adduser has too many arguments. This drove me nuts. I checked every single line of code, looked at execution of stored procedure in profiler. Everything looked good. I have 4 parameters defined for my stored procedure and one is of type OUTPUT. Then I thought since I am using SQL Server 2008, there may be something different about how OUTPUT parameters are handled. I could not find anything different about it in SQL Server 2008 documentation. Then I decided to create my database in SQL Server 2005 and executed my code against it. Same error from SQL Server 2005 as well. At this point I figured that there is something else going on with this stored procedure. Since SQL Server only tells you name of parameter if there is one missing. So there was no way I was going to get name of parameter that was extra from the error message. So I decided to remove OUTPUT parameter from my call. Then I get following error.

Procedure or function 'sp_adduser' expects parameter '@loginname', which was not supplied

Now this error message was interesting. My stored procedure does not have @loginname parameter defined. That hinted me that my call is colliding with some other stored procedure in database and that stored procedure's name is same as what I have created. I looked at System Stored Procedures. Thats where I found stored procedure sys.sp_adduser defined as below.

procedure [sys].[sp_adduser]
@loginame       sysname,	    -- user's login name in syslogins
@name_in_db     sysname = NULL, -- user's name to add to current db
@grpname sysname = NULL  -- role to which user should be added.

After I changed name of my stored procedure, everything worked fine. This was kind of interesting debugging exercise. And it showed me that SQL Server query engine first matches count of parameters before it checks individual stored procedure names and other related metadata. And that perfectly makes sense from optimization point of view. Anyways, when you get these kidn of weired errors, do check if there is some system stored procedure or function name that may be colliding with yours.

For the first time I tried to write a stored procedure in SQL Server 2008 query editor in SQL Management studio. I was impressed to see how complete intellisense support has been implemented in SQL Management studio. It event recoganized alias table name. And when i entered DOT after alias name, it immediately gave me name of all the columns in the table. Made my life very easy. In the past I had to keep tree view open for a given table in left side to see all the column names. Good work Microsoft team!

Finally I started using SQL Server 2008 on my Windows 7 work station. I created a new database. Created a new table to test some code. Then i decided to change schema of my table. Made a simple change and marked one field to be Identity field. Moment I hit OK, i got the following error message.

This is fresh install of SQL Server 2008 and I did not recall of enabling such option. These minor changes in SQL2005 never used to trigger such warnings. After clicking on few options menu options i figured out where this option is enabled. Goto to Tools > Options menu. It will bring up wizard for database options. Click on Designers link in left pane. It will bring up following options in right pane. Notice that Prevent saving changes that require table re-creation option is checked. You can uncheck this option to enable these table schema changes.