Find the size of Index in SQL Server

sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.What if you need to capture size of individual index on a table.Thats where the DMF sys.dm_db_index_physical_stats comes handy.

This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They  are avg_record_size_in_bytes and record_count.
If these columns are multiplied the resultant is the size of  that index.

The query given below returns the name of Database, ObjectId, Objectname, IndexId, IndexDescription, Size of Index in MB, Last Updated Statistics Date and Avg Fragmentation.

SELECT DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, CONVERT(DECIMAL(16,1), (SUM(avg_record_size_in_bytes * record_count)/ (1024.0 *1024))) AS  [Size of Index(MB)], last_updated AS [Statistic last Updated], Avg_Fragmentation_In_Percent FROM (SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName', OBJECT_ID AS ObjectId, Object_Name(Object_id) AS ObjectName, Index_ID AS IndexId, Index_Type_Desc AS Index_Description, avg_record_size_in_bytes , record_count, STATS_DATE(object_id,index_id) AS 'last_updated', Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) AS 'Avg_Fragmentation_In_Percent' FROM sys.dm_db_index_physical_stats(db_id('PM_Db'), NULL, NULL, NULL, 'detailed') Where Object_id IS NOT NULL AND Avg_Fragmentation_In_Percent <> 0) T GROUP BY DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, last_updated, Avg_Fragmentation_In_Percent

Detect SQL Server Version, Edition, Product Level

SELECT  
 CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER) AS MajorVersion  
,SERVERPROPERTY('ProductVersion') AS ProductVersion  
,CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1)  
 WHEN 8 THEN 'SQL 2000'  
 WHEN 9 THEN 'SQL 2005'  
 WHEN 10 THEN 'SQL 2008'  
END AS ProductVersion  
,SERVERPROPERTY('Edition') AS Edition  
,SERVERPROPERTY('ProductLevel') AS ProductLevel  

Causes for network related errors in SQL Server

Following are the main reasons for network related errors in SQL Server.


Firewall does not allow connections to SQL Server Service.
Solution: Add exception to Firewall to allow connections to SQL Server Service.


Firewall does not allow connections to SQL Browser Service.
Solution:  Add exception to Firewall to allow connections to SQL Browser Service.


Server/Instance name is wrong.
Solution: Check for the correct name of the Server/Instance and try to connect again.


TCP/IP is disabled on the Server Machine.
Solution: Enable TCP/IP
Steps to enable TCP IP On SQL Server
Open SQL Server Configuration Manager
- All programs ->(Microsoft SQL Server 2008 R2 (or) Microsoft SQL Server 2008 (or) Microsoft SQL Server 2005 ) -> SQL Server Configuration Manager
- Expand SQL Server Network configuration
- Click on protocols for MSSQLSERVER (If its an instance click on the respective instance name)
- RightClick on TCP/IP and click on enable


SQL Server Browser Service is not running on the Server Machine.
Solution: Start the SQL Server Browser service on the Server Machine.
Steps to start the SQL Server Browser Service:
- Run -> Services.msc
- RightClick on SQL Server Browser
- Click on Start


Remote connections are disabled on SQL Server
Solution: Allow remote connections on sql server
Steps:
- Connect to SQL Server on SSMS
- RightClick on Server
- Click On Properties
- In the Server Properties Popup Select Connections
- Check the Allow remote connections to Server and click on OK


SQL Server Service is not running on the Server Machine.
Solution: Start the SQL Server service on the Server Machine.
Steps to start the SQL Server Service:
- Run -> Services.msc
- RightClick on SQL Server (MSSQLSERVER)
- Click on Start


SQL Server instance Service is not running on the Server Machine.
Solution: Start the SQL Server instance service on the Server Machine.
Steps to start the SQL Server instance Service:
- Run -> Services.msc
- RightClick on SQL Server (instancename)
- Click on Start


Invalid Credentials
Solution: Fetch the latest creadentials to login into the server or ask the administrator to give your credentials necessary oermissions on the server.


