Msg 157 - An aggregate may not appear in the set list of an UPDATE

If you are trying to update a column using an aggregate function like SUM, MAX or MIN etc the following error will come.


Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.


To understand this you try the following example. Suppose you have two tables called ProductMaster and StockTransaction respectively. And ProductMaster table have the fields ProductID, ProductName, Stock, LastTransactionDate. And StockTransaction table have the fields called ProductID, Quantity, TransactionDate


The first table contains all the Products with the current Stock and the last transaction date. The second table contains all the transactions performed on those Products. You are trying to update the Stock and Last Transaction Date columns of your ProductMaster table using the following UPDATE statement:


UPDATE A
SET [Stock] = SUM([Quantity]),
[Last TransactionDate] = MAX([TransactionDate])
FROM [dbo].[ProductMaster] A INNER JOIN [dbo].[StockTransaction] B
ON A.[ProductID] = B.[ProductID]


To update a table with the aggregate values of another table you should use a sub-query:


UPDATE A
SET [Stock] = B.[Quantity],
A.[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[ProductMaster] A INNER JOIN 
(SELECT [ProductID], SUM([Quantity]) AS [Quantity],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[StockTransaction]
GROUP BY [ProductID]) B
ON A.[ProductId] = B.[ProductID]

How to sent email by using Sql Server

Sql Mail System provides an easy way to sent and receive emails using microsoft sql server. But you should have to install a MAPI susystem in the server which is comming automatically when you install windows NT. If you are using windows 2000 you should have to install an mapi client like microsoft outlook


There are other methods to send emails by using Microsoft sql server. You can configure CDONT(Collaboration Data Objects for NT Server),CDOSYS(Collaboration Data Objects for Windows 2000 ) to send emails directly from sql server. CDOSYS is available from windows 2000 and CDONTS is not supported by windows server 2003 and later Operating systems.


Confugure CDO for sending email using Microsoft Sql Server
CDONTS is a mail transfer protocol. CDONTS sends mail through SMTP. If you do not have SMTP you can use the SMTP.To use CDONTS to send email from sql server do the following steps.


1. Install iis in your coputer which running sql server.


2. Specify SMTP mail server as your "smart-host".


3. Create a stored procedure which can send mail.


When using CDONTS you need not have to use outlook in your server. To confugure smart host follow the steps below


1. start->programs->administrative tools->internet service manager 


2. It will open the IIS manager a tree. Right click on the Default SMTP Virtual Server. Select properties.


3. Locate delivery tab->Advanced


4. Type SMTP mail server name in smart host textbox.


5. Make sure SMTP service is running.


Sample Stored Procedure which is sending CDONTS mail


CREATE PROCEDURE [dbo].[sp_send_cdontsmail] 
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID


Send Email using CDOSYS


CDOSYS provides enhancement on CDONTS mail. CDOSYS can configure programmatically so it is flexible. 


Sample Stored procedure sending CDOSYS Mail


IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].[cdosysmail_failures]
GO
-- Create new cdosysmail_failures table
CREATE TABLE [dbo].[cdosysmail_failures]
([Date of Failure] datetime, 
[Spid] int NULL,
[From] varchar(100) NULL,
[To] varchar(100) NULL,
[Subject] varchar(100) NULL,
[Body] varchar(4000) NULL,
[iMsg] int NULL,
[Hr] int NULL,
[Source of Failure] varchar(255) NULL,
[Description of Failure] varchar(500) NULL,
[Output from Failure] varchar(1000) NULL,
[Comment about Failure] varchar(50) NULL)
GO


IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].[sp_send_cdosysmail]
GO


CREATE PROCEDURE [dbo].[sp_send_cdosysmail] 
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************


This stored procedure takes the parameters and sends an e-mail. 
All the mail configurations are hard-coded in the stored procedure. 
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp


***********************************************************************/ 
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)


--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END


--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- This is to configure the Server Name or IP address. 
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', cdoSMTPServerName 
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update') 
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To') 
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From') 
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0 
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END




-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg


-- handle the failure of the destroy if needed
IF @hr <>0 
BEGIN
select @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT


-- if sp_OAGetErrorInfo was successful, print errors
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
END


-- else sp_OAGetErrorInfo failed
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE 
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
RETURN
END


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

advantages and disadvantages of Indexed views

Advantages of indexed views

• To Improve the performance of select queries
• Index data on columns on multiple tables
• The data from multiple tables is denormalized and helps in fastening the queries that fetch data
• Avoid Complex joins at run time and make the joined data readyly available
• Joins that are frequently used can be converted into indexed views and thus reducing the query resopnse time of multiple queries
• Advantage of having Higher Disk Space can be converted into having high query performance using Indexed views

Disadvantages of indexed views

