Archive for the ‘SQL Server 2005’ Category

SQL Server Stored Procedure to generate different types of reference number

At work, there is a website that we need to generate several types of unique reference numbers. Numbers such as A000013579 for answer id and Q000024680 for question id. To generate numbers like that, we created a stored procedure. I can see that we can use this method in other websites as well.

First, we created a table to save the last used number. Let’s call it [SequenceTable]. In this table, we need 4 columns: [Type],[Prefix],[LastNumber] and [Length].
values for [Type] column could be: “Answer ID”
values for [Prefix] column could be: “A”
values for [LastNumber] column could be: 13579
values for [Length] column could be: 10

Then, we created a Stored Procedure that take value for [Type]. Look for the value of [LastNumber] where [Type] is the input. Fill 0’s using the REPLICATE function. Increase the value of [LastNumber] by 1 and update it in the table. Append prefix and return it as an output. The stored procedure is defined as follow:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_GetNextReferenceID]
  @Type as varchar(25),
  @ID varchar(20) OUTPUT
AS
DECLARE 
  @Prefix VARCHAR(10),
  @LastNumber int, 
  @Length smallint,
  @charLastNumber as varchar(15)
BEGIN
  if exists (Select * From SEQUENCETABLE Where Type = @Type)
    begin
      Select @prefix = Prefix,
          @LastNumber = LastNumber,
          @Length = Length
      From SEQUENCETABLE Where Type = @Type
      
      Select @LastNumber = @LastNumber + 1
      Update SEQUENCETABLE Set LastNumber = @LastNumber Where Type = @Type

      -- To Fill 0 in between with the REPLICATE function
      Select @charLastNumber = REPLICATE('0',@length - len(@LastNumber) - len(@prefix)) 
+ cast(@LastNumber as varchar(10))
      Select @ID = @prefix + @charLastNumber
    End
  Else
    Begin
	SELECT @ID = 'INVALID TYPE'
	RAISERROR('Invalid Type',16,1)
    End
END

use coalesce function to concat field values

Problem: I got 3 tables: [Posts],[Tags] and [PostsTags]
When the tables join, I got multiple records for each post with distinct tags.
I want a table that contains single record for each post with tags concatenated.

Solution:

  1. In SQL Server 2005, I created a Scalar-valued Function that takes one parameter (the post id). This function returns concatenated values of the tag name given the post id.
  2. Then I called this function in the Views and Stored Procedure.
  3. The Scalar-valued Function is defined as below:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[SVF_TagNameByPostID] (@post_id int)
RETURNS nvarchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000)

	SELECT @Result = COALESCE(@Result + '|', '') +
	CAST(Table_Tags.tag_name AS nvarchar(50))
	FROM Table_Tags INNER JOIN
	Table_PostsTags ON Table_Tags.tag_id = Table_PostsTags.tag_id INNER JOIN
	View_Posts ON Table_PostsTags.post_id = View_Posts.post_id
	WHERE View_Posts.post_id = @post_id

	RETURN @Result
END

move sql server 2005 database to sql server 2000

I need to move a database that I created in sql server 2005 express to an old development environment which run sql server 2000. After searching on the web for half hour, I follow the instructions in one of the posts. It works. Below are the steps to do this.

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

Other Thoughts:
The keyword in the store procedures needs to be changed. For example, the transaction keywords is different in version 2005 from version 2000.


Source:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1

Use different connection strings depends on current URL

I manage two servers at work. One for development and the other one is production server. I used to have two different web.config file and have different connection string to the database in them. But what I should do is write an if statement to decide what to use depends on what the current URL is.

In <appSettings> tag of the “web.config” file, add the following:

<appSettings>
<add key=”Staging” value=”http://my-staging-server.com&#8221; />
<add key=”DevelopmentConnectionString” value=”server=Dev-DB01; database=DBSample;Integrated Security=SSPI”/>
<add key=”ProductionConnectionString” value=”server=Dev-DB01; database=DBSample;Integrated Security=SSPI”/>
</appSettings>

Create a Global Application Class file and name it “Global.asax“. In the file, add the following:

Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
If (Request.Url.Host = ConfigurationManager.AppSettings(“Staging”)) Then
Context.Items.Add(“ConnectionString”,ConfigurationManager.AppSettings( “DevelopmentConnectionString”))
Else
context.Items.Add(“ConnectionString”,ConfigurationManager.AppSettings( “ProductionConnectionString”))
End If
End Sub

Reporting Services Startup Slow

Unlike other ASP.NET application, the reporting services that comes with the SQL Server always startup slow. I google it a while and comes to an article on how to make it faster. Below is the section explaining why it is slow and how to make it faster:

Other things that cause additional overhead when Reporting Services starts up:

  1. Every time the SRS 2005 web service loads, it also has to read and decrypt the rsreportserver.config file
  2. Since there are no connections in the connection pool, we have to physically open up a socket connection between the two servers, plus log into the database instance
  3. The web service has to make RPC calls into the Windows Service to get the symmetric encryption key

