COLLATION in bulk insert

Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:
> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have a bulk insert statement:
> >
> > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > WITH (
> > DATAFILETYPE = 'WIDECHAR'
> > FIRSTROW = 1,
> > FIELDTERMINATOR = 'COL_SEP',
> > ROWTERMINATOR = 'ROW_SEP',
> > CODEPAGE = 'RAW')
> >
> > Some fields that are inserted are encrypted using special characters
> > beyond the normal ascii character set. I have a database that has a
> > collation set to Arabic. When the collation is set to arabic the import
> > does not transfer some data across properly.
> >
> > When I set the collation to latin on this database, the data comes
> > across correctly. Can someone help me to explain why this is the case?
> >
> >|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:
> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
> > Hi
> >
> > You don't say how the data file is created! Have you tried using code page
> > 1256?
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have a bulk insert statement:
> > >
> > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > WITH (
> > > DATAFILETYPE = 'WIDECHAR'
> > > FIRSTROW = 1,
> > > FIELDTERMINATOR = 'COL_SEP',
> > > ROWTERMINATOR = 'ROW_SEP',
> > > CODEPAGE = 'RAW')
> > >
> > > Some fields that are inserted are encrypted using special characters
> > > beyond the normal ascii character set. I have a database that has a
> > > collation set to Arabic. When the collation is set to arabic the import
> > > does not transfer some data across properly.
> > >
> > > When I set the collation to latin on this database, the data comes
> > > across correctly. Can someone help me to explain why this is the case?
> > >
> > >
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have tried using codepage 1256 but to no luck. The data file is
> > created in code just by running through some old DBF files and
> > extracting there values and putting them in to a csv file.
> >
> >
> > John Bell wrote:
> >
> > > Hi
> > >
> > > You don't say how the data file is created! Have you tried using code page
> > > 1256?
> > >
> > > John
> > >
> > > "Feldaspar" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a bulk insert statement:
> > > >
> > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > WITH (
> > > > DATAFILETYPE = 'WIDECHAR'
> > > > FIRSTROW = 1,
> > > > FIELDTERMINATOR = 'COL_SEP',
> > > > ROWTERMINATOR = 'ROW_SEP',
> > > > CODEPAGE = 'RAW')
> > > >
> > > > Some fields that are inserted are encrypted using special characters
> > > > beyond the normal ascii character set. I have a database that has a
> > > > collation set to Arabic. When the collation is set to arabic the import
> > > > does not transfer some data across properly.
> > > >
> > > > When I set the collation to latin on this database, the data comes
> > > > across correctly. Can someone help me to explain why this is the case?
> > > >
> > > >
> >
> >|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sample
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:
> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
> > Hi
> >
> > Is the file created unicode? If not then you may wish to change the
> > Datafiletype. Also make sure that contents of the file are as expected for
> > the Arabic characters.
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have tried using codepage 1256 but to no luck. The data file is
> > > created in code just by running through some old DBF files and
> > > extracting there values and putting them in to a csv file.
> > >
> > >
> > > John Bell wrote:
> > >
> > > > Hi
> > > >
> > > > You don't say how the data file is created! Have you tried using code page
> > > > 1256?
> > > >
> > > > John
> > > >
> > > > "Feldaspar" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a bulk insert statement:
> > > > >
> > > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > > WITH (
> > > > > DATAFILETYPE = 'WIDECHAR'
> > > > > FIRSTROW = 1,
> > > > > FIELDTERMINATOR = 'COL_SEP',
> > > > > ROWTERMINATOR = 'ROW_SEP',
> > > > > CODEPAGE = 'RAW')
> > > > >
> > > > > Some fields that are inserted are encrypted using special characters
> > > > > beyond the normal ascii character set. I have a database that has a
> > > > > collation set to Arabic. When the collation is set to arabic the import
> > > > > does not transfer some data across properly.
> > > > >
> > > > > When I set the collation to latin on this database, the data comes
> > > > > across correctly. Can someone help me to explain why this is the case?
> > > > >
> > > > >
> > >
> > >
>sqlsql

collation error...

I have a database on one SQL server.
I have backed up the database and then restored it onto another SQLServer.
I have the following SQL statement:
exec myStoredProcedure 'stress', NULL,NULL, 1, -1
When I run it against the database on serverA it works, but when I run it
against the backup SQL server I get the following error message:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Server: Msg 446, Level 16, State 1, Line 1
Cannot resolve collation conflict for equal to operation.
I don't know how to fix this...
I presume that it's the server collation, but don't know how to update it.
Please advise...
Thanks
GriffIs there a join aganst a temp table? If the system db's are a different
collation and the join is there, that would explain it. To change server
collation, you'll need to use the rebuildm utility, and there are lots of
caveats there because you will loose all of your system database objects
like jobs, maintenance plans, logins, dts packages, and so on.
See REBUILDM or rebuild master in books on line.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oz7R447zDHA.536@.tk2msftngp13.phx.gbl...
> I have a database on one SQL server.
> I have backed up the database and then restored it onto another SQLServer.
> I have the following SQL statement:
> exec myStoredProcedure 'stress', NULL,NULL, 1, -1
> When I run it against the database on serverA it works, but when I run it
> against the backup SQL server I get the following error message:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> Server: Msg 446, Level 16, State 1, Line 1
> Cannot resolve collation conflict for equal to operation.
> I don't know how to fix this...
> I presume that it's the server collation, but don't know how to update it.
> Please advise...
> Thanks
> Griff
>|||Great help - thanks.
Had to resort to that... as you pointed out, now need to rebuild everything.
That will have to wait until next year though!
Happy New Year
Griff

