Collation Error

When I change db collation sql says
"You cannot change database collation"
"The object XXXX is dependent on database collation.". Is there any way to
change database collation? (without creating new database from script)
thank you
Hi
Have you tried changing the column colations before changing the database
collation?
John
"Cenk FIRAT" wrote:

> When I change db collation sql says
> "You cannot change database collation"
> "The object XXXX is dependent on database collation.". Is there any way to
> change database collation? (without creating new database from script)
> thank you
|||All columns collation is <database default> and our database file is
orginally sql 2000 file but use in sql 2005. because of that our database
collations is seems "Compatibility_129_41f_0"..
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you tried changing the column colations before changing the database
> collation?
> John
> "Cenk FIRAT" wrote:
|||Hi
If these are not columns that are causing the issue then you should be able
to script them, drop them, change the database collation and then re-create
them.
John
"Cenk FIRAT" wrote:
[vbcol=seagreen]
> All columns collation is <database default> and our database file is
> orginally sql 2000 file but use in sql 2005. because of that our database
> collations is seems "Compatibility_129_41f_0"..
> "John Bell" wrote:

Collation Error

When I change db collation sql says
"You cannot change database collation"
"The object XXXX is dependent on database collation.". Is there any way to
change database collation? (without creating new database from script)
thank youHi
Have you tried changing the column colations before changing the database
collation?
John
"Cenk FIRAT" wrote:
> When I change db collation sql says
> "You cannot change database collation"
> "The object XXXX is dependent on database collation.". Is there any way to
> change database collation? (without creating new database from script)
> thank you|||All columns collation is <database default> and our database file is
orginally sql 2000 file but use in sql 2005. because of that our database
collations is seems "Compatibility_129_41f_0"..
"John Bell" wrote:
> Hi
> Have you tried changing the column colations before changing the database
> collation?
> John
> "Cenk FIRAT" wrote:
> > When I change db collation sql says
> > "You cannot change database collation"
> > "The object XXXX is dependent on database collation.". Is there any way to
> > change database collation? (without creating new database from script)
> >
> > thank you|||Hi
If these are not columns that are causing the issue then you should be able
to script them, drop them, change the database collation and then re-create
them.
John
"Cenk FIRAT" wrote:
> All columns collation is <database default> and our database file is
> orginally sql 2000 file but use in sql 2005. because of that our database
> collations is seems "Compatibility_129_41f_0"..
> "John Bell" wrote:
> > Hi
> >
> > Have you tried changing the column colations before changing the database
> > collation?
> >
> > John
> >
> > "Cenk FIRAT" wrote:
> >
> > > When I change db collation sql says
> > > "You cannot change database collation"
> > > "The object XXXX is dependent on database collation.". Is there any way to
> > > change database collation? (without creating new database from script)
> > >
> > > thank yousqlsql

Collation Error

When I change db collation sql says
"You cannot change database collation"
"The object XXXX is dependent on database collation.". Is there any way to
change database collation? (without creating new database from script)
thank youHi
Have you tried changing the column colations before changing the database
collation?
John
"Cenk FIRAT" wrote:

> When I change db collation sql says
> "You cannot change database collation"
> "The object XXXX is dependent on database collation.". Is there any way to
> change database collation? (without creating new database from script)
> thank you|||All columns collation is <database default> and our database file is
orginally sql 2000 file but use in sql 2005. because of that our database
collations is seems "Compatibility_129_41f_0"..
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you tried changing the column colations before changing the database
> collation?
> John
> "Cenk FIRAT" wrote:
>|||Hi
If these are not columns that are causing the issue then you should be able
to script them, drop them, change the database collation and then re-create
them.
John
"Cenk FIRAT" wrote:
[vbcol=seagreen]
> All columns collation is <database default> and our database file is
> orginally sql 2000 file but use in sql 2005. because of that our database
> collations is seems "Compatibility_129_41f_0"..
> "John Bell" wrote:
>

Code Initialization Problem

I try to initialize my object in OnInit, however, there is always an erroe saying
"[BC30469] Reference to a non-shared member requires an object reference."
My code is very simple and it is like this :
Dim MyObj as MyClass
Protected Overrides Sub OnInit()
MyObj = New MyClass
MyObj.Init(User!Language)
End Sub
The error part is when I want to pass in User!language to my method. It seems whenever I access
a global collection object, the system will give me that error. I've tried acess User,Parameters, Globals
all give me the same error when compile. I've even tried the following and it still give me error. so it
is not realted to my class.
Protected Overrides Sub OnInit()
Dim Test as object
Test = User!Language
End Sub
According to Book online, I should be able to access User, Parameter and Globals in Oninit.
Anyone has a clue on what is happening ?
Thanks
EdchyOne of the developers took a look at this and found that the documentation
is incomplete.
You need to access global collections from the Report object, like this:
Test = Report.User!Language
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Edchy Tsoi" <edchytsoi@.techland.com.hk> wrote in message
news:uALwmlhaEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I try to initialize my object in OnInit, however, there is always an erroe
saying
> "[BC30469] Reference to a non-shared member requires an object reference."
> My code is very simple and it is like this :
> Dim MyObj as MyClass
> Protected Overrides Sub OnInit()
> MyObj = New MyClass
> MyObj.Init(User!Language)
> End Sub
> The error part is when I want to pass in User!language to my method. It
seems whenever I access
> a global collection object, the system will give me that error. I've tried
acess User,Parameters, Globals
> all give me the same error when compile. I've even tried the following
and it still give me error. so it
> is not realted to my class.
> Protected Overrides Sub OnInit()
> Dim Test as object
> Test = User!Language
> End Sub
> According to Book online, I should be able to access User, Parameter and
Globals in Oninit.
> Anyone has a clue on what is happening ?
> Thanks
> Edchy
>

Clustered Indexed View or Custom Sync Object?

sql2k sp3
If you had more columns on Subscriber than Publisher,
would you be more likely to use Clustered Indexed Views or
Custom Sync Objects to implement 1 way, Transactional
Replication? Pros and Cons for each?
TIA, ChrisR
ChrisR,
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is increased overhead on
the publisher to maintain the materialized data. The upside is that there is
increased transparency so far as maintenance of replication is concerned.
Regards,
Paul Ibison
|||the problem is that for Chris Indexed Views won't support sp_repladdcolumn
and sp_repldropcolumn.
I am mystified as to why what works for me on my machine doesn't work on
ChrisRs. I am revisiting this script and I am hoping ChrisR and others can
test it. There may be a bug somewhere here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ChrisR,
> I personally would prefer to use indexed views.
> I suppose simplistically the downside is that there is increased overhead
on
> the publisher to maintain the materialized data. The upside is that there
is
> increased transparency so far as maintenance of replication is concerned.
> Regards,
> Paul Ibison
>
|||I thought it couldnt be done. Thats what I got from the
replies to my post:
sp_repladdcolumn on Indexed View
08/04/04 4:24 pm.
Did I misunderstand?

>--Original Message--
>the problem is that for Chris Indexed Views won't support
sp_repladdcolumn
>and sp_repldropcolumn.
>I am mystified as to why what works for me on my machine
doesn't work on
>ChrisRs. I am revisiting this script and I am hoping
ChrisR and others can[vbcol=seagreen]
>test it. There may be a bug somewhere here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
increased overhead[vbcol=seagreen]
>on
upside is that there[vbcol=seagreen]
>is
replication is concerned.
>
>.
>
|||Sorry for the confusion.
You can't use sp_repladdcolumn or sp_repldropcolumn on an indexed view which
you are publishing for replication.
The Custom Sync object works for me.
Here is the script that works for me.
Here is my TransDTL.SQL script.
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10),
[TranCode] [char] (4),
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10),
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23),
[MerchName] [varchar] (25),
[City] [varchar] (15),
[State] [varchar] (3),
[RejectReason] [varchar] (15),
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10),
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4)
) ON [PRIMARY]
GO
--this is my replication script
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
go
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--creating subscription
sp_addsubscription 'ChrisR','all', @.@.ServerName, 'ChrisRSub'
GO
--startup snapshot agent
DECLARE @.string varchar(200)
SET @.string = 'exec msdb.dbo.sp_start_job @.job_name =
'SELECT @.string = @.string + char(39) +
msdb.dbo.sysjobs.name + char(39) FROM
msdb.dbo.sysjobs,ChrisR.dbo.syspublications
WHERE ChrisR.dbo.syspublications.name = 'ChrisR'
AND ChrisR.dbo.syspublications.snapshot_jobid =
msdb.dbo.sysjobs.job_id
exec (@.string)
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=1
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=5
go
select * from transdtl where transdtlkey=5
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=5
GO
use chrisR
GO
insert into
transdtl(TransDtlKey,CustomerKey,SerialNbr,TranCod e,TransDate,TransDateShort
,TransDateMonth,TransDateYear,TransAmt,RefNbr,Merc hName,City,State,RejectRea
son,PostDate,PostDateShort,PostDateMonth,PostDateY ear,CreateDate,MerchSIC)
select
TransDtlKey+100,CustomerKey,SerialNbr,TranCode,Tra nsDate,TransDateShort,Tran
sDateMonth,TransDateYear,TransAmt,RefNbr,MerchName ,City,State,RejectReason,P
ostDate,PostDateShort,PostDateMonth,PostDateYear,C reateDate,MerchSIC from
transdtl where transdtlkey=5
go
select * from transdtl where transdtlkey=105
use chrisRSUB
GO
select * from transdtl where transdtlkey=105
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2001c486c6$432bff60$a501280a@.phx.gbl...[vbcol=seagreen]
> I thought it couldnt be done. Thats what I got from the
> replies to my post:
> sp_repladdcolumn on Indexed View
> 08/04/04 4:24 pm.
> Did I misunderstand?
>
>
> sp_repladdcolumn
> doesn't work on
> ChrisR and others can
> increased overhead
> upside is that there
> replication is concerned.