• Increases the disc space used by the database as the views are created with physical data
• Slows down the performance of the insert,update,delete statements on the tables used in indexed views.

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Today I encountered this error when my SQL Server instance wouldn't start after I made some changes in connection yesterday. 

After checking the logs, I received the following error:
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

So, I went back to undo each change I had made, and noticed that the last change I made was to enable VIA as a protocol for my SQL Server instance. Disabled that, and SQL Server now starts fine.

Start, Stop or Restart SQL Server from Command Prompt

Start SQL Server from command prompt:


net start mssqlserver


If you try to start a SQL Server which is already started then you will get this error: 



The requested service has already been started.
More help is available by typing NET HELPMSG 2182.

Stop SQL Server from command prompt:


net stop mssqlserver



If you try to stop a SQL Server which is already stopped then you will get this error: 


The SQL Server (MSSQLSERVER) service is not started.
More help is available by typing NET HELPMSG 3521.

Restart SQL Server from command prompt:


net stop mssqlserver
net start mssqlserver

Msg 107 - The column prefix does not match with a table name or alias name used in the query.

Sometime you may get the following error:


Server: Msg 107, Level 16, State 3, Line 1
The column prefix does not match with a table name or alias name used in the query.


The main reason for this error is using table name as prefix of a column name and that table name is not included in the from clause. Another reason is you may be assigning an alias to the table name and prefix is still the table name.


Example


SELECT [Employees].[FirstName], [Customers].[LastName] FROM [dbo].[Customers]


In the above cause I had used prefixed as customers and employees but in from clause theire is only customers.


To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.


SELECT [Customers].[FirstName], [Customers].[LastName] FROM [dbo].[Customers]


To illustrate the second cause of this error, the following SELECT statement will generate the error:


SELECT [Customers].[FirstName], [Customers].[LastName] FROM [dbo].[Customers] Cust


Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:


SELECT [Cust].[FirstName], [Cust].[LastName] FROM [dbo].[Customers] Cust

Difference Between SET and SELECT

SELECT and SET statements are similar in many ways but there are some differences between them.

• SELECT has better performance over SET when used for assigning values to multiple variables at the same time.
• SET is ANSI Standard for value assignment to variables but SELECT is not an ANSI Standard for variable assignment.
• SET can be used to assign value to one variable at a time but SELECT can be used to assign values to multiple variables in a single SELECT statement.
• When an output of a query is used to assign values to a variable then SET Statement would fail and give an error if multiple rows are returned by the query but the SELECT statement would assign the last result of the query to the the variable.
• If the variable is initially assigned a value following is the behavior of variable assignment using SET and SELECT.

SET – Assigns null if the query does not return any rows.
SELECT – Retains the initially assigned value and does not assign null if the query does not return any rows.

Error showing on windows vista when connecting to sql server in remote server : Specified SQL server not found or Error Locating Server/Instance Specified

Sometime when you are trying to connect a client computer running windows vista or windows server 2008 to a named instance of sql server, the connection may fail. If you are using windows data access component 6.0 to connect to named instance of sql server, you will receive the following error message.


[DBNETLIB]Specified SQL server not found.
[DBNETLIB]ConnectionOpen (Connect()).


And if you are using sql native instance to connect with named instance of sql server you will receive the following error message.


[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified
[SQL Native Client]Login timeout expired.


In connecting the client sends a UDP request to the Ip address of the named instance. Then sql browser returns a Udp response which contains information about endpoints. However, in the UDP response packet, the source IP address may not be the IP address to which the UDP request packet was sent. If the named instance is a failover cluster instance, the source IP address is the IP address of the physical computer instead of the virtual IP address of the remote server. If the remote server has multiple IP addresses, the source IP address may be any of the IP addresses that are assigned to the remote server.


There are lots of ways to solve this error.


Specify the TCP portname or named pipename in the connection string


Some other ways also there to solve this error


In Windows Firewall with Advanced Security in Control Panel, create an outgoing rule for the application that connects to SQL Server. To do this, follow these steps:


1. Take the control pannel.


2. Double click on administrative tool


3. In the administrative tools double click Windows Firewall with Advanced Security.


4. In Windows Firewall with Advanced Security, click Outbound Rules, and then click New Rule.


5. Click Program, and then click Next.


6. Click This program path, specify the path of the application, and then click Next.


7. Click Allow the connection, and then click Next.


8. Complete the steps of the New Outbound Rule Wizard


In Windows Firewall with Advanced Security in Control Panel, create an incoming rule that allows for traffic from all possible IP addresses of the remote server or from all possible IP addresses that are configured for the failover cluster instance. To do this, follow these steps:


1. In Control Panel, double-click Administrative Tools.


2. In Administrative Tools, double-click Windows Firewall with Advanced Security.


3. In Windows Firewall with Advanced Security, click Inbound Rules, and then click New Rule.


4 Click Custom, and then click Next.


5. Click All programs, and then click Next.


6. In the Protocol type list, click Any, and then click Next.


7. Under Which remote IP addresses does this rule match, click These IP addresses, and then click Add.


8. In the IP Address dialog box, type one of the IP addresses under This IP address or subnet, and then click OK.


9. To add other IP addresses, repeat steps 7 through 8, and then click Next.


10. Click Allow the connection, and then click Next.


11. Complete the steps of the New Inbound Rule Wizard

The media family on device is incorrectly formed. SQL Server cannot process this media family Error: 3241

Sometime when you try to restore a database backup, you got the following error


The media family on device ” is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)


