Ads

Wednesday, June 13, 2012


While restoring a db in production server . I got a following issue

i tried to restored using wizard it failed
Error 3154: The backup set holds a backup of a database other than the existing database.


first i tried following sql command

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

It raised following error

Exclusive access could not be obtained because the database is in use.

Later i tried following sql script

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
no result same error

fix:

ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE;


Friday, June 08, 2012

Case sensitive comparison in Sql server

to compare case sensitive fields in sql server u need to cast the fields to varbinary
then compare

for example

Scenario

select * from tbluser
username='test'

select * from tbluser
username='Test'

both of above queries will give same results


fix

select * from tbluser

cast (username as varbinary(lengthof username field)=
cast('test' as varbinary(length of usrname field)

Friday, June 01, 2012

Yesterday i got a requirement as follows

As in the screen shot depending upon the selection in the radio button list I need to fill drop down, similar to cascading drop down lists I used Jquery ajax to call database. Following steps are used in developing functionality Step 1 Add using System.Web.Services; in the name space region Add attribute [WebMethod] above the method to fill data. example [WebMethod] function filldropdown() { // do db interaction to get data } Step 2 Add following javascript to web page $(document).ready(function () { $('#<%= rdbFormType.ClientID%> :input').click(function () { var selRad = $("input[@name=<%=rdbFormType.ClientID%>]:radio:checked").val(); if (selRad == 1) { $('#AssociatedForm'). css('visibility', 'visible'); var loc = window.location.href; loc = (loc.substr(loc.length - 1, 1) == "/") ? loc + "AddForm.aspx" : loc; $.ajax({ type: "POST", url: loc + "/" + 'methodname', data: "{}", beforeSend: function (xhr) { xhr.setRequestHeader("Content-type", "application/json; charset=utf-8"); }, dataType: "json", success: function (msg) { $('#<%= ddlForm.ClientID%>') .get(0).options.length = 0; $('#<%= ddlForm.ClientID%>') .get(0).options[0] = new Option("Select Form", "-1"); $.each(msg.d, function (index, item) { $('#<%= ddlForm.ClientID%>').get(0).options[$('#<%= ddlForm.ClientID%>').get(0).options.length] = new Option(item.Display,item.Value); }); }, error: function (res, status, ex) { alert('Not able to load Form Data'); } }); } else { $('#AssociatedForm').css('visibility', 'collapse'); } }); }); Step 3 Add following to the web.config add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" In following above steps you may encounter following errors Error 1) 500 internal server error while posting data to server fix : replace data: "{}" with data: {} Error 2) 200 +ok but it will return whole html design. Fix: check your database calling method is having keywords Public and static and also check whether step 3 is followed or not Error 3)Invalid postback or callback argument. A Event validation is enabled using in configuration or <%@ Page EnableEventValidation=”true” %> in a page. Â For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. Â If the data is valid and expected, use the ClientScriptManager. RegisterForEventValidation method in order to register the postback or callback data for validation. Fix: protected override void Render(HtmlTextWriter writer) { Page.ClientScript. RegisterForEventValidation (dropdownlistcontrol.UniqueID, value); base.Render(writer); }

Tuesday, May 29, 2012

 I found a e book for 70-515 Found at ebookbrowse.com



Sunday, May 20, 2012

Hi Guys,

I cleared CSM. Now Certified Scrum Master. Its fun to work with agile methodologies.
Earlier i passed MCPD in 2008




Wednesday, April 18, 2012

New features in Sql server 2012

 Sequences
Sequences are introduced in sql server 2012 which are similar to sequences in oracle.
We can associate sequences to a column in the table. Then a question came into my mind we have already identity in sq server. Why we need another object to have auto increment column?
Sequences have following advantages over Identity

There are not related to particular table.
The are not stored in disk. They will come from memory ,So they are faster than identity
We will have control over sequences like  max value, min value starting value and restart value
which are not feasible in case of identity
With out inserting value value we can get next sequence value (Identity value will be associated once column is inserted into table other wise we cant get identity of that column)

We can set default value for a column using NEXT VALUE FOR function
We can insert multiple sequence values at once.

All in all we can say it gives more performance than identity based columns

Ad-Hoc Query Paging: Using a combination of OFFSET and FETCH along with ORDER BY we can have control of paging through a result set.

For suppose we have grid view with pagination we can use this ad-hoc query paging for better perfomance



Thursday, December 31, 2009

Good article about running totals and need to store previous row values

On other day i am supposed to work on balance sheet calculations . I am literally dumb in such accounting things. I struggled get the ending balance, opening balance and carry forward values. I sterched my hand to get help from jeff moden( sql server central.com) He gave such a good idea which absoultely master piece.

Here is the link of it. Read it


http://www.sqlservercentral.com/articles/T-SQL/68467/