How to configure SQL Server 2005 to allow remote connections?

To configure SQL Server 2005 to allow remote connections, complete all the following steps.
• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Enable remote connections for SQL Server 2005 Express

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop. wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service

1.Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2.On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3.On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.
Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4.Click Start, and then click OK.

Create exceptions in Windows Firewall

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.
To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.
Create an exception for SQL Server 2005 in Windows Firewall
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK.
Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.
Create an exception for the SQL Server Browser service in Windows Firewall
To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

---
Hope this helps everyone.

Encryption & Decryption : ASCII encoding

The GetByteCount method determines how many bytes result in encoding a set of Unicode characters, and the GetBytes method performs the actual encoding.

Likewise, the GetCharCount method determines how many characters result in decoding a sequence of bytes, and the GetChars and GetString methods perform the actual decoding.

When selecting the ASCII encoding for your applications, consider the following:

    * The ASCII encoding is usually appropriate for protocols that require ASCII.
    * If your application requires 8-bit encoding, the UTF-8 encoding is recommended over the ASCII encoding. For the characters 0-7F, the results are identical, but use

of UTF-8 avoids data loss by allowing representation of all Unicode characters that are representable. Note that the ASCII encoding has an 8th bit ambiguity that can allow malicious use, but the UTF-8 encoding removes ambiguity about the 8th bit.

    * Previous versions of .NET Framework allowed spoofing by merely ignoring the 8th bit. The current version has been changed so that non-ASCII code points fall back during the decoding of bytes.

——————————————————————
Example of ASCII encoding
——————————————————————

SecurityClass.cs
————————–

public static string EnryptString(string strEncrypted)
{
byte[] b = System.Text.ASCIIEncoding.ASCII.GetBytes(strEncrypted);
string encryptedConnectionString = Convert.ToBase64String(b);
return encryptedConnectionString;
}

public static string DecryptString(string encrString)
{
byte[] b = Convert.FromBase64String(encrString);
string decryptedConnectionString = System.Text.ASCIIEncoding.ASCII.GetString(b);
return decryptedConnectionString;
}

Default.aspx.cs
———————

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string Password = “SandeepMRamani”;
lblOriginal.Text = Password;
lblEncryption.Text = SecurityClass.EnryptString(Password);
lblDecryption.Text = SecurityClass.DecryptString(lblEncryption.Text);
}
}

Default.aspx
——————

<div align=”center” style=”font-size:xx-large;”>
<h1> Encryption / Decryption Example </h1>
<hr />

Original String :
<asp:Label ID=”lblOriginal” style=”font-weight:bolder;” runat=”server” Text=”"></asp:Label>
<br />
Encrypted Text :
<asp:Label ID=”lblEncryption” style=”font-weight:bolder;” runat=”server” Text=”"></asp:Label>
<br />
Decrypted Text :
<asp:Label ID=”lblDecryption” style=”font-weight:bolder;” runat=”server” Text=”"></asp:Label>
<hr />
</div>

--

Happy programming

Differences between varchar and nvarchar in SQL Server

=>SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.
To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX),  NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.



The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference? VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.
The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.
The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.
Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

How to dynamically adjust an iframe’s height?

I was looking to display content of the other aspx page inside iframe but i wasnt able to adjust the height of the iframe. so try out this solution to resolve it.

Insert iframe on page

   <iframe scrolling='no' frameborder='0' id='frmid' src=’getad.aspx'
            onload='javascript:resizeIframe(this);'>
   </iframe>

Use this javascript to resize iframe based on the height & width of child page

<script language="javascript" type="text/javascript">
 function resizeIframe(obj)
 {
   obj.style.height = obj.contentWindow.document.body.scrollHeight + 'px';
   obj.style.width = obj.contentWindow.document.body.scrollWidth + 'px';
 }
 </script>

What does this code do? When the body of the parent frame loads, it looks up the document element “childframe” which corresponds to the iframe. Then the page calls a function resizeFrame(). The function sets the height of the frame to be the scrollHeight, which effectively removes the scrollbar.

happy programming!

Parameter Sniffing and OPTIMZE FOR

Parameter Sniffing refers to a process whereby SQL Server’s execution environment “sniffs” the parameter values during first invocation, and passes it along to the query optimizer so that they can be used to generate optimal query execution plans.


While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is not optimal when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance when executed with typical values. 

Click here to read full article.

Timestamp (rowversion) Data Type

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. Of course this means we should not use it as a primary key, because we can get many orphans quickly if updates are performed on other columns. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function.

Timestamp is the wrong name, quite confusing in fact. It has nothing to do with time. Microsoft will rename it rowversion in the future. Rowversion is the synonym for timestamp in SQL Server 2005 and SQL Server 2008.

It is an 8 bytes unique binary key within the database.

Here is how it looks like: 0x0000000000000743. After an update to the row: 0x0000000000000745.
The rowversion(timestamp) starts changing as soon as the transaction begins. If the transaction is rolled back, it returns to the original value.

So how can we use it?

The main purpose is row versioning in multi user environment, in other words concurrency checking.
Assume you are a developer and developing a program in Visual Basic to update the name and address table of customers. There will be 100 staff member who can perform this application function. How can you be sure that while staff A typing in the change, staff X is not changing the same row?

Here is what you do:

1. Read the name and address table including the timestamp. You display the info to the user for update and save the timestamp. 2. Certain amount of time later, like 2 minutes, the user presses the submit button after changes were typed in. 3. You open a transaction with Begin Transaction 4. You read the timestamp of the name and address row 5. You compare the current timestamp to the saved timestamp. 6. If the timestamps are same, you update the row and commit the transaction 7. If timestamps are different, you roll back the transaction and notify the user about the fact that the data was changed by someone else. You can let the user decide what to do or follow the appropriate company business rule for data entry conflict resolution.

