Simple way to creating the insert query for the table in SQL Server

Simple way to creating the insert query for the table in SQL Server

Declare @TableName sysname = 'MemberRegistration'

Declare @result varchar(max) = ''

Declare @StartString varchar(max) = 'Insert into '+@TableName+''

SELECT

       @result = @result + '

       [' + ColumnName + '],' + +''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @StartString + '

(' + @result + ')

values ('

SELECT

       @result = @result + '

       @' + ColumnName + ',' + +''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @result + ')'

SET @result = REPLACE(@result, ',)', ')')

print @result

Read More »

How to disable the unwanted SSRS report export formats using asp.net c#

How to disable the unwanted SSRS report export formats using asp.net c#

/// <summary>

        /// Disable Unwanted Export Formats

        /// </summary>

        public void DisableUnwantedExportFormats()

        {

            FieldInfo info;

            foreach (RenderingExtension extension in this.rptname.ServerReport.ListRenderingExtensions())

            {

                if (extension.Name == "EXCEL" || extension.Name == "PDF" || extension.Name == "WORD")

                {

                    //info = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);

                    //if (info != null)

                    //{

                    //    Extension rsExtension = info.GetValue(extension) as Extension;

                    //    if (rsExtension != null)

                    //    {

                    //        rsExtension.Visible = false;

                    //    }

                    //}

                    info = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);

                    info.SetValue(extension, true);

                }

                else

                {

                    info = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);

                    info.SetValue(extension, false);

                }

            }

        }

Call this method in page load method

protected void Page_Load(object sender, EventArgs e)

        {

            try

            {

               

                if (!IsPostBack)

                {

                   

                }

                this.DisableUnwantedExportFormats();

            }

            catch (Exception ex)

            {

            }

        }

Read More »

Simple way to creating the Store procedure parameters for the table in SQL Server

Simple way to creating the Store procedure parameters for the table in SQL Server

Step 1: Pass the Table name as parameter for the below query and execute

Using the code

//

// code

//

declare @TableName sysname = '@TableName'

declare @result varchar(max) = ''

SELECT

       @result = @result + '

@' + ColumnName + ' ' + ColumnType + ''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + ') =NULL,'

              ELSE typ.name + '=NULL,'

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @result

print @result

Read More »

SQL Server Split Function

SQL Server Split Function

Split the input string and convert to table in SQL Server

Introduction

This article explains you about the SQL function to get the table format while passing the comma separator string

Using the code

Copy the below code and page in the SQL editor

--//

--// Main Function

--//

CREATE FUNCTION dbo.Split

(

    @Line nvarchar(MAX),

    @SplitOn nvarchar(5) = ','

)

RETURNS @RtnValue table

(

    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    Data nvarchar(100) NOT NULL

)

AS

BEGIN

    IF @Line IS NULL RETURN

    DECLARE @split_on_len INT = LEN(@SplitOn)

    DECLARE @start_at INT = 1

    DECLARE @end_at INT

    DECLARE @data_len INT

    WHILE 1=1

    BEGIN

SET @end_at = CHARINDEX(@SplitOn, @Line, @start_at)

SET @data_len =

                           CASE @end_at

                                  WHEN 0 THEN LEN(@Line)

                                  ELSE @end_at - @start_at

                           END

INSERT INTO @RtnValue (data)

       VALUES (SUBSTRING(@Line, @start_at, @data_len));

IF @end_at = 0 BREAK;

SET @start_at = @end_at + @split_on_len

END

RETURN

END

Steps to check the function execution

select * from dbo.split('S,D,U,V',',')

OutPut:-

	ID	Data
1	1	S
2	2	D
3	3	U
4	4	V

Read More »

How to run the tables & Store procedure from one database to another database using SQL Server

How to run the tables & Store procedure from one database to another database using SQL Server

For Execution the Table

EXEC sys.sp_dropserver @server = 'VCNSQL90.sample.com', @droplogins = 'droplogins';

EXEC master.dbo.sp_addlinkedserver @server = N'VCNSQL90.webhost4life.com', @srvproduct=N'VCNSQL90. sample.com', @provider=N'SQLNCLI', @datasrc=N'VCNSQL90. sample.com'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VCNSQL90. sample.com',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='**********'

GO

exec sp_serveroption @server='VCNSQL90. sample.com', @optname='rpc', @optvalue='true'

exec sp_serveroption @server='VCNSQL90. sample.com', @optname='rpc out', @optvalue='true'

SELECT * FROM ['VCNSQL90. sample.com].smartfleetproduct.dbo.DataSendingDetails

For Executing the Storeprocedure

EXEC sys.sp_dropserver @server = 'VCNSQL90.sample.com', @droplogins = 'droplogins';

EXEC master.dbo.sp_addlinkedserver @server = N'VCNSQL90.webhost4life.com', @srvproduct=N'VCNSQL90. sample.com', @provider=N'SQLNCLI', @datasrc=N'VCNSQL90. sample.com'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VCNSQL90. sample.com',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='**********'

GO

exec sp_serveroption @server='VCNSQL90. sample.com', @optname='rpc', @optvalue='true'

exec sp_serveroption @server='VCNSQL90. sample.com', @optname='rpc out', @optvalue='true'

EXECUTE ['VCNSQL90. sample.com].smartfleetproduct.dbo.AlertAll

Read More »