Miscellaneous Problems and Solutions

When client not connect to SQL Server instance The following post describes about the troubleshoot steps involved when we find that the SQL server management studio is not getting connected to the sql server 2005 instance (using TCP/named pipes) The following error comes sometimes when we try to connect to remote SQL server : 1. First check whether the firewall is turned on. If it is then you have to do the following settings :    1. Add an exception for sqlsrvr.exe and sqlbrowser.exe (must be in Program Files/Microsoft SQL Server/ 90/shared)    2. Add a port number 1432 (it is the port on which SQL database listens the requests coming from its clients). 2. Go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. From the left pane, click on Protocols for . Enable Shared Memory, Named pipes, TCP/IP. 3.  Go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration. Click on "Surface area configuration for services and connections". Under MSSQL -> Database Engine, select Remote connections and choose "Using both TCP/IP and named pipes". So with these steps your SQL client would be getting connected to remote sql server.

The hierarchical query and SQL Server 2005 We can create hierarchical queries using CTE Here is the common example : create table Employees ( id int primary key nonclustered, name varchar(50), boss int constraint FK_boss foreign key references employees(id) ) insert into employees values(1, 'Company CEO', null) insert into employees values(2, 'Manager 1', 1) insert into employees values(3, 'Manager 2', 1) insert into employees values(4, 'Manager 3', 1) insert into employees values(5, 'Asst. Manager', 2) insert into employees values(6, 'TeamLead1', 5) insert into employees values(7, 'TeamLead2', 5) insert into employees values(8, 'TeamMember1', 6) insert into employees values(9, 'TeamMember2', 6) insert into employees values(10, 'TeamMember3', 6) insert into employees values(11, 'TeamMember4', 7) insert into employees values(12, 'TeamMember5', 7)  declare @employee_id as smallint  set @employee_id = 6 ;with report(id, name) AS ( SELECT id, name from employees where boss =  @employee_id union all SELECT E.id, E.name from employees E JOIN report R on R.id = E.boss ) Select * from report

Derived tables Benefits of Derived Tables The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance. Here are the steps when you use a temporary table: 1) Lock tempdb database 2) CREATE the temporary table (write activity) 3) SELECT data & INSERT data (read & write activity) 4) SELECT data from temporary table and permanent table(s) (read activity) 5) DROP TABLE (write activity) 4) Release the locks Compare the above to the number of steps it takes for a derived table: 1) CREATE locks, unless isolation level of "read uncommitted" is used 2) SELECT data (read activity) 3) Release the locks  Using derived tables instead of temporary tables reduces disk I/O and can boost performance

ORDERED View A. SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can't fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround: CREATE VIEW AuthorsByNameASSELECT TOP 100 PERCENT *FROM authors ORDER BY au_lname, au_fname The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it is used in conjunction with the TOP keyword. Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.

