Behavior of SQL Server default instance on a NON-Default port

Unable to connect to SQLServer listening on Non-Default Port?  If you connect to SQLServer using the “hostname” it connects to the instance which is listening on 1433. If you make named instance listen on 1433 and connect using “hostname” it connects to named instance which is listening on 1433. if you connect to SQLServer using…

1

Queries with table variables are slow in SQL2005 compared with SQL2000 (Table Variables (VS) Temp Tables)

Table Variables (VS) Temp Tables SQLServer2005 caches temp tables and temp variables only under some conditions. Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000. Following are scenarios where temp table/variable are not cached:1. select into #t2. alter table #t3. create index on #t4. Global temp tables…

2

How to Browse (or) view objects and there code in mssqlsystemresource Database

How to Browse (or) view mssqlsystemresource Database. 1. Stop the SQL Server service    Copy the mssqlsystemresource.MDF and mssqlsystemresource.LDF to a new path (This    two files will be in same path where master databse is located).   Start the SQL Server Service   Ues the following command to attach the data and log file as a new…

2

How to enable Constraint delegation for SQLServer2005

Constraint delegation for SQLServer2005 Follow the below steps to set the constraint delegation for SQLServer2005 1. In active directory users and computers for the startup account of SQLServera.Select “Trust this user for delegation to specified services only”b.Add host service for each node of the cluster2. In active directory users and computers for each node under…


Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher

Error=== 2008-03-19 23:06:23 – ? [100] Microsoft SQLServerAgent version 9.00.3186.00 (x86 unicode retail build) : Process ID 24282008-03-19 23:06:23 – ? [101] SQL Server SNETNAME version 9.00.3186 (0 connection limit)2008-03-19 23:06:23 – ? [102] SQL Server ODBC driver version 9.00.30422008-03-19 23:06:23 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is…

5

Unable to open SQL2000 DTS packages in SQLserver2008 SSMS/BIDS 2008

Problem=======When we open a SQL2000 DTS package in SQL2008 SSMS/BIDS It fails with below error. Error: ——–SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)   Resolution========1.Install SQL Server 2000 DTS Designer Components, Microsoft SQL Server…

9