Above error comes due to following reasons:


Backup file is corrupted.
Solution: If this is the problem then take backup again.


Backup is taken on a higher version of SQL Server and restore is done in some lower version.
Solution: Upgrade the SQL Server where you want to restore.

Error converting data type varchar to int

When inserting or updating data into a table then you may come across this error "Error converting data type varchar to int". As the name indicates this error is comming because you have a field which is integer and while querying he passes any data which is not convertible to int 


Solutions


The first and best solution is to make sure that the data passing to sql server is compatible with datatype. i.e. if passing a string to an int field it should be convertible to integer say '0','222' etc


And if trying to copy data from one table to another there is chance to fire this error if either the column mapping is not correct or there is null in the source field


If there is null in the source field use isnull function. See the following query


INSERT INTO Table1(id) SELECT ISNULL(id, 0) FROM Table2

Find the tables without primary Key

It is generally mandatory to have clustered indexes in each of the tables. It helps in performance optimization, and in most cases it is the best starting point for a table.


Now the tedious job is to find the table names from existing database in which primary key (clustered index) is not implemented, the manual process is to right click on each of the table and go in design view to check it.


We can also do it by executing below T-SQL in the database; it will show the table names in which primary key are not implemented.


SELECT SCHEMA_NAME(o.schema_id) AS [schema], object_name(i.object_id ) AS [table], p.rows, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.OBJECT_ID INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = 'HEAP' ORDER BY rows desc

Invalid operator for data type. Operator equals add, type equals text.

Today I got the below error when I tried to concatenate two columns of VarChar datatype. 


Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type.
Operator equals add, type equals text.


Example


I have a table called StudentComments with fields StudentID, CommentsOnTeacher, CommentsOnSubject. If we are running the following select statement will get error


SELECT [StudentID], [CommentsOnTeacher] + [CommentsOnSubject] AS [AllComments] FROM [dbo].[StudentComments]


To work around this error you need to CAST the VarChar column into VarChar first before concatenating the columns. The following query will avoid this error:


SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VarChar(8000)) +
CAST([CommentsOnSubject] AS VarChar(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]


The only drawback with this work around is that you are limiting both TEXT columns to 8000 characters each. One other way to work around this is to do the concatenation in the client application instead of having SQL Server do the concatenation.

Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

We may get this error message when there is high workload on the server. And servers are experiencing high memory pressure.


In this error there are some additional symptoms also.


1. When connecting to server will get the error message as "Login Failed".


2. Will get disconnected from server.


3. CPU usage will be very high.


4. if running "select * from sysprocesses" SPIDs will have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.


5. The System Monitor object SQLServer:Memory Manager displays a non-zero value for Memory Grants Pending.


6. SQL Profiler displays the event "Execution Warnings" that includes the "Wait For Memory" or the "Wait For Memory Timeout" text.


Reasons for this error is memory intensive queries are getting qued and are not getting resources before timeout period. And after timout period and getting timeout. By default query wait period is -1 by setting non-negative number you can improve the query wait time.


Other reasons for this errors are not properly optimized queries, memory allocation for sql server is too small.


Solutions for this error include the following.


1. Optimize the performance of queries using sql profiler. 


2. Distribution statistics should be uptodate.


3. Watch the system monitor trace to see the memory usage of sql server.

Database is in transition

Today I had detached a database in SQL SERVER USING sp_detach. 
The operation should have been pretty instant, but after a few minutes I realised something was up.


The database was stuck 'in transition', or so it appeared to Management Studio. I contemplated what to do. I wanted to avoid restarting the service and leaving the db in an inconsistent state, and hence googled the following...


Error 952 Database is in Transition 


As it happens, the database isn't stuck at all, it's just Management Studio thinks it is.
solution. RESTART MANAGEMENT STUDIO.   

List all Primary keys column

Run below script to get list of all primary keys column:

SELECT  
sysobjects.name AS TableName  
,sysindexes.name AS PKName  
,syscolumns.colid AS ColumnOrder  
,index_col(object_name(sysindexes.id), sysindexes.indid,syscolumns.colid) AS ColumnName  
FROM sysobjects   
INNER JOIN sysindexes  
ON sysobjects.id = sysindexes.id   
INNER JOIN syscolumns  
ON sysindexes.id = syscolumns.id  
WHERE syscolumns.colid <= sysindexes.keycnt  
AND sysindexes.indid = 1  AND index_col(object_name(sysindexes.id), sysindexes.indid,syscolumns.colid) IS NOT NULL
--AND sysobjects.name = 'tablename'  
ORDER BY sysobjects.name ,sysindexes.name  

DBCC InputBuffer and error code 800A0E78

Today I got the following error while executing DBCC InputBuffer from a vbscript:


Error: Operation is not allowed when the object is closed.
Code: 800A0E78
Source: ADODB.Recordset


I verified my script and I understood the error, the problem was generated because connection was generated with an account without System Administration privileges.

SQL Server 2008 Cluster: The current SKU is invalid

I was trying to add a second node to a SQL Server 2008 Cluster but it fails with below error:


The current SKU is invalid.


After searching net I got suggestion from  many blogs that launch setup using command line 
but I got one easier solution:


Copy Setup Files to second node and launch setup from there.


UPDATE:
Trying to add the 2nd node to the second SQL failover cluster instance I got again the problem and the solution is: 


Copy the key from DefaultSetup.ini and delete file, then retry to install 2nd node and enter the key manualy.

Row_Number, Rank, Dense_Rank, Partition By

Below script shows demo of all of the above


SELECT name, type_desc  
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords  
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName  
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType  
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType  
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName  
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName  
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1  
FROM sys.objects  
ORDER BY name  

SQL Doesn't start and Event ID = 17204

After hardening a server, the service didn't start anymore and I found the following error:

Event ID = 17204

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DemoData.ldf for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).

