Dear fellows,
Sometimes when I obtain a script for tables and then I copy them to another
Sql Server, collation at field level is lost. Why?
Thank you very much,If no specifiy coallation is named on the column, it wont be scripted.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Enric" <Enric@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6322A9EB-4603-43C6-BC2C-09DBB81F45D3@.microsoft.com...
> Dear fellows,
> Sometimes when I obtain a script for tables and then I copy them to
> another
> Sql Server, collation at field level is lost. Why?
> Thank you very much,
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Sunday, March 25, 2012
Collation Errors
All my tables have the same collation, the groups default collation is the same as all the tables I have copied over but I still get the :
Server: Msg 446, Level 16, State 9, Procedure SP_TT_EnqSearchByPhoneV2, Line 27
Cannot resolve collation conflict for equal to operation.
error
The stored procedure is this:
declare @.enqid int, @.count int ,@.launchNextPage int
set @.count = (select count(enqid) from TT_EnquiryTable where REPLACE(EnqPhone,' ','') = @.phone)
set @.count = @.count + (select count(enqid) from TT_ENquiryTable where REPLACE(EnqPhone,' ','') = @.phone)
IF NOT EXISTS(select * from TT_EnquiryTableLocal where enqphone = @.phone)
BEGIN
IF NOT EXISTS(select * from TT_EnquiryTable where REPLACE(enqphone,' ','') = @.phone )
BEGIN
insert into traveltime..TT_enquiryTableLocal(enqphone, opcomid)
values (@.phone, @.opcomid)
set @.enqid = @.@.identity
END
ELSE
BEGIN
insert TT_EnquiryTableLocal(enqphone, opcomid)
values(@.phone, @.opcomid)
set @.enqid = @.@.identity
update l
set l.enqentrydate = getdate(),l.enqname= e.enqname, l.enqaddress = e.enqaddress,
l.enqtown = e.enqtown,l.enqcounty = e.enqcounty, l.enqpostcode = e.enqpostcode,
l.enqemail = e.enqemail, l.enqcomments = e.enqcomments, l.enqfutureaccept = e.enqfutureaccept,
l.officeid = e.officeid,l.oldenqid = e.enqid
from traveltime..TT_enquiryTableLocal l , internet..TT_enquiryTable e
where REPLACE(e.enqphone,' ','') = @.phone and l.enqphone = e.enqphone
and l.enqid = @.enqid
END
END
ELSE
BEGIN
IF EXISTS (Select * from TT_enquirytableLocal where enqphone = @.phone and sent >= 1 )
--and datediff(dy,enqentrydate,getdate()) >=1 )
AND NOT EXISTS (Select * from TT_enquirytableLocal where enqphone = @.phone and sent = 0)
--and datediff(dy,enqentrydate,getdate()) >=1 )
BEGIN
INSERT TT_EnquiryTableLocal
select TOP 1 '',getdate(),enqname,enqaddress,enqtown,enqcounty, enqpostcode,@.phone,enqemail,
enqcomments, enqfutureaccept,officeid, 0 /*sent*/,7,'',@.opcomid
from TT_EnquiryTableLocal
where enqphone = @.phone
set @.enqid = @.@.identity
set @.count = 0
set @.launchNextPage =0
END
ELSE
BEGIN
IF EXISTS(select top 1 *
from TT_EnquiryTable e, TT_enquiryTable l
where e.enqphone = @.phone and l.enqphone = e.enqphone
and (e.enqphone is not null and e.enqphone <> ''))
BEGIN
select top 1 @.count as 'count',*
from TT_EnquiryTable e, TT_enquiryTable l
where e.enqphone = @.phone and l.enqphone = e.enqphone
and (e.enqphone is not null and e.enqphone <> '')
order by e.enqid desc
END
END
END
IF @.enqid >=1
BEGIN
SELECT @.count as 'count',@.enqid as 'enqid', * FROM TT_EnquiryTableLocal where enqid = @.enqid and sent =0 order by e.enqid desc
END
ELSE
BEGIN
SELECT TOP 1 @.count as 'count',@.enqid as 'enqid', * FROM TT_EnquiryTableLocal where enqphone = @.phone and sent =0 order by e.enqid desc
END
If anybody can help I will be hugely grateful..
Thanks in advance
NathanWhat does this give you
SELECT LTRIM(RTRIM(CONVERT(varchar(255),DATABASEPROPERTYE X('CompDB','Collation'))))|||I ran the code you gave me against each database and it returned
Latin1_General_CI_AS
for each database.
Originally the databases were :
SQL_Latin1_General_CP1_CI_AS
the code all worked fine on that SQL server
but we moved to a new server were the collation on the sql database is set to Latin1_General_CI_AS
And now we are having all sorts of problems|||I checked all the databases and they have all been sucessfully changed to Latin1_General_CI_AS
however when I check the tables and columns within those databases they are still:
SQL_Latin1_Genaeral_CP1_CI_AS
Could this be where my problems are stemming from?
If so is there a way of updating all the tables and columns within to be the the new collation of Latin1_General_CI_AS?
PLEASE PLEASE HELP!! Tight Deadline on this.
Nathan|||Sorry, but the server was built with a different collation than the standard.
Your best bet (easiest) is to reinstall sql server with the correct collation.
Server: Msg 446, Level 16, State 9, Procedure SP_TT_EnqSearchByPhoneV2, Line 27
Cannot resolve collation conflict for equal to operation.
error
The stored procedure is this:
declare @.enqid int, @.count int ,@.launchNextPage int
set @.count = (select count(enqid) from TT_EnquiryTable where REPLACE(EnqPhone,' ','') = @.phone)
set @.count = @.count + (select count(enqid) from TT_ENquiryTable where REPLACE(EnqPhone,' ','') = @.phone)
IF NOT EXISTS(select * from TT_EnquiryTableLocal where enqphone = @.phone)
BEGIN
IF NOT EXISTS(select * from TT_EnquiryTable where REPLACE(enqphone,' ','') = @.phone )
BEGIN
insert into traveltime..TT_enquiryTableLocal(enqphone, opcomid)
values (@.phone, @.opcomid)
set @.enqid = @.@.identity
END
ELSE
BEGIN
insert TT_EnquiryTableLocal(enqphone, opcomid)
values(@.phone, @.opcomid)
set @.enqid = @.@.identity
update l
set l.enqentrydate = getdate(),l.enqname= e.enqname, l.enqaddress = e.enqaddress,
l.enqtown = e.enqtown,l.enqcounty = e.enqcounty, l.enqpostcode = e.enqpostcode,
l.enqemail = e.enqemail, l.enqcomments = e.enqcomments, l.enqfutureaccept = e.enqfutureaccept,
l.officeid = e.officeid,l.oldenqid = e.enqid
from traveltime..TT_enquiryTableLocal l , internet..TT_enquiryTable e
where REPLACE(e.enqphone,' ','') = @.phone and l.enqphone = e.enqphone
and l.enqid = @.enqid
END
END
ELSE
BEGIN
IF EXISTS (Select * from TT_enquirytableLocal where enqphone = @.phone and sent >= 1 )
--and datediff(dy,enqentrydate,getdate()) >=1 )
AND NOT EXISTS (Select * from TT_enquirytableLocal where enqphone = @.phone and sent = 0)
--and datediff(dy,enqentrydate,getdate()) >=1 )
BEGIN
INSERT TT_EnquiryTableLocal
select TOP 1 '',getdate(),enqname,enqaddress,enqtown,enqcounty, enqpostcode,@.phone,enqemail,
enqcomments, enqfutureaccept,officeid, 0 /*sent*/,7,'',@.opcomid
from TT_EnquiryTableLocal
where enqphone = @.phone
set @.enqid = @.@.identity
set @.count = 0
set @.launchNextPage =0
END
ELSE
BEGIN
IF EXISTS(select top 1 *
from TT_EnquiryTable e, TT_enquiryTable l
where e.enqphone = @.phone and l.enqphone = e.enqphone
and (e.enqphone is not null and e.enqphone <> ''))
BEGIN
select top 1 @.count as 'count',*
from TT_EnquiryTable e, TT_enquiryTable l
where e.enqphone = @.phone and l.enqphone = e.enqphone
and (e.enqphone is not null and e.enqphone <> '')
order by e.enqid desc
END
END
END
IF @.enqid >=1
BEGIN
SELECT @.count as 'count',@.enqid as 'enqid', * FROM TT_EnquiryTableLocal where enqid = @.enqid and sent =0 order by e.enqid desc
END
ELSE
BEGIN
SELECT TOP 1 @.count as 'count',@.enqid as 'enqid', * FROM TT_EnquiryTableLocal where enqphone = @.phone and sent =0 order by e.enqid desc
END
If anybody can help I will be hugely grateful..
Thanks in advance
NathanWhat does this give you
SELECT LTRIM(RTRIM(CONVERT(varchar(255),DATABASEPROPERTYE X('CompDB','Collation'))))|||I ran the code you gave me against each database and it returned
Latin1_General_CI_AS
for each database.
Originally the databases were :
SQL_Latin1_General_CP1_CI_AS
the code all worked fine on that SQL server
but we moved to a new server were the collation on the sql database is set to Latin1_General_CI_AS
And now we are having all sorts of problems|||I checked all the databases and they have all been sucessfully changed to Latin1_General_CI_AS
however when I check the tables and columns within those databases they are still:
SQL_Latin1_Genaeral_CP1_CI_AS
Could this be where my problems are stemming from?
If so is there a way of updating all the tables and columns within to be the the new collation of Latin1_General_CI_AS?
PLEASE PLEASE HELP!! Tight Deadline on this.
Nathan|||Sorry, but the server was built with a different collation than the standard.
Your best bet (easiest) is to reinstall sql server with the correct collation.
Thursday, March 22, 2012
Collation Conflict (DHL)
Moving code from Sybase to SQL Server 2005. I have two tables. One table (vINID) is reset and populated right before this code runs. The other table (vINPA) is a table in the database used by the Front End App. This is the error I get when I try and run on our SQL Server 2005.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
Here is the SQL code:
SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID
WHERE vINID.BLIV_ID = '071600007594'
Thank you for your help.
DavidIdeally, you would change the collation on the oddball table. Was there a reason to make one of these tables with a collation different from the database default collation?|||Just noticed one of the tables is actually on a remote server. Try this:
SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID COLLATE database_default
WHERE vINID.BLIV_ID = '071600007594'|||Code fix works. Thank you. Not sure what "Collation" is or how to change it or what the database default collations is? New to SQL Server 2005.sqlsql
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
Here is the SQL code:
SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID
WHERE vINID.BLIV_ID = '071600007594'
Thank you for your help.
DavidIdeally, you would change the collation on the oddball table. Was there a reason to make one of these tables with a collation different from the database default collation?|||Just noticed one of the tables is actually on a remote server. Try this:
SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID COLLATE database_default
WHERE vINID.BLIV_ID = '071600007594'|||Code fix works. Thank you. Not sure what "Collation" is or how to change it or what the database default collations is? New to SQL Server 2005.sqlsql
collation conflict
Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
Elizabeta
Elizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>
|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
[vbcol=seagreen]
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
ening.
>
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
Elizabeta
Elizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>
|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
[vbcol=seagreen]
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
ening.
>
Collation Conflict
Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
Tim
Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
sqlsql
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
Tim
Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
sqlsql
collation conflict
Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
> >Hi, all
> >
> >I am doing testing of my application, actualy one Store Procedure at the moment.
> >
> >I have development database.
> >
> >I copy tables that I need for the testing.
> >
> >The same queries that run on live database fail here wth the message
> >'cannot resolve collation conflict...'
> >
> >I discovered that it is due to all copied tables have other collation in char columns.
> >
> >When I change them to database default setting, the stored procedure works OK.
> >
> >My question is:
> >
> >how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
> >
> >I am very grateful for any helpful information.
> >
> >TIA
> >
> >Elizabeta
> >
> >
>
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
> >Hi, all
> >
> >I am doing testing of my application, actualy one Store Procedure at the moment.
> >
> >I have development database.
> >
> >I copy tables that I need for the testing.
> >
> >The same queries that run on live database fail here wth the message
> >'cannot resolve collation conflict...'
> >
> >I discovered that it is due to all copied tables have other collation in char columns.
> >
> >When I change them to database default setting, the stored procedure works OK.
> >
> >My question is:
> >
> >how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
> >
> >I am very grateful for any helpful information.
> >
> >TIA
> >
> >Elizabeta
> >
> >
>
Collation Conflict
Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimModify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>|||Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>|||Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimModify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>|||Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>|||Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >
collation conflict
Hi, all
I am doing testing of my application, actualy one Store Procedure at the mo
ment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in cha
r columns.
When I change them to database default setting, the stored procedure works O
K.
My question is:
how can I avoid to have collation in new copied tables, although the databas
es have it the same. I tried to copy using CREATE TABLE scripts or DTS servi
ces, copy as objects, always the same problem. Is there any setting on SQL s
erver to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the m
oment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in ch
ar columns.
>When I change them to database default setting, the stored procedure works
OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have
it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as ob
jects, always the same problem. Is there any setting on SQL server to avoid this hap
pen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "scri
pt
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
>
ening.[vbcol=seagreen]
>sqlsql
I am doing testing of my application, actualy one Store Procedure at the mo
ment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in cha
r columns.
When I change them to database default setting, the stored procedure works O
K.
My question is:
how can I avoid to have collation in new copied tables, although the databas
es have it the same. I tried to copy using CREATE TABLE scripts or DTS servi
ces, copy as objects, always the same problem. Is there any setting on SQL s
erver to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the m
oment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in ch
ar columns.
>When I change them to database default setting, the stored procedure works
OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have
it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as ob
jects, always the same problem. Is there any setting on SQL server to avoid this hap
pen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "scri
pt
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
>
ening.[vbcol=seagreen]
>sqlsql
Collation Conflict
Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo
].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product
]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimYour TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[d
bo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Produ
ct]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo
].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product
]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimYour TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[d
bo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Produ
ct]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
Collation Change
I need to change the collation of a database (Including all the tables,
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.
DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.
|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>
|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.
|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.
DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.
|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>
|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.
|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
Collation Change
I need to change the collation of a database (Including all the tables,
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
> >I need to change the collation of a database (Including all the tables,
> > columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> > other than dropping indexes, creating a script for everything and running
> > it ?
> >
> >
> > Thanks.
>
>|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
> >I need to change the collation of a database (Including all the tables,
> > columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> > other than dropping indexes, creating a script for everything and running
> > it ?
> >
> >
> > Thanks.
>
>|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
Collation Change
I need to change the collation of a database (Including all the tables,
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it
?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creatin
g
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/datab...eCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||Thanks David............This one says "SQL Server 2000 Collation Changer"
.
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/datab...eCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it
?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creatin
g
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/datab...eCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||Thanks David............This one says "SQL Server 2000 Collation Changer"
.
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/datab...eCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
Collation and views
Suppose you have your databases's collation as X but the value of collation
on the varchar fields of some of your tables as Y.
Suppose you create a view like this:
SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
Table_A
UNIONA ALL
SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
Table_B
You've got an error of incompatble collation on the first two columns of the
view. I think because on the constant string values the db assign the
collation X while the corresponding varchar fields of Table_B have
collation Y.
Is there any solution to this problem?
Thank you all
Andreayes, there is: use COLLATE clause in the select statement.
dean
"Andrea Temporin" <NOSPAM_temporin@.encopro.it> wrote in message
news:%232K2hKyGFHA.3108@.tk2msftngp13.phx.gbl...
> Suppose you have your databases's collation as X but the value of
collation
> on the varchar fields of some of your tables as Y.
> Suppose you create a view like this:
> SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
> Table_A
> UNIONA ALL
> SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
> Table_B
> You've got an error of incompatble collation on the first two columns of
the
> view. I think because on the constant string values the db assign the
> collation X while the corresponding varchar fields of Table_B have
> collation Y.
> Is there any solution to this problem?
> Thank you all
> Andrea
>
on the varchar fields of some of your tables as Y.
Suppose you create a view like this:
SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
Table_A
UNIONA ALL
SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
Table_B
You've got an error of incompatble collation on the first two columns of the
view. I think because on the constant string values the db assign the
collation X while the corresponding varchar fields of Table_B have
collation Y.
Is there any solution to this problem?
Thank you all
Andreayes, there is: use COLLATE clause in the select statement.
dean
"Andrea Temporin" <NOSPAM_temporin@.encopro.it> wrote in message
news:%232K2hKyGFHA.3108@.tk2msftngp13.phx.gbl...
> Suppose you have your databases's collation as X but the value of
collation
> on the varchar fields of some of your tables as Y.
> Suppose you create a view like this:
> SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
> Table_A
> UNIONA ALL
> SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
> Table_B
> You've got an error of incompatble collation on the first two columns of
the
> view. I think because on the constant string values the db assign the
> collation X while the corresponding varchar fields of Table_B have
> collation Y.
> Is there any solution to this problem?
> Thank you all
> Andrea
>
Monday, March 19, 2012
Colation problem
Hi,
I am using SQLServer 2000 with SP4 and I am getting a strange collation problem.
I have three tables, TEmployee, TMechanic and TManager, each with the two columns Firstname, Lastname which are both varchars.
I run the following query:
<PRE>
SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM ((
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TEmployee query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TCoManager query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM TMechanic query1 WHERE FIRSTNAME = 'John')
UNION
(SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM TEmployee query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM
TCoManager query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
)) OUTERUNION
</PRE>
I get the following error:
Cannot resolve collation conflict for column 2 in SELECT statement.
If I change my select statement to only have one column (doesn't matter which column) it doesn't happen.
Any ideas, greatly appreciated?I am betting the collations on the columns comprising your union query do not match. when someone created one of these tables they may have specified some column level collations. I think you are going to have to export the data from the problematic table recreate your table with the write collations and then reimport your data. However this may have been done for a reason. Got documentation?|||Hi Thrasymachus,
Thanks for getting back to me.
I have looked at the scripts and they look ok, they are just simply create table statements with n oreference to collation.
So I need to know what does SQLServer set them to, or what does it think they are?
Ok this sounds like a stupid question so forgive, how do I check the collation levels for the columns?
What's interesting is a similar query works fine:
SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM (
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TEmployee query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TCoManager query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM TMechanic query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM TPerson query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM
TCoManager query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
) OUTERUNION
Note the differences are:
I have changed the "Union" keyword in the middle to a "Union All" and removed inner brackets.
Any help greatly appreciated.|||execute sp_help sp in both table and check the difference.
exec sp_help tablename|||I have checked the collations, of all the tables, they are all Latin1_General_CI_AS. I think the problem is because SQLServer decides to change the collation in one of the sub selects. As the problem doesn't happen if I limit the number of nested sub selects to one.
Does anybody know how I get the default collation for a database, i.e. not just the collation for a column / table?
Thanks|||SELECT DatabasePropertyEx (http://msdn2.microsoft.com/en-us/library/ms186823.aspx)('master', 'Collation')
-PatP|||Hi,
I wonder if the problem might be with tempdb... is the default collation for tempdb different from your user database ? SQL might be storing intermediate data in tempdb, particularly if you are using UNION vs. UNION ALL.
Bill
I am using SQLServer 2000 with SP4 and I am getting a strange collation problem.
I have three tables, TEmployee, TMechanic and TManager, each with the two columns Firstname, Lastname which are both varchars.
I run the following query:
<PRE>
SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM ((
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TEmployee query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TCoManager query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM TMechanic query1 WHERE FIRSTNAME = 'John')
UNION
(SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM TEmployee query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM
TCoManager query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
)) OUTERUNION
</PRE>
I get the following error:
Cannot resolve collation conflict for column 2 in SELECT statement.
If I change my select statement to only have one column (doesn't matter which column) it doesn't happen.
Any ideas, greatly appreciated?I am betting the collations on the columns comprising your union query do not match. when someone created one of these tables they may have specified some column level collations. I think you are going to have to export the data from the problematic table recreate your table with the write collations and then reimport your data. However this may have been done for a reason. Got documentation?|||Hi Thrasymachus,
Thanks for getting back to me.
I have looked at the scripts and they look ok, they are just simply create table statements with n oreference to collation.
So I need to know what does SQLServer set them to, or what does it think they are?
Ok this sounds like a stupid question so forgive, how do I check the collation levels for the columns?
What's interesting is a similar query works fine:
SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM (
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TEmployee query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TCoManager query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM TMechanic query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM TPerson query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM
TCoManager query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
) OUTERUNION
Note the differences are:
I have changed the "Union" keyword in the middle to a "Union All" and removed inner brackets.
Any help greatly appreciated.|||execute sp_help sp in both table and check the difference.
exec sp_help tablename|||I have checked the collations, of all the tables, they are all Latin1_General_CI_AS. I think the problem is because SQLServer decides to change the collation in one of the sub selects. As the problem doesn't happen if I limit the number of nested sub selects to one.
Does anybody know how I get the default collation for a database, i.e. not just the collation for a column / table?
Thanks|||SELECT DatabasePropertyEx (http://msdn2.microsoft.com/en-us/library/ms186823.aspx)('master', 'Collation')
-PatP|||Hi,
I wonder if the problem might be with tempdb... is the default collation for tempdb different from your user database ? SQL might be storing intermediate data in tempdb, particularly if you are using UNION vs. UNION ALL.
Bill
Sunday, March 11, 2012
Code with color
Hi
I have three tables in my db called Products, Receives, Issues.
Products
ProductID, ProductName
Receives
ProductID, ReceiveQty
Issues
ProductID, IssueQty
I want to pass an ProductID and display columns from a sproc like this -
ProductName, ReceiveQty, IssueQty, BalanceQty ( ReceiveQty - IssueQty )
A record may exist or not in the Issues table for a given ProductID. What will be the syntex to write this sproc?
Regards
Kapalic
Something like this:
Code Snippet
SELECT
p.ProductName,
isnull( r.ReceiveQty, 0 ),
isnull( i.IssueQty, 0 ),
BalanceQty = ( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID
I must note: this seems like a very odd design, and may be troublesome to properly 'tune'.
|||Try the following, it may be helpful for you.
Code Snippet
select ProductName, ReceiveQty, IssueQty, (ReceiveQty-IssueQty) as BalanceQtyfrom
(
select Products.ProductName,
isnull((select sum(ReceiveQty) from Receives where Receives.ProductID=Products.ProductID),0) as ReceiveQty,
isnull((select sum(IssueQty) from Issues where Issues.ProductID=Products.ProductID),0) as IssueQty
from Products where Products.ProductID = yourProductId
) as temp
|||to:Arnie Rowland
what will happen if i recieve a product 2 times or more?
|||You make a good observation. I should have included a [GROUP BY].
I think that this revision will be more in line with an acceptable solution:
Code Snippet
SELECT
p.ProductName,
p.ProductID,
dt.ReceiveQty,
dt.IssueQty,
dt.BalanceQty
FROM Products p
JOIN (SELECT
p.ProductID,
ReceiveQty = sum( isnull( r.ReceiveQty, 0 )),
IssueQty = sum( isnull( i.IssueQty, 0 )),
BalanceQty = sum( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
LEFT JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID
WHERE p.ProductID = @.ProductID
GROUP BY ProductID
) dt
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID
IssueQty will be 0 (zero) if no rows exist in the Issues table.
ReceiveQty will be 0 (zero) if no rows exist in the Receives table.
|||Arnie
I m getting incorrect result - while I enter multiple receive record, every of three qty become double of actual result! Whats the prob?
Kapalic
|||In order by continue assisting you, it would be useful to have sample data to test.
Please include table DDL and sample data in the form of INSERT statements.
|||Kapalic,
First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.
Hopefully, this code will get you what you want.
-Arnie
Code Snippet
DECLARE @.Products table
( ProductID int,
ProductName varchar(25)
)
DECLARE @.Receives table
( ProductID int,
ReceiveQty int
)
DECLARE @.Issues table
( ProductID int,
IssueQty int
)
INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )
INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )
INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )
DECLARE @.ProductID int
SET @.ProductID = 1
-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,
ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))
FROM @.Receives
WHERE ProductID = @.ProductID
),
IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))
FROM @.Issues
WHERE ProductID = @.ProductID
)
) dt
JOIN @.Products p
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID
Dear Arnie,
Thank you very much for your great help! This sproc is now working allright. Will you please tell me what was wrong with the sproc?
And I need a bit more help. I can't understand how to modify the sproc so that it displays all the products which are listed in the product table. Please help!!
Regards
Kapalic
|||
Code Snippet
SELECTp.ProductName,
p.ProductID,
p.AdditionalProductsColumnsHere,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )), You can add additional columns from the Products table in the outer query. Prefix each column with the alias 'p'.
|||
I didn't want to show another column. I wanted to show all the records from the products table, not just a single record about a product. I couldn't make myself clear.
Thnx
Kapalic
|||
Arnie Rowland wrote:
Kapalic,
First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.
Hopefully, this code will get you what you want.
-Arnie
Code Snippet
DECLARE @.Products table( ProductID int,
ProductName varchar(25)
)
DECLARE @.Receives table( ProductID int,
ReceiveQty int
)
DECLARE @.Issues table( ProductID int,
IssueQty int
)
INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )
DECLARE @.ProductID int
SET @.ProductID = 1
-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))
FROM @.Receives
WHERE ProductID = @.ProductID
),
IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))
FROM @.Issues
WHERE ProductID = @.ProductID
)
) dt
JOIN @.Products p
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID
Can you tell me how to post a sample code with color?
|||It's very easy. Copy the code to MS Word, and copy and paste it from Word to post. It will appear colorful.
Kapalic
Code to Import Bank Files (BAI, BAI2)
Anyone have some code to import bank files to SQL Server 2000 tables in the formats BAI or BAI2? I'm using a DTS package currently, but I'd like to have more control over this by using T-SQL or vbscript or something.
These files have complex structures/multiple record types. Not to mention my main problem of trying to use a bulk insert which doesn't seem to work because of something to do with the row delimiters. Not too sure what's up with that because I usually do this for all my flat file imports. I'm certain vbscript will work, but I'd like to know if anyone out there has already built something for these particular files?
Thanks!
I have not worked on BAI in years, but what I ended up doing was importing the file into a table with 2 fields: linetype and linevalue, then parsing the line based on the type.
The problem with the stored proc is the string processing is very limited in TSQL. It is much easier to mainupliate in VB using Split().
You could create the same affect using SSIS in a Dataflow with conditional branching on linetype. I would have to think about the type 88 lines continuation. I am not sure how you would handle that.
Also, I wrote a preproceesor which converted BAI1 to BAI2, there is not much difference. Then my code only had to deal with BAI2 files.
Good luck.
Code table maintenance
Hello,
I am working on the logical model for a database. I need to use a number of
code tables (tables that keep typically name value pairs. I need maintain da
ta like products, services etc).
I am wondering if I increase the abstraction and use one table to represent
the name value pairs but use a category to identify each type is there is an
y value in doing this?
The advantage with this I think is consolidating the data and probably minim
izing the administration
The disadvantages may be too many joins that need to be qualified by the cat
egory type. Also, I may end up having too many self-joins.
Any suggestions'?doesnt seem to make sense to me.
I would keep them seperate.
Greg Jackson
PDX, Oregon
I am working on the logical model for a database. I need to use a number of
code tables (tables that keep typically name value pairs. I need maintain da
ta like products, services etc).
I am wondering if I increase the abstraction and use one table to represent
the name value pairs but use a category to identify each type is there is an
y value in doing this?
The advantage with this I think is consolidating the data and probably minim
izing the administration
The disadvantages may be too many joins that need to be qualified by the cat
egory type. Also, I may end up having too many self-joins.
Any suggestions'?doesnt seem to make sense to me.
I would keep them seperate.
Greg Jackson
PDX, Oregon
Code Table Maintenance
Hello,
I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc).
I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?
The advantage with this I think is consolidating the data and probably minimizing the administration
The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.
Any suggestions???Personally I like this approach because then I don't have a bunch of hash tables scattered around the database. Adding new groups of name value pairs becomes a lot easier.
I haven't found the need to perform self-joins, but yes the large number of joins to the same table tends to be a pain. But, you'd still have to have the joins regardless (just to different tables).
Honestly, I am not sure of the performance benefits. But I think the "compactness" of the solution has value.
My 2 cents, maybe only 1 cent.
Terri
Code suggestions for database searches
I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits.
Thank you
>> I need to create an admin section that lets me search on one field or the combination of several.
Do you means within a given table or across all tables?
>> database search strategy and admin edits
If across all tables, what about validation? A generic edit solution would bypasss data validation checks - not a good idea.
|||I need it across several tables and I am experimenting with the Multi_View control because although it only displays one view at a time all controls are accessible because the Views do not function as seperate containers. So far it seems to be meeting the major requirements however the displays are a little hard to figure out.|||
Have you considered how to handle data validation?
|||
I am doing that using Validation controls on the database submission form and in the View Edit template. At least I expect Validation will work in the Views.
Thursday, March 8, 2012
Code Level for Deadlock Detection
I've got a VB app that calls an SQL stored procedure that calls
another SQL stored procedure that updates multiple tables. I want
deadlock detection and recovery. Where can it be done?
The inherited code attempts to check for error 1205 inside the inner
sql stored procedure - an error code check after every update
statement. Will that work? I thought the deadlock victim would kill
the entire stored procedure, not just the blocked update statement.
If the inherited code is wrong, will it work to put the deadlock test
in the outer stored procedure? Will the deadlock victim kill only the
inner stored procedure and return error code 1205 to the outer stored
procedure? Or does it kill both the inner and outer stored
procedures?The transaction is rolled back and the entire back is aborted when you are
chosen as the deadlock victim
(http://www.sommarskog.se/error-handling-I.html). If you want to retry the
operation, you'll need to handle the error in your VB code.
Hope this helps.
Dan Guzman
SQL Server MVP
"William Lipp" <w.b.(MyLastNameHere)@.ieee.org> wrote in message
news:t6sv32d0vahsjme8olhjc90l7dnqu1a9gj@.
4ax.com...
> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?|||Try testing it.
A deadlock will terminate the connection so there's nothing that can be done
in the stored procedures.
Put the retry in the VB app.
I use an interface class to access the database so it's easy to put code
like this in all calls.
see
http://www.nigelrivett.net/VB/VB6DataAccessLayer.html
"William Lipp" wrote:
> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?
>|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
Up to SQL 2000 this is correct.
In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
procedure. Since you say "error check after every update statement",
it does not sound like TRY-CATCH, and thus that code is not really
working.
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|||On Sat, 15 Apr 2006 12:23:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>William Lipp writes:
> Erland Sommarskog writes
>In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
>procedure. Since you say "error check after every update statement",
>it does not sound like TRY-CATCH, and thus that code is not really
>working.
Yes, the existing code just checks @.@.error after the insert - I was
pretty sure that wasn't working. I had hoped that the outer stored
procedure could that method to detect deadlocks within the inner
stored procedure. I believe these responses say that won't work
either - that a deadlock victim in the inner stored procedure will
also kill the outer stored procedure, and the only place I can detect
the deadlock victim and reattempt is from the VB code that called the
outer procedure. Have I got that right?|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> Yes, the existing code just checks @.@.error after the insert - I was
> pretty sure that wasn't working. I had hoped that the outer stored
> procedure could that method to detect deadlocks within the inner
> stored procedure. I believe these responses say that won't work
> either - that a deadlock victim in the inner stored procedure will
> also kill the outer stored procedure, and the only place I can detect
> the deadlock victim and reattempt is from the VB code that called the
> outer procedure. Have I got that right?
Yes, for SQL 2000 your assumption is entirely accurate. (Well, there is a
trick you can play with linked servers, but it's fairly obscure.)
For SQL 2005, as I said, it is possible to catch the error in a stored
procedure with the new TRY-CATCH construct.
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
another SQL stored procedure that updates multiple tables. I want
deadlock detection and recovery. Where can it be done?
The inherited code attempts to check for error 1205 inside the inner
sql stored procedure - an error code check after every update
statement. Will that work? I thought the deadlock victim would kill
the entire stored procedure, not just the blocked update statement.
If the inherited code is wrong, will it work to put the deadlock test
in the outer stored procedure? Will the deadlock victim kill only the
inner stored procedure and return error code 1205 to the outer stored
procedure? Or does it kill both the inner and outer stored
procedures?The transaction is rolled back and the entire back is aborted when you are
chosen as the deadlock victim
(http://www.sommarskog.se/error-handling-I.html). If you want to retry the
operation, you'll need to handle the error in your VB code.
Hope this helps.
Dan Guzman
SQL Server MVP
"William Lipp" <w.b.(MyLastNameHere)@.ieee.org> wrote in message
news:t6sv32d0vahsjme8olhjc90l7dnqu1a9gj@.
4ax.com...
> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?|||Try testing it.
A deadlock will terminate the connection so there's nothing that can be done
in the stored procedures.
Put the retry in the VB app.
I use an interface class to access the database so it's easy to put code
like this in all calls.
see
http://www.nigelrivett.net/VB/VB6DataAccessLayer.html
"William Lipp" wrote:
> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?
>|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
Up to SQL 2000 this is correct.
In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
procedure. Since you say "error check after every update statement",
it does not sound like TRY-CATCH, and thus that code is not really
working.
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|||On Sat, 15 Apr 2006 12:23:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>William Lipp writes:
> Erland Sommarskog writes
>In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
>procedure. Since you say "error check after every update statement",
>it does not sound like TRY-CATCH, and thus that code is not really
>working.
Yes, the existing code just checks @.@.error after the insert - I was
pretty sure that wasn't working. I had hoped that the outer stored
procedure could that method to detect deadlocks within the inner
stored procedure. I believe these responses say that won't work
either - that a deadlock victim in the inner stored procedure will
also kill the outer stored procedure, and the only place I can detect
the deadlock victim and reattempt is from the VB code that called the
outer procedure. Have I got that right?|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> Yes, the existing code just checks @.@.error after the insert - I was
> pretty sure that wasn't working. I had hoped that the outer stored
> procedure could that method to detect deadlocks within the inner
> stored procedure. I believe these responses say that won't work
> either - that a deadlock victim in the inner stored procedure will
> also kill the outer stored procedure, and the only place I can detect
> the deadlock victim and reattempt is from the VB code that called the
> outer procedure. Have I got that right?
Yes, for SQL 2000 your assumption is entirely accurate. (Well, there is a
trick you can play with linked servers, but it's fairly obscure.)
For SQL 2005, as I said, it is possible to catch the error in a stored
procedure with the new TRY-CATCH construct.
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
Subscribe to:
Posts (Atom)