This is pretty common practice in multi user environment. The alternate would be to examine a datetime column, or the entire row which is more processing intensive.

The following example shows timestamp (rowversion in SQL Server 2008) in action:

-- SQL Server 2008 T-SQL Code
USE tempdb;

-- SQL create table for Concurrency Checking demo
CREATE TABLE Celebrity (
CelebrityID INT     IDENTITY     PRIMARY KEY,
FirstName   VARCHAR(25),
LastName     VARCHAR(30),
VERSIONSTAMP  ROWVERSION)
GO

-- SQL insert - populate table
INSERT Celebrity (FirstName, LastName)
VALUES
('Jessica', 'Simpson'),
('Nick', 'Carter'),
('Stevie', 'Brock'),
('Christina', 'Aguilera'),
('Frank','Sinatra'),
('Doris','Day'),
('Elvis', 'Presley')
GO

SELECT * FROM Celebrity
GO

/* Results
CelebrityID FirstName  VERSIONSTAMP
1             Jessica      Simpson      0x0000000000000876
2             Nick         Carter       0x0000000000000877
3             Stevie       Brock        0x0000000000000878
4             Christina   Aguilera     0x0000000000000879
5             Frank        Sinatra      0x000000000000087A
6             Doris        Day          0x000000000000087B
7             Elvis        Presley      0x000000000000087C
*/

-- SQL update demo: SOMEONE UPDATED RECORD since it was read
CREATE TABLE #Semaphore (ID int identity(1,1) primary key,
StartVersion bigint,
PK int)
DECLARE @MyKey int
INSERT INTO #Semaphore (StartVersion, PK)
SELECT  VERSIONSTAMP, 1
FROM Celebrity WHERE CelebrityID=1
SELECT @MyKey = SCOPE_IDENTITY()

-- SIMULATION: somebody else updating the same record
UPDATE Celebrity
SET     FirstName = 'Celine',
LastName = 'Dion'
WHERE  CelebrityID = 1

-- We are attempting to update.
BEGIN TRANSACTION
IF (SELECT StartVersion
FROM   #Semaphore
WHERE  ID = @MyKey) = (SELECT VERSIONSTAMP
FROM   Celebrity
WHERE  CelebrityID = 1)
BEGIN
UPDATE Celebrity
SET     FirstName = 'Lindsay',
LastName = 'Lohan'
WHERE  CelebrityID = 1

COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK - UPDATE CONFLICT'
RAISERROR ('Celebrity update conflict.',10,0)
END
DELETE #Semaphore WHERE ID = @MyKey
SELECT * FROM   Celebrity
GO
/* CelebrityID     FirstName   LastName     VERSIONSTAMP
1             Celine       Dion         0x000000000000087D
2             Nick         Carter       0x0000000000000877
3             Stevie       Brock        0x0000000000000878
4             Christina   Aguilera     0x0000000000000879
5             Frank        Sinatra      0x000000000000087A
6             Doris        Day          0x000000000000087B
7             Elvis        Presley      0x000000000000087C
*/

-- SQL UPDATE with NO CONFLICT
DECLARE @MyKey int
INSERT INTO #Semaphore (StartVersion, PK)
SELECT  VERSIONSTAMP, 1
FROM Celebrity WHERE CelebrityID=1
SELECT @MyKey = SCOPE_IDENTITY()

-- We are trying to update.
BEGIN TRANSACTION
IF (SELECT StartVersion
FROM   #Semaphore
WHERE  ID = @MyKey) = (SELECT VERSIONSTAMP
FROM   Celebrity
WHERE  CelebrityID = 1)
BEGIN
UPDATE Celebrity
SET     FirstName = 'Lindsay',
LastName = 'Lohan'
WHERE  CelebrityID = 1

COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK - UPDATE CONFLICT'
RAISERROR ('Celebrity update conflict.',10,0)
END
DELETE #Semaphore WHERE ID = @MyKey
SELECT * FROM   Celebrity
GO
/*
CelebrityID FirstName   LastName     VERSIONSTAMP
1             Lindsay      Lohan        0x000000000000087E
2             Nick         Carter       0x0000000000000877
3             Stevie       Brock        0x0000000000000878
4             Christina   Aguilera     0x0000000000000879
5             Frank        Sinatra      0x000000000000087A
6             Doris        Day          0x000000000000087B
7             Elvis        Presley      0x000000000000087C
*/
-- Cleanup
DROP TABLE #Semaphore
DROP TABLE Celebrity

---
got mail from http://www.sqlusa.com/register/

Time Delay While Running T-SQL Query – Sleep Facility in SQL Server

Today, When I was created one store procedure, I fetched one problem. I have created on store procedure which will
generate 25 digits Primary key based on date and time.

I have to generate the primary key for 100k records to insert into another table.
I was using my Store procedure to generate the ID, but it gives me error which is very familiar for us.

Violation of PRIMARY KEY constraint

Now i have searching for function or way to wait for seconds or mili seconds in sql which is vary familiar in our programming language Sleep method to wait for some time before completion of code.


Today I will explain one very small but interesting feature of SQL Server. This is not much known feature of SQL Server. In SQL Server sometime, there are requirement like I described above when T-SQL script has to wait for some time before executing next statement. It is quite common that developers depends on application to take over this delay issue. However, SQL Server itself has very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.

Official explanation of WAITFOR clause from Book Online is “Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.”

Please run the following query into your sql server query analyzer.


SELECT GETDATE() WAITFOR DELAY '00:00:00:010' 
-- wait for 10 mill seconds to run the below code SELECT GETDATE()


--
Happy programming