Digicorp

Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.

For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.

Today, I will explain how to get data from Dataset into Sqlserver Table.

– Code for .Net

Dim ds as new Dataset

Dim dt as new Table

dt.TableName = “MyTable”

ds.Tables.add(dt)

Dim lStrWriter As New StringWriter

Dim lStr As String

ds.WriteXML(lStrWriter)

lstr = lStrWriter.Tostring

Pass this lstr variable to the procedure as parameter – @xmldoc.

Now write the code procedure to get the data from Dataset.

— Code in Sqlserver Store Procedure

CREATE PROCEDURE [dbo].[TestXML]

@xmldoc text — Give Text datatype for getting xml from dataset

as

———Read the Datset  From XML —————–

– call sp_xml_preparedocument

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #TestTable

FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)

WITH (pk_Userid  BigInt,

UserName  Varchar(10))

EXEC sp_xml_removedocument @idoc

———- End Read ———-

END

BY the use of this procedure you get all the data from dataset in to the ”#Testtable” and we can use this as Sqlserver table in procedure.

I hope this article will be useful to you, Please give feedback on this article.

Today, we will examine something very simple and very generic that can apply to hordes of programming languages. Let’s take a common question that is frequently discussed – What is difference between Line Feed (\n) and Carriage Return (\r)?

Prior to continuing with this article let us first look into few synonyms for LF and CR.

Line Feed – LF – \n – 0×0a – 10 (decimal)

Carriage Return – CR – \r – 0×0D – 13 (decimal)

Now that we have understood that we have two different options to get new line, the question that arises is – why is it so?

The reason is simple. Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. Our favorite OS windows needs both the characters together to interpret as new line, which is ‘\r\n’. This is the reason why a file created in one OS does not open properly in another OS and makes it messy.

Now, let us see how we can create a new line in SQL Server. It is a very simple script yet very useful when we have to do run print something or generate scripts. I have illustrated two examples below that are very easy to understand. In the first example, there are no new line chars inserted and for the same, everything is displayed in a single line. However, in the second example, new line char is inserted and the lines are separated with a new line.

Example 1: No new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
GO

Example 2: With new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
GO


I hope my examples make things more clear to you all. Let me have your feedback on this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Download code & SQL statements:

Introduction:

We usually face problem in Stored Procedures and other database object when we need to implement some complicated logic within it. We found inefficient performance when we try to implement complex logic & business rules in database objects. In many cases we found C# or VB classes more powerful to implement such things. Microsoft has launched a new concept to resolve such issues with SQL server 2005 called “CLR Stored Procedure”.

What is CLR Stored Procedure?

Now, let us understand CLR stored procedure. CLR as most of .Net programmer knows is Common Language Runtime and Stored Procedures are routine stored procedures of database. Thus, CLR Stored Procedures are combination of both. As we all know, Common Language Runtime is core .Net component. The Common Language Runtime is runtime execution environment which supplies managed code with various services like cross language integration, code access security, lifetime management of object, resources management, threading, debugging & type safety etc. So now, CLR Stored Procedures are .Net objects which run in the memory of database.

The very first usage of CLR Stored Procedures can be said accessing system resources. Accessing system resources could also be done using Extended Stored Procedures which are again database object like Stored Procedures, Functions etc. Extended Stored Procedures can do most of the things which a standard executable program can do. Then, why are CLR Stored Procedures? The very first advantage of CLR Stored Procedures is it is a managed object unlike Extended Stored Procedures, which are unmanaged objects. The common thing between them is both runs under database memory. In this way CLR Stored Procedures gives all the benefits of managed objects. Following screen explains memory allocation while execution of CLR Stored Procedure.

When should I use CLR Stored Procedure?

Extended stored procedures run in the same process space as the database engine, memory leaks, bugs etc., can affects the performance of database engine. CLR stored procedures resolves these issues as they are managed object and runs per specifications of Common Language Runtime. CLR Stored Procedures can replace a standard stored procedure that contains complex logic and business rules. CLR Stored Procedures takes benefit of .Net classes and thus makes easy to implement complex logic, calculation, intense string operations, complex iterations, data encryptions etc., that are difficult to obtain in standard stored procedures. Standard stored procedures are still best for data oriented tasks. CLR Stored Procedures not only includes stored procedures but also includes Functions, Triggers etc. CLR Stored Procedures are compiled one so gives better performance.

Benefits of CLR Stored Procedures:

1. Gives better results while executing complex logic, intense string operation or string manipulations, cryptography, accessing system resources and file management etc.
2. CLR Stored Procedures are managed codes so ensures type safety, memory management etc.
3. Better code management and provides object oriented programming capability thus enables encapsulation, polymorphism & inheritance.
4. Convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that .Net Framework supports.
5. CLR Stored Procedures can also be used with Oracle 10g Release 2 or later versions.
6. Not convenient in all contexts for e.g. it should not be used to execute simple queries. In that case standard stored procedures give better results.
7. Deployment may be difficult in some scenarios.

