Hi,
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
Regards
Is netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Sunday, March 11, 2012
COM+ Application server has problems with SQL2000 both Win2003
Hi,
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
RegardsIs netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
RegardsIs netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
columns_updated compatibility between sql2000 and sql 2005
Hi,
I am working on a trigger that could be installed on both sql2000 and
sql2005, so the code has to work on both systems.
The trigger uses COLUMNS_UPDATED() function to determine which fields were
updated, as BOL for sql 2005 indicate there is a slight difference
in this function parameters: if you work with sql2000 you can use
ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
calculations and then use the value with columns_updated, in case of sql2005
you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
does not work properly in sql 2000.
My question: Is it possible to write a single trigger that uses
columns_updated and works on both versions, if not how to distinguish
between 2 versions in a trigger,
e.g. if ver2000 set @.var = ....
else if ver2005 set @.var=...
Please let me know if the question is not clear I'll try to add more info.
Thank you
VadimHi
The ColumnId property is new in SQL 2005, therefore earlier versions would
return NULL so you could try something like:
ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
You could use the columnid and other information directly from syscolumns if
you aren't concerned about using system catalogues.
If you want to check SQL Server version look at
SELECT SERVERPROPERTY('ProductVersion')
other ways are listed at
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John
"Vadim" wrote:
> Hi,
> I am working on a trigger that could be installed on both sql2000 and
> sql2005, so the code has to work on both systems.
> The trigger uses COLUMNS_UPDATED() function to determine which fields were
> updated, as BOL for sql 2005 indicate there is a slight difference
> in this function parameters: if you work with sql2000 you can use
> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
> calculations and then use the value with columns_updated, in case of sql2005
> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
> does not work properly in sql 2000.
> My question: Is it possible to write a single trigger that uses
> columns_updated and works on both versions, if not how to distinguish
> between 2 versions in a trigger,
> e.g. if ver2000 set @.var = ....
> else if ver2005 set @.var=...
> Please let me know if the question is not clear I'll try to add more info.
> Thank you
> Vadim
>
>|||John,
Thank you very much, that's exactly what I needed, it worked.
Vadim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F8A7747-8E97-432B-A722-A50801BDB805@.microsoft.com...
> Hi
> The ColumnId property is new in SQL 2005, therefore earlier versions would
> return NULL so you could try something like:
> ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
> You could use the columnid and other information directly from syscolumns
> if
> you aren't concerned about using system catalogues.
> If you want to check SQL Server version look at
> SELECT SERVERPROPERTY('ProductVersion')
> other ways are listed at
> http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
>
> John
> "Vadim" wrote:
>> Hi,
>> I am working on a trigger that could be installed on both sql2000 and
>> sql2005, so the code has to work on both systems.
>> The trigger uses COLUMNS_UPDATED() function to determine which fields
>> were
>> updated, as BOL for sql 2005 indicate there is a slight difference
>> in this function parameters: if you work with sql2000 you can use
>> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
>> calculations and then use the value with columns_updated, in case of
>> sql2005
>> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' +
>> TABLE_NAME),
>> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter
>> version
>> does not work properly in sql 2000.
>> My question: Is it possible to write a single trigger that uses
>> columns_updated and works on both versions, if not how to distinguish
>> between 2 versions in a trigger,
>> e.g. if ver2000 set @.var = ....
>> else if ver2005 set @.var=...
>> Please let me know if the question is not clear I'll try to add more
>> info.
>> Thank you
>> Vadim
>>
I am working on a trigger that could be installed on both sql2000 and
sql2005, so the code has to work on both systems.
The trigger uses COLUMNS_UPDATED() function to determine which fields were
updated, as BOL for sql 2005 indicate there is a slight difference
in this function parameters: if you work with sql2000 you can use
ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
calculations and then use the value with columns_updated, in case of sql2005
you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
does not work properly in sql 2000.
My question: Is it possible to write a single trigger that uses
columns_updated and works on both versions, if not how to distinguish
between 2 versions in a trigger,
e.g. if ver2000 set @.var = ....
else if ver2005 set @.var=...
Please let me know if the question is not clear I'll try to add more info.
Thank you
VadimHi
The ColumnId property is new in SQL 2005, therefore earlier versions would
return NULL so you could try something like:
ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
You could use the columnid and other information directly from syscolumns if
you aren't concerned about using system catalogues.
If you want to check SQL Server version look at
SELECT SERVERPROPERTY('ProductVersion')
other ways are listed at
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John
"Vadim" wrote:
> Hi,
> I am working on a trigger that could be installed on both sql2000 and
> sql2005, so the code has to work on both systems.
> The trigger uses COLUMNS_UPDATED() function to determine which fields were
> updated, as BOL for sql 2005 indicate there is a slight difference
> in this function parameters: if you work with sql2000 you can use
> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
> calculations and then use the value with columns_updated, in case of sql2005
> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
> does not work properly in sql 2000.
> My question: Is it possible to write a single trigger that uses
> columns_updated and works on both versions, if not how to distinguish
> between 2 versions in a trigger,
> e.g. if ver2000 set @.var = ....
> else if ver2005 set @.var=...
> Please let me know if the question is not clear I'll try to add more info.
> Thank you
> Vadim
>
>|||John,
Thank you very much, that's exactly what I needed, it worked.
Vadim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F8A7747-8E97-432B-A722-A50801BDB805@.microsoft.com...
> Hi
> The ColumnId property is new in SQL 2005, therefore earlier versions would
> return NULL so you could try something like:
> ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
> You could use the columnid and other information directly from syscolumns
> if
> you aren't concerned about using system catalogues.
> If you want to check SQL Server version look at
> SELECT SERVERPROPERTY('ProductVersion')
> other ways are listed at
> http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
>
> John
> "Vadim" wrote:
>> Hi,
>> I am working on a trigger that could be installed on both sql2000 and
>> sql2005, so the code has to work on both systems.
>> The trigger uses COLUMNS_UPDATED() function to determine which fields
>> were
>> updated, as BOL for sql 2005 indicate there is a slight difference
>> in this function parameters: if you work with sql2000 you can use
>> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
>> calculations and then use the value with columns_updated, in case of
>> sql2005
>> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' +
>> TABLE_NAME),
>> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter
>> version
>> does not work properly in sql 2000.
>> My question: Is it possible to write a single trigger that uses
>> columns_updated and works on both versions, if not how to distinguish
>> between 2 versions in a trigger,
>> e.g. if ver2000 set @.var = ....
>> else if ver2005 set @.var=...
>> Please let me know if the question is not clear I'll try to add more
>> info.
>> Thank you
>> Vadim
>>
Saturday, February 25, 2012
column names, table name, user name
Hi;
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.
Friday, February 10, 2012
Collation problem migrating 2000 > 2005
Hi,
Migrating SQL from 2000 to 2005 I did:
1/ backup of SQL2000 db :
BACKUP DATABASE [HCW] TO DISK = 'C:\data2\db.bak'
2/ restore it on 2005 :
RESTORE FILELISTONLY FROM DISK= 'C:\data2\db.bak'
RESTORE DATABASE [HCW] FROM DISK= 'C:\data2\db.bak' WITH
MOVE 'db_moss_Data' TO 'C:\Program files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\db_Data.mdf',
MOVE 'db_moss_Log' TO 'C:\Program files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\db_Log.ldf',
REPLACE
After updating connection string my .Net site works like a charm now on
2005.
Except FTS which returns following error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
and "Latin1_General_CI_AS" in the equal to operation.
What is the good practice to deal with this issue?
Thx for any help.This happened to me when some old SQL 7.0 table were brought into SQL
2000 with the conversion being done. I believe the term
"Latin1_General_CI_AS" is the old term for collation on 7.0. To solve
it I went into the table definitions with this collation and deleted
the user defined collation. When no collation is specified the
database default is inserted making it compatible with the current
database. You could also put in the value you wanted if you do not
want the default.
This fixed my problem.|||thank you.
solved by recreating the table using collation that I needed.
"miathet" <miathet@.gmail.com> wrote in message
news:1194295291.875474.263720@.o80g2000hse.googlegroups.com...
> This happened to me when some old SQL 7.0 table were brought into SQL
> 2000 with the conversion being done. I believe the term
> "Latin1_General_CI_AS" is the old term for collation on 7.0. To solve
> it I went into the table definitions with this collation and deleted
> the user defined collation. When no collation is specified the
> database default is inserted making it compatible with the current
> database. You could also put in the value you wanted if you do not
> want the default.
> This fixed my problem.
>
Migrating SQL from 2000 to 2005 I did:
1/ backup of SQL2000 db :
BACKUP DATABASE [HCW] TO DISK = 'C:\data2\db.bak'
2/ restore it on 2005 :
RESTORE FILELISTONLY FROM DISK= 'C:\data2\db.bak'
RESTORE DATABASE [HCW] FROM DISK= 'C:\data2\db.bak' WITH
MOVE 'db_moss_Data' TO 'C:\Program files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\db_Data.mdf',
MOVE 'db_moss_Log' TO 'C:\Program files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\db_Log.ldf',
REPLACE
After updating connection string my .Net site works like a charm now on
2005.
Except FTS which returns following error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
and "Latin1_General_CI_AS" in the equal to operation.
What is the good practice to deal with this issue?
Thx for any help.This happened to me when some old SQL 7.0 table were brought into SQL
2000 with the conversion being done. I believe the term
"Latin1_General_CI_AS" is the old term for collation on 7.0. To solve
it I went into the table definitions with this collation and deleted
the user defined collation. When no collation is specified the
database default is inserted making it compatible with the current
database. You could also put in the value you wanted if you do not
want the default.
This fixed my problem.|||thank you.
solved by recreating the table using collation that I needed.
"miathet" <miathet@.gmail.com> wrote in message
news:1194295291.875474.263720@.o80g2000hse.googlegroups.com...
> This happened to me when some old SQL 7.0 table were brought into SQL
> 2000 with the conversion being done. I believe the term
> "Latin1_General_CI_AS" is the old term for collation on 7.0. To solve
> it I went into the table definitions with this collation and deleted
> the user defined collation. When no collation is specified the
> database default is inserted making it compatible with the current
> database. You could also put in the value you wanted if you do not
> want the default.
> This fixed my problem.
>
Collation Problem - Migration 7.0 to 2000
Hi,
I have a SQL7 user database backup that I load into the
SQL2000 without problem. When the restore operation is
finished I got the database loaded with
Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command
alter database Glb
COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
the on-line book but I got this message
Line 1: Incorrect syntax
near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with
the same tempdb COLLATION to avoid further problem
like "Cannot resolve collation conflict for equal to
operation."This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Leave out the quotes:
alter database Glb COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
Hi,
I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in the on-line book but I got this message
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with the same tempdb COLLATION to avoid further problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Leave out the quotes:
alter database Glb =COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...Hi,I have a SQL7 user database backup that I load into the SQL2000 =without problem. When the restore operation is finished I got the database =loaded with Compatibility_52_409_300003 COLLATION.How could I =change the COLLATION set for that database?I am trying to use this following =command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as =described in the on-line book but I got this messageLine 1: Incorrect =syntax near 'SQL_Latin1_General_CP1_CI_AS'.In fact, I am trying to =do this to have my database with the same tempdb COLLATION to avoid further =problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0--|||Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just curious, what is the database compatibility level? Have you =changed it to 80? Check out sp_dbcmptlevel in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the ISQL too and put the database in the restricted use... and nothing.
Thanks in advance and if you have another suggestion, please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the >SQL2000 without problem. When the restore operation is >finished I got the database loaded with >Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book but I got this message
>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with >the same tempdb COLLATION to avoid further problem >like "Cannot resolve collation conflict for equal to >operation."
>
>
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just curious, what is the database =compatibility level? Have you changed it to 80? Check out sp_dbcmptlevel =in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0e7301c3576d$ae=023d60$a301280a@.phx.gbl...Thanks TomI got the same error without the quotes. I tried in the =ISQL too and put the database in the restricted use... and =nothing.Thanks in advance and if you have another suggestion, please send it.>--Original Message-->Leave out the quotes:>>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Marc=elo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...>Hi,>>I have a SQL7 user database backup that I load into the >SQL2000 =without problem. When the restore operation is >finished I got the =database loaded with >Compatibility_52_409_300003 =COLLATION.>>How could I change the COLLATION set for that database?>I am trying =to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book =but I got this message>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.>>In fact, I am trying to =do this to have my database with >the same tempdb COLLATION to avoid =further problem >like "Cannot resolve collation conflict for equal to >operation.">>>
--=_NextPart_000_0172_01C3574D.102E6480--|||Unfortunately, changing the database's default collation won't change the
collation of any existing tables or columns; it only affects new tables
that are created in that database. In other words, it won't have any
effect on your collation conflict errors.
It is not trivial to change the collation of all the columns in an existing
database. If you have the option, probably the easiest solution by far is
to modify your stored procs slightly so that they use "COLLATE
database_default" when create temp tables. This will tell SQL that the
temp table's collation should be inherited from the current user database,
not tempdb. From that point forward your code will be "server
collation-agnostic", meaning you can move this database to any server with
any collation and the code will run fine. An example:
CREATE #temptbl (
col1 int,
col2 varchar(20) COLLATE database_default,
col3 nchar(4) COLLATE database_default
)
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Tom Moreau" <tom@.dont.spam.me.cips.ca>
References: <0f3101c35768$1eefe780$a101280a@.phx.gbl>
<#J4n2n2VDHA.392@.TK2MSFTNGP11.phx.gbl>
<0e7301c3576d$ae023d60$a301280a@.phx.gbl>
Subject: Re: Collation Problem - Migration 7.0 to 2000
Date: Thu, 31 Jul 2003 10:18:19 -0400
Lines: 154
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="--=_NextPart_000_0172_01C3574D.102E6480"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
Message-ID: <e78Jk62VDHA.3220@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: 142.108.149.181
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298816
X-Tomcat-NG: microsoft.public.sqlserver.server
Just curious, what is the database compatibility level? Have you changed
it to 80? Check out sp_dbcmptlevel in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message
news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>
I have a SQL7 user database backup that I load into the
SQL2000 without problem. When the restore operation is
finished I got the database loaded with
Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command
alter database Glb
COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
the on-line book but I got this message
Line 1: Incorrect syntax
near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with
the same tempdb COLLATION to avoid further problem
like "Cannot resolve collation conflict for equal to
operation."This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Leave out the quotes:
alter database Glb COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
Hi,
I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in the on-line book but I got this message
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with the same tempdb COLLATION to avoid further problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Leave out the quotes:
alter database Glb =COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado"
--=_NextPart_000_0101_01C35748.686076C0--|||Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just curious, what is the database compatibility level? Have you =changed it to 80? Check out sp_dbcmptlevel in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the ISQL too and put the database in the restricted use... and nothing.
Thanks in advance and if you have another suggestion, please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the >SQL2000 without problem. When the restore operation is >finished I got the database loaded with >Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book but I got this message
>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with >the same tempdb COLLATION to avoid further problem >like "Cannot resolve collation conflict for equal to >operation."
>
>
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just curious, what is the database =compatibility level? Have you changed it to 80? Check out sp_dbcmptlevel =in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado"
--=_NextPart_000_0172_01C3574D.102E6480--|||Unfortunately, changing the database's default collation won't change the
collation of any existing tables or columns; it only affects new tables
that are created in that database. In other words, it won't have any
effect on your collation conflict errors.
It is not trivial to change the collation of all the columns in an existing
database. If you have the option, probably the easiest solution by far is
to modify your stored procs slightly so that they use "COLLATE
database_default" when create temp tables. This will tell SQL that the
temp table's collation should be inherited from the current user database,
not tempdb. From that point forward your code will be "server
collation-agnostic", meaning you can move this database to any server with
any collation and the code will run fine. An example:
CREATE #temptbl (
col1 int,
col2 varchar(20) COLLATE database_default,
col3 nchar(4) COLLATE database_default
)
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Tom Moreau" <tom@.dont.spam.me.cips.ca>
References: <0f3101c35768$1eefe780$a101280a@.phx.gbl>
<#J4n2n2VDHA.392@.TK2MSFTNGP11.phx.gbl>
<0e7301c3576d$ae023d60$a301280a@.phx.gbl>
Subject: Re: Collation Problem - Migration 7.0 to 2000
Date: Thu, 31 Jul 2003 10:18:19 -0400
Lines: 154
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="--=_NextPart_000_0172_01C3574D.102E6480"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
Message-ID: <e78Jk62VDHA.3220@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: 142.108.149.181
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298816
X-Tomcat-NG: microsoft.public.sqlserver.server
Just curious, what is the database compatibility level? Have you changed
it to 80? Check out sp_dbcmptlevel in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message
news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>
Subscribe to:
Posts (Atom)