pass table name as a parameter in stored procedure

use northwind
--Select Rows
Create proc mm(@tablename varchar(max))
as
begin
declare @tablename1 as varchar(max)
 set @tablename1 ='select * from ' + @tablename
 --print @tablename1
EXEC (@tablename1)
end
--EXEC mm Customers


--Delete Rows
alter proc DeleteDynamic(@Tablename Varchar(max),@OId Varchar(max))
As
Begin
Declare @tablename1 as varchar(max)
Set @tablename1='delete from '+@Tablename + ' Where orderId='+ @OId
Exec(@tablename1)
End
-- DeleteDynamic orders,10248

Continue Reading

XMLData in SqlServer /ASp.Net Xml File insert Via Sp

create table XmlData(CustID int,EmpID int)

Declare @hdoc int
Declare @doc Varchar(1000)
set @doc='
<?xml version="1.0" encoding="utf-8"?>
<XmlData>
<CustID>105</CustID>
<EmpID>105</EmpID>
</XmlData>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
INSERT INTO XmlData( CustID, EmpID)
SELECT CustID, EmpID FROM Openxml( @hdoc, '/XmlData', 3) WITH ( CustID int,EmpID int)
exec sp_xml_removedocument @hdoc

select * from XmlData

--Procedure method insert Xml Value to sql server

create Proc InsertXmlDocument(@documents varchar(max))
With Encryption
as
begin
Declare @doc Varchar(1000)
set @doc=@documents
Declare @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
INSERT INTO XmlData( CustID, EmpID)
SELECT CustID, EmpID FROM Openxml( @hdoc, '/XmlData', 3) WITH ( CustID int,EmpID int)
exec sp_xml_removedocument @hdoc
End

Exec InsertXmlDocument '
<?xml version="1.0" encoding="utf-8"?>
<XmlData>
<CustID>999</CustID>
<EmpID>888</EmpID>
</XmlData>'

-- Asp.net Using Insert Proc Via Xml File

 string xml = File.ReadAllText("C:\\Test.xml");
            string strQry = "exec InsertXmlDocument '" + xml + "'";
            DB.Instance.GetDataSet(strQry);

Continue Reading

CDC in Sql Developer Enterprise Edition

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.


CREATE DATABASE TestDB;
Use TestDB
GO

CREATE TABLE Employee(
EID INT IDENTITY(1,1)PRIMARY KEY,
ENAME VARCHAR(50),
DEPT VARCHAR(20)
);

INSERT INTO Employee
Values ('Rambo','IT'),
('Jason','Finance'),
('Brad','HR');
SELECT * FROM Employee;

Use TestDB
GO
EXEC sys.sp_cdc_enable_db

SELECT name,is_cdc_enabled FROM sys.databases WHERE name='TestDB'

EXEC sys.sp_cdc_enable_table
    @source_schema ='dbo',
    @source_name ='Employee',
    @role_name ='EmpCDCRole',
    @supports_net_changes = 1
  SELECT name,is_tracked_by_cdc FROM sys.tables WHERE name='Employee'

//DDL And DML Operation
DELETE FROM Employee WHERE EID=3
INSERT INTO Employee VALUES('Mary','HR')
UPDATE Employee SET ENAME='Nichole' WHERE EID=2
UPDATE Employee SET ENAME='EMMA' WHERE EID=2

SELECT * FROM Employee

DECLARE @Begin_LSN binary(10), @End_LSN binary(10)
-- get the first LSN
SELECT @Begin_LSN =sys.fn_cdc_get_min_lsn('dbo_Employee')
-- get the last LSN
SELECT @End_LSN =sys.fn_cdc_get_max_lsn()
-- returns net changes
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Employee(
 @Begin_LSN, @End_LSN,'ALL');

select * from cdc.ddl_history

Ref: http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008----Change-Data-Capture-150-Part-I.htm

Example 2:
use mssqltips
go

declare
 @is_cdc_enabled bit
,@rc int


--
-- turn on change data capture at the database level
--

select @is_cdc_enabled = is_cdc_enabled
from sys.databases
where [name] = N'mssqltips'

if @is_cdc_enabled = 0
    exec sys.sp_cdc_enable_db
else
    print 'CDC is already enabled on the database'


--
-- turn on cdc for the customer table
--
if not exists (select 1 from sys.tables
               where [name] = N'customer'
               and is_tracked_by_cdc = 1)
    exec @rc = sys.sp_cdc_enable_table
    @source_schema = N'dbo'
    ,@source_name = N'customer'
    ,@role_name = N'cdc_admin'
    ,@capture_instance = N'customer_all'
    ,@supports_net_changes = 1
    ,@index_name = NULL
    ,@captured_column_list = NULL
    ,@filegroup_name = NULL
else
    print 'CDC is already enabled on customer'


--
-- turn off change tracking
--
/*
exec sys.sp_cdc_disable_table
  @source_schema = 'dbo' ,
  @source_name = 'customer',
  @capture_instance = 'customer_all'

 */


--
-- Track the history of processing CDC by capture instance
--
create table dbo.cdc_capture_log (
    cdc_capture_log_id    int identity not null
,    capture_instance    nvarchar(50) not null   
,    start_time            datetime not null   
,    min_lsn                binary(10) not null
,    max_lsn                binary(10) not null
,    end_time            datetime null
,    insert_count        int not null default 0
,    update_count        int not null default 0
,    delete_count        int not null default 0
,    status_code            int not null default 0
)

Continue Reading

MemberShip Web Config

<?xml version="1.0"?>

<!--

  For more information on how to configure your ASP.NET application, please visit

  http://go.microsoft.com/fwlink/?LinkId=169433

  -->

<configuration>

  <appSettings>

    <add key="muniyappan" value="10" />

    <add key="Email" value="****************" />

  </appSettings>

  <connectionStrings>

      <remove name="PSConStr" />

      <add name="PSConStr" connectionString="Data Source=***********;Initial Catalog=*********;Persist Security Info=True;User ID=*******;password=*********;MultipleActiveResultSets=True;Trusted_Connection=False; Enlist=false;Max Pool Size=1000;Packet Size=512;Min Pool Size=5; Pooling=True; Connection Timeout=0" providerName="System.Data.SqlClient" />

  </connectionStrings>

  <system.web>

    <compilation debug="true" targetFramework="4.0" >

    </compilation>

    <authentication mode="Forms">

      <forms loginUrl="~/Account/Login.aspx" timeout="2880" />

    </authentication>

    <customErrors mode="Off" >

    </customErrors>

    <roleManager enabled="true">

      <providers>

        <clear />

        <add connectionStringName="PSConStr" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

        <add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

      </providers>

    </roleManager>

    <membership defaultProvider="AspNetSqlMembershipProvider" userIsOnlineTimeWindow="15" hashAlgorithmType="">

      <providers>

        <clear />

        <add connectionStringName="PSConStr" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

      </providers>

    </membership>

  </system.web>

  <system.webServer>

   <staticContent>

      <clientCache cacheControlMode="DisableCache" cacheControlMaxAge="1.00:00:00" />

    </staticContent>

     <validation validateIntegratedModeConfiguration="false" />

   </system.webServer>

</configuration>

Continue Reading