SQL Server

...now browsing by category

 

Table variable vs tamp table in a T-SQL transaction

Sunday, March 6th, 2016

There are plenty of posts out there talking about the differences and disadvantages/advantages of using table variable vs temp table in a T-SQL query. The one thing I wanted to illustrate here is the difference on how they behave when a transaction rollback is involved. In short, transaction involving a table variable cannot be rollback whereas in a temp table it can. Here are two examples I tried to prove that:

Example 1 – in-memory table variable

declare @tranTest table (rowId int identity(1,1), rowVal tinyint)

begin try
begin transaction

insert into @tranTest (rowVal) values (200)
insert into @tranTest (rowVal) values (240)
insert into @tranTest (rowVal) values (256)

commit transaction

end try

begin catch
declare @errorMsg nvarchar(4000), @errorSeverity int, @errorState int;

select
@errorMsg=ERROR_MESSAGE(), @errorSeverity=ERROR_SEVERITY(), @errorState=ERROR_STATE();

raiserror (@errorMsg, @errorSeverity, @errorState);
rollback transaction;
end catch
set nocount on
select * from @tranTest

As the tinyint can only holds integer up to 255, inserting 256 throws an exception; since all three inserts are in a transaction, naturally I thought that select statement from @tranTest would return no records; but I was wrong! Actually, it returned:

rowId rowVal
1 200
2 240

Example 2: transaction in temp table

create table #tranTest (rowId int identity(1,1), rowVal tinyint)

begin try
begin transaction

insert into #tranTest (rowVal) values (200)
insert into #tranTest (rowVal) values (240)
insert into #tranTest (rowVal) values (256)
commit transaction
end try

begin catch
declare @errorMsg nvarchar(4000), @errorSeverity int, @errorState int;

select
@errorMsg=ERROR_MESSAGE(), @errorSeverity=ERROR_SEVERITY(), @errorState=ERROR_STATE();

raiserror (@errorMsg, @errorSeverity, @errorState);
rollback transaction;
end catch
set nocount on

select * from #tranTest
drop table #tranTest

Guess what will be returned? Yes, no record; as this time the transaction does what it is supposed to do – rollback all inserts when any of them fails in the transaction.

In conclusion, do not use in-memory table variable if transaction roll back is needed in case of operation error.

Posted in SQL Server, T-SQL | No Responses »

The bad (might be good to some) attribute introduced in MVC4 – [InitializeSimpleMembership]

Thursday, August 21st, 2014

Yesterday I deployed a MVC4 app to Winhost and got this error below when I clicked on “Log In” or “Register”, which I didn’t when deployed to local IIS.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I had seen such error before; mostly the error was due to some misspelled SQL server name or firewall blocking remote access, etc. I checked my SQL server connection strings for both SqlClient and Entities Framework model and were able to connect to the remote SQL db hosted at Winhost. When I submitted a support ticket to Winhost tech team, their server side log showed that my app tried to connect to a SQL Express instance which they don’t support. How strange was that? I do not use SQL Express and my database which contains the ASP.Net Membership objects resides on a full version local SQL Server, not Express. There is no place in my web.config that has anything to do with SQL Express instance. So I examined the Controller codes where the Log In link is clicked; here it was, the new “InitializeSimpleMembership” attribute on the AccountController class which was created automatically by my selecting the “Internet Application” MVC4 project template. This was something new introduced by MVC4 team at Microsoft, where a bunch of genius are always trying to come up with some dummy robot codes to  make everything so automatic – by “simply” looking for a SQL Express db to initiate the Membership provider for Web.Security; this “InitializeSimpleMembership” attribute, as it turns out, was the only culprit for all these non-simple headaches when you deploy your site to hosting server that usually do not support SQL Express. And there was no warning or error when I used the Publishing wizard from VS2012 to deploy the site to Winhost.

The fix? Simply commented out the attribute, as shown below,and re-deployed to Winhost; then everything works!

[Authorize]

  //[InitializeSimpleMembership]
    public class AccountController : Controller
    {
        //
        // GET: /Account/Login

        [AllowAnonymous]
        public ActionResult Login(string returnUrl)
        {
            ViewBag.ReturnUrl = returnUrl;
            return View();

Posted in .Net Framework, ASP.Net, C#, Entity Framework, MVC4, SQL Server | No Responses »
Tags: , ,

Overcame the error: The database principal owns a schema in the database..

Friday, January 17th, 2014

Today I tried to remove a user from SQL server database and encountered this error:

The database principal owns a schema in the database, and cannot be dropped (Microsoft SQL Server, Error: 15138)

Followed this link from TechNet at http://blogs.technet.com/b/mdegre/archive/2010/12/19/the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped.aspx

Here were the steps I took to drop the user “syang”:

  1. Find the schema name by running this query: 
    select * from sys.schemas where principal_id=USER_ID('syang')

    which returned:

     db_owner 16384 6 (name, schema_id, and principal_id)
  2. Run this query to alter the schema name: 
    alter authorization on schema::db_owner to dbo
go
  3. Now, I was able to drop the user syang successfully by running this query: 
    drop user syang
  4. To remove user login syang completely from the SQL server, run this: 
    IF  EXISTS (SELECT * FROM sys.server_principals WHERE name ='syang')
DROP LOGIN syang 
GO

Posted in SQL Server | No Responses »

Changing a column to identity column and add constraint, SQL server

Thursday, October 31st, 2013

I had to copy two tables from a database to another last night. After the tables were Imported successfully, the identity column and primary key constraint, however, were lost. To bring them back, there was no other way but to add a new column as identity and add constraint of primary key to that column; drop the old column and rename the new column to old column’s name. Here were what I did:

–this adds a new Identity column and automatically set not null and populate it with sequence data
alter table Category
add CategoryID2 int identity(1,1)
go
–drop the old CategoryId column
alter table Category
drop column CategoryID
go

–rename column CategoryID2 to CategoryID
sp_rename ‘Category.CategoryID2′,’CategoryID’,’Column’

–now add the primary key constraint
alter table Category
add constraint PK_CategoryId primary key clustered (CategoryId)
 

This completed the transition.

Posted in SQL Server | No Responses »

Good read on SSIS

Wednesday, February 6th, 2013

Liked this article series on MS SSIS from sqlservercentral.com:
This is an introductory one and quickly helped to understand what is SSIS and it is a replacement of DTS, not upgrade; and what’s BIDS (Business Intelligence Development Studio) and how it is integrated into Visual Studio IDE for development SSIS in C#, etc.
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/72492/

Posted in SQL Server, SQL Server Integration Service (SSIS) | No Responses »