CustomColumn

Hi All, How to define a column in sql2k with format of its value as
10,000,000
TIA"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>
Formatting is a front-end issue. You should probably create the column as a
BIGINT, or some type of Decimal.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi
create table #t
(
col1 int,
col2 as convert(varchar,cast(col1 as money),1)
)
insert into #t values (10000)
select * from #t
If it does not help you ,please lookup SUBSTRING system function in the BOL
"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>|||You don't. Formatting is controlled by your client application, not by
SQL Server.
If it's a numeric column then use one of the numeric datatypes.
David Portas
SQL Server MVP
--|||how about if you are exporting data to excel and want to populate in excel
in that format...avoiding writing format in excel...
TIA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128957032.919975.66170@.f14g2000cwb.googlegroups.com...
> You don't. Formatting is controlled by your client application, not by
> SQL Server.
> If it's a numeric column then use one of the numeric datatypes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
> how about if you are exporting data to excel and want to populate in excel
> in that format...avoiding writing format in excel...
Still a front-end issue.
Storing is not the same as displaying or migrating.
If you want to migrate it as something else, then you have multiple options.
You could create a view and convert it to just about any format that you
wish.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I am looking the convert format to the desired Column
type..(1,00,000)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
excel
>
> Still a front-end issue.
> Storing is not the same as displaying or migrating.
> If you want to migrate it as something else, then you have multiple
options.
> You could create a view and convert it to just about any format that you
> wish.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||how to convert a view to desired format?
Thanks a lot
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23sqAnObzFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks, I am looking the convert format to the desired Column
> type..(1,00,000)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> excel
> options.
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23iPvPQbzFHA.3660@.TK2MSFTNGP15.phx.gbl...
> how to convert a view to desired format?
> Thanks a lot
Take a look at the CONVERT function in SQL Server.
-- Create a table variable to hold our data.
DECLARE @.Foo TABLE (
x bigint
)
-- Load a value
INSERT @.Foo VALUES (10000000)
-- Convert BigInt to money datatype, then convert the money to a character
string and specify the "1" flag
-- so that commas are added in the appropriate place.
SELECT CONVERT(varchar(20), CONVERT(money, x), 1) FROM @.Foo
Rick Sawtell
MCT, MCSD, MCDBA

Custom Sync Objects

sql2k sp3
Can someone point out to me the steps to take to use
Custom Sync Objects? My Publisher and Subscriber have
different schemas which is the reason for they're use.
Obviously I need to take into account the Insert, and
Update Procs, as well as the Subscriber schema. I could
see this taking quite a while until I get into the groove
of how this all works.
TIA, ChrisR
Hi chris,
I have a slightly similar issue where I wanted to change the ins stored
procs for a number of the tables on the subscriber. I ended up putting all
my alter proc commands in a PostSnapshot script and specifying that in the
sp_addpublication procedure. This runs after the snapshot so all the data
should be in the tables and you could run alter table.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> sql2k sp3
> Can someone point out to me the steps to take to use
> Custom Sync Objects? My Publisher and Subscriber have
> different schemas which is the reason for they're use.
> Obviously I need to take into account the Insert, and
> Update Procs, as well as the Subscriber schema. I could
> see this taking quite a while until I get into the groove
> of how this all works.
> TIA, ChrisR
|||Thanks Mary!!!

>--Original Message--
>Hi chris,
>I have a slightly similar issue where I wanted to change
the ins stored
>procs for a number of the tables on the subscriber. I
ended up putting all
>my alter proc commands in a PostSnapshot script and
specifying that in the
>sp_addpublication procedure. This runs after the snapshot
so all the data
>should be in the tables and you could run alter table.
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
groove
>
>.
>
|||You use custom sync objects when you want to bcp data for the snapshot which
will be sent to the Subscriber.
The custom sync object can send different data than what is in the
underlying object, or different schema, or a combination of the two.
To get this to work you need to
1) create the custom sync object - a view
2) tell SQL Server to use this custom sync object with the @.sync_object
parameter of sp_addarticle.
If you are replicating to a table with a different schema where the object
on the subscriber has more columns than the object on the publisher you have
to ask yourself this question. How am I going to fill in these columns as
the log reader will only fill in values for the columns which are present in
the underlying object on the Publisher?
If you are putting defaults on the additional columns on this object on the
Subscriber, its not a problem, but if you aren't you have to trick the log
reader into reading these additional columns. Most often you have to
replicate from a different source object, or replicate tables containing
these additional values, and then in your custom stored procedure do a join
to fill them in.
Here is an example:
CREATE VIEW Authors_View
AS
SELECT authors.au_id, titles.title_id, au_lname,
au_fname, phone, address, state, zip, contract, city,
title
FROM authors, titles, titleauthor
WHERE authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
GO
sp_addarticle @.publication = 'test',
@.article = 'AuthorTitle',
@.source_object = 'AuthorTitle',
@.destination_table = 'AuthorTitle',
-Specifying that we are using a custom sync object
@.type = 'logbased manualview',
-Our sync object
@.sync_object='Authors_View',
-Our custom script
@.creation_script = 'c:\temp\AuthorTitle.sql',
@.pre_creation_cmd = 'delete',
-Specifying we want to auto generate our stored
procedures
@.schema_option = 0x02,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_AuthorTitle',
@.del_cmd = 'CALL sp_MSdel_AuthorTitle ',
@.upd_cmd = 'MCALL sp_MSupd_AuthorTitle '
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:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> sql2k sp3
> Can someone point out to me the steps to take to use
> Custom Sync Objects? My Publisher and Subscriber have
> different schemas which is the reason for they're use.
> Obviously I need to take into account the Insert, and
> Update Procs, as well as the Subscriber schema. I could
> see this taking quite a while until I get into the groove
> of how this all works.
> TIA, ChrisR
|||That example wasn't the best - try this one.
create database CustomSyncObject
GO
create database CustomSyncObjectSub
GO
use CustomSyncObject
go
sp_dboption 'CustomSyncObject','published', 'true'
go
create table test(pk int not null primary key identity(1,1),
charcol1 char(20),
charcol2 char(20),
charcol3 char(20))
go
declare @.counter int
set @.counter=0
while @.counter<100
begin
insert into test (charcol1, charcol2, charcol3) values(getdate(),
System_user,@.counter)
select @.counter=@.counter+1
end
create View TestView
as
select charcol1, charcol2, charcol4=convert(char(20),'') From test
GO
sp_addpublication 'Custom', @.status='active'
GO
sp_addpublication_snapshot 'custom'
GO
sp_addarticle @.publication = 'Custom',
@.article = 'Custom',
@.source_object = 'test',
@.destination_table = 'TestWithDifferentSchema',
@.type = 'logbased manualview',
@.sync_object='TestView',
@.creation_script = 'c:\temp\TestWithDifferentSchema.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_CustomProc',
@.del_cmd = 'CALL sp_MSdel_CustomProc',
@.upd_cmd = 'MCALL sp_MSupd_CustomProc'
GO
sp_addsubscription 'Custom', 'Custom', @.@.Servername, 'CustomSyncObjectSub'
GO
Use CustomSyncObjectSub
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
go
create procedure [sp_MSins_CustomProc] @.c1 int,@.c2 char(20),@.c3 char(20),@.c4
char(20)
AS
BEGIN
insert into [TestWithDifferentSchema](
[pk], [charcol1], [charcol2]
)
values (
@.c1, @.c2, @.c3
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
go
create procedure [sp_MSdel_CustomProc] @.pkc1 int
as
delete [TestWithDifferentSchema]
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
go
create procedure [sp_MSupd_CustomProc]
@.c1 int,@.c2 char(20),@.c3 char(20),@.c4 char(20),@.pkc1 int
as
if @.c1 = @.pkc1
begin
update [TestWithDifferentSchema] set [charcol1] = @.c2,[charcol2] = @.c3
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [test] set [pk] = @.c1,[charcol1] = @.c2,[charcol2] = @.c3
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
Use CustomSyncObject
sp_repladdcolumn 'test','chrisRSpecial','char(20)'
insert into test (charcol1, charcol2,charcol3) values('test','test','test')
--before reinitializing we have to
--update custom sync object for newly added columns
--update replication stored procedurs
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
> You use custom sync objects when you want to bcp data for the snapshot
which
> will be sent to the Subscriber.
> The custom sync object can send different data than what is in the
> underlying object, or different schema, or a combination of the two.
> To get this to work you need to
> 1) create the custom sync object - a view
> 2) tell SQL Server to use this custom sync object with the @.sync_object
> parameter of sp_addarticle.
> If you are replicating to a table with a different schema where the object
> on the subscriber has more columns than the object on the publisher you
have
> to ask yourself this question. How am I going to fill in these columns as
> the log reader will only fill in values for the columns which are present
in
> the underlying object on the Publisher?
> If you are putting defaults on the additional columns on this object on
the
> Subscriber, its not a problem, but if you aren't you have to trick the log
> reader into reading these additional columns. Most often you have to
> replicate from a different source object, or replicate tables containing
> these additional values, and then in your custom stored procedure do a
join
> to fill them in.
> Here is an example:
> CREATE VIEW Authors_View
> AS
> SELECT authors.au_id, titles.title_id, au_lname,
> au_fname, phone, address, state, zip, contract, city,
> title
> FROM authors, titles, titleauthor
> WHERE authors.au_id = titleauthor.au_id
> AND titles.title_id = titleauthor.title_id
> GO
> sp_addarticle @.publication = 'test',
> @.article = 'AuthorTitle',
> @.source_object = 'AuthorTitle',
> @.destination_table = 'AuthorTitle',
> -Specifying that we are using a custom sync object
> @.type = 'logbased manualview',
> -Our sync object
> @.sync_object='Authors_View',
> -Our custom script
> @.creation_script = 'c:\temp\AuthorTitle.sql',
> @.pre_creation_cmd = 'delete',
> -Specifying we want to auto generate our stored
> procedures
> @.schema_option = 0x02,
> @.status = 8,
> @.ins_cmd = 'CALL sp_MSins_AuthorTitle',
> @.del_cmd = 'CALL sp_MSdel_AuthorTitle ',
> @.upd_cmd = 'MCALL sp_MSupd_AuthorTitle '
> 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:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
>
|||Thank you so much. As it turns out Ive been putting out a
day long fire and havent even tried any of it. Theres
always tomorrow. Thanks again.

>--Original Message--
>That example wasn't the best - try this one.
>create database CustomSyncObject
>GO
>create database CustomSyncObjectSub
>GO
>use CustomSyncObject
>go
>sp_dboption 'CustomSyncObject','published', 'true'
>go
>create table test(pk int not null primary key identity
(1,1),
>charcol1 char(20),
>charcol2 char(20),
>charcol3 char(20))
>go
>declare @.counter int
>set @.counter=0
>while @.counter<100
>begin
>insert into test (charcol1, charcol2, charcol3) values
(getdate(),
>System_user,@.counter)
>select @.counter=@.counter+1
>end
>create View TestView
>as
>select charcol1, charcol2, charcol4=convert(char(20),'')
From test
>GO
>sp_addpublication 'Custom', @.status='active'
>GO
>sp_addpublication_snapshot 'custom'
>GO
>sp_addarticle @.publication = 'Custom',
>@.article = 'Custom',
>@.source_object = 'test',
>@.destination_table = 'TestWithDifferentSchema',
>@.type = 'logbased manualview',
>@.sync_object='TestView',
>@.creation_script = 'c:\temp\TestWithDifferentSchema.sql',
>@.pre_creation_cmd = 'delete',
>@.schema_option = 0x0,
>@.status = 8,
>@.ins_cmd = 'CALL sp_MSins_CustomProc',
>@.del_cmd = 'CALL sp_MSdel_CustomProc',
>@.upd_cmd = 'MCALL sp_MSupd_CustomProc'
>GO
>sp_addsubscription 'Custom', 'Custom',
@.@.Servername, 'CustomSyncObjectSub'
>GO
>Use CustomSyncObjectSub
>GO
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
>go
>create procedure [sp_MSins_CustomProc] @.c1 int,@.c2 char
(20),@.c3 char(20),@.c4
>char(20)
>AS
>BEGIN
>insert into [TestWithDifferentSchema](
>[pk], [charcol1], [charcol2]
> )
>values (
>@.c1, @.c2, @.c3
> )
>END
>go
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
>go
>create procedure [sp_MSdel_CustomProc] @.pkc1 int
>as
>delete [TestWithDifferentSchema]
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>GO
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
>go
>create procedure [sp_MSupd_CustomProc]
> @.c1 int,@.c2 char(20),@.c3 char(20),@.c4 char(20),@.pkc1 int
>as
>if @.c1 = @.pkc1
>begin
>update [TestWithDifferentSchema] set [charcol1] = @.c2,
[charcol2] = @.c3
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>end
>else
>begin
>update [test] set [pk] = @.c1,[charcol1] = @.c2,[charcol2]
= @.c3
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>end
>GO
>Use CustomSyncObject
>sp_repladdcolumn 'test','chrisRSpecial','char(20)'
>insert into test (charcol1, charcol2,charcol3) values
('test','test','test')[vbcol=seagreen]
>--before reinitializing we have to
>--update custom sync object for newly added columns
>--update replication stored procedurs
>
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Hilary Cotter" <hilaryk@.att.net> wrote in message
>news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
for the snapshot[vbcol=seagreen]
>which
what is in the[vbcol=seagreen]
combination of the two.[vbcol=seagreen]
the @.sync_object[vbcol=seagreen]
schema where the object[vbcol=seagreen]
the publisher you[vbcol=seagreen]
>have
in these columns as[vbcol=seagreen]
which are present[vbcol=seagreen]
>in
on this object on[vbcol=seagreen]
>the
have to trick the log[vbcol=seagreen]
often you have to[vbcol=seagreen]
tables containing[vbcol=seagreen]
procedure do a[vbcol=seagreen]
>join
message[vbcol=seagreen]
could[vbcol=seagreen]
groove
>
>.
>