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.