Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Hello All,

Recently i am facing one common error in store procedure. “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements” comes when you write begin transaction statement in store procedure. When you try to set variable value and exit without commit then it will gives error.

For example

CREATE PROCEDURE [dbo].[StatusDelete]
(
	@StatusID SMALLINT,
	@outResult BIT OUTPUT
)
AS BEGIN
	
	DECLARE @count INT
	
	SET @count = 0

	BEGIN TRANSACTION
		

		SELECT
			@count = COUNT(*)
		FROM
			Types
		WHERE
			Types.StatusID = @StatusID
		
		IF @count > 0
		BEGIN
			SET @outResult = CAST(0 AS BIT)
			RETURN
		END

		DELETE FROM
			Status
		WHERE
			StatusID = @StatusID
	
		SET @outResult = CAST(1 AS BIT)
	COMMIT TRANSACTION
END

Above example will gives you error to solve that problem just add following line

.....
IF @count > 0
BEGIN
     SET @outResult = CAST(0 AS BIT)
     COMMIT TRANSACTION -- Add this line
     RETURN
END
..........

This will solve your issue.

Using Pivot in Sql Server

Hi All,

First of all we need to know about pivot in sql server.

pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

The following is the syntax for Pivot in sql.

SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
.............................
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column], .............................)
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Let’s take a basic example so we can understand it easily.

There is a table “Employee” with below columns,
ID : autogenerate int NOT NULL
name : nvarchar(50) NOT NULL
address : nvarchar(250) NOT NULL
contactNo : nvarchar(50) NOT NULL
leaveType : nvarchar(2) NOT NULL

You can create it by using below query

GO
CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[address] [nvarchar](250) NOT NULL,
	[contactNo] [nvarchar](50) NOT NULL,
	[leaveType] [nvarchar](2) NOT NULL
) ON [PRIMARY]
GO

Now insert some records in table using below query

INSERT INTO Employee(name, address, contactNo, leaveType)VALUES
('sachin', 'aaa', '111111111', 'PL'),  
('sehwag', 'bbb', '222222222', 'CL'),
('dravid', 'ccc', '333333333', 'SL'),  
('sehwag', 'bbb', '222222222', 'CL'),
('sachin', 'aaa', '111111111', 'SL'),
('Yuvraj', 'ddd', '444444444', 'PL'),
('Virat', 'eee', '555555555', 'SL'),
('Yuvraj', 'ddd', '444444444', 'CL')

now we need to count leave of all employee by leaveType and need to display it as below
name   address   contactNo   CL   PL   SL

see below query which use pivot feature and get the above result:

SELECT name, address, contactNo, CL, PL, SL
FROM (
SELECT name, address, contactNo, leaveType
FROM Employee) srcTable
PIVOT (COUNT(leaveType) FOR leaveType IN (CL, PL,SL)) AS pvtTable
ORDER BY name

see the below result
piv_result
Hope this will clear the concept of pivot feature.

Saving changes is not permitted in sql server

Hello,

When we install new version of sql server then sometimes we are getting this error “Saving changes is not permitted in sql server” on adding new column or changing column in table.

tblsave_error

If we check this in other PC then it is working fine. This is not a hosting problem.
we need to uncheck prevent saving changes option from Tools >> Options >> Designers >> Table and Database Designer. see below screenshot. so you can get better idea.

tblsave_error1

After unchecking the option, you can add or modify the tables.

Hope this will help you.

The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader.

Recently i got error on my WCF service. which is something like “The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader.”

The error says clearly that it has not able to read more than 8192 string length. WCF service by default set 8192 string length for security to prevent not submit more data. But, in my case i need more data to send. so, i have set following things in my bindings and all done.




maxStringContentLength is set length and solve the problem.

Hope it will help you other also..

Use space value in QueryString

Hello all,

Today we will talk on query string value. We are using query string to pass value from one page to another page. Some time we need to pass value as string and string has some space value too. That time we are not getting exact value on another page because query sting is not pass correctly.

For e.g.

some text

OUTPUT :
str = test

To solve this problem we have to encode url. URL does not contain spaces.
There are two possible solution of this problem one is remove space from url another one is encode/decode url.

StringBuilder sb = new StringBuilder();
string strValue = "test value";
sb.Append("" + some text + "");
sb.Append("");

another way is

StringBuilder sb = new StringBuilder();
string strValue = "test value";
sb.Append("" + some text + "");
sb.Append("");

OUTPUT :
str = test value

HttpUtility is referenced to System.Web class. I prefer to use HttpUtility because it will eliminate space and other characters too.

The server tag is not well formed

Hello all,

The server tag is not well formed error occurs because you have missed to write something or written something wrong. In my case i have written one link button and it gives me the server tag is not well formed error.

My link button is as follow

" OnClick="lnkStudio_Click">

It gives me error. Actually everything is right but i have done small mistake in Text. Correct link button text is as follow

Text='<%# Eval("studio") %>'

It is working perfectly after replace double quote to single in Text. Because of Eval use we have to use single quote not double quote.

Hope it will help you too.

call javascript function on asp.net content page body load

Hello,

Today i faced one problem in asp.net content page. I would like to share it with you.

