Sql Case sensitivity: migrate

Showing posts with label migrate. Show all posts

Friday, February 10, 2012

cast ( host_name() as int )

hi. i'm new to sql server administration.
i need to migrate a table that has field (from memory)
uid INT DEFAULT CAST(HOST_NAME() AS INT)
the current table has field values that r integers
new table of same structure is blank
trying to export the data to new table i get error saying some thin
like
cannot convert RIYAZM (computer_name) to int
seems to be converting the host_name value to int without actually
checking that there is an actual integer supplied!!!
how to solve this?
thanx
riyazWhy would you want to have a machine name in an int columns which also is na
med uid? Talk to the one
who did this data model, as it seems screwed up. Short story is that you can
not input anything which
isn't castable to an int into an int column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<rmanchu@.gmail.com> wrote in message news:1146991157.328130.266300@.g10g2000cwb.googlegroups
.com...
>
> hi. i'm new to sql server administration.
> i need to migrate a table that has field (from memory)
> uid INT DEFAULT CAST(HOST_NAME() AS INT)
> the current table has field values that r integers
> new table of same structure is blank
> trying to export the data to new table i get error saying some thin
> like
> cannot convert RIYAZM (computer_name) to int
> seems to be converting the host_name value to int without actually
> checking that there is an actual integer supplied!!!
> how to solve this?
> thanx
> riyaz
>|||(rmanchu@.gmail.com) writes:
> hi. i'm new to sql server administration.
> i need to migrate a table that has field (from memory)
> uid INT DEFAULT CAST(HOST_NAME() AS INT)
> the current table has field values that r integers
> new table of same structure is blank
> trying to export the data to new table i get error saying some thin
> like
> cannot convert RIYAZM (computer_name) to int
> seems to be converting the host_name value to int without actually
> checking that there is an actual integer supplied!!!
> how to solve this?
As Tibor said, this is completely wretched. Maybe the person who
designed the database worked from the assumption that all machine
names were numeric.
Or the memory you are working from is not correct. There is a host_id()
function, maybe that is what is in the default constraint.
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|||had a talk with the programmer. he's using WSID=# in the connection
string to set the int, different values for different users.
the original tables are fine => it contains ints in those fields.
the method does seem wierd, i guess its a short cut to do something
internal.
but i don't see a reason why the export will not work!
the field contain ints => implies the default value should not be
invoked! just insert the existing value.
perhaps my thinking is wrong.
thanx
riyaz|||the Enterprise Manager - during connect to the SQL Server - does not
allow additional options to be set?
i'd like to test using the setting WSID=#
riyaz|||You can connect using OSQL or SQLCMD, specifying a name for the machine usin
g one of the
command-line switches.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<rmanchu@.gmail.com> wrote in message news:1147059568.367462.322520@.i40g2000cwc.googlegroups
.com...
> the Enterprise Manager - during connect to the SQL Server - does not
> allow additional options to be set?
> i'd like to test using the setting WSID=#
> riyaz
>|||(rmanchu@.gmail.com) writes:
> had a talk with the programmer. he's using WSID=# in the connection
> string to set the int, different values for different users.
> the original tables are fine => it contains ints in those fields.
> the method does seem wierd, i guess its a short cut to do something
> internal.
> but i don't see a reason why the export will not work!
> the field contain ints => implies the default value should not be
> invoked! just insert the existing value.
> perhaps my thinking is wrong.
I've never used the export stuff in Enterprise Manager, so I have
no idea what it is up to. But I agree that it sounds funny that rhe
default gets in the way.
Could it be that columns are not in the same order in the source and
target tables? (This is just wild speculation.)
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|||columns r in the same order.
have given up on an automated migration
am using enterprise manager to import to a tmp "results" table in the
new database, one table at a time.
after that
insert into newdb_table (columnnames)
select columnnames from results
it works => default values do NOT get in the way but to say its tedious
is ... is an understatement!
thanx
riyaz

Case-sensitivity