table has identity field? Posted on Mar. 17 2008 When client not connect to SQL Server instance Posted on Aug. 20 2008 The following post describes about the troubleshoot steps involved when we find that the SQL server management studio is not getting connected to the sql server 2005 instance (using TCP/named pipes) The following error comes sometimes when we try to connect to remote SQL server : 1. First check whether the firewall is turned on. If it is then you have to do the following settings :    1. Add an exception for sqlsrvr.exe and sqlbrowser.exe (must be in Program Files/Microsoft SQL Server/ 90/shared)    2. Add a port number 1432 (it is the port on which SQL database listens the requests coming from its clients). 2. Go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. From the left pane, click on Protocols for . Enable Shared Memory, Named pipes, TCP/IP. 3.  Go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration. Click on "Surface area configuration for services and connections". Under MSSQL -> Database Engine, select Remote connections and choose "Using both TCP/IP and named pipes". So with these steps your SQL client would be getting connected to remote sql server. The hierarchical query and SQL Server 2005 Posted on Mar. 20 2008 We can create hierarchical queries using CTE Here is the common example : create table Employees ( id int primary key nonclustered, name varchar(50), boss int constraint FK_boss foreign key references employees(id) ) insert into employees values(1, 'Company CEO', null) insert into employees values(2, 'Manager 1', 1) insert into employees values(3, 'Manager 2', 1) insert into employees values(4, 'Manager 3', 1) insert into employees values(5, 'Asst. Manager', 2) insert into employees values(6, 'TeamLead1', 5) insert into employees values(7, 'TeamLead2', 5) insert into employees values(8, 'TeamMember1', 6) insert into employees values(9, 'TeamMember2', 6) insert into employees values(10, 'TeamMember3', 6) insert into employees values(11, 'TeamMember4', 7) insert into employees values(12, 'TeamMember5', 7)  declare @employee_id as smallint  set @employee_id = 6 ;with report(id, name) AS ( SELECT id, name from employees where boss =  @employee_id union all SELECT E.id, E.name from employees E JOIN report R on R.id = E.boss ) Select * from report Derived tables Posted on Mar. 17 2008 Benefits of Derived Tables The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance. Here are the steps when you use a temporary table: 1) Lock tempdb database 2) CREATE the temporary table (write activity) 3) SELECT data & INSERT data (read & write activity) 4) SELECT data from temporary table and permanent table(s) (read activity) 5) DROP TABLE (write activity) 4) Release the locks Compare the above to the number of steps it takes for a derived table: 1) CREATE locks, unless isolation level of "read uncommitted" is used 2) SELECT data (read activity) 3) Release the locks  Using derived tables instead of temporary tables reduces disk I/O and can boost performance ORDERED View Posted on Mar. 17 2008 A. SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can't fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround: CREATE VIEW AuthorsByNameASSELECT TOP 100 PERCENT *FROM authors ORDER BY au_lname, au_fname The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it is used in conjunction with the TOP keyword. Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard. table has identity field? Is there a way to read a system table to determine if a specific table has an identity column? User the following SELECT OBJECTPROPERTY(OBJECT_ID('tablename'), 'TableHasIdentity'); Returns 1 or 0. Is there a way to read a system table to determine if a specific table has an identity column? User the following SELECT OBJECTPROPERTY(OBJECT_ID('tablename'), 'TableHasIdentity'); Returns 1 or 0.

Updating top n records

Sometimes we need to update top n records, try using following synatax:
update top (5) [aspnet_Membership] set IsLockedOut =1

Synonyms

SQL Server 2005's synonyms allow you to give an alias to an already existing object. Realize the benefits of synonyms by using them as a layer of abstraction between the underlying objects and the synonym.Suppose we have a table named 'SalesHistoryOfITDepartmentYear2008'.So instead of writting queries using this long name we can make make a synonym for this table.

CREATE SYNONYM SalesHistoryFOR SalesData.dbo.SalesHistoryOfITDepartmentYear2008;

Lets understand the use of Synonym with example.Due to expanding business and scalability needs, you decide that it's a good idea to remove the SalesHistory table from your current database named Inventory and put it in a separate database named SalesData. There is a lot of code in the Inventory database that references the SalesHistory table. Most of this code is simple queries that only read data from the SalesHistory table. The project deadline is in the very near future, so it will be difficult to change all existing code to reference the table in the new database.So we can use a synonym to drastically reduce your development time.
Assume that you have already copied your SalesHistory table into the new SalesData database and have renamed the current SalesHistory table in the Inventory database. (I always prefer renaming a table that I intend to delete first. This gives me the opportunity to discover any errors on the system caused by removing a table.)
Once you rename the SalesHistory table, you can create a synonym that references to the SalesHistory table that you have placed in the new database. The script is below.

USE InventoryGOCREATE SYNONYM SalesHistoryFOR SalesData.dbo.SalesHistory;
This synonym has created a pointer to the SalesHistory table in the new SalesData database; however, because the synonym is in the Inventory database and is named SalesHistory, any queries or DML statements executed on the SalesHistory synonym are actually executed against the SalesHistory table in the SalesData database

Synonyms

SQL Server 2005's synonyms allow you to give an alias to an already existing object. Realize the benefits of synonyms by using them as a layer of abstraction between the underlying objects and the synonym.Suppose we have a table named 'SalesHistoryOfITDepartmentYear2008'.So instead of writting queries using this long name we can make make a synonym for this table.

CREATE SYNONYM SalesHistoryFOR SalesData.dbo.SalesHistoryOfITDepartmentYear2008;

