Feeds:
Posts
Comments

Archive for March, 2013

EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the statement tried to send more result sets than this is one of the new error messages introduced in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS.

Let’s discuss this in detail:
Message Number: 11535

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the statement tried to send more result sets than this.

Error Generation:

Let me create a sample stored procedure to generate this error.

Create Procedure UDP_Resultset
As
Select 'A' as [First Letter]
Select 'B' as [Second Letter]
GO
EXEC UDP_Resultset

exec resultset1.1

You can view that the above stored procedure is executed successfully, and it has two result sets.

Lets try to execute it with WITH RESULT SETS clause.

EXEC UDP_Resultset
WITH RESULT SETS
(([1st Letter] varchar(50)
));
--OUTPUT

Msg 11535, Level 16, State 1, Procedure UDP_Resultset, Line 5
EXECUTE statement failed because its WITH RESULT SETS
clause specified 1 result set(s), and the statement tried
to send more result sets than this.
Ooopps…… I am unable to execute it properly. It returned the first result set and the error message.

exec resultset1.2

Resolution:
Why this error ? Because, the above stored procedure returns two result sets but we defined WITH RESULT SETS clause for only one result set. Lets rewrite the script and define two result sets (The same number of result sets returned by stored procedure) to avoid this error.

EXEC UDP_Resultset
WITH RESULT SETS
(([1st Letter] varchar(50)
)
,([2nd Letter] varchar(50)
));
--OUTPUT

exec resultset1.3

Conclusion :
Remember, whenever you use any stored procedure WITH RESULT SETS clause, make sure you use exactly the same number of result set returned by stored procedure to avoid such errors.

Read Full Post »

Mostly, there are multiple ways to write a script to perform a single task. It varies from Developers to Developers, who normally try to follow the best practice. But sometimes some standards discard from time to time (version to version). In this article, I will go through some standard formats that we used in the prior version of SQL server but does not work in SQL Server 2012. It may break your script.

Let me create a sample in SQL Server 2005/ 2008 to explain it.

USE [test]
GO
CREATE TABLE [dbo].[t_Student](
	[Student ID] [int] IDENTITY(1,1) NOT NULL,
	[Student Name] [nvarchar](50) NULL
)

After creating the sample table lets alter this table. But there are three different formats that you can use to alter the table in earlier version of SQL server (2005/2008)


Method 1 :
In method 1, the format to alter the table will be .Database Name.Schema.Table Name

Alter table .[test].dbo.[t_Student] Add [Contact Details] varchar(50) NULL
--OUTPUT

Command(s) completed successfully

Method 2 :
In method 2, the format to alter the table will be ..Schema.Table Name

use test
GO
Alter table ..dbo.[t_Student] Add [Date of Birth] Datetime NULL
--OUTPUT

Command(s) completed successfully

Method 3 :
In method 3, the format to alter the table will be Schema.Table Name

use test
GO
Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL
--OUTPUT

Command(s) completed successfully

All of the above methods executed successfully in SQL Server 2005/ 2008.

Lets browse the table.

Select * from t_Student

alter table 1.1

Now, lets do the same exercise in SQL Server 2012.

Method 1 :
In method 1, the format to alter the table will be .Database Name.Schema.Table Name

Alter table .[test].dbo.[t_Student] Add [Contact Details] varchar(50) NULL
--OUTPUT

Msg 117, Level 15, State 1, Line 1
The object name ‘.test.dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2.

Method 2 :
In method 2, the format to alter the table will be ..Schema.Table Name

use test
GO
Alter table ..dbo.[t_Student] Add [Date of Birth] Datetime NULL
--OUTPUT

Msg 117, Level 15, State 1, Line 1
The object name ‘..dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2.

Method 3 :
In method 3, the format to alter the table will be Schema.Table Name

use test
GO
Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL
--OUTPUT

Command(s) completed successfully

Conclusion :
If you are planning to upgrade your SQL Server earlier version to SQL Server 2012 or you are using SQL Server 2012, remember, it will not allow you to use methods 1 & 2. In case your are using above methods 1 & 2 in Dynamic SQL or Static SQL, it may break your script.

Reference : MSDN

Read Full Post »

I have been using Database email for a long time. This is very nice features introduced in SQL Server 2005. But when it comes to HTML formatting, you need to know a little bit HTML to format it and it takes a quite a while to format it. In this article, I will share a tip that can make your life easy and you can format even a complex HTML formatted email in few minutes.