Code formatting for SQL2005 TSQL

Hi !
I have a monster case statement in TSL, autogenerated with some external
program I do not have access to.
The source file is about 4K in size, and the resulting source code probably
will have a few hunderd lines of code if formatted properly for reading.
Is there a feature in SQL 2005 management studio for code formatting ? BIDS
then ?
If not, a reccommendation for a tool for code formatting, preferably free,
would be greatly appreciated.
Regards,
Palli
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:ejv5S05nHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Hi !
> I have a monster case statement in TSL, autogenerated with some external
> program I do not have access to.
> The source file is about 4K in size, and the resulting source code
> probably will have a few hunderd lines of code if formatted properly for
> reading.
> Is there a feature in SQL 2005 management studio for code formatting ?
> BIDS then ?
> If not, a reccommendation for a tool for code formatting, preferably free,
> would be greatly appreciated.
> Regards,
> Palli
Like this?
http://www.simple-talk.com/sql/sql-tools/the-simple-talk-sql-prettifier/
|||>
> Like this?
> http://www.simple-talk.com/sql/sql-tools/the-simple-talk-sql-prettifier/
>
Thanks for the link!
It does half the job, but not quite. It gets confused when 5 or 6 case
statement's END appear in a row!
Any other free options ?
Palli
|||"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:ealOwM8nHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Thanks for the link!
> It does half the job, but not quite. It gets confused when 5 or 6 case
> statement's END appear in a row!
> Any other free options ?
> Palli
If you report that as a possible bug to the author, I imagine he'll jump in
and try to fix it. I don't really know of any other free options myself. I
have heard about some old VB6 trick to access some undocumented feature of a
system DLL to format SQL/T-SQL Code. Not sure where the info is located for
this trick exactly, or if it would even work for SQL 2005, but you might be
able to Google something on it. The problem is that generally a custom code
prettifier for a language as complex as SQL/T-SQL would require you to
implement an LALR parser and a complete SQL/T-SQL grammar from scratch, or a
very deep (read: "slow") recursive-descent parser. For most people it's
probably just a lot easier to prettify their code by hand or just pay for a
pre-built package.
|||Try http://www.sqlinform.com/ and
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
TheSQLGuru
President
Indicium Resources, Inc.
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:ealOwM8nHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Thanks for the link!
> It does half the job, but not quite. It gets confused when 5 or 6 case
> statement's END appear in a row!
> Any other free options ?
> Palli
>
|||Now that link is too cool I've heard of the GSP, but didn't realize
someone had set up a Web Service to use it.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OHbsgqYoHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Try http://www.sqlinform.com/ and
> http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Pall Bjornsson" <palli@.kvos.is> wrote in message
> news:ealOwM8nHHA.4424@.TK2MSFTNGP03.phx.gbl...
>
|||> Try http://www.sqlinform.com/ and
> http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
>
This is really cool... both of those tools. Thanks a lot.
Palli
|||"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:uWen8lpoHHA.3460@.TK2MSFTNGP04.phx.gbl...
> This is really cool... both of those tools. Thanks a lot.
> Palli
BTW, Phil Factor (the author of the one I sent you) has updated his code.
He also has the source code for the T-SQL stored procedure available for
download. Not sure if it fixes the bug you pointed out, but it might.

Coalesce Question

Hi.
I'm using Coalesce to change a NULL returned value from a Select statement.
Here's my code:
SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FROM
MyTable WHERE RTrim(Subtable_Id) = 'I03U'
Print @.StartDate
When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
@.StartDate
printed out.
When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
get the @.StartDate printed out. I was expecting to see 01/01/1980.
What am I missing?
TIA.
RitaRitaG wrote:
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select statement
.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FRO
M
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
The problem is that if the SELECT doesn't return any rows then the
assignment will never be made. For single value assignments use SET
instead of SELECT.
SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
I assume you meant to retrieve the minimum date but in the query you
posted you specified TOP without ORDER BY so in fact your results may
be unpredictable.
The YYYYMMDD format I've used is better for dates because it's
unambiguous and doesn't depend on any of your regional connection
settings.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Coalesce works on individual columns, for each row returned by your query.
If no row is returned, then there is no column value for coalesce to work
on. Coalesce is only useful when you have rows returned, but a particular
column is null, and you want to return a default value instead of the null.
You need to either change your SQL to insure that a row is always returned
(probably not what you want to do) or check to see if any rows were returned
(which appears to be your goal anyway).
Simply checking to see if @.StartDate is null AFTER your select may
accomplish what you want.
If you post the rest of your code, folks may be able to give some better
advice. Since this code is out of context, I can only guess at what you are
doing with it.
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita|||"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
> statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
> FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
Your WHERE clause only allows data to be printed out WHERE your Subtable ID
= I03U.
The other issue I see here is the use of your COALESCE... While this should
work for what you are doing, an ISNULL(Discount_Effect_Date, '01/01/1980')
may work better for you. COALESCE is generally used to find the first
non-null value in a list of columns. For example COALESCE(payrate_salary,
payrate_daily, payrate_hourly)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks to all for your responses.
The dates are all the same that satisfy the WHERE clause so I could have
used a Distinct but I think Top 1 is faster.
The SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
will work for me. Thanks for the "YYYYMMDD format" suggestion.
Learn something every day! :-)
"David Portas" wrote:

> RitaG wrote:
> The problem is that if the SELECT doesn't return any rows then the
> assignment will never be made. For single value assignments use SET
> instead of SELECT.
> SET @.startdate =
> (SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
> FROM mytable
> WHERE RTRIM(subtable_id) = 'I03U');
> I assume you meant to retrieve the minimum date but in the query you
> posted you specified TOP without ORDER BY so in fact your results may
> be unpredictable.
> The YYYYMMDD format I've used is better for dates because it's
> unambiguous and doesn't depend on any of your regional connection
> settings.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Coalesce breaking SQL statement in SSRS?

I've run into what may an odd bug. I'm using SQL Server 2005, and
Visual Studio 2005. I was provided with a SQL statement (that is, I
did not originally write the SQL) for use in creating a simple
manifest report. Names are changed for security, but it is like so:
SELECT DISTINCT
A.NAME [name],
B.NAME [item_type],
CASE B.NAME,
WHEN N'option1' THEN COALESCE( C.name, N'')
WHEN N'option2' THEN COALESCE( D.name, N'')
WHEN N'option3' THEN COALESCE( E.name, N'')
WHEN N'option4' THEN COALESCE( F.name, N'')
WHEN N'option5' THEN COALESCE( G.name, N'')
...
FROM TABLE_A A
INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
...
and onward, for 27 tables. There is no WHERE clause.
The quest runs perfectly fine in the SQL Server Management studio,
returning around 4000 rows in under one second. However, when I try to
run the same statement in an .RDL file, the query takes as long as
10-15 minutes, and locks up the Design Studio while it waits. If I
remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
with ( C.name) ), the problem disappears entirely.
I have tried using ISNULL as well, and the problem remains. I have
tried creating an entirely new solution, new .RDL, new .RDS, with no
luck. sp_who2 indicates the the process is sleeping, no CPU usage or
DiskIO.
Has anyone seen this before, or have an idea?On Oct 1, 10:05 am, avat...@.gmail.com wrote:
> I've run into what may an odd bug. I'm using SQL Server 2005, and
> Visual Studio 2005. I was provided with a SQL statement (that is, I
> did not originally write the SQL) for use in creating a simple
> manifest report. Names are changed for security, but it is like so:
> SELECT DISTINCT
> A.NAME [name],
> B.NAME [item_type],
> CASE B.NAME,
> WHEN N'option1' THEN COALESCE( C.name, N'')
> WHEN N'option2' THEN COALESCE( D.name, N'')
> WHEN N'option3' THEN COALESCE( E.name, N'')
> WHEN N'option4' THEN COALESCE( F.name, N'')
> WHEN N'option5' THEN COALESCE( G.name, N'')
> ...
> FROM TABLE_A A
> INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
> INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
> LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
> LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
> LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
> LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
> ...
> and onward, for 27 tables. There is no WHERE clause.
> The quest runs perfectly fine in the SQL Server Management studio,
> returning around 4000 rows in under one second. However, when I try to
> run the same statement in an .RDL file, the query takes as long as
> 10-15 minutes, and locks up the Design Studio while it waits. If I
> remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
> with ( C.name) ), the problem disappears entirely.
> I have tried using ISNULL as well, and the problem remains. I have
> tried creating an entirely new solution, new .RDL, new .RDS, with no
> luck. sp_who2 indicates the the process is sleeping, no CPU usage or
> DiskIO.
> Has anyone seen this before, or have an idea?
A couple of suggestions. Firstly, if you aren't already, you will want
to create a stored procedure out of the query above and access it in
the report. This should improve your performance a little. Secondly,
you might want to run the SQL Profiler while you are running the
report to see what kind of processing is going on and if there is an
exception/error hidden somewhere, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Clustered Primary Key

Hello!
How can I create a clustered primary key?
This alter table statement get the following error:
ALTER TABLE test ADD CONSTRAINT
PK_Table_1 Primary Key CLUSTERED
(
[Field 1],
[Field 2]
)
The constraint specified is not valid.

Thank you
SaschaThe CLUSTERED keyword is not supported by SQL Compact Edition. If you drop it, the statement should run OK.