Use of BCP Utility

The syntax is:
bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-x] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-t field_term]
[-r row_term] [-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]


for examle:

Export:

run cmd:

bcp "select * from db.dbo.table where ..." queryout "\\..." -Ssever -Uuser -Ppass -c

or we can also use exec master..xp_cmdshell to run it.

if table use "out" option instead of queryout

bcp

Import:

declare @bcp as varchar(200)

set @bcp = 'bcp DBname.dbo.tablename in \\server\filepath\tmpTestFile.txt -c -t : -r # -Sserver -Ulogin -Ppassword -F2

exec master..xp_cmdshell @bcp

or:

exec master.dbo.xp_cmdshell 'bcp AdventureWorks.dbo.test_import in c:\temp\employee.txt -S localhost\SQLExpress -T -c -t ""'

For Example, we can write C# code:
using System;
using System.Diagnostics;


using (Process p = new Process())
{
p.StartInfo.FileName = "bcp";
p.StartInfo.Arguments = "databasename .dbo.tablename in myoutput.txt -Sservername -Usa -Ppass -c -t| -F2";
p.Start();
p.WaitForExit();
}

//Note -t| is delimeter is pipe, -F2 is import from line 2

We can specify the output txt format when use bcp ... out in command line.
When input to db from a txt file, we can write the format file by ourselves:

For example, we have a pipe delimited txt file with txt format like the following.
(file name is C:\mybcp.txt)

ID|Name|Age|Sex
1|Jerry|30|Male
2|Lina|15|Female
3|Jay|88|Male
4|Eric|30|NoRecord
5|Someone|40|NoRecord

We can write the format file (C:\mybcptest.fmt) use txt pad:


9.0
4
1 SQLCHAR 0 100 "|" 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "|" 2 DBColumn1 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "|" 3 Age SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r\n" 4 SourceID SQL_Latin1_General_CP1_CI_AS


Notice the 9.0 is the bcp version, we can use command bcp -v to check the bcp version.
4 means the columns in txt files

After create the format files, we can impot the txt files to database, use command:

bcp mydb.dbo.MyClassmates in C:\mybcp.txt -SMyServer -USa -Ppass -fC:\mybcptest.fmt

Debug and Trace

Calling Trace class executes in both debug and release build type, while using Debug only execute in debug mode.

So the following code in Debug and Release build type has different output:

using System;
using System.Diagnostics;

Debug.Listeners.Add(new ConsoleTraceListener());
Debug.AutoFlush = true;

Debug.WriteLine("This is Debug");
Trace.WriteLine("This is Trace");
Console.WriteLine("This is Console");



In Debug build type, output is:

This is Debug
This is Trace
This is Console

In Release build type, output is

This is Debug
This is Console

Reading and Writing Event Log

In my computer, right click manage, we can see the events logs by event viewer. The majory types of the event log are: system, security and application types.

In order an application program writing events to the Event log, the application need to be register as an Evnet Source. We can use Regedit.exe to register manually: locate the registry subkey: HKEY_LOCAL_MACHINE\SYSTEM|CurrentControlSet\Services\Eventlog\Application, right click the Application subkey, and click New and type the event source name.

We can also use EventLog object to register event source, read and write to event logs. Reference the name space, System.Diagnostics, the code can be like:

1. Register Application EventLog Source:

if(!EventLog.SourceExists("My Application"))
EventLog.CreateEventSource("My Application","Application");

or:

if(!EventLog.Exists("My Log"))
EventLog.CreateEventSource("My App","My Log");

2. Log Events
We can use EventLog.WriteEntry() method and specify the message, type, eventID,category, rawData. The code sample like:

EventLog myLog=new EventLog("Application");
//Notice this parameter can also be "System",
//"Security", so we can also read/write
//systme and security logs.
myLog.Source="My Application";
myLog.WriteEntry("Could not connect",EventLogEntryType.Error, 1001,1);

3. Read Events
We can also create EventLog instance and access the EventLog.Entries Collection:
EventLog myLog=new EventLog("Application");
foreach(EventLogEntry entry in myLog.Entries)
Console.WriteLine(entry.Message);