Hello gurus!
I have to migrate my DB from Oracle to SQL Server 2000. There are some
unique indexes on some String columns of my DB-Schema. In Oracle the unique
index constraint concerns case-sensitivity but SQL Server does not.
So when I have to migrate the two values 'A' and 'a' into a column with a
unique index I got an SQL error concerning the uniqueness of the index.
I already successfully changed the collation mode from case-insensitive to
case-sensitive. Now the collation mode is set to case-sensitive, but the
above example still doesn't work...
Does somebody have an idea how to reach real case-sensitivity in SQL Server?
By the way the database in SQL Server is restored from a file which was
backupped from my SQL Server, when the collation mode was set to
case-insensitive.
Thanks in advance
RobertHow did you change the collation? In SQL Server, the collation of a
database is the default for objects created in that database -- but only a
default. Every object that can have a collation can have a different
collation. Every column, for instance, will maintain its own. Whether
that's the default or something else is up to you... It sounds as though you
may need to change the collation for the column you're indexing. Although
it may be better for you to start over and re-create the database with a
case-sensitive collation to begin with, so that every column has the correct
collation.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> Hello gurus!
> I have to migrate my DB from Oracle to SQL Server 2000. There are some
> unique indexes on some String columns of my DB-Schema. In Oracle the
unique
> index constraint concerns case-sensitivity but SQL Server does not.
> So when I have to migrate the two values 'A' and 'a' into a column with a
> unique index I got an SQL error concerning the uniqueness of the index.
> I already successfully changed the collation mode from case-insensitive to
> case-sensitive. Now the collation mode is set to case-sensitive, but the
> above example still doesn't work...
> Does somebody have an idea how to reach real case-sensitivity in SQL
Server?
> By the way the database in SQL Server is restored from a file which was
> backupped from my SQL Server, when the collation mode was set to
> case-insensitive.
> Thanks in advance
> Robert|||I changed collation with rbuildm.exe. I tried a few SQL collations which are
case-sensitive. Then I created a new database and created a new table. Now
the collation is case-sensitive. That's good.
But it's bad that now also the tablename in the FROM clause of an SQL
Statement have to be written exactly like it was created.
Example:
CREATE TABLE OBJ_STATUS (
obj_status_id int NOT NULL,
name varchar(50) NOT NULL,
note varchar(250) NULL,
)
SELECT * FROM obj_status -> this does not work
SELECT * FROM OBJ_STATUS -> this works
My problem is that I have to migrate an application which was based on
Oracle till now. In Oracle for example the tablename in the FROM clause could
be upper or lower case, it doesn't matter, and when I query a string column
it is case-sensitive.
How can I reach such an 'case-sensitivity status'?
If this is not possible it is not practicable to migrate to SQL Server.
Regards Robert
"Adam Machanic" wrote:
> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert MÃ¶stl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > Hello gurus!
> >
> > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > unique indexes on some String columns of my DB-Schema. In Oracle the
> unique
> > index constraint concerns case-sensitivity but SQL Server does not.
> >
> > So when I have to migrate the two values 'A' and 'a' into a column with a
> > unique index I got an SQL error concerning the uniqueness of the index.
> >
> > I already successfully changed the collation mode from case-insensitive to
> > case-sensitive. Now the collation mode is set to case-sensitive, but the
> > above example still doesn't work...
> >
> > Does somebody have an idea how to reach real case-sensitivity in SQL
> Server?
> > By the way the database in SQL Server is restored from a file which was
> > backupped from my SQL Server, when the collation mode was set to
> > case-insensitive.
> >
> > Thanks in advance
> >
> > Robert
>
>|||You've made your entire server case-sensitive. You can control default
collation at various levels:
server
database
column
statement
Look up the COLLATE keyword in Books Online.
You should put your server back to case-insensitive collation and control
the collation at the column level instead.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Möstl" <RobertMstl@.discussions.microsoft.com> wrote in message
news:7DB932A6-846D-41CE-8F9E-44ED68A91681@.microsoft.com...
> I changed collation with rbuildm.exe. I tried a few SQL collations which
are
> case-sensitive. Then I created a new database and created a new table. Now
> the collation is case-sensitive. That's good.
> But it's bad that now also the tablename in the FROM clause of an SQL
> Statement have to be written exactly like it was created.
> Example:
> CREATE TABLE OBJ_STATUS (
> obj_status_id int NOT NULL,
> name varchar(50) NOT NULL,
> note varchar(250) NULL,
> )
> SELECT * FROM obj_status -> this does not work
> SELECT * FROM OBJ_STATUS -> this works
> My problem is that I have to migrate an application which was based on
> Oracle till now. In Oracle for example the tablename in the FROM clause
could
> be upper or lower case, it doesn't matter, and when I query a string
column
> it is case-sensitive.
> How can I reach such an 'case-sensitivity status'?
> If this is not possible it is not practicable to migrate to SQL Server.
> Regards Robert
>
> "Adam Machanic" wrote:
> > How did you change the collation? In SQL Server, the collation of a
> > database is the default for objects created in that database -- but only
a
> > default. Every object that can have a collation can have a different
> > collation. Every column, for instance, will maintain its own. Whether
> > that's the default or something else is up to you... It sounds as though
you
> > may need to change the collation for the column you're indexing.
Although
> > it may be better for you to start over and re-create the database with a
> > case-sensitive collation to begin with, so that every column has the
correct
> > collation.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> > news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > > Hello gurus!
> > >
> > > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > > unique indexes on some String columns of my DB-Schema. In Oracle the
> > unique
> > > index constraint concerns case-sensitivity but SQL Server does not.
> > >
> > > So when I have to migrate the two values 'A' and 'a' into a column
with a
> > > unique index I got an SQL error concerning the uniqueness of the
index.
> > >
> > > I already successfully changed the collation mode from
case-insensitive to
> > > case-sensitive. Now the collation mode is set to case-sensitive, but
the
> > > above example still doesn't work...
> > >
> > > Does somebody have an idea how to reach real case-sensitivity in SQL
> > Server?
> > > By the way the database in SQL Server is restored from a file which
was
> > > backupped from my SQL Server, when the collation mode was set to
> > > case-insensitive.
> > >
> > > Thanks in advance
> > >
> > > Robert
> >
> >
> >|||After I changed collation mode to case-sensitive with rebuildm.exe I
generated a new database. Then I created a new table. Now when I want to
query this table the tablename in the FROM clause have to be written just
like it was in the CREATE TABLE statement. Queries on a string column are no
case-sensitive.
My problem is that I have to migrate an existing application based on
Oracle. There the tablenames in the SQL statements are written in upper case
or lower case. In Oracle it doesn't matter.
How can I reach in SQL Server that I can write tablenames in upper or lower
case but although the queries of string columns are case-sensitive?
If this is not possible, I have to cancel this migration project.
Regards,
Robert
"Adam Machanic" wrote:
> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert MÃ¶stl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > Hello gurus!
> >
> > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > unique indexes on some String columns of my DB-Schema. In Oracle the
> unique
> > index constraint concerns case-sensitivity but SQL Server does not.
> >
> > So when I have to migrate the two values 'A' and 'a' into a column with a
> > unique index I got an SQL error concerning the uniqueness of the index.
> >
> > I already successfully changed the collation mode from case-insensitive to
> > case-sensitive. Now the collation mode is set to case-sensitive, but the
> > above example still doesn't work...
> >
> > Does somebody have an idea how to reach real case-sensitivity in SQL
> Server?
> > By the way the database in SQL Server is restored from a file which was
> > backupped from my SQL Server, when the collation mode was set to
> > case-insensitive.
> >
> > Thanks in advance
> >
> > Robert
>
>|||Robert MÃ¶stl wrote:
> After I changed collation mode to case-sensitive with rebuildm.exe I
> generated a new database. Then I created a new table. Now when I want
> to query this table the tablename in the FROM clause have to be
> written just like it was in the CREATE TABLE statement. Queries on a
> string column are no case-sensitive.
> My problem is that I have to migrate an existing application based on
> Oracle. There the tablenames in the SQL statements are written in
> upper case or lower case. In Oracle it doesn't matter.
> How can I reach in SQL Server that I can write tablenames in upper or
> lower case but although the queries of string columns are
> case-sensitive?
> If this is not possible, I have to cancel this migration project.
> Regards,
> Robert
> "Adam Machanic" wrote:
>> How did you change the collation? In SQL Server, the collation of a
>> database is the default for objects created in that database -- but
>> only a default. Every object that can have a collation can have a
>> different collation. Every column, for instance, will maintain its
>> own. Whether that's the default or something else is up to you...
>> It sounds as though you may need to change the collation for the
>> column you're indexing. Although it may be better for you to start
>> over and re-create the database with a case-sensitive collation to
>> begin with, so that every column has the correct collation.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Robert MÃ¶stl" <Robert Mstl@.discussions.microsoft.com> wrote in
>> message news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
>> Hello gurus!
>> I have to migrate my DB from Oracle to SQL Server 2000. There are
>> some unique indexes on some String columns of my DB-Schema. In
>> Oracle the unique index constraint concerns case-sensitivity but
>> SQL Server does not.
>> So when I have to migrate the two values 'A' and 'a' into a column
>> with a unique index I got an SQL error concerning the uniqueness of
>> the index.
>> I already successfully changed the collation mode from
>> case-insensitive to case-sensitive. Now the collation mode is set
>> to case-sensitive, but the above example still doesn't work...
>> Does somebody have an idea how to reach real case-sensitivity in
>> SQL Server? By the way the database in SQL Server is restored from
>> a file which was backupped from my SQL Server, when the collation
>> mode was set to case-insensitive.
>> Thanks in advance
>> Robert
Not sure what collation you used, but when I select
Collation=SQL_Latin1_General_CP1_CS_AS both my table names and column
names are case-sensitive. If you're talking about string comparisons
then they will only match when there's an exact match:
For example:
create table testing (col1 nvarchar(50))
go
insert into testing values (N'ABC123')
insert into testing values (N'aBC123')
insert into testing values (N'ABC')
insert into testing values (N'abc')
select * from testing
go
select * from testing where col1 = N'ABC' -- matches one row
select * from testing where col1 = N'abc' -- matches one row
select * from testing where col1 LIKE N'A%' -- matches two rows
Drop table testing
BTW, you didn't need to rebuild the master database. You could have just
created a new database using the collation you want. Which one are you
using?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Friday, February 10, 2012

cast ( host_name() as int )

Case-sensitivity

Case-sensitivity

Case-sensitivity

Sql Case sensitivity

Blog Archive

About Me