Thursday, December 8, 2011

DBCC RESEED Table Identity Value – Reset Table Identity

During application development, we often input dummy data into our database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the truncate command. This will delete data from table and also reset the identity column value to 0.

truncate table [table_name] -- for example truncate table product

But the truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

The other way is...

In this case, first you need to delete data from the child and the master table.

After deleting data, fire this command and it will reset your identity column to 0.

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]) DBCC CHECKIDENT('product', RESEED, 0)

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

DBCC CHECKIDENT (yourtable, reseed, 34)


Friday, November 18, 2011

How to use Web Service in Web Application


Wcf Service:
There is new baby of microsoft in the world. called WCF service. But there is huge Question how to call it in webapplication. thoug it is as easy as we are doing with webservice.
there is a huge differance in the plateform on which webservice is built and on which WCF is service in built.
Differance:
The Main differance bet'n this two services (WebService/WCF) is With WCF Service SOAP messages can be transmitted over a variety of supported protocols including IPC (named pipes), TCP, HTTP and MSMQ. Like any distributed messaging platform,on the other hand in WebService SOAP message can only transmitted via HTTP.
How To Create WCF Service:
To Create WCF service Create
1).Create blank website
2).Right-Click > Add Item >WCF Service
3). Add referance to
System.ServiceModel.
Three files will be created in solution .
A) App_Code/IService.cs
Content:
using System.ServiceModel;// NOTE: If you change the interface name "IService" here, you must also update the reference to "IService" in Web.config.
[ServiceContract]
public interface IService
{
[OperationContract]
string DoWork();

}

B) App_Code/Service.cs
Content:
using System;
using System.ServiceModel.Activation;
// NOTE: If you change the class name "Service" here, you must also update the reference to "Service" in Web.config.
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class Service : IService
{
public string DoWork()
{

return DateTime.Now.ToString();
}
}
C) Service.svc
Content:
<%@ ServiceHost Language="C#" Debug="true" Service="Service" CodeBehind="~/App_Code/Service.cs" %>

And Following lines of Configuration will automatically added to Web.Config file of your web site.
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="ServiceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
behavior>
serviceBehaviors>
behaviors>
<services>
<service behaviorConfiguration="ServiceBehavior" name="Service">
<endpoint address="" binding="wsHttpBinding" contract="IService">
<identity>
<dns value="localhost" />
identity>
endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
service>
services>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
system.serviceModel>
This are the steps to create WCF service.To call WCF service in our application we have to Add bindings in web.Config file & also have to create a class which acts as proxy of WCF service in our application.
How to Consume WCF service in WebApplication:
to call WCF service we have to add following service contract in our web.config file. please copy and paste below configuration tags between <system.serviceModel> system.serviceModel> elements.
Service binding configuration:
<bindings>
<wsHttpBinding>
<binding name="WSHttpBinding_IService" closeTimeout="00:01:00">
binding>
wsHttpBinding>
bindings>
<client>
<endpoint address="http://localhost:54464/SVCTest/Service.svc"
binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IService"
contract="IService" name="WSHttpBinding_IService">
<identity>
<dns value="localhost" />
identity>
endpoint>
client>
Now we have done with configuration of WCF service configuration.now we have to create ClassLibrary which contains proxy class to call WCF service methods.
Steps to create proxy is:
1) on solution Right-Click >Add New Project >Class Library.
2) Add Class and name it ServiceCaller.cs.
3) Add Referance to System.ServiceModel in Class Library project.
4 ) Add following code in that class file.
namespace WCFServices
{
[System.ServiceModel.ServiceContractAttribute(ConfigurationName = "IService")]
public interface IService
{

[System.ServiceModel.OperationContractAttribute(Action = "http://tempuri.org/IService/DoWork", ReplyAction = "http://tempuri.org/IService/DoWorkResponse")] //This line is must
string DoWork();
}
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
public partial class ServiceClient : System.ServiceModel.ClientBase<IService>, IService
{

public ServiceClient(){}
public string DoWork()
{

return base.Channel.DoWork();
}
}
}
Finally now we are done with creating WCF service caller class.I have given sample code for service proxy and configuration elements.But you can also create configuration Using Command line Called svcutil.exe Automatically.
Steps to create configuration and proxy class using Command-line(svcutil.exe):
1) Open Visual Studio 2008 Command Prompt.
Service.cs and output.config will be automaticall create at
Program Files (x86)\Microsoft Visual Studio 9.0\VC.
you can use this Service.cs as ServiceCaller and elements inside output.config as configuration elements for web.config file.
To call WCF service in our application is very easy now.
Just Create object of ServiceClient Class. and you will be able to call methos in WCF service.
Eg.
protected void Page_Load(object sender, EventArgs e)
{

WCFServices.ServiceClient sc = new WCFServices.ServiceClient();
string currentTime = sc.DoWork();
Response.Write(currentTime);
sc.Close();
//Response.Write(DateTime.Now.ToString());

}
Here we GO.
you can find more details of each and every class and terms and Namespace used here in following links.also can find more about making WCF service more secure from the referances given here.

