Nik's Technology Blog

Travels through programming, networks, and computers

Data Layer with Business Logic

Deciding when to move business logic to the data layer in a multi-tiered application is a tricky one and depends a lot on the RDMS you are using.
I've been looking at an ASP.NET application that creates 7 rows in a database for each "musical performance" when a button is clicked on a web form to rebuild a database.
To begin with a SQL 2005 stored procedure written in VB.NET (But could easily have been C# with the same results since all .NET languages are compiled to IL). was called from a loop in the business layer 7 times to create the 7 rows. The application was run and it took about 90 seconds to rebuild a test database in this manner.

This logic was then moved into 1 stored procedure still written in VB.NET; The result was that instead of making 7 calls to the database each time, we make 1 call to the database and move the business logic, executing the loop there, and hence at the data layer. The database rebuild took about 30 seconds this time.

Stored Procedures in Native T-SQL

To try and make the execution faster we decided to write the stored procedure in T-SQL, which is still the native language of SQL Server. This shaved another 15 seconds of the execution time! Just proving what sort of overhead writing your stored procedures in anything other than T-SQL introduces.

This exercise provides a glimpse into how the decisions you make when designing an application radically affect the responsiveness of the system.

Business Logic at the Data Layer

If you were pulling lots of data to the business layer performing some processing then discarding most of the data at that point, then it may improve performance if this operation was moved down to the data layer. It really depends on the data and the application.
One of the problems with storing too much application logic in the data layer is version control. It can become a mine field managing lots of complex stored procedures between test and live environments and keeping them in-sync with the application.

Truncating and Shrinking a SQL Server logfile

It's such a pain when a SQL Server log file runs away with it's self on a development machine. I always forget the T-SQL to truncate and shrink the log file, so I thought I'd post it here for future reference.

BACKUP LOG <database name> WITH TRUNCATE_ONLY

DBCC SHRINKFILE ('<datbase file minus extension>',1)

Importing data from one Database to another with slightly different sized fields?

Preparation is the key to a faultless import.

Sometimes it's useful to know the maximum length of the data you store in your database. Even if you know the maximum size of the cell, the actual data stored may be smaller.

In Microsoft SQL use:

SELECT MAX(LEN(firstname))

FROM tblData

This MS SQL command will find the maximum length of the data in the firstname field of tblData table.

Using this method you can avoid having to increase the destination tables field sizes in cases where you haven't actually used the full capacity in the source database.

So if the source field size for firstname is varchar 50 and the destination is varchar 40, you can use the above SQL to determine if anyone's first name is actually longer than 40 characters. If it's not then you don't need to increase the cell size in the destination table.

If you get the following error when you try to use the LEN() SQL statement above, then you are trying to find the maximum length of a text field.

Error: Argument data type text is invalid for argument 1 of len function.

For text fields in SQL use Datalength() instead of LEN().

SELECT MAX(DATALENGTH(Description))

FROM tblJobs

The Datalength() function which will return the length of any expression, in this case the maximum length of the description field from the tblJobs table.

This can be used on all data types including text, ntext, image and varbinary.

Of course a perfectly designed database would have as little spare capacity in fields as possible, but with fields such as people's names this is hard to achieve this.

Determining which SQL Server Service Pack you have

Before installing Microsoft SQL Service Packs you need to find out which version you are currently running. The commands below should be run in Query Analyser.

SQL 2000

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

RTM 2000.80.194.0

SQL Server 2000 SP1 2000.80.384.0

SQL Server 2000 SP2 2000.80.534.0

SQL Server 2000 SP3 2000.80.760.0

SQL 7

SELECT @@VERSION

7.00.1063 SQL Server 7.0 Service Pack 4 (SP4)

7.00.961 SQL Server 7.0 Service Pack 3 (SP3)

7.00.842 SQL Server 7.0 Service Pack 2 (SP2)

7.00.699 SQL Server 7.0 Service Pack 1 (SP1)

7.00.623 SQL Server 7.0 RTM (Release To Manufacturing)

SQL 6.5

SELECT @@VERSION

6.50.479 SQL Server 6.5 Service Pack 5a (SP5a) Update

6.50.416 SQL Server 6.5 Service Pack 5a (SP5a)

6.50.415 SQL Server 6.5 Service Pack 5 (SP5)

6.50.281 SQL Server 6.5 Service Pack 4 (SP4)

6.50.258 SQL Server 6.5 Service Pack 3 (SP3)

6.50.240 SQL Server 6.5 Service Pack 2 (SP2)

6.50.213 SQL Server 6.5 Service Pack 1 (SP1)

6.50.201 SQL Server 6.5 RTM

Note: If the version number of your server is not listed here then please visit Microsoft's site for the latest versions. Hotfixes and security patches are not listed.

Fix Orphaned Users in SQL Server after Restoring a Database to another Server

Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.

So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).

Common symptoms of this problem are:

  • Applications may experience 'login failed' error messages and fail to log into the database.
  • Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'

Solution:

Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.

--Change the user account to link with the 'NewHarry' login.

USE Books

go

EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'

This will change the user Harry in the restored database to link with NewHarry instead.

How to troubleshoot orphan users in SQL Server databases?

Fix Orphaned Users in SQL Server after Restoring a Database to another Server

Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.

So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).

Common symptoms of this problem are:

  • Applications may experience 'login failed' error messages and fail to log into the database.
  • Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'

Solution:

Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.

--Change the user account to link with the 'NewHarry' login.

USE Books

go

EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'

This will change the user Harry in the restored database to link with NewHarry instead.

How to troubleshoot orphan users in SQL Server databases?

SQL Server Login Change

If you happen to install Microsoft SQL Server under a Windows account you later want to change to another Windows account you will have to login to Windows using the account it was installed under (Go to Control Panel>Administrative Tools>Services to find this out). You should then stop all SQL server services (MSSQLServer, SQLServerAgent, Microsoft Search, Full-Text Search, Distributed Transaction Coordinator etc). In Control Panel, click on Administrative Tools and then Services. For every SQL service you have installed you will need to view and edit the logon properties, changing the account for each one. A reboot should then complete the process.

SQL DTS Designer -

Currently working on trying to automate the bulk input of web log files into a MS SQL database with DTS Designer and Active-X script.

Managed to concatenate fields and perform VB commands on the data prior to input. However running through a directory of files to input is somewhat more difficult.

Ideal DTS import solution

Adding Contraints to SQL tables

Constraints are important within a database structure as they insure database integrity.

This useful SQL command shows how to add constraints to existing SQL tables.

In this command a constraint between 2 tables (tblCountries and tblCompanies) is being created called FK_tblCompanies_tblCountries.

alter table dbo.tblCompanies add constraint FK_tblCompanies_tblCountries foreign key (ISOCountryCode) references dbo.tblCountries(ISOCountryCode)