Drawbacks of CLR Stored Procedures:

1. Not convenient in all contexts for e.g. it should not be used to execute simple queries. In that case standard stored procedures give better results.
2. Deployment may be difficult in some scenarios.

Standard Stored Procedures vs. CLR Stored Procedures:

You are the best judge when to use regular Stored Procedures and when to use CLR Stored Procedures. CLR Stored Procedures can be used in following scenarios.

1. When the program requires complex logic or business rules.
2. When the flow is CPU intensive. CLR Stored Procedures gives better results as they are in complied form and managed one.
3. The tasks which are not possible in TSQL, accessing system resources, cryptography, accessing web services etc.
4. In option of Extended Stored Procedures. One should always consider CLR Stored Procedures before going for Extended Stored Procedures.
5. An operation requires higher data safety.

Creating CLR Stored Procedure step by step:

Let us create one simple CLR Stored Procedure which fetches all the rows from one table of the database. I have attached whole list of SQL statement used for creating database, creating table, inserting dummy records in the table etc., under “SQL statements used in the demo” section.

Application development specification:

• IDE: Visual Studio 2008
• Framework: 3.5 with SP 1
• Language: C# 3.0
• Database MS SQL Server 2005 Express edition

Steps to create CLR Stored Procedure:

1) Open Microsoft Visual Studio >> Click on New Project >> Select Database Projects >> SQL Server Project.

2) You can choose reference of existing database connection or click on Add New Reference.

3) If you selects from existing references skit step 3 else add new database reference as shown in following image and click on Test Connection to test the connection.

4) On clicking on OK button, Visual Studio will ask you to enable SQL/CLR debugging on the selected connection. You can select “Yes” to enable debugging or “No” to disable the same.

5) Once the database reference and debugging option is selected, the project will be displayed in Solution Explorer. Select the project and right click on Solution Explorer >> Click on Add >> Stored Procedure.

6) Add new procedure from the installed templates as shown in following screen. Give proper name to it.

7) Once you select the template, it will create .cs file with the content shown in following image.

8 ) Add following code in the method already created. Pass “context connection=true” as connection string in the constructor while creating new SqlConnection. This CLR stored procedure is going to be the part of the database, so it will be the internal part of database so no need to connect database externally. So, no need to provide connection string that we usually provide in applications. Then Click on Build menu >> Click on Build Solution. Also click on Build menu >> Deploy solution. This will deploy the assembly to the database for which we have made connection initially.

9) Now, select the database >> Programmability. Right click on Stored Procedures >> Click on Refresh. The list of Stored Procedures should show one newly added stored procedure. Also right click on Assemblies >> click on Refresh. This should show newly added Assembly. Also, enable CLR Stored Procedure by following query.

sp_configure ‘clr enabled’, 1

Run following query to take effect or above query.

RECONFIGURE

Now, execute the stored procedure. It should give similar results shown in following screen.

SQL statements used in the demo:

Following is the queries to create database, create table, insert records in the table etc.

–Create a new databse for demo

CREATE DATABASE DbForClrDemo

–Use database

USE DbForClrDemo

–Create table for CustomerSalesInformation

CREATE TABLE [dbo].[CustomerSalesInformation](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NOT NULL,

[Sales] [decimal](18, 2) NOT NULL DEFAULT ((0)),

CONSTRAINT [PK_CustomerSalesInformation] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

–Insert dummy data to CustomerSalesInformation table

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Virat Kothari’, 50000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Dhruval Shah’, 5000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Urvish Sheth’, 15000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Rakesh Bajania’, 25000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Dhaval Shah’, 150000)

–Enable CLR Stored Procedure in database

sp_configure ‘clr enabled’, 1

–Run following statuement to take effect of above statement

RECONFIGURE

–Now execute our CLR Stored Procedure. Remember “ClrDemo” is name of our Stored Procedure

EXEC [dbo].ClrDemo

Conclusion:

CLR Stored procedures are very simple and can be used in most complex scenarios.

Bibliography:

1. MSDN (http://msdn.microsoft.com/en-us/library/ms131094.aspx)
2. Code project
3. http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1251402,00.html

Reference : Virat Kothari (www.viratkothari.wordpress.com)

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is no tpossible. Except very extreme situations dedicated administrator connection (DAC) is always successfull.

Let us see how we can use DAC using sqlcmd.

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9
GO

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

SELECT
req.session_id,
req.status,
req.total_elapsed_time
FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.

KILL 52

Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server is great product and it has many feature which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.

Method 1: Multiple statements for declaring multiple variables

DECLARE @Var1 INT
DECLARE @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

Method 2: Single statements for declaring multiple variables

DECLARE @Var1 INT, @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

From above example it is clear that multiple variables can be declared in one statement. In SQL Server 2008 when variables are declared they can be assigned values as well.

Reference : Pinal Dave (http://www.SQLAuthority.com)

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT -  Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions

Reference : Pinal Dave (http://blog.SQLAuthority.com), Original Source