Lets understand the use of Synonym with example.Due to expanding business and scalability needs, you decide that it's a good idea to remove the SalesHistory table from your current database named Inventory and put it in a separate database named SalesData. There is a lot of code in the Inventory database that references the SalesHistory table. Most of this code is simple queries that only read data from the SalesHistory table. The project deadline is in the very near future, so it will be difficult to change all existing code to reference the table in the new database.So we can use a synonym to drastically reduce your development time.
Assume that you have already copied your SalesHistory table into the new SalesData database and have renamed the current SalesHistory table in the Inventory database. (I always prefer renaming a table that I intend to delete first. This gives me the opportunity to discover any errors on the system caused by removing a table.)
Once you rename the SalesHistory table, you can create a synonym that references to the SalesHistory table that you have placed in the new database. The script is below.

USE InventoryGOCREATE SYNONYM SalesHistoryFOR SalesData.dbo.SalesHistory;
This synonym has created a pointer to the SalesHistory table in the new SalesData database; however, because the synonym is in the Inventory database and is named SalesHistory, any queries or DML statements executed on the SalesHistory synonym are actually executed against the SalesHistory table in the SalesData database

multi-criteria filtering without dynamic SQL

Sometimes we're developing applications that require performing searches against database tables in SQL. The search must enable using a set of optional parameters (up to 10 parameters for example) as the main search criteria (a null value indicates not to filter the data, and a non-null value indicate to filter the data by the underlying value). Some developers use complicated T/SQL statements (a handful of if and if/else statements) to implement this requirement, thus making the code illegible. Because I have to deal with this kind of problems, now I'd like to suggest you an alternative technique that I use in my solutions.

Let's supposed that we have the following table:create table CIS_CLIENT( ID int primary key, first_name varchar(50) not null,
last_name varchar(50) null,
case_officer_fk int,
branch_fk int
);

Now, our requirements indicate the option to look up for information by specifying any criteria value (the search parameters are based on the first name , last name, case officer of the client and the the branch office to whom it belongs to).

CREATE PROC st_Search_clients
(
@clientID int = null, @first_name varchar(50) not null,
@last_name null,
@case_officer_fk int,
@branch_fk int
)
as
BEGIN
SELECT * FROM cis_client client
WHERE
(@clientID is null OR @clientID = client.id) AND
(@first_name is null OR @first_name = client.first_name) AND
(@last_name is null OR @last_name = client.last_name ) AND
(@case_officer_fk is null OR @case_officer_fk = client.case_officer_fk) AND
(@branch_fk is null OR @branch_fk = client.branch_fk )
END

The same thing can also be achieved by COALESCE function in where clause :
client.id = COALESCE(@clientID, client.id) AND client.first_name = COALESCE(@first_name, client.first_name) AND client.last_name = COALESCE(@last_name, client.last_name) AND client.case_officer_fk = COALESCE(@case_officer_fk,client.case_officer_fk) AND client.branch_fk = COALESCE(@branch_fk, client.branch_fk)

The hierarchical query and SQL Server 2005

We can create hierarchical queries using CTE
Here is the common example :
create table Employees
( id int primary key nonclustered, name varchar(50), boss int constraint FK_boss foreign key references employees(id)
)insert into employees values(1, 'Company CEO', null) insert into employees values(2, 'Manager 1', 1)
insert into employees values(3, 'Manager 2', 1)
insert into employees values(4, 'Manager 3', 1)
insert into employees values(5, 'Asst. Manager', 2)
insert into employees values(6, 'TeamLead1', 5)
insert into employees values(7, 'TeamLead2', 5)
insert into employees values(8, 'TeamMember1', 6)
insert into employees values(9, 'TeamMember2', 6)
insert into employees values(10, 'TeamMember3', 6)
insert into employees values(11, 'TeamMember4', 7)
insert into employees values(12, 'TeamMember5', 7) declare @employee_id as smallint
set @employee_id = 6
;with report(id, name)
AS
( SELECT id, name from employees where boss = @employee_id
union all
SELECT E.id, E.name from employees E JOIN report R on R.id = E.boss
)
Select * from report