MS SQL bulk export / import

To export in native format execute from the command-line:

bcp MyDatabase..MyTable format nul -n -x -f C:\MyFolder\MyTableFormat.xml -T
bcp MyDatabase..MyTable out C:\MyFolder\MyTableData.dat -n -T

To deal with tables containing XML datatype the format file (MyTableFormat.xml) must be modified: add MAX_LENGTH=”8001″ to the FIELD element that represents column containing XML!

To import data in existing table that already contains data execute SQL:

print 'Inserting MyTable with KEEPIDENTITY ...'
INSERT INTO [dbo].[MyTable]
WITH (KEEPDEFAULTS, KEEPIDENTITY)
([Id],[Col1],[Col2],[ColXml])
SELECT [Id],[Col1],[Col2],CONVERT(xml, [ColXml])
FROM
OPENROWSET
(
BULK 'C:\MyFolder\MyTableData.dat',
FORMATFILE='C:\MyFolder\MyTableFormat.xml''
)
as t1
WHERE t1.Id NOT IN (SELECT Id FROM [dbo].[MyTable])

If you don’t want to preserve Id values from imported table then instead of previous example execute following:

print 'Inserting MyTable with autoincrement ...'
INSERT INTO [dbo].[MyTable]
WITH (KEEPDEFAULTS)
[Col1],[Col2],[ColXml])
SELECT [Col1],[Col2],CONVERT(xml, [ColXml])
FROM
OPENROWSET
(
BULK 'C:\MyFolder\MyTableData.dat',
FORMATFILE='C:\MyFolder\MyTableFormat.xml''
)

Advertisements

Enable WCF message logging/tracing

Place following in the config file:

<system.serviceModel>
<diagnostics>
<messageLogging logEntireMessage=”true”
logMalformedMessages=”false”
logMessagesAtServiceLevel=”false”
logMessagesAtTransportLevel=”true”
maxMessagesToLog=”3000″
maxSizeOfMessageToLog=”2000″/>
</diagnostics>

. . .

<system.diagnostics>
<sources>
<source name=”System.ServiceModel.MessageLogging” switchValue=”Information, ActivityTracing”>
<listeners>
<add name=”log”
initializeData=”c:\temp\Traces.svclog”
type=”System.Diagnostics.XmlWriterTraceListener” />
</listeners>
</source>
</sources>
<trace autoflush=”true”/>
</system.diagnostics>

  • Use¬†SvcTraceViewer.exe to view svclog file (visual studio command prompt).
  • Change parameter swithcValue to “Warning, ActivityTracing” when in production!
  • Message will not be placed into the log, if size will be larger than maxSizeOfMessagesToLog (in bytes)!
Posted in WCF. Tags: . Leave a Comment »