Lets process it step by step.

Step 1 :
Mostly we receive the email format in word documents. First of all, you need to open the word document in MS word.
Sample attached Test Document 1.1.
SQL email1.1

Step 2 :
Then Go to File menu>>Save as and save it as html document.
SQL email1.2

Step 3 :
Open the saved html document, right click on it and go to view source.
SQL email1.3

Step 4 :
It will open a new window. Now you need to search for the tags namely Body and /Body and whatever text lies between both tags, just copy and paste it in the SQL query window.
SQL email1.4

Step 5 :
Now, you need to replace one single quote (‘) to two single quotes (”) in SQL query window and add single quote (‘) in the beginning and end of the text. Rest of the format will remain same.

Use msdb
GO
EXEC sp_send_dbmail @profile_name='My profile',
@recipients='mimran18@gmail.com',
@subject='Test Message with Formatted HTML',
@body_format = 'HTML' ,
@body='</pre>
<div class="WordSection1"><s>This is a TEST email</s>

<i><span style="text-decoration: underline;">This
is a TEST email</span></i>

This is a TEST email

This is a TEST
<table class="MsoTableLightListAccent1" border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td valign="top" width="97"><span class="SpellE"><b>S.No</b></span><b></b></td>
<td valign="top" width="375"><b>SQL Server Versions</b></td>
</tr>
<tr>
<td valign="top" width="97"><b>1</b></td>
<td valign="top" width="375">SQL Server 2005</td>
</tr>
<tr>
<td valign="top" width="97"><b>2</b></td>
<td valign="top" width="375">SQL Server 2008</td>
</tr>
<tr>
<td valign="top" width="97"><b>3</b></td>
<td valign="top" width="375">SQL Server 2012</td>
</tr>
</tbody>
</table>
</div>
<pre>
'

Step 6 :
Now, just execute it and it will send a nice formatted HTML email.

Read Full Post »

In this article,  we will discuss how to convert user defined objects to system objects and the reason to convert. Sometimes you develop general database procedures and functions that help you perform your daily operation quickly. But the issue is you need to create all these procedures and functions in the databases and after finishing the task you need to remove it also. A few days ago during my research I found a solution and with the help of this solution you don’t need to create / remove the script again and again in all databases. The solution is an undocumented stored procedure namely sp_ms_marksystemobject . But how it works, lets discuss stepwise.

Step 1 :
Given below is the script that can give you the column name (along with comma and brackets [ ]) of any table in a particular database and later on you can use it to insert, update or select statements.

Use AdventureWorks2012
GO
Create Procedure SP_ColumnNames
@object_name nvarchar(Max)
as
Select (Case when [column_id] = 1 then ' ' else ',' end)
+ QuoteName([name]) As [Column Name]
from sys.columns
where object_id=Object_id(@Object_name)
GO

Step 2 :
Now, you need to use the same database to execute the above stored procedure.

Use AdventureWorks2012
Go
SP_ColumnNames 'Sales'

systemobjects1.1

Step 3 :
If you try to use the same procedure in another database it will not work until unless you create the same procedure there. Lets see.

Use test
GO
SP_ColumnNames 'Student'

systemobjects1.2

If you look at the above error, it says you must deploy the above stored procedure in all databases wherever you need to use it. But this is a common tool and I need to deploy it once and need to use it in all the databases at the server. The solution is available in Step 4.

Step 4 :
Simply create your user defined stored procedure in master database and convert it to system stored procedure using sp_ms_marksystemobject.

Use master
GO
Create Procedure SP_ColumnNames
@object_name nvarchar(Max)
as
Select (Case when [column_id] = 1 then ' ' else ',' end)
+ QuoteName([name]) As [Column Name]
from sys.columns
where object_id=Object_id(@Object_name)
GO
sp_ms_marksystemobject  'SP_ColumnNames'

Step 5 :
Now, you can use this stored procedure in any database in the same server without creating it in all databases.
Lets try.

Use AdventureWorks2012
Go
SP_ColumnNames 'dbo.sales'
GO
Use test
Go
SP_ColumnNames 'Student'

sytemobject1.4

Conclusion :
You can deploy your general stored procedure and function once in the master database to perform daily operations and as many times as you can use it, in as many as databases you wish, without creating the stored procedure and functions time and again.

