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]

The Rollback Transaction request has no corresponding Begin Transaction

Sometimes while using transaction in SQL Sesrver you get the following error:


The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.


The main reason for this error is that there is no Transaction is running but you had issued Rollback or Commit transaction.

When my Database is last accessed

The below script uses sys.dm_db_index_usage_stats to get timestamps for the last read and write operations on a database. 


SELECT  DB_NAME(database_id) AS DatabaseName, MAX(CASE WHEN ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_scan,'1900-01-01') AND ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_seek,'1900-01-01')  WHEN ISNULL(last_user_scan,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_scan,'1900-01-01')   ELSE ISNULL(last_user_lookup,'1900-01-01') END) AS LastReadOperation, MAX(last_user_update) AS LastWriteOperation  
FROM sys.dm_db_index_usage_stats GROUP BY DB_NAME(database_id)