Details:

The File C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\

DATA\DemoData.ldf  in the message is not accessible to SQL Server for read and/or write 

operations during startup or database recovery. SQL Server might not be able to find the file, 

the file may be locked by another application, or SQL Server may not have permissions to 

access the file. 

Solution: 

• Check that the file listed above is exist in the specified location.
• Check the read write attribute for the file.
• Check the SQL Server startup account has read and write permission to the folder and file.
• Check that the file is not opened by another process.
• Check that the file is not encrypted.
• Provide FULL CONTROL (not just modify) to the service logon account (in my scenario was Network Service).

Remove Auto Shrink from all databases

DECLARE @databasename varchar(100)  
DECLARE @sqlAlterStatement varchar(500)  
  
DECLARE NastyCursorThing CURSOR READ_ONLY FOR  
SELECT name FROM sys.databases  
WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1  
  
OPEN NastyCursorThing  
FETCH NEXT FROM NastyCursorThing INTO @databasename  
WHILE @@FETCH_STATUS = 0  
    BEGIN  
    SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)  
    print @sqlAlterStatement  
    EXEC(@sqlAlterStatement)  
    FETCH NEXT FROM NastyCursorThing INTO @databasename  
    END  
  
CLOSE NastyCursorThing  
DEALLOCATE NastyCursorThing  

List / Drop all stored procedures from a database

1. List all Stored procedures in a database:

Select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%'


OR



Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'


Please note that I have added the 'NOT LIKE' part to get rid of stored procedures created during database installation.

2. Delete all [user created] Stored procedures in a database:

Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

This would list down all the user defined Stored procedure as 'Drop Procedure procedureName' in the result pane. Just copy-paste it into the query window and execute it once. 

What happens when my integer IDENTITY runs out of scope?

You can define the IDENTITY property on columns of the INT data type and on DECIMAL with scale 0. This gives you a range of: 

TINYINT	0 – 255
SMALLINT	-32.768 – 32.767
INT	-2.147.483.648 – 2.147.483.647
BIGINT	-2^63 – 2^63-1

When you decide to use the DECIMAL datatype you have a potential range from -10^38 to 10^38-1. 


Now, what happens when an integer identity crosses the range.


Example


CREATE TABLE Int_Identity ( col1 INT IDENTITY(2147483647,1) )

GO

INSERT INTO  Int_Identity DEFAULT VALUES 

INSERT INTO  Int_Identity DEFAULT VALUES 

SELECT * FROM Int_Identity

DROP TABLE Int_Identity

(1 row(s) affected) 

Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

The above script we have created a simple table with just one column and also created Identity property for this column. Now instead of adding 2 billion rows, we just seed value to maximum positive number for integer. Now the first row inserted and assigned that seed value. Now when we try to insert the second record, It failed showing above error.

Now the easiest solution is to alter the data type of the column to BIGINT, or maybe right on to DECIMAL(38,0).