Tuesday, September 6, 2011

Trigger

Create table test (Id int,Name varchar(50))
Create table tblTest (Id int,testvalue varchar(50),Name varchar(50))
Go
Create TRIGGER tritest /* change to ALTER when you edit this trigger */
ON tbltest
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS
BEGIN
UPDATE tblTest SET tbltest.testvalue=tbltest.name FROM INSERTED WHERE inserted.id=tbltest.id
END
Go
insert into tblTest values(2,'TEST','MURLI')

Sunday, September 4, 2011

SQL SERVER – Introduction to SERVERPROPERTY and example

SERVERPROPERTY is very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.

Run following script to see all the properties of server.
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

Thursday, August 11, 2011

Using MERGE in SQL Server to insert, update and delete at the same time

Beginning with SQL Server 2008, now you can use MERGE SQL command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle; it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

SYntex :-
MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE] ON <search_condition>
[WHEN MATCHED THEN <merge_matched>]
[WHEN NOT MATCHED [BY TARGET] THEN <merge_not_matched>]
[WHEN NOT MATCHED BY SOURCE THEN <merge_ matched=""> ]

Example:-

--Create a target table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO


--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO

Wednesday, August 10, 2011

List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

2.
Select * from INFORMATION_SCHEMA.CONSTRAINT_Table_Usage

Insert and Update with OpenXML in SQL

This is pretty easy to do. Below is an example that uses a table named 'test' that has an ID column called xmlID and a data column called xmlData.
 

declare @i int
 
exec sp_xml_preparedocument @i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
 
insert into test
select xmlID, xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30))
where xmlID not in (select xmlID from test)
 
update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
 
exec sp_xml_removedocument @i
 

Tuesday, June 7, 2011

How to check an array item exist or not in the json array based on the item property value in javascript

In my current project i am working more with jquery and json.In this project i had a requirement to find the json object exist or not in the json array based on the item property value.I had googled a bit and did not find the answer.

Then i wrote a prototype method to find the json object exist or not.

Array.prototype.containsJsonObj = function(name,value) {
var isExists=false;
$.each(this,function(){
if(this[name]==value){
isExists=true;
return false;
}});
return isExists;
};


Below is the json array

var jsonArray=[{"firstName":"John","lastName":"Doe","age": 23 },
{ "firstName":"Mary","lastName" :"Smith","age": 32 }]

we can check whether the array item exist or not as shown below

if(jsonArray.containsJsonObj("firstName","John"))
{
//if it exists enter into this loop
}
else
{
//if it does not exist enter into this loop
}

How to get checkboxlist selected item values on client side using javascript

Sometimes, you may need to find out server side checkboxlist selected item values on client side.By default checkboxlist does not send item value to the client.This problem can be solved by assigning values to each item of checkboxlist in page load event as shown below

protected void Page_Load(object sender, EventArgs e)
{
foreach (ListItem li in cblListItems.Items)
li.Attributes.Add("mainValue", li.Value);
}

Then call the below javascript function on click of checkboxlist.

function GetCheckBoxListValues(chkBoxID)
{
var chkBox = document.getElementById(chkBoxID);
var options = chkBox.getElementsByTagName('input');
var listOfSpans = chkBox.getElementsByTagName('span');
for (var i = 0; i < options.length; i++)
{
if(options[i].checked)
{
alert(listOfSpans[i].attributes["mainvalue"].value);
}
}
}

How To Sort ListItems In JavaScript

First Declare ListItemArray Varaible:-

var myOptions = [];

//Fill ListItem Array With Some Data