Today I have derived content page from master page and I want to call one javascript function on body onload event. You know that, There is no body tag in asp.net content page.

I have used below different ways to call it.
1. I have used window.onload event like
window.onload = fnName();
Generally this is working in simple html and javascript page but this doesn’t work for me

2. I have used javascript pageload function but it doesn’t work for me

3. I have make master page body tag to runat as server and created one property for accessing it.
After that I have accessed it from content page but it doesn’t give me solution for my problem

4. Lastly I have used javascript addEventListener event for registering a load event like
window.addEventListener(‘load’, function () { fnName() }); and I got the solution.

Hope this will help you.

javascript e.preventDefault() and return false

Hello,

In javascript; When I want to prevent other event handlers from executing after a certain event is fired, I can use one of two techniques; e.preventDefault() or return false.

So what is the difference in between e.preventDefault() and return false and When to use return false and when preventDefault()?

First we need to understand below things
1. e.preventDefault() = It stops the browsers default behaviour
2. e.stopPropagation() = It prevents the event from propagating.

Return False :
when we call return false in our function then it does below things
1. e.preventDefault()
2. e.stopPropagation()
3. Stops the callback execution and returns immediately

e.preventDefault() :
when we call e.preventDefault() in our function then it does only one thing i.e e.preventDefault()

In short return false takes things a bit further in, it also prevents that event from propagating (or “bubbling up”) the DOM.
function()
{
return false;
}

is euqal to

function(e)
{
e.preventDefault();
e.stopPropagation();
}

Hope this will help you in understanding e.preventdefault() and return false;

error 413 Request Entity Too Large in wcf request

Hello All,

Today i am facing one error in my WCF request. The error is “413 Request Entity Too Large”. My page request is larger than normal size and i encounter this error.

To overcome from above error use following code it is work for me.



  
	
	
  


IIS has not any problem but WCF has this error. It works in earlier version of Visual Studio but this error shows in Visual Studio 2010. WCF limits message to 65KB. It will set this limit to provide security from unknown attacks. Set maxReceivedMessageSize will solve your problem.

Hope it will help you too!!!

T-SQL Enhancements in SQL Server 2008 phase-5.1

Hello,

we have seen some development features of sql server 2008 in my previous articles like Table-Valued Parameters, Grouping Sets, cube and rollup, grouping_ID, merge statement, compound assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe Datatype Enhancements.

Datatype Enhancements :

  • Temporal Data
  • Spatial Data
  • Hierarchical Data

Temporal Data :

  • New Datatypes
  • New and Enhanced Functions

New Datatypes :

Datatype Storage (bytes) Date Range Accuracy Recommended Entry Format and Example
DATE 3 January 1st, 0001 through December 31st, 9999 (in Gregorian calendar) 1 day ‘YYYY-MM-DD’ ’2009-02-12′
TIME 3 to 5 100 nanoseconds ‘hh:mm:ss.nnnnnnn’ ’12:30:15.1234567′
DATETIME2 6 to 8 January 1st, 0001 through December 31st, 9999 100 nanoseconds ‘YYYY-MM-DD hh:mm:ss.nnnnnnn’ ’2009-02-12 12:30:15.1234567′
DATETIMEOFFSET 8 to 10 January 1st, 0001 through December 31st, 9999 100 nanoseconds ‘YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm’ ’2009-02-12 12:30:15.1234567 +02:00′

New Functions :

1. Return DATETIME2:

  • SYSDATETIME
  • SYSDATETIMEOFFSET
  • SYSUTCDATETIME

2. Return DATETIMEOFFSET:

  • SWITCHOFFSET (input DATETIMEOFFSET)
  • TODATETIMEOFFSET – returns input value with specified time zone

3. New date and time parts:

  • microsecond, nanosecond, Tzoffset, ISO_WEEK

Example :

DECLARE @d DATE
SET @d = '20110103'
SELECT  @d

DECLARE @t TIME
SET @t = GETDATE()
SELECT @t

DECLARE @decimalprecision TIME(3)
SET @decimalprecision = GETDATE()
SELECT @decimalprecision

DECLARE @dt2 DATETIME2
SET @dt2 = '20110103 23:59:59.9999999'
SELECT @dt2

DECLARE @decimalprecision2 DATETIME2(3)
SET @decimalprecision2 = '20110103 23:59:59.9999999'
SELECT @decimalprecision2

DECLARE @dto DATETIMEOFFSET
SET @dto = '20110103 23:59:59.9999 +09:00'
SELECT @dto

DECLARE @decimalprecision3 DATETIMEOFFSET(3)
SET @decimalprecision3 = '20110103 23:59:59.9999 +09:00'
SELECT @decimalprecision3


SELECT GETDATE()
SELECT SYSDATETIME()
SELECT SYSUTCDATETIME()
SELECT SYSDATETIMEOFFSET()
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-01:00')
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-01:00')

SELECT DATEPART(ISO_WEEK, SYSDATETIMEOFFSET())
SELECT DATEPART(ISO_WEEK, '2011-05-10')
SELECT DATEPART(TZoffset, '2011-05-10 00:00:01.1234567 +05:10')
SELECT DATEPART(TZoffset, SYSDATETIMEOFFSET())

That’s it.
we will see Spatial Data in next article