Time out errors due to network latency
Solution:  Increase Remote Query Time out
Steps:
- Connect to SQL Server on SSMS
- RightClick on Server
- Click On Properties
- In the Server Properties Popup Select Connections
- Increase the Remote query timeout value and click on OK


Ref: http://sqlserverlearner.com/2012/causes-for-network-related-errors-in-sql-server

Change all databases compatibility level at once

Below script simply gets all databases that don't have a latest compatibility level and then changes the level to latest. I am sure there could be some error handling code written in that, but it kinda does the job as it is. 


DECLARE @ServerVersion INT  
SELECT @ServerVersion = 10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))  
 -- loop databases setting compatibility mode correctly  
DECLARE GET_DATABASES CURSOR  
READ_ONLY  
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != CAST(@ServerVersion AS VARCHAR(10))  
DECLARE @DATABASENAME NVARCHAR(255)  
DECLARE @COUNTER INT  
SET @COUNTER = 1  
OPEN GET_DATABASES  
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME  
WHILE (@@fetch_status <> -1)  
BEGIN  
IF (@@fetch_status <> -2)  
BEGIN  
-- change database compatibility  
EXECUTE sp_dbcmptlevel @DATABASENAME , @ServerVersion  
PRINT  @DATABASENAME + ' changed'  
SET @COUNTER = @COUNTER + 1  
END  
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME  
END  
CLOSE GET_DATABASES  
DEALLOCATE GET_DATABASES  

DCL–Data control language

DCL means Data Control Language. DCL is used to grant , revoke permissions on SQL Server objects to the SQL Server Users and Roles.


Some example of DCL statement are : GRANT, REVOKE


GRANT - gives a user permission to perform certain tasks on database objects
REVOKE - removes a grant or deny permission from a user on certain database objects

The GRANT statement is used to give permissions to a user or role to perform certain tasks on database objects. It is possible to assign permissions to both statements as well as objects by using the GRANT statement. You can use the GRANT statement with the WITH GRANT OPTION clause to permit the user or role receiving the permission to further grant/revoke access to other accounts.

This example grants the SELECT permission on the authors table to Arun

GRANT SELECT ON authors TO Arun

The REVOKE statement is used to remove a previously granted or denied permission from a user in the current database. You can use the REVOKE statement to remove both statements and objects permissions. You can specify the GRANT OPTION FOR clause with the REVOKE statement to remove the WITH GRANT OPTION permissions. Therefore, the user will have the objects permissions, but cannot grant the permissions to other users. 




The following example revokes SELECT permissions to the authors table from the user, Arun:

REVOKE SELECT ON authors TO Arun

Error 916 : The server principal “username” is not able to access the database “database_name” under the current security context.

I got below error when trying to connect to my database using SQL Server Management Studio 2008. 


The server principal “username” is not able to access the database “database_name” under the current security context. (Microsoft SQL Server, Error: 916)


This error can be encountered when connecting to either SQL Server 2005 or 2008 databases using SQL Server 2008 Management Studio. 
The error itself indicates the problem that connecting to a database that you do not own.


This issue is fixed and already released in SQL Server 2008 SP1. Please install it for the fix.
Please try using the following workaround without installing SQL Server 2008 SP1 and let us know if that helps:

• Bring Object Explorer Details window by clicking on “View” in the Menu bar –> Select “Object Explorer Details” in menu (or hitting F7)
• In Object Explorer window click at Databases folder
• In Object Explorer Details window right-click at the column header and deselect “Collation”
• Refresh Databases folder in Object Explorer.

If the issue is not solved, uncheck the following items:
-Size (MB)
-Database Space Used (KB)
-Index Space Used (KB)
-Space Available (KB)
-Default File Group
-Mail Host
-Collation


Ref: http://sql.widestuff.com/?p=168

TEMPDB issue in SQL server

Today I had received a call from one customer that they are getting error of TempDB.