// copy options into an array
function SortListItems(optionText,optionValue) //where optionText and optionValue are array of texts and values
{ myOptions.clear();//clear array if any data present

// copy options into an array
for (var i=0; i
{ myOptions[i] ={ optText:optionText[i], optValue:optionValue[i]}; }

// sort array myOptions.sort(SortFuncAsc);}

// sort functionfunction SortFuncAsc(record1, record2){ var value1 = record1.optText.toLowerCase(); var value2 = record2.optText.toLowerCase();; if (value1 > value2) return(1); if (value1 < value2) return(-1); return(0);
}

Bandwidth detection with javascript

In one of my current projects, I came across a scenario to play a video based on the users downloading bandwidth. The detection should be done with javascript. There is a technique, to load an image of known size and calculating the bandwidth on basis of time taken to load that image. Though this technique is not 100% reliable, this will give an approximate estimate of bandwidth.

Here is the javascript code to find users bandwidth:

var userBandwidth = 0;
var startTime;
var endTime;
var imgSize = 39842;
var loadTimeInSec;

function GetUserBandwidth() {
var testImage = new Image();
testImage.src = "bwtest.jpg";
startTime = (new Date()).getTime();
testImage.onload = CreateDelegate(testImage, DoneWithTest);
}

function DoneWithTest() {
endTime = (new Date()).getTime();
loadTimeInSec = (endTime - startTime) / 1000;
userBandwidth = (imgSize / loadTimeInSec) / 1024;
}

Here we are loading an image of size 38Kb and added a delegate on image loaded event. In the call back function, we calculate end time, with that we can calculate the bandwidth of the user.

Check my previous post to add delegate in javascript.

Delegate in Javascript

Before going to the topic, lets know brief about “Delegate”:
A delegate is like a function pointer, which stores reference of a method. It specifies a method to call and optionally an object to call the method on. They are used, among other things, to implement callbacks and event listeners.

We can create and define delegates in Javascript to perform some specific tasks on an object. The following is the code to define delegate.


function CreateDelegate(contextObject, delegateMethod) {
return function() {
return delegateMethod.apply(contextObject, arguments);
}
}


Let us take a simple example and use the delegate in that. Here is the scenario, there is a large image to be loaded on a web page and we need to display the height and width of that image after loading. Delegate method will be very handy in this situation.


var image = new Image();
image.src = "img.jpg";
image.name = image.id = "img1";
image.onload = CreateDelegate(image, ImageLoaded);

function ImageLoaded() {
alert(this.width + " X " + this.height);
}


We are creating a delegate on image loaded event, which will call method ‘ImageLoaded’. This delegate automatically pass the object on which the delegate is defined, to the method. We can refer the actual image object using ‘this’ in the method.

Monday, June 6, 2011

One of the most usefull link for learing Silver Light


http://samples.msdn.microsoft.com/Silverlight/SampleBrowser/#/?sref=animation_ovw_intro

Update data in one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.


Table Structures and values:


TableA has four columns: a, b, c, d (a is the primary key column)

TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)


The foreign key relationship between the two tables is based on A.a = B.a1


The data in these 2 tables is as follows:

I.    TableA

a    b    c    d

1    x    y    z

2    a    b    c

3    t    x    z


II.    TableB

a1    b1    c1    d1    e1

1    x1    y1    z1    40

2    a1    b1    c1    50


The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.


Oracle:


UPDATE TABLEA

SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

/


Results after the update:


a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z


SQL Server:


UPDATE TABLEA

SET     b = TABLEB.b1,

c = TABLEB.c1,

d = TABLEB.d1

FROM TABLEA, TABLEB

WHERE TABLEA.a = TABLEB.a1

AND TABLEB.e1 > 40

GO


Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.


Results after the update:


a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z


DB2 LUW:


–Same as Oracle–


UPDATE TABLEA

SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);


Results after the update:


a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z


NOTE:


It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon.  If it is incorrect, then you can get wrong results.  The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.


In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:


a    b    c    d

————————————

1     Null      Null      Null

2     a1        b1         c1

3     Null     Null      Null

Wednesday, May 11, 2011

Maintain Scrollbar Position Inside UpdatePanel After Partial PostBack

Do you want to maintain the scroll position of a GridView, Div, Panel, or whatever that is inside of an UpdatePanel after an asynchronous postback? Normally, if the updatepanel posts back, the item will scroll back to the top because it has been reloaded. What you need to do is “remember” where the item was scrolled to and jump back to there after the postback. Place the following script after the ScriptManager on your page. And since the _endRequest event of the PageRequestManager happens before the page is rendered, you’ll never even see your item move!

Code Screen Shot below :



Actual Code below :


<script type="text/javascript">

var xPos,yPos;
var prm = Sys.WebForms.PageRequestManager.getInstance();

