BULK INSERTing UNICODE data with format files

Sorry but could you please show me how I convert (specifically the
separators) this bcp format file to unicode so that it works (with a unicod
e
file). I have tried various methods without success.
8.0
2
1 SQLNCHAR 2 100 "," 1 RecordId
Latin1_General_CI_AS
2 SQLNCHAR 4 0 "\r\n" 2 TheText
Latin1_General_CI_AS
Thanks
Peter Doyle
"Erland Sommarskog" wrote:

> Nitin M (nitin@.nowhere.com) writes:
> Ah! Glad to hear that you where able to find it out yourself.
>
> The obvious idea would be to save the format file as Unicode, but that
> does not work; you only get a message about unknown version. I tried in
> SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
> be an XML format file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Peter Doyle (PeterDoyle@.discussions.microsoft.com) writes:
> Sorry but could you please show me how I convert (specifically the
> separators) this bcp format file to unicode so that it works (with a
> unicode file). I have tried various methods without success.
> 8.0
> 2
> 1 SQLNCHAR 2 100 "," 1 RecordId
> Latin1_General_CI_AS
> 2 SQLNCHAR 4 0 "\r\n" 2 TheText
> Latin1_General_CI_AS
The above won't work for several reasons. You have specified a
prefix length, but prefix lengths is only for binary formats.
The problem with impoting Unicode files, is that the format file itself
must be a 8-bit file. At least I seem to recall that BCP freaked out
when I tried with a Unicode file. (Hm, did I try BULK INSERT as well+)
Anyway, with the format indicated by your format file, you don't any
format file at all. For a table like:
CREATE TABLE test (a nvarchar(100) NOT NULL,
b nvarchar(100) NOT NULL)
And this data (saved as Unicode):
Detta r lite data,Och detta r mer data.
Sedan kommer det n mer data hr,Och nu kommer det n mer
Trams!, Det r vad det r!
This command line will do:
bcp tempdb..test in slask.bcp -w -t, -T
-w specifies that the file a Unicode file.
But in case that your real case calls for a format file, here is one
that works with the above table and data:
8.0
2
1 SQLNCHAR 0 0 ",\0" 1 a ""
2 SQLNCHAR 0 0 "\r\0\n\0" 2 b ""
The tricky part is the \0 that must come aftet the character it belongs
to, due to endianness.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

built-in functions - owner required? (newbie)

Hi All,
Ingres has some functions for converting UUIDs. Specifically, they are
uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
to write some built-in functions (or any other solution) that will
allow me to use the same code in MS SQL and Ingres.
Example:
select uuid_to_char(xyz_uuid) from xyz
I have written a simple built-in function to do this and it works just
fine. The one fly in the ointment is that the "database owner" seems
to be required to execute it.
"select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
a recognized function name".
"select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
I have found in the documentation where it says that the owner is
required in this case.
Is there some other technique available to eliminate use of the owner
name?
Thanks!
Troy RudolphThere is, but it's undocumented/unsupported, and I guess that you wouldn't
want to rely on such a technique:
- create the function in master
- function name must start with fn_
- owner must be system_function_schema
- name must be all lowercase
- function should be created when the 'allow updates' server configuration
option is turned on
If you follow all the above requirements, you don't need to owner qualify
it, plus you can invoke it from any database. But again, I wouldn't rely on
an unsupported technique in a production environment.
The only supported way to invoke a scalar UDF in a query is to owner qualify
it, I'm afraid.
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125340946.797761.324100@.z14g2000cwz.googlegroups.com...
> Hi All,
> Ingres has some functions for converting UUIDs. Specifically, they are
> uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
> to write some built-in functions (or any other solution) that will
> allow me to use the same code in MS SQL and Ingres.
> Example:
> select uuid_to_char(xyz_uuid) from xyz
> I have written a simple built-in function to do this and it works just
> fine. The one fly in the ointment is that the "database owner" seems
> to be required to execute it.
> "select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
> a recognized function name".
> "select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
> I have found in the documentation where it says that the owner is
> required in this case.
> Is there some other technique available to eliminate use of the owner
> name?
> Thanks!
> Troy Rudolph
>|||Thank you for the quick reply.
You mentioned that the function name has to start with "fn_". It that
true for invocation as well as definition? In other words, do I have
to invoke it like this:
select fn_uuid_to_char(xyz_uuid) from uuid
If so, it really won't help.
Thanks again
Troy|||> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
Yep.

> If so, it really won't help.
Probably for the best. ;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125342504.627559.165260@.o13g2000cwo.googlegroups.com...
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy
>|||Yes, defined as fn_myfunction, then called as fn_myfunction. There is no
shortcut.
I really would not recommend it for any custom database. Maybe, in some
exceptional situations I would use it for very generic functions that
Microsoft should have provided out of the box, never for customer or
business specific functions.
Reasons not to take this road:
- You have to do a lot of 'special' stuff. And everyone who ever has to
maintain this function needs to know all this. It would definitely add
to the cost of maintaining the software
- It is undocumented and therefore unsupported. Although unlikely, its
behavior could change with the next service pack
- Microsoft could decide to add system function and 'accidentally'
choose the same function name as you did
Gert-Jan
Troy.Rudolph@.ca.com wrote:
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy