Exporting SQL Server Data using BCP Utility

Problem

Develop a solution that automates the process of exporting specific data from a Microsoft SQL Server database to a CSV file using the BCP utility. The solution should allow to specify the database, table, fields, and destination file path as inputs, and generate the CSV file with the desired data automatically.

Introduction:

In today’s data-driven world, the ability to export data from databases is crucial for various purposes, such as analysis, reporting, and sharing information. One popular tool for exporting data from Microsoft SQL Server is the Bulk Copy Program (BCP) utility. In this blog post, we will explore a step-by-step guide on how to use the BCP utility to export data from a SQL Server database and save it to a CSV file.

Step 1: Understanding the BCP Utility

BCP is a command-line tool provided by Microsoft SQL Server. It allows you to import and export large amounts of data between SQL Server databases and external files. With BCP, you can control the format and structure of the exported data, making it versatile for various data export needs.

Step 2: Setting Up the Command

Let’s break down the follwing bcp command and understand its components:bcp "select fldName, fldValue from [DBNAME].[dbo].[tblName]" queryout "Q:\Jeevan\exportdata.csv" -c -t, -S DBServer -T

• bcp: The command itself that invokes the BCP utility.
• "select fldName, fldValue from [DBNAME].[dbo].[tblName]": The SQL query that selects the desired fields from the table you specified. Replace [DBNAME], [dbo], and [tblName] with the actual database, schema, and table names, respectively.
• queryout: Specifies that the BCP utility should export the data from the SQL query.
• "Q:\Jeevan\exportdata.csv": The file path and name of the output CSV file. Modify it to the desired location and filename.
• -c: Specifies that the data should be exported in a character format.
• -t,: Specifies the field terminator character to be a comma (,) in this case.
• -S DBServer: Specifies the SQL Server instance or server name where the database resides. Replace DBServer with the appropriate server name or connection string.
• -T: Specifies that the BCP utility should use Windows Authentication for the connection.

Step 3: Executing the Command

To execute the BCP command, follow these steps:

1. Open the Command Prompt or your preferred command-line interface.
2. Navigate to the directory where the BCP utility is located. By default, it can be found in the SQL Server installation directory under the “Tools\Binn” folder (ex. C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn). IF PATH variable is configures bcp caommand can be executed anywhere
3. Copy and paste the modified BCP command into the command-line interface.
4. Press Enter to execute the command.

Step 4: Verifying the Exported Data

Once the BCP command is executed successfully, navigate to the file path specified in the command ("Q:\Jeevan\exportdata.csv" in the provided command) to find the exported CSV file. Open the file using a spreadsheet application or a text editor to verify the exported data.

Key features of bcp

1. High-performance data transfer: BCP is designed for high-speed data transfer between SQL Server and external files. It can handle large volumes of data quickly and efficiently.
2. Command-line interface: BCP is a command-line tool, which allows for automation and scripting of data import/export operations. It can be easily integrated into batch files or used within scripts for seamless data processing.
3. Flexible data formats: BCP supports various data formats, including delimited text files, fixed-width text files, native SQL Server binary files, and XML format. This flexibility enables importing and exporting data in a format that best suits your needs.
4. Customizable field and row terminators: BCP allows you to specify custom field and row terminators when importing or exporting data. This is particularly useful when working with non-standard or special characters in your data.
5. Data transformations: BCP provides options for transforming data during the import or export process. You can define custom transformations using format files, which allow you to map data columns, skip columns, apply data conversions, and handle special data formats.
6. Batch size control: BCP allows you to control the batch size when importing or exporting data. You can specify the number of rows per batch, which helps optimize performance and manage memory usage.
7. Error handling and logging: BCP provides options to handle errors during data transfer and log error messages to a specified file. This enables you to track and troubleshoot any issues that may occur during the import or export process.
8. Security features: BCP supports authentication and encryption options to ensure secure data transfer. You can specify the login credentials and use SSL encryption for data protection.
9. Integration with SQL Server features: BCP integrates well with other SQL Server features and components. It can be used in conjunction with SQL Server Integration Services (SSIS), SQL Server Agent, and other tools for comprehensive data management tasks.

These are some of the main features offered by the BCP utility. Depending on your specific use case, you can leverage these features to efficiently import or export data in bulk with Microsoft SQL Server.

Conclusion:

The BCP utility is a powerful tool for exporting data from SQL Server databases to external files. By following the step-by-step guide provided in this blog post, you can easily export data from your SQL Server database and save it to a CSV file. Remember to modify the command with your specific database, table, file path, and connection details to suit your requirements.

Calling web service from sql server using sql clr

Problem :

In my previous project I was been asked call web service from sql server stored procedure.

So I get it done using SQL CLR. So using CLR we can run managed code inside sql server.

Code that runs within the CLR is referred to as managed code.

We can create stored procedures, triggers, user defined types, and user-defined aggregates in managed code.

We can achieve significant performance increases because managed code compiles to native code prior to execution.

We can use SQL CLR in in SQL Server 2005 and later.

Why Sql CLR in sql server:

In some cases some tasks are not possible by T-SQL as my requirement.

So in that point we can go with SQL CLR.

What tools I have used in this post

Visual Studio 2015

Sql Server 2014

In Action:

1. Create SQL Server Database project in VS 2015

2. Add SQL CLR C# Stored Procedure

Name stored procedure As CallWebService

3. Add C# codes to Call Web service. I am using below web service to do test

http://www.webservicex.net/globalweather.asmx?op=GetCitiesByCountry

HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://www.webserviceX.NET//globalweather.asmx//GetCitiesByCountry?CountryName=Sri Lanka");

request.Method = "GET";

request.ContentLength = 0;

request.Credentials = CredentialCache.DefaultCredentials;

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

Stream receiveStream = response.GetResponseStream();

// Pipes the stream to a higher level stream reader with the required encoding format.

StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);

System.IO.File.WriteAllText("d://response.txt", readStream.ReadToEnd());

response.Close();

readStream.Close();

I am simply writing response to a text file.  You can do whatever in here.

4.Enable clr and set trust worthy on database. I am using AdventureWorks database

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

alter database [AdventureDatabase] set trustworthy on;

5. Build Visual Studio Project

Then it will generate dll in bin folder

6. Register assembly in Database.

Go to AdventureWorks > Programmability > Assemblies

Right click on Assemblies and Click new Assembly

Set Permission to External access and browse for Our dll.  It is in bin folder of your project

Once you added we can see assembly registered in side Assemblies as below.

7. Create stored procedures to call assembly’s stored procedure

Once you created a stored procedure you can see locked stored procedure.

8. So Now we have finished just execute the stored procedure. You can see text file is generated in drive

Source code

MERGE in Sql Server

From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done.

Syntax

MERGE <target_table>
USING <table_source>
ON <Search>
WHEN MATCHED THEN UPDATE SET <set_clause>
WHEN NOT MATCHED THEN INSERT VALUES (<values_list>)

Create two tables and Insert some records

CREATE TABLE TargetTable([Id] INT, [Name] NVARCHAR(50))

CREATE TABLE SourceTable([Id] INT, [Name] NVARCHAR(50))

INSERT INTO TargetTable(Id,Name)
VALUES(1, 'John'), (2, 'Mezi'), (3, 'Tom')

INSERT INTO SourceTable(Id,Name)
VALUES(1, 'JohnNew'), (4, 'Peter'), (3, 'Tom-new')

Then do Merge

MERGE TargetTable AS T
USING SourceTable AS S
ON T.Id = S.Id
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN INSERT VALUES (S.Id, S.Name);

Now view TargetTable Data

 

CHOOSE and IIF in Sql server

CHOOSE

• Returns a list item based on its location
• First parameter is index
• Next parameters are list

SELECT CHOOSE(2,'item1','item2' )
-- this will return item2

SELECT CHOOSE(1,'item1','item2' )
-- this will return item1

SELECT
CHOOSE(1,[StandardCost],[ListPrice])
FROM [Production].[Product] p
WHERE p.ProductID = 680

--this will return StandardCost

IIF

• Instant if
• Three parameters
  • Boolean expression
  • Return value if true
  • Return value if false

SELECT IIF([ListPrice] > 0, [ListPrice], [StandardCost]) AS 'price'
FROM [Production].[Product] p
-- if ListPrice is greater than 0 it returns Listprice. Otherwise returns StandardCost

Sub Query in Sql

What is subquery?

Subquery is  just a query inside of a query.

Why Subquery?

• Break down complex logic
• Simplify reading

Subqueries can be replaced by joins. But some times Joins can be complex. Other thing is we are not allowed to put aggregates in WHERE. But we can put it aggreagates inside subquery .

Where to Use?

We can put sub queries in  three main places in query.

• SELECT
• FROM
• WHERE

Using in SELECT

• Access informations from tables  withing select query
• Query must return a single(scalar) value

Creates a dynamic table. Useful for  breaking down queries. Query must be aliased.

Note : I have used Adventureworks 2012 database for following queries 

SELECT so.[SalesOrderID]
,so.OrderDate
,(SELECT COUNT([SalesOrderDetailID])
FROM [Sales].[SalesOrderDetail]sod
WHERE sod.SalesOrderID = so.SalesOrderID ) AS 'OrderLineCount'
FROM [Sales].[SalesOrderHeader] so

Using with FROM

SELECT so.[SalesOrderID]
,so.OrderDate
,X.OrderQty AS 'TotalItems'
FROM [Sales].[SalesOrderHeader] so
INNER JOIN (SELECT
SUM([OrderQty]) AS 'OrderQty'
, [SalesOrderID]
FROM [Sales].[SalesOrderDetail] sod
GROUP BY [SalesOrderID]) AS X
ON so.[SalesOrderID] = X.[SalesOrderID]

With WHERE

Useful for comparing values from other tables

Predicates used with subqueries

IN

• Confirm column value exist in subquery
• Similar to inner join

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID IN ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

EXISTS

• Returns true if subquery returns value
• Used with correlated queries

Correlated subquery

Pass outer query column into subquery

</h4>
SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE EXISTS ( SELECT 1
FROM [Sales].[SalesOrderHeader] soh
WHERE sc.CustomerID = soh.CustomerID )

ALL

• Compares column values to all items returned by subquery
• Subquery must return only one column

ANY or SOME

• Compare column value to any item returned by subquery
• Subquery must return only one column
• ANY and SOME are identical

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID = ANY ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

SELECT [AccountNumber] FROM [Sales].[Customer] sc

WHERE sc.CustomerID = SOME( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

Error handling in sql server . Try / catch

Things can go wrong in sql operation. So how we handle Errors in sql server.

We can detect errors in two ways

1. Try/catch
2. @@ERROR

@@ERROR

If there is no errors based on the last operation @@ERROR will be set to 0 . If something went wrong it will be set to value other than 0. How ever this not best way to handle errors in sql server.

TRY/CATCH

BEGIN TRY

-- this is where we put our codes that can be generate errors

END TRY

BEGIN CATCH

-- this where we put code to handle errors

END CATCH

Big thing in TRY block is always we’re going to assume success because only way that we’re going to get to the next line of code is if the line before is succeeded

Transactions in sql server

Why transactions.

We generally works with multiple tables in one operation. May be we want to insert data to order header and order detail table in one operation. Then what happens if only part of the operation succeeds. Some statements can be failed.

So we are with lot of possible problems inside our database when it comes to data integrity . This is where we need transactions.

Transactions will allow us to control the entirety of an operation and ensure that everything’s going to succeed or everything is going to get rolled back should something fail.

Transaction must meet ACID.

ACID is comprised with four different components.

A -> Atomic

Entire operation must succeed or the entire operation must fail, and it must do so as a single unit

C -> Consistent

Once the operation is complete, database must be left in a consistent or in a valid state

I -> Isolated

Every transaction must also be isolated, simply meaning that somebody else’s operation at the same time that I’m working on my data, is not allowed to impact my operation.

D -> Durable

The database should be durable enough to hold all its latest updates even if the system fails or restarts

There are three main commands in managing transaction manually.

1. BEGIN TRANSACTION

This will mark the start of the transaction

2.COMMIT TRANSACTION

We are going to execute this once we know everything is works fine.

3.ROLLBACK TRANSACTION

If some thing went wrong we are going to execute this one. We got some errors and we want to Rollback the transaction.

 

 

reading xml in sql server

So some times we want to read xml file in sql server. I am passing list of details to sql server using xml file format. It reduce several db calls when we passing list of data.

Suppose we have List of Students. In List of Student we have to loop it and fire db calls to save data. To overcome this I create xml of List of Student and Pass it to sql server with one db call and then in my stored procedure I will handle those data as follow.

DECLARE @Xml XML
DECLARE @HDoc INT

SET @Xml = '<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Student>
<Id>1</Id>
<FirstName>jeevan</FirstName>
<LastName>JLast name</LastName>
</Student>
<Student>
<Id>2</Id>
<FirstName>john</FirstName>
<LastName>johnLast name</LastName>
</Student>
</ArrayOfStudent>'
EXECUTE SP_XML_PREPAREDOCUMENT @HDoc OUTPUT,@Xml

SELECT [Id],
[FirstName],
[LastName]
INTO #Temp
FROM OPENXML(@HDoc, '/ArrayOfStudent/Student', 1)
WITH (
[Id] INT 'Id',
[FirstName] VARCHAR(400) 'FirstName',
[LastName] VARCHAR(400) 'LastName'
)

EXECUTE SP_XML_REMOVEDOCUMENT @HDoc

SELECT  *  FROM  #Temp

PIVOT in SQL SERVER

One of the more complex statements that we can execute inside sql server is a PIVOT statement.

What it does? 

PIVOT statement allows us to convert what would normally be row data into column data.

Sounds Crazy…

In simply let’s imagine we have a table as follow.

In above table years are in rows. Let’s suppose we want to get data by years. Now it’s time to use PIVOT. Using PIVOT we can convert years into columns.

PIVOT syntax

SELECT <NonPivot>
, <FirstPivotedColumn>
, …
FROM <Table containing data>
PIVOT (FUNCTION(<data column>)
FOR <List of pivoted columns>)
AS <alias>

Let’s do

SELECT * FROM @Table
PIVOT (SUM(Sales)
FOR [Year] IN([2010],[2012])
)AS pvt

The we get result