bcp utf8 file

I'm outputting a file from a mySQL server that contains 1 column that
has input it many languages. The base definition for the column is
UTF8.

I output the data into a flat file and bcp it to a sql2000 server, and
the multi-byte data does not survive the translation.

Any ideas on how to input a UTF8 flat file into the server using BCP?

I've tried the -n -N -w options -- no luck.

thanks,

edAssuming that your destination column is nvarchar or nchar, then -w
should be the correct option; -n or -N are specific to MSSQL, so
they're unlikely to work. I've never used bcp to import a file like
that myself, so I can't really say for sure what the solution is, but
under "Copying Data Between Different Collations" in Books Online, it
suggests using a bcp format file with specific collations. You might
also try using DTS to import the data, as it's generally a lot smarter
than bcp, however again I've never tried importing a UTF8 file with it.

Simon|||(geekboyed@.gmail.com) writes:
> I'm outputting a file from a mySQL server that contains 1 column that
> has input it many languages. The base definition for the column is
> UTF8.
> I output the data into a flat file and bcp it to a sql2000 server, and
> the multi-byte data does not survive the translation.
> Any ideas on how to input a UTF8 flat file into the server using BCP?

Since you cannot store UTF-8 data in SQL 2000 in any civilized way,
you need to first convert the file to UCS-2. (Or UTF_16 if you prefer.
I hope you are not using surrogates.)

A simple way to do this is to open the file in Notepad, and then change
encoding when you do Save As.

I'm a little uncertain how any byte-order mark would affect BCP.
Notepad is likely to add one, but I don't know if BCP is smart
enough to ignore it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Basing Report on a Procedure with Cursor

I need some advice on how to design a report using cursors.

Is it possible to base a report on a cursor?

I would think that I would have to put the cursor into a stored procedure and call that procedure. But if I do use the cursor in the procedure, will the looped variables automatically populate the report or will I need to insert them into a table that populates the report?

Thanks,

cj

If the data source type is "Oracle" and you want to call a Oracle stored procedure, please search for related threads on this forum regarding Oracle stored procedures (e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1).

-- Robert

|||No, it is SQL Server.|||

This should work just fine if you follow the guidelines in http://msdn2.microsoft.com/en-us/library/ms159260.aspx - scroll to the stored procedure section.

If you experience problems, can you post a sample of a SQL stored procedure that doesn't work?

-- Robert

|||

Thanks, I understand that part now. Usually I am populating a table to get my output from the stored procedure. For example, I'll use the INSERT clause to populate as the cursor loops. Can I populate the dataset instead? If so, how?

Thanks,

cj

|||

You could use temp tables in the stored procedure. For example:

CREATE PROCEDURE MyProc
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT * FROM #t
GO

However, in that case make sure to use the text-based query designer (with 2 panes) and keep in mind that automatically generating a schema (getting the list of fields) fails on SPs with temp tables. This automatic method is invoked when you switch from Data view to Layout view.
Instead, before going to Layout, get the schema manually by clicking on the Refresh Fields button which will manually refresh the query.

The automatic method gets a schema quickly when available without any necessary user interaction. (Behind the scenes it is running the query with SET FMTONLY ON). The manual method gets the schema by running the query. It takes longer and will prompt the user for query parameter values if required. The former works for most queries with the exceptions of temp table use or some dynamic SQL. The latter always works, but has performance drawbacks for some queries.

-- Robert