“The log file for database ‘tempdb’ is full. Back up the transaction log for the database to free up some log space.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.”


One thing we should note here that TempDB is a sensitive database so shrinking of log file may cause data corruption


So First I had taken backup of tempdb on live server downloaded it on local system and attached and change the database mode in Simple and then given following command in Local SQL Server Management Studio.


DBCC SHRINKDATABASE TempdB TRUNCATEONLY


I got a message


“Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named ’1525580473'. Check sysobjects. “


After this I had taken backup of this local Tempdb of which size was reduced drastically and again uploaded on live server and attached by stoping SQL Server and then restarted SQL Server of live server.


After this i got confirmation from customer that now itw working properly and tranasactions are getting processed fastly.

Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

Have you noticed the Context Menu , when you right click on the table in the SQL Server Management Studio Express 2008 .

The SQL Server Management Studio Express 2005 allows us to “Edit” or “View” the entire records in a table.

You might see the following options in the SQL Server Management Studio Express 2008 .
1. Select Top 1000 Rows
2. Edit Top 200 Rows

The idea to include this looks good for performance reasons .But what if you are a person like me who wants want to display all the records .

In SQL Server 2008 Management Studio Express , you can change the default settings thats allows to edit more than the 200 rows at a time, or select more than 1000 rows

To modify the “Edit Top 200 Rows” or “Select Top 1000 Rows” setttings do the following:

1. Run the SQL Management Studio Express 2008
2. Click the Tools -> Options
3. Select SQL Server Object Explorer . Now you should be able to see the options
* Value for Edit Top Rows Command
* Value for Select Top Rows Command

4. Give the Values 0 here to select/ Edit all the Records

5. Now you should see the Edit All and Select All options on the table .

Ref: http://www.ginktage.com/2010/09/sql-server-2008-change-edit-top-200-rows-and-select-top-1000-rows-to-selectedit-all/

Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

If you are assigning column values from a Select to some local values but not all columns are assigned to a corresponding local variable the following error message will come.


Server: Msg 141, Level 15, State 1, Line 2
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


For example if you are running following SELECT statement in the northwind database will generate error.


DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)


SELECT @CompanyName = [CompanyName], @ContactName = [ContactName], [ContactTitle] FROM [dbo].[Customers] WHERE [CustomerID] = 'ALFKI'


The error is caused by the [ContactTitle] because it is not assigned to a local variable.


To avoid this error you declare a local variable called contact title and assign to the contacttitle column


DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
DECLARE @ContactTitle NVARCHAR(30)


SELECT @CompanyName = [CompanyName], @ContactName = [ContactName], @ContactTitle = [ContactTitle]
FROM [dbo].[Customers] WHERE [CustomerID] = 'ALFKI'


Alternatively, if you will not be using the column, simply remove it from the SELECT statement.


DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)


SELECT @CompanyName = [CompanyName], @ContactName = [ContactName] 
FROM [dbo].[Customers] WHERE [CustomerID] = 'ALFKI'


If you really need to do both, meaning to assign the value to local variables and to return the columns as a result set, you have to do it in 2 steps instead of combining them into one SELECT statement:


DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)


SELECT @CompanyName = [CompanyName], @ContactName = [ContactName], [ContactTitle]
FROM [dbo].[Customers] WHERE [CustomerID] = 'ALFKI'


SELECT [ContactTitle] FROM [dbo].[Customers] WHERE [CustomerID] = 'ALFKI'

Connecting SQL Server on Different Port using Management Studio

We have installed two instances on one of server and while connecting from management studio default instance was getting connected as it was configured to run on 1433. 

But another instance was configured to run on 1443 port and it was not getting connected from management studio so followed below steps to resolve it.


Start SQL Server Management Studio


(in Server Name specify instance name ,port no)


e.g. 127.0.0.1:1443

Restoring SQL Server 2008 R2 Backup file to SQL Server 2008

Recently i had to restore a SQL Server 2008 R2 Database to a Database in another machine and i ended up getting the message