Note : This solution is not recommended for live database servers, you can use it in the development and testing server to expedite your development and testing.

Read Full Post »

Whenever you upgrade previous version of SQL Server to SQL Server 2012 you need to take care of few issues. In this article, I will discuss one of the important issues that is Create trigger with append clause. This clause will not stop you to upgrade it to SQL Server 2012 but it will break your scripts. But how ?
Let me explain it Step by Step with the simple examples.
Step 1 :
First create given below table and triggers on any database prior to SQL Server 2012.

CREATE TABLE dbo.Student
([Student_ID] INT,
 [Student_Name] VARCHAR(100)
)
GO
CREATE TRIGGER dbo.FirstTrigger
ON dbo.Student
FOR INSERT
AS
BEGIN
  PRINT 'First Trigger'
END
GO
CREATE TRIGGER dbo.SecondTrigger
ON dbo.Student
FOR INSERT
WITH APPEND
AS
BEGIN
  PRINT 'Second Trigger'
END
GO

Step 2 :
Upgrade the database on SQL Server 2012. Once you upgrade it will not alert you.
Step 3 :
Immediately after upgrade, run the given below query to view if any with append clause is available in the trigger.

Select A.object_id,A.definition from sys.sql_modules A
Inner Join sys.triggers B On A.object_id =B.object_id
where definition like '%with append%'

withappend1.1-1

Step 4 :
In the above example, we have one trigger with append clause, so alter the trigger and remove the with append clause because it is no more required for the triggers in latest versions of SQL Server.

Now, you are ready to use this trigger in the SQL Server 2012.

In case, if you don’t follow step 3 and 4, lets see what may happen.

 insert into student values (1,'Imran')
--OUTPUT

First Trigger
Msg 195, Level 15, State 1, Procedure SecondTrigger, Line 5
‘APPEND’ is not a recognized option.

Conclusion :
Remember, whenever you upgrade to SQL Server 2012, check WITH APPEND clause  in the triggers to avoid this error.

Read Full Post »

DBCC SHRINKFILE for FILESTREAM containers requires EMPTYFILE option and does not allow any other options is one of the error messages shipped with SQL Server 2012. It happens due to the new feature File table (File stream) introduced in SQL Server 2012. Lets explore this error message.
Message Number: 2563
Severity : 16
Error Message: DBCC SHRINKFILE for FILESTREAM containers requires EMPTYFILE option and does not allow any other options.

Error Generation:
This error message looks as if something is wrong with the “DBCC SHRINKFILE”. Lets generate this error step by step.
Step 1 :
First of all create a database having File stream.

CREATE DATABASE [MyFileTableDB]
ON PRIMARY
( NAME = N'MyFileTableDB', FILENAME = N'C:\DATA\MyFileTableDB.mdf'),
FILEGROUP MyFileGroup CONTAINS FILESTREAM (NAME = MyFileTableFS,FILENAME='C:\Data\MyFileTable')
LOG ON
( NAME = N'MyFileTableDB_log', FILENAME = N'C:\DATA\MyFileTableDB_log.ldf')
WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyFileTableDB' )
GO

Step 2 :
Shrink the data file.

-- Shrink the data file
USE My_FileTableDB
GO
DBCC SHRINKFILE (N'MyFileTableDB' , 0, TRUNCATEONLY)

msg25631.1

Step 3 :
Shrink the log file.

-- Shrink the log file
USE My_FileTableDB
GO
DBCC SHRINKFILE (N'MyFileTableDB_log' , 0, TRUNCATEONLY)

msg25631.2

Step 4 :
Shrink the filestream file.

-- Shrink the FileStream file
USE My_FileTableDB
GO
DBCC SHRINKFILE (N'MyFileTableFS' , 0, TRUNCATEONLY)

Oooops……………
Msg 2563, Level 16, State 1, Line 1
DBCC SHRINKFILE for FILESTREAM containers requires EMPTYFILE option and does not allow any other options.

Resolution:
You cannot shrink FileStream files like data file or log file. The correct syntax to shrink file stream is given below :

 DBCC SHRINKFILE (N'MyFileTableFS' , EMPTYFILE)

msg25631.3

Conclusion:
Remember, whenever you shrink the FILESTREAM files follow the exact syntax given above to avoid this error.

Read Full Post »

« Newer Posts