BCP Usage with SQL Server 2005 Clustered Server

How can I use BCP to create an XML format file on a SQL Server 2005
cluster that has multiple instances of SQL Server running? Is there
some sample syntax, etc.?
Thx.
-ak
This syntax "appears" to work...
bcp [sqlcls1dev\inst2005dev].[BusinessContacts].[Branch] format -x -T
-fc:\temp\myfile.xml
However, I get a connection timeout error that also states that the SQL
Server 2005 by default doesn't allow remote connections. But, I see
that this server does allow remote connections in the options. I am
logged into the box using Win authent and my Win acct has sysadmin
privileges on the sql server instance. Any information would be
appreciated.
-ak
Ayron wrote:
> How can I use BCP to create an XML format file on a SQL Server 2005
> cluster that has multiple instances of SQL Server running? Is there
> some sample syntax, etc.?
> Thx.
> -ak

BCP Usage with SQL Server 2005 Clustered Server

How can I use BCP to create an XML format file on a SQL Server 2005
cluster that has multiple instances of SQL Server running? Is there
some sample syntax, etc.?
Thx.
-akThis syntax "appears" to work...
bcp [sqlcls1dev\inst2005dev].[BusinessContacts].[Branch] format -x -T
-fc:\temp\myfile.xml
However, I get a connection timeout error that also states that the SQL
Server 2005 by default doesn't allow remote connections. But, I see
that this server does allow remote connections in the options. I am
logged into the box using Win authent and my Win acct has symin
privileges on the sql server instance. Any information would be
appreciated.
-ak
Ayron wrote:
> How can I use BCP to create an XML format file on a SQL Server 2005
> cluster that has multiple instances of SQL Server running? Is there
> some sample syntax, etc.?
> Thx.
> -ak

BCP exporting data, what's t

Hi
You may want to post DDL and sample data for this so it can be re-produced.
If you try bcp with the format option, does the format file differ to the
one you have?
John
"Scott A. Keen" wrote:

> I'm using BCP to export data.
> When I look at the data with Notepad, some empty fields have this "?"
> character, but this character does not appear in the database table.
> This is apparently causing problems with the company we're exchange data
> with. They are asking for the field to be empty, not to contain this "?"
> character.
> What can I do to make sure the field is blank instead of containing this "
?"
> character? Is this a character set issue? How do I correct this?
> Thanks,
> Scott
>
> For your reference, I've set up a format file which looks like this...
> 8.0
> 55
> 1 SQLCHAR 0 36 "|" 1 Order_GUID SQL_Latin1_General_Cp437_BIN
> ...snip...
> 54 SQLCHAR 0 50 "|" 54 PlaceReceipt SQL_Latin1_General_Cp437_BIN
> 55 SQLCHAR 0 50 "\r\n" 55 container_SealNumber SQL_Latin1_General_Cp437_BI
N
>
>We have a UNIX system that exports that character when a blank field should
be returned. That strange character typically represents a place holder for
the table / field DB structure. On the UNIX system we have it uses Informix
as its DB and informix uses the ? to repersent a blank field. Thus, when I
import into MS SQL the character gets loaded into the table.
When exporting the data, I have got around this by using the replace
command, replace(Field, '?','') to get the blank field, but I do not know i
f
you can do this with bcp, I typically use dts.
-David
"John Bell" wrote:
> Hi
> You may want to post DDL and sample data for this so it can be re-produced
.
> If you try bcp with the format option, does the format file differ to the
> one you have?
> John
> "Scott A. Keen" wrote:
>

BCP API

Does anyone know where I can find information on BCP API including sample coding?Hi,

Are you speaking about BulkLoad ?

If you are using .NET it's easy to use the Sql Bulk Load component.

HH

NeuralC|||Yes, I am using .NET. I have an application where I need to insert a large number of records into SQL Server from the application. From what I have found thus far, using BCP API is the best way to do it from an application.|||I'm pretty sure it was in BOL

BCP - Skip the first column in a BCP opperation

How can I skip the first column in a BCP operation. My data file does not contain the data for the first column. From the sample below I want to skip the column named GCRecord.

Table schema [StopOrderCode]

[GCRecord] int NULL,
[Id] int NOT NULL,
[EmployerPayCode] nvarchar(100) NULL,
[EmployerName] nvarchar(100) NULL,
[EnglishEmployerName] nvarchar(100) NULL,
[AfrikaansEmployerName] nvarchar(100) NULL,
[IsGovernmentCode] bit NULL

Format file

9.0
6
1 SQLINT "" 4 "\t" 2 Id ""
2 SQLNCHAR "" 200 "\t" 3 EmployerPayCode ""
3 SQLNCHAR "" 200 "\t" 4 EmployerName Latin1_General_CI_AS
4 SQLNCHAR "" 200 "\t" 5 EnglishEmployerName Latin1_General_CI_AS
5 SQLNCHAR "" 200 "\t" 6 AfrikaansEmployerName Latin1_General_CI_AS
6 SQLBIT "" 1 "\r\n" 7 IsGovernmentCode

Data sample (Tab deliminated)

Id EmployerPayCode EmployerName EnglishEmployerName AfrikaansEmployerName IsGovernmentCode
676 9271 Abakor Bpk Abakor Bpk Abakor Bpk 0
837 9639 Aberdare Telecom Division Aberdare Telecom Division Aberdare Telecom Division 0

BCP statement

bcp DBName.dbo.StopOrderCode in ".\Table Data\StopOrderCode.txt" -S DBServer\InstanceName -T -f ".\Table Data\StopOrderCode.fmt" -C ACP -b 1000 -F 2

I am using sql 2005.

Thanks in advance

When the datafile contains more columns than the table,

bcp into a 'Staging' table that maps to the data file, and then copy appropriate columns to the final table.

When the table has more columns than the datafile,

CREATE a VIEW that maps the data to the table, and bcp into the VIEW.

Batch insert (OLAP)

Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches...... I need to do it in batches as the input data is huge....

The logic is I have to insert thru bcp in fact table...

After that batch execution for 50,000 thousand record... wise... if any of the batch failes i need to identify and have to rerun from that point onwards..... this is OLAP thing...what's the data source?

50,000 ain't that much btw|||50,000 record comes in one batch that waht I meant...... so if there is 1 million record 20 batches will be there...

The source come from DB2 or so which we get it as source file... we create staging table for that in Sql Server... Now we need to do the rest porting data to Fact & Dimension tables|||what script?
you can use create a failover process in dts to allow x number of error rows to pass through the ETL and then you can clean them up the next day if you prefer. its part of the error reporting process in dts tasks.|||So the data is already in a table and you're trying to limit the impact to the logs...can you describe your process how are you building your warehouse..|||:eek: I didnt get u I never asked for error reporting stuff!!!!!|||http://www.winnetmag.com/article/articleid/42903/42903.html

I was trying to do the same way how the above article describe to solve such kinda stuff