function BeginRequestHandler(sender,args){
if($get('<%= Panel1.ClientID %>') != null){
xPos = $get('<%= Panel1.ClientID %>').scrollLeft;
yPos = $get('<%= Panel1.ClientID %>').scrollTop;
}
}

function EndRequestHandler(sender,args){
if($get('<%= Panel1.ClientID %>') != null){
$get('<%= Panel1.ClientID %>').scrollLeft = xPos;
$get('<%= Panel1.ClientID %>').scrollTop = yPos;
}
}

prm.add_beginRequest(BeginRequestHandler);
prm.add_endRequest(EndRequestHandler);
</script>


<form runat="server">
<asp:ScriptManager id="ScriptManager1" runat="server" ScriptMode="Release" />
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Panel ID="Panel1" runat="server" >
<pre>
<asp:Literal ID="litXML" runat="server" />
</pre>
</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>
</form>

Friday, April 1, 2011

Backup and Restore From Sql to Local /Network Machine

Backup/Restore for local machine....

Declare
@dbName varchar(100)
Set @dbName = 'dbMurli'
Declare @filelocation varchar(Max)
Set @filelocation = 'D:/'+@dbName+'.bak';
BACKUP DATABASE @dbName TO DISK = @filelocation
Restore DATABASE @dbName FROM DISK = @filelocation

Backup/Restore for Network machine....

Declare @dbName varchar(100)
Set @dbName = 'dbMurli'
Declare @filelocation nvarchar(Max)
Set @filelocation = N'\\192.0.0.72\Share\db\'+@dbName+'.bak';
BACKUP DATABASE @dbName TO DISK = @filelocation
Restore DATABASE @dbName FROM DISK = @filelocation

If you Found

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command.

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE 'dbMurli'
FROM DISK = D:\dbMurli
.bak'
WITH REPLACE

Is Exists in SQL

For database check

if db_id('dbname') is not null

For table check

if object_id('object_name', 'U') is not null -- for table

For Procedure existing

if object_id('object_name', 'P') is not null -- for SP

Simple script to Restore all SQL Server databases

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
--BACKUP DATABASE @name TO DISK = @fileName
RESTORE DATABASE @name FROM DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

If you Found

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command. View Example

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE 'dbMurli'
FROM DISK = D:\dbMurli
.bak'
WITH REPLACE

Wednesday, March 9, 2011

Find Specific String or Char Count occur in a string

For getting The String count I have created three methods might be this will helpful to you.

in SQl server, we dont have any function which can return the number of words in a perticular string .but from small tricks we can do that . now Consider here we can separating the character on space basis

example :


DECLARE @String VARCHAR(4000)

SET @String = 'WelCome to D Murli Blog Spot.'

SELECT LEN(@String) - LEN(REPLACE(@String, ',', '')) + 1

Above query will return value 6 , but if the words are separate by more than one space then it will aslo count that space. but its wrong as per the answer.
in that case Create one function which can keep multiple spaces as a single space and return proper result

Below is a Function which can remove
white space and all and retrun peoper result.

CREATE FUNCTION [dbo].[WordCount] ( @inStr VARCHAR(4000) )

RETURNS INT

AS

BEGIN

DECLARE @Index INT

DECLARE @Char CHAR(1)

DECLARE @PrevChar CHAR(1)

DECLARE @WordCount INT

SET @Index = 1

SET @WordCount = 0

WHILE @Index <= LEN(@InStr)

BEGIN

SET @Char = SUBSTRING(@InStr, @Index, 1)

SET @PrevChar = CASE WHEN @Index = 1 THEN ' '

ELSE SUBSTRING(@InStr, @Index - 1, 1)

END

IF @PrevChar = ' ' AND @Char != ' '

SET @WordCount = @WordCount + 1

SET @Index = @Index + 1

END

RETURN @WordCount

END

GO

This is third method that will first split the string and count the string bases on loop

DECLARE @NextString NVARCHAR(40)

DECLARE @Pos INT

DECLARE @NextPos INT

DECLARE @String NVARCHAR(40)

DECLARE @Delimiter NVARCHAR(40)

DECLARE @Counter int

Set @Counter = 0

SET @String ='18,21,22'

SET @Delimiter = ','

SET @String = @String + @Delimiter

SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)

BEGIN

SET @NextString = substring(@String,1,@Pos - 1)

--SELECT @NextString -- Show Results

SET @String = substring(@String,@pos+1,len(@String))

SET @pos = charindex(@Delimiter,@String)

Set @Counter = @Counter + 1

END