This explanation is to try and provide an overview of some of the things that are going on during Reporting Services initial start up.  Remember, most of these things probably do not happen in traditional web applications. Again, all of this is completely expected behavior.

As discussed earlier, if this behavior causes some business issues, you could consider modifying the recycle options on your IIS process.  You can either increase the recycle time (causing them to be recycled less frequently) or schedule the recycle to occur at a non-peak time.  You could then combine this second option with a “ping” process that hits the process shortly after the recycle.  This will “wake” the ReportServer processes so that your initial customer doesn’t see the initialization time.  If you combine these options with turning off the idle worker process shutdown, you can significantly minimize the instances where a user would run into the startup delay.

Working with email addresses in SQL Server

Below is an example of adding email address constraint in the database field.

ALTER TABLE [dbo].[Subscribers]
WITH CHECK ADD
CONSTRAINT [EmailValidator]
CHECK
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces in email
AND LEFT(LTRIM([Email]),1) <> '@'
-- '@' can't be the first character of an email address
AND RIGHT(RTRIM([Email]),1) <> '.'
-- '.' can't be the last character of an email address
AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1
-- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 
-- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3 
-- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) 
-- can't have patterns like '.@' and '..'
)
GO

To check invalid email address in the database field, use the code below.


SELECT * FROM Subscribers
WHERE NOT
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0
AND LEFT(LTRIM([Email]),1) <> '@'
AND RIGHT(RTRIM([Email]),1) <> '.'
AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0)
)

Reference:
http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm

Encrypt DB Connection String

Problem:
The web.config file of the website contains the user name and password to the SQL Server database. When uploading a website to another server, whoever has access to the server can read the login info for the database server. We can encrypt this connection string info in the web.config file.

Solution:
Create a page with two buttons. One to encrypt and one to decrypt.

Protected Sub Button_Encrypt_Click(ByVal sender As Object, ByVal e As System.EventArgs)
EncryptConfig(True)
End Sub

Protected Sub Button_Decrypt_Click(ByVal sender As Object, ByVal e As System.EventArgs)
EncryptConfig(False)
End Sub

Protected Sub EncryptConfig(ByVal bEncrypt As Boolean)
Dim path = “/SCRP”

Dim config As Configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(path)
Dim appSettings As ConfigurationSection = config.GetSection(“connectionStrings”)

If bEncrypt Then
appSettings.SectionInformation.ProtectSection(“DataProtectionConfigurationProvider”)
Else
appSettings.SectionInformation.UnprotectSection()
End If

config.Save()
End Sub

Other thoughts:
Maybe it’s better to compiled the whole website into .dll files…

Source:
http://www.asp.net/learn/videos/video-39.aspx

Scalar-valued Functions in SQL Server

Problem:
The new reporting features in SQL Server 2005 is very useful to generate a report, but putting the logic in the report is not as easy as in a regular aspx page. For example, I want to display some text in a new column depending on the values stored in the other columns in the database. I want it to be sortable in the report as well. This is where the Scalar-valued Functions in SQL Server comes in. Instead putting the logic in the report, it is much better putting it in the SQL Server.

Solution:
Scalar-valued Functions in SQL Server allows DBA to create function to be called in views or stored procedures. The function can contains very complex logic to determine the output. The example below determines the text to be returned depends on the values of the fields. If season=summer and cloud=none, then it’s sunny.


USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[svf_Weather]
{
@Weather_Date smalldatetime — input parameter
}

RETURNS varchar(20)

AS
BEGIN
DECLARE @Weather varchar(20), @Season varchar(20), @Cloud varchar(20)

— Assigned values to variables
SELECT @Season = [Season], @Cloud = [Cloud]
FROM [DB_Name].[dbo].[qry_view_or_table]
WHERE [Weather_Date] = @Weather_Date

— The logic to determine the returned text
SELECT @Weather = CASE
WHEN @Season = ‘summer’ AND @Cloud = ‘none’ THEN ‘sunny’
WHEN @Season = ‘winter’ AND @Cloud = ‘cloudy’ THEN ‘freezing’
ELSE ‘others’
END
RETURN @Weather
END

Copy mdf sql server file

To copy the .mdf file in SQL Server 2005, don’t take the database offline. Login to the server and stop the SQL Server services. Then you can copy the database.

replacing Alt Enter character in Excel with HTML line break in a DataList

Problem: Excel cells contain Alt+Enter line breaks. After the Excel file is imported to the SQL Server database, the line break is not showing in a datalist inside of a webpage.

Solution:

Trigger a function for the ItemDataBound event for the DataList. Replace the Alt+Enter character in Excel which is “\n” with HTML line break “<br />”

Example:

protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
{

String temp = ((Label)e.Item.FindControl(“YourControlName”)).Text;

((Label)e.Item.FindControl(“YourControlName “)).Text = temp.Replace(“\n”, “<br />”);

}