“The Database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running 10.00.1600″ .

Then when exploring the cause of the reason ,i found that the database that i took the backup was from SQL Server 2008 R2 .

It was the same Backup file that was used to restore in another machine and interestingly , the other machine had SQL Server 2008 .

The Version 10.50 is SQL Server 2008 R2 whereas 10.00 is SQL Server 2008.

Also , the same SQL Server Management Studio 2008 was used to access both the server instances ( 2008 and 2008 R2 ) .

It was a bit confusing for me since i was able to access the SQL Server 2008 R2 Express from the SQL Server Management Studio 2008 , but was unable to restore it to the SQL Server 2008 Express ,but then realised that since the on-disk format is different the versions and the restoring the SQL Server 2008 database to SQL Server 2008 R2 is possible . 

I also had another option to generate the Create SQL Scripts and execute it in SQL Server 2008 and 2005 and it worked fine too.
To find the version of Microsoft SQL Server 2008 , connect to SQL Server 2008 by using SQL Server Management Studio and execute the query

1	SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Ref: http://www.ginktage.com/2010/10/restoring-sql-server-2008-r2-backup-file-to-sql-server-2008/

Schedule automatic backup in SQL Server

To schedule a database backup operation by using SQL Server Management Studio in SQL Server, follow below steps:

• Start SQL Server Management Studio.
• In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.
• Click Connect.
• In Object Explorer, expand Databases.
• Right-click the database that you want to back up, click Tasks, and then click Back Up.
• In the Back Up Database – DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.
• In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.
• In the Script list, click Script Action to Job.
• In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.
• Note In the Job Step Properties – 1 dialog box, you can see the backup command.
• Under Select a page, click Schedules, and then click New.
• In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.
• Note: If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.
• Click OK two times.

You receive the following message:

The backup of database ‘DatabaseName’ completed successfully.

Note: To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this, the SQL Server Agent service must be running.

Links: http://support.microsoft.com/kb/930615

Rename SQL Server Instance Name

Run below queries to rename SQLserver Instance Name


sp_helpserver  
select @@servername  
go  
   
sp_dropserver 'OLDSNAME'  
go  
sp_addserver 'NEWNAME','local'  
go  
   
sp_helpserver  
select @@servername  
go  


Remember you have to restart the SQL Server service after executing this.

SQL Server Browser not working properly

Today my SQL Server cluster stopped answering on a named instance, I restarted SQL Server Browser but it didn't started.


So I started the process from console using -C argument in order to get more information.


C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c


I noticed that query against instance 1 generated CLNT_UCAST_INST and CLNT_BCAST_EX packets but query against instance 2 generated only CLNT_UCAST_INST packets.


I googled for this and then I found a post saying to delete this registry key:


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.2], 
I did it and then I restarted service, and now my SQL server is running perfectly.

Foreign Keys without Index

Run below script to generate a create-script for inserting indexes – over all tables in the database.


