OepnRowSet Example

DECLARE @WorkingTempTbl Varchar(20)
SET @WorkingTempTbl = 'FL_HUM_Census';

DECLARE @SourceFileName Varchar(200)
SET @SourceFileName = 'D:\JSAOBDSL_2015-06-01'

DECLARE @SqlTempTbl varchar(MAX)

SET @SqlTempTbl = '
Insert into ' + @WorkingTempTbl + '(
RECORDTYPE)
Select RECORDTYPE from OPENROWSET( BULK ''' + @SourceFileName + ''' ,FORMATFILE = ''D:\GetRowCount.xml'')'

Exec @SqlTempTbl


BULK INSERT FL_HUM_Census
 select *  FROM 'D:\JSAOBDSL_2015-06-01.csv'
   WITH (FORMATFILE = 'D:\JSAOBDSL_19cols.xml');
GO
SELECT * FROM FL_HUM_Census;
truncate Table FL_HUM_Census
GO

INSERT into FL_HUM_Census
SELECT * FROM   OPENROWSET
( BULK 'D:\Sample_OpenRowSet\JSAOBDSL_2015-06-18.csv',
FORMATFILE = 'D:\JSAOBDSL_19cols.xml',FIRSTROW = 2)t;

Select * from ETLTableMetaData
Select * from ETLTables

SQL Server db_executor role to grant execute permission

Recently I was answering to a question on a SQL Server public forum, that how to grant only execute permission on a database for some users? Yes, It’s very easy to grant execute permission to a user but somehow I feel there should have some built-in roles like db_datareader or db_datawriter for the same? Anyway it’s not that difficult to create one. Let’s create a role and name it db_execproc, then add role-members.

 

USE [DBA] --Change the database name

GO

--It will drop the role if it is already exists.

DECLARE @RoleName sysname

set @RoleName = N'db_execproc'

IF @RoleName <> N'public' and (select is_fixed_role from sys.database_principals where name = @RoleName) = 0

BEGIN

DECLARE @RoleMemberName sysname

DECLARE Member_Cursor CURSOR FOR

select [name]

from sys.database_principals

where principal_id in (

select member_principal_id

from sys.database_role_members

where role_principal_id in (

select principal_id

FROM sys.database_principals where [name] = @RoleName AND type = 'R'))

OPEN Member_Cursor;

FETCH NEXT FROM Member_Cursor

into @RoleMemberName

DECLARE @SQL NVARCHAR(4000)

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'ALTER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')

EXEC(@SQL)

FETCH NEXT FROM Member_Cursor

into @RoleMemberName

END;

CLOSE Member_Cursor;

DEALLOCATE Member_Cursor;

END

/****** Object:  DatabaseRole [db_execproc]    Script Date: 3/5/2016 8:40:20 PM ******/

DROP ROLE [db_execproc]

GO

/****** Object:  DatabaseRole [db_execproc]    Script Date: 3/5/2016 8:40:20 PM ******/

CREATE ROLE [db_execproc]

GO

--Grant EXECUTE permission at the schema level to the role.

GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;

GO

--EXEC sp_addrolemember N'db_execproc', N'TestUser'; -- Add user here

GO

Get The OUTPUT Paramete Value from SP Using Dot Net

Get The OUTPUT Paramete Value from SP Using Dot Net using asp.net. This code can be used to get the data from database using Out parameter

Imports System.Data

Imports System.Data.SqlClient

Partial Class SP

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

 Handles Button1.Click

        Try

            Dim Con As New SqlConnection

            Con.ConnectionString = "Password=RR;Persist Security Info=True;

User ID=RR;Initial Catalog=RR;Data Source=RR"

            Con.Open()

            Dim INparam, OutPram As New SqlParameter

            INparam.Direction = ParameterDirection.Input

            INparam.SqlDbType = SqlDbType.Int

            INparam.ParameterName = "@ID"

            INparam.Size = 20

            INparam.Value = Textbox1.text

            OutPram.Direction = ParameterDirection.Output

            OutPram.SqlDbType = DbType.String

            OutPram.ParameterName = "@Name"

            OutPram.Size = 200

            Dim com As New SqlCommand

            com.Connection = Con

            com.CommandType = CommandType.StoredProcedure

            com.CommandText = "spGetOutput"

            com.Parameters.Add(INparam)

            com.Parameters.Add(OutPram)

            com.ExecuteNonQuery()

            Label1.Text = OutPram.Value()

        Catch ex As Exception

        End Try

    End Sub

End Class

XmlReader to Retrieve Multiple Rows

XmlReader to Retrieve Multiple Rows

You can use the SqlCommand object to generate an XmlReader object, which provides forward-only, stream-based access to XML data. The command (usually a stored procedure) must generate an XML-based result set, which for SQL Server 2000 usually consists of a SELECT statement with a valid FOR XML clause. The following code fragment illustrates this approach:

public void RetrieveAndDisplayRowsWithXmlReader()

{

  using( SqlConnection conn = new SqlConnection(connectionString) )

  {;

    SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );

    cmd.CommandType = CommandType.StoredProcedure;

try

  {

      conn.Open();

    XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();

    while ( xreader.Read() )

    {

      if ( xreader.Name == "PRODUCTS" )

      {

        string strOutput = xreader.GetAttribute("ProductID");

        strOutput += " ";

        strOutput += xreader.GetAttribute("ProductName");

        Console.WriteLine( strOutput );

      }

    }

    xreader.Close();  // XmlTextReader does not support IDisposable so it can't be

                      // used within a using keyword

  }

}

 

The preceding code uses the following stored procedure:

CREATE PROCEDURE DATRetrieveProductsXML

AS

SELECT * FROM PRODUCTS

FOR XML AUTO

GO

SqlDataReader to Retrieve Multiple Rows

SqlDataReader to Retrieve Multiple Rows

The SqlDataReader approach to retrieve multiple rows is illustrated in the following code fragment.

using System.IO;

using System.Data;

using System.Data.SqlClient;

public SqlDataReader RetrieveRowsWithDataReader()

{

  SqlConnection conn = new SqlConnection(

         "server=(local);Integrated Security=SSPI;database=northwind");

  SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn );

  cmd.CommandType = CommandType.StoredProcedure;

  try