Find all The User Defined Procedures in Sql

There is two ways to get the result:-
1. Using Comination of System define Procedure table and Schema table

Select * From sys.procedures As P
Inner Join sys.schemas As S On S.schema_id = P.schema_id
Where P.type = 'P' And is_ms_shipped = 0 And P.name Not Like 'Sp[_]%diagram%'

2. Using System define Object table
Select * From sys.objects
Where type = 'P' And is_ms_shipped=0 And name Not Like 'Sp_%diagram%'


Note:- is_ms_shipped Equals Zreo Means Show all the User defined events

Validate number in textbox using Javascript

function fnSubmit(){
var str = "ab126";
var ValidStr = "0123456789.";
var flag;
for(var i=0;i if(ValidStr.indexOf(str.charAt(i)) > 0)
flag = true;
else
flag = false;
}
}

Copy Entire Folder from one location to another in .Net

Copying the entire file under the folder there is two ways
1. Using component
2. Using Programatically

=======================================================================================
1) Add this line of code and pass your source folder path and destination folder path, then this component will copying all the files from source to desc.

Microsoft.VisualBasic.FileIO.FileSystem.CopyDirectory(fromDirectory, toDirectory);


2. Via Programatically

first you need to add namesapce System.IO on the top of the page


protected void Page_Load(object sender, EventArgs e)
{
DirectoryInfo SourceLoc = new DirectoryInfo(@"D:\Projects\SitePages\common");
DirectoryInfo TargetLoc = new DirectoryInfo(@"D:\Projects\Production\common");

CopyAllFiles(SourceLoc, TargetLoc);
}

///

/// Copy All files from the source directory into the target directory
///

///
///
public void CopyAllFiles(DirectoryInfo Source, DirectoryInfo target)
{
//Validate the existance of the target dir, if fail then create the dir
if (Directory.Exists(target.FullName) == false)
{
Directory.CreateDirectory(target.FullName);
}

//Copy individual file into the new directory
foreach (FileInfo file in Source.GetFiles())
{
file.CopyTo(Path.Combine(target.ToString(), file.Name), true);
}

//Recursion loop to copy each sub directory
foreach (DirectoryInfo diSource in Source.GetDirectories())
{
DirectoryInfo nexttarget = target.CreateSubdirectory(diSource.Name);
CopyAllFiles(diSource, nexttarget);
}
}

Create Constraints/Delete Foreign Key linked record

Suppose you want to delete the one record that is associate/linked to some other table with foreign key reference then we need to set/alter cascade command on the table then it will delete/update the record which is associated to the same table/column


For Reference:-

1. Create One Table Called tblFirst With Primary Key
2. Create another table called tblSecond with foreign key.make sure your column key column doesn't contain any identity
3. While creating the foreign key you need to set the cascade. This cascade word delete/update the all records that linked to the same row.


Create table tblFirst(Id Int Identity(1,1) Primary Key, Name varchar(50))


Create table tblSecond(Id Int Primary Key, Phone varchar(50), Foreign Key (ID) References tblFirst(Id) On Delete Cascade On Update Cascade)

INsert into tblFirst(Name) values('Murli')
INsert into tblFirst(Name) values('Anup')

INsert into tblSecond(Id,Phone) values(1,'9323399705')
INsert into tblSecond(Id,Phone) values(2,'9000000000')

Delete From tblFirst Where Id = 1

Create Linked Server From Query in Sql Server

EXEC sp_addlinkedserver
@server='MY_TEMP_linkServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='MYDATASOURCE\SQLEXPRESS2005'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MY_TEMP_linkServer'
, @useself = 'false'
, @locallogin = 'sa'
, @rmtuser = 'sa'
, @rmtpassword = 'admin123'


Create Linked Server From Query:-

For Link the Two or More Server You need to follow this two steps

A) First Need to create/assign/add server name(Which you can use in program) in linked server list
B) Then set your user id and password to same linked list.

Note :- i) Server Name could be anything which you want to associate in Query.
ii) Need to assign Provider
iii) User names are must be permission to view the table/database.

Syntax :-

EXEC SP_ADDLINKEDSERVER @server=N'SOMESERVER', @srvproduct=N'', @provider=N'SQLOLEDB', @datasrc=N'IP/HOST of server'
GO
EXEC SP_ADDLINKEDSRVLOGIN 'SOMESERVER', 'false', 'remoteuser', 'remoteuser', 'remotepassword'
GO

For Example for Sql Server :-

A)
EXEC sp_addlinkedserver
@server='my_temp_inventory',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='INDIGO49\SQLEXPRESS'

B)
EXEC sp_addlinkedsrvlogin 'my_temp_inventory',
'true', 'my_db_username', 'my_db_username', 'murli'

Another Example :-

EXEC sp_addlinkedserver @server='my_temp_inventory',@srvproduct='',@provider='SQLNCLI',@datasrc='mutli89\SQLEXPRESS2008'
EXEC sp_addlinkedsrvlogin 'my_temp_inventory','true', 'sa', 'sa', 'admin123'

sp_linkedservers --SHOW ALL linked server list

Select * from LINKEDSERVERNAME.DATABASENAME.OWNER.TABLENAME --Execute records

SELECT * FROM OPENQUERY(LINKED_SERVER, ' DROP TABLE DB.dbo.TABLE SELECT NULL') -- drop table

Best Example :-
http://technet.microsoft.com/en-us/library/ms190479.aspx

http://networking.ringofsaturn.com/SQL/linkedservers.php

The following example enables the SQL Mail extended stored procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'SQL Mail XPs', 1;
GO
RECONFIGURE;

Create Parameterize Query in Sql

Create your query as usewal and pass parameters in a querys


DECLARE @Query NVARCHAR(max)
DECLARE @Parameters NVARCHAR(max)

SET @Query = N'Select * from dbSeminar.dbo.indigo_tech Where Gender = @Gender And EmpGroup = @EmpGroup And FirstName = @FirstName'

SET @Parameters = N'@Gender varchar(3),@EmpGroup int,@FirstName varchar(25)'

EXECUTE sp_executesql @Query, @Parameters, @Gender = 'M' ,@FirstName = 'Murli', @EmpGroup = 1


OR
Exec Sp_executesql N'Select * from Deepak_PC.mydb.dbo.Branch Where RegionID = @RegionID',N'@RegionID int',@RegionID = 1