BCP Parameter for Unicode file

Hi,
Can any one suggest the bcp parameter for importing file , which is in unicode format, delimeted by tabs. i tried -w, -N all in vain :-(
TIA,
sudheerWhat do you mean that the input file is in unicode format?

I don't understand...|||Originally posted by Brett Kaiser
What do you mean that the input file is in unicode format?

I don't understand...

I meant the file is in Unicode format

i m using sql server 2000 .

Error message is

SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid

character value for cast spec
ification

and string i used for bcp is

bcp <DBname>..<tablename> in

D:\a.dat -S<servername> -T -m0 -w|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_9mwg.asp and verify the data in the file against the data type of the columns. This error usually indicates that the data is incorrectly formatted & is failing to get converted.

http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html for more help.|||Originally posted by Satya
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_9mwg.asp and verify the data in the file against the data type of the columns. This error usually indicates that the data is incorrectly formatted & is failing to get converted.

http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html for more help.

Hi,
thnx for info, i solved the prob. the no.of columns in the datafiles and tht of the target table were not matching.

sudheer|||I still want to know what a unicode flat file is...|||I assume that a Unicode flat file is a fixed width columnar report that contains Unicode (16 bit wide characters) data.

-PatP

BCP Field Separator

Hi All,
I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or o
perator. The bcp is giving error if I sepcify -t | .
Please suggest something as I have to upload a file separated with pipe.
DipankarHi
Use -t "|" (Inside double quotes)
Thanks
Hari
MCDBA
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:EF3D99FB-31F9-42D7-B920-BCEF1EB52541@.microsoft.com...
> Hi All,
> I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or
operator. The bcp is giving error if I sepcify -t | .
> Please suggest something as I have to upload a file separated with pipe.
> Dipankar|||Dipankar,
put double quotes around the delimiter. i.e.
bcp mydb..mytable out C:\mytable.bcp -c -T -t"|"
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Thanks all...It's working perfectly now.

BCP Field Separator

Hi All,
I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or operator. The bcp is giving error if I sepcify -t | .
Please suggest something as I have to upload a file separated with pipe.
Dipankar
Hi
Use -t "|" (Inside double quotes)
Thanks
Hari
MCDBA
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:EF3D99FB-31F9-42D7-B920-BCEF1EB52541@.microsoft.com...
> Hi All,
> I am trying to use bcp with Field Separator as |. Now, | is the bitwise Or
operator. The bcp is giving error if I sepcify -t | .
> Please suggest something as I have to upload a file separated with pipe.
> Dipankar
|||Dipankar,
put double quotes around the delimiter. i.e.
bcp mydb..mytable out C:\mytable.bcp -c -T -t"|"
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Thanks all...It's working perfectly now.

BCP derived fields

thanx in advance....

Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?

eg: in the data file there are

Location UserLocId
USA 1
USA 2
IND 1
IND 2

I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'

If there is any way to upload this using BCP
( updating the usercode after loading will take more time)As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by dipu vp

thanx in advance....

Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?

eg: in the data file there are

Location UserLocId
USA 1
USA 2
IND 1
IND 2

I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'

If there is any way to upload this using BCP
( updating the usercode after loading will take more time)

i have not tried this, but here's an idea:

i think you can BCP into an existing table. so your FMT (format file) could be different from that of the existing table structure. can you try to have an format file based on Location, UserLocId structure and have an existing table with an extra calculated filed called UserCode with default value rtrim(Location) + '_' + ltrim(UserLocId )

i don't know if it will work but worth a shot.

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done bu
t
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!
Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>
|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...[vbcol=seagreen]
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done but
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>
|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...[vbcol=seagreen]
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!
|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>
|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...[vbcol=seagreen]
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote: