bcp Question

Without using format file, is there a way for bcp to ignore single quote in
an import file? Thanks.Why are you not wanting to use a format file?
mason wrote:
> Without using format file, is there a way for bcp to ignore single quote i
n
> an import file? Thanks.
>|||Try -q option. More information in BOL.|||I have no problem using format files, but it would be simpler to do without
in terms of maintenance. -c option works well when there is no quote in
import files (about 36 of them). I just want to verify whether there is an
option somewhere to ignore quotes for bcp.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:OvDmLQsQGHA.5768@.tk2msftngp13.phx.gbl...
> Why are you not wanting to use a format file?
> mason wrote:|||Just tried. It's not what I wanted. I think -q affects identifiers, not the
data elements in a data file.
For example, a data record looks like this. I would like bcp to ignore
single quotes when importing.
1,'John','2006-03-08 12:00:00.000'
"Green" <subhash.daga@.gmail.com> wrote in message
news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
> Try -q option. More information in BOL.|||Would it be possible to create the data file without the apostrophes?
Looking at your example, it seems that you have a comma-delimited file
with apostrophes delimiting text fields but not numeric fields.
I needed to deal with that situation once - I was faced with quotes
rather than apostrophes, but the concept was the same. The only thing I
was able to figure out was to import the text file to a SQL Server table
via DTS because of the fact that some fields have a delimiting
character, some do not.
Good luck - hope this was helpful.
Carl
mason wrote:

> Just tried. It's not what I wanted. I think -q affects identifiers, not
> the data elements in a data file.
> For example, a data record looks like this. I would like bcp to ignore
> single quotes when importing.
> 1,'John','2006-03-08 12:00:00.000'
>
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
>
>|||Of course. I will most likely do that at the export end. Since the files may
also feed Sybase and Oracle, it gets ugly. Thanks.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:eJ2605sQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Would it be possible to create the data file without the apostrophes?
> Looking at your example, it seems that you have a comma-delimited file
> with apostrophes delimiting text fields but not numeric fields.
> I needed to deal with that situation once - I was faced with quotes rather
> than apostrophes, but the concept was the same. The only thing I was able
> to figure out was to import the text file to a SQL Server table via DTS
> because of the fact that some fields have a delimiting character, some do
> not.
> Good luck - hope this was helpful.
> Carl
> mason wrote:
>
>

BCP not exporting the "{" or "}" characters

Hello,

I am running a bcp command to export all data from one single table. One of the table field has the flower brackets at the begining and the end and the column is defined as of data type uniqueidentifier (length 16).

The original command that I used is :

c:\bcp dbo.Product out c:\product.csv -w -t"|" -r\n

The ProdID column in Product table have values like : {3E116F82-5E52-4EF9-9A97-8756EA6E9A16}

But in the out put file, the flower brackets are being omitted.

So, I tried the following query :

c:\bcp "Select ID,Name,"+'"{"+'+"convert(varchar(100),ProdID)"+'+"}"'+",ProdType,IsActive,TitleCode,CreationDate,CreatorID,AllowView,AllowRead,AllowWrite,AllowDelete,AdminURL,ReportProc from dbo.Product" queryout c:\Product16Feb.csv -w -t"|" -r\n

But it is giving the following error message:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ','.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

The SQL statement is correct because when I run it individuall, it works but not with the bcp command.

I just want the ProdID column to have the flower brackets in the output file using bcp. How can I achieve this?

Many thanks

Ratan

You can define your SQL statement as a view and then BCP the view. Otherwise, consider editing the file after the BCP operation and applying the brackets then.

BATCH Update ?

Hi all,

we need to update many single cells with individual MDX Update Statements.
We're doing this with ADOMD now (C# Project) in a loop.
In order to save roundtrips and put things in one transaction
we considered using the <Batch> Element of XMLA.
I could'nt find a example how to use this with MDX Commands.
this doesnt work:
<Batch>
<Command>
<Statement>
UPDATE ..
</Statement>
</Command>
<Command>
<Statement>
UPDATE..
</Statement>
</Command>
</Batch>

The Batch element at line 7, column 22 (namespace urn:schemas-microsoft-com:xml-analysis) cannot appear under Envelope/Body/Execute/Command.

Do you have any hints ?
BTW: what happend to www.xmla.org, its down?
Wher can I find a complete schema file for XMLA?
a lot of questions...
Thanks a lot,
mik

You can update multiple cell values in a single Update statement seperated by commas.

For example:

UPDATE CUBE [Cube1] SET

(USA, Sales) = 100 USE_EQUAL_ALLOCATION,

(Canada, Sales) = 50 USE_EQUAL_ALLOCATION

You can also find some information on XML/A at

http://msdn2.microsoft.com/de-de/library/ms186604.aspx