SELECT  
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['  
+ IndexTables.[name]  
+ ']'') AND name = N''NCI_'  
+ IndexTables.[name] + '_' + IndexColumns.[name]  
+ ''') '  
+ 'CREATE NONCLUSTERED INDEX [NCI_'  
+ IndexTables.[name] + '_' + IndexColumns.[name]  
+ '] ON [dbo].['  
+ IndexTables.[name]  
+ ']( ['  
+ IndexColumns.[name]  
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'  
FROM sys.foreign_keys ForeignKeys  
INNER JOIN sys.foreign_key_columns ForeignKeyColumns  
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id  
INNER JOIN sys.columns IndexColumns  
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id  
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id  
INNER JOIN sys.tables IndexTables  
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id  
ORDER BY IndexTables.[name], IndexColumns.[name]  

Clustered SQL Server fail to start

Today I got the following error:


The SQL Server (INST1) service terminated with service-specific error 10048 (0x2740).


and also


Server is listening on [ 192.168.51.27 1433].
Server TCP provider failed to listen on [ 192.168.51.27 1433]. Tcp port is already in use.
TDSSNIClient initialization failed with error 0x2740, status code 0xa.


The root cause of this issue is because of cluster checkpoint service behavior. If a setting is changed while the resource is online, that change will get check pointed to the CPT have file in the cluster quorum disk.
To fix it follow the instructions specified on: http://support.microsoft.com/default.aspx?scid=kb;EN-US;912397


cluster res "SQL Server" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"


Correct the protocol parameter for the clustered instance of SQL Server on all nodes


cluster res "SQL Server" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

Releasing unused memory in SQL server

As SQL Server does automatic Memory management , it means that SQL Server will ask for memory from the OS as and when it requires, and will keep it as long as it can. This is intended and a default behavior of SQL Server. The memory component (Buffer Pool) does this. So, when does SQL Server release memory back to the OS? It releases memory back to the OS when any other process will ask for memory from the OS. Despite this automatic memory management, there are 2 commands that can be used to free up memory in SQL Server.


DBCC FREEPROCCACHE :- To flush out execution plans from the procedure cache &


DBCC DROPCLEANBUFFERS :- To flush out data pages from the memory.

How to make Like case sensitive

I have one column (Type: nVarChar) in a table which has data something like this:
A1
A2
A3
a4
a5
A6
B1
b2
CA
CB
c9


I want to show all the rows which have all the upper case letters in the above mentioned column.


The following query works:


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[ABC]%' Collate Latin1_General_CS_AI  


And Surprisingly this one does not:


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[A-C]%' Collate Latin1_General_CS_AI  --Would actually be [A-Z]


Is it like the case sensitivity does not work with ranges i.e. [A-C] or I am missing something here?


Here is my answer:


Can you try


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[A-C]%' Collate Latin1_General_BIN


  this should give you case sensitive result.


The reason is that  [A-C] means include all characters which is bigger or equal than A, and smaller than C,  so lowercase a is also in the range even according to linguistic order for Latin1_General_CA_A. In other case, for case sensitive collation,  A is not equal to a, not  the order of the characters might still be  a <A <b < B <c <C, etc.

Is My Computer/Server Running 32-bit or 64-bit?

Windows 7
Click Start
Click Control Panel
Click System
The operating system is displayed as follows:
For a 64-bit version operating system: 64-bit Operating System appears for the System Type under System
For a 32-bit version operating system: 32-bit Operating System appears for the System Type under System


Windows Server 2008
Click Start
Click Control Panel
Click System
The operating system is displayed as follows:
For a 64-bit version operating system: 64-bit Operating System appears for the System Type under System
For a 32-bit version operating system: 32-bit Operating System appears for the System Type under System


Windows Vista
Click Start
Click Control Panel
Click System
The operating system is displayed as follows:
For a 64-bit version operating system: 64-bit Operating System appears for the System Type under System
For a 32-bit version operating system: 32-bit Operating System appears for the System Type under System


Windows XP
Click Start, and then click Run
Type sysdm.cpl, and then click OK.
Click the General tab. The operating system is displayed as follows:
For a 64-bit version operating system: Windows XP Professional x64 Edition Version Year appears under System.
For a 32-bit version operating system: Windows XP Professional Version Year appears under System.


Windows Server 2003
Click Start, and then click Run
Type sysdm.cpl, and then click OK.
Click the General tab. The operating system is displayed as follows:
For a 64-bit version operating system: Windows Server 2003 Enterprise x64 Edition appears under System.
For a 32-bit version operating system: Windows Server 2003 Enterprise Edition appears under System.

CPU usage by each database

Below script shows all CPU usage of all session for all database at a certain time:


DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid


SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

Error 3023

Today I got below error while executing a backup query 
BACKUP LOG MyDB WITH TRUNCATE_ONLY


I got the following error:


Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.


I checked the sysprocesses and I found a backup background process launched from someone else. so I waited for the process to end and above query worked. 

Auto generate change scripts in SQL Server Management Studio

SQL Server Management Studio has a feature that lets you to automatically generate Create or Alter scripts when using the table designer .

By deafult , this option is disabled . You can enable it by following the below steps

1. Start SQL Server Management Studio

2. Click Tools -> Options

3. Click Designers in the TreeList in the Options Dialog and select the option “Auto Generate Change Scripts” and click OK

4. Now , for test , try adding a column to an existing table and Save it . You will be prompted to save the change script .

Ref: http://www.ginktage.com/2012/04/auto-generate-change-scripts-in-sql-server-management-studio/

Unable to Connect to SQL Server. sql server protocol asp.net sqlconnection sql c#.net sms system management services

For system management services we require sql server named pipes network support.
Because system management services have to communicate with sql server which stores the system management server database. SMS utilizes names pipes by default.


It is important that the client net library which establishes the connection must match the server net library which accepts the connection. It is important that both client and server must use the same protocol.


If named pipes network support is not installed then system management services may failed to start and produce the following error message.


Unable to connect to SQL Server.


To correct this error follow the following steps:


1. Start the sql client configuration utility.


2. Select net Library tab.


3. Select Named pipes in the default network drop-down combobox.


4. Click done




To check sql setup follow these steps


1. Run Sql server setup and click continue.


2. Select change network support and choose continue.


3. Select the network protocol which you wants to use in case named pipes.


4. The named pipe name dialog box appears. Click continue


5. Select Exit


Ref: http://sqlerrormessages.blogspot.in/

Msg 130 - Cannot perform an aggregate function on an expression

Sometime you may got the below error when running select query:


Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression 
containing an aggregate or a subquery.


The reason of this error is included in the error message itself. This error is comming when you are performing an aggregate function such as MAX,MIN,SUM etc on a subquery. Or on another expression which is using an aggregate function.


Example
Suppose we have a table called ExamResult with ExamID, StudentID, Number. From this table, you want to determine which exams or tests have the lowest and highest deviation in terms of the Numbers of the students to determine the tests which are too easy or too hard for the students.To determine the Number deviation, you used the difference between the lowest Number received and the highest Number received for each test. You used the following SELECT statement for this purpose:


SELECT [ExamID],
MAX(MAX([Number]) - MIN([Number])) AS [HighDeviation],
MIN(MAX([Number]) - MIN([Number])) AS [LowDeviation]
FROM [dbo].[ExamResult]
GROUP BY [ExamID]


Then the error will come as above. If you simply want the test with the highest deviation in the Numbers, you can simply sort the output by the difference between the highest Number and lowest Number in descending order, as follows:


SELECT [ExamID], MAX([Number]) - MIN([Number])
FROM [dbo].[ExamResult]
GROUP BY [ExamID]
ORDER BY MAX([Number]) - MIN([Number]) DESC


Or you can use the relative position of the column in the SELECT list to sort in the ORDER BY clause:


SELECT [ExamID], MAX([Number]) - MIN([Number])
FROM [dbo].[ExamResult]
GROUP BY [ExamID]
ORDER BY 2 DESC


Similarly, if you simply want the test with the lowest deviation in the Numbers, you can simply sort the output by the difference between the highest Number and lowest Number in ascending order, as follows:


SELECT [ExamID], MAX([Number]) - MIN([Number])
FROM [dbo].[ExamResult]
GROUP BY [ExamID]
ORDER BY MAX([Number]) - MIN([Number]) ASC


Now, if you want to return the highest and lowest deviation for each exam in a single result set, you have to use a sub-query or a derived table for this purpose:


SELECT [ExamID], MAX([Deviation]) AS [HighestDeviation],
MIN([Deviation]) AS [LowestDeviation]
FROM (SELECT [ExamID], MAX([Number]) - MIN([Number]) AS [Deviation]
FROM [dbo].[ExamResult]
GROUP BY [ExamID]) A
GROUP BY [ExamID]
ORDER BY [ExamID]