adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

adding a new column to a table involved in merge replication

Hi everyone,
I would like to know if there is some way to add a new column to an
existing table in a database published via merge replication that does
not require reinitialization of the articles.
Thanks.
Aramid
Aramid,
have a look at sp_repladdcolumn in BOL. This can also be done through the
GUI in Enterprise manager. In SQL Server 2005 the same effect is achieved
directly using Alter Table statements (provided the subscriber is also SQL
2005).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a new article to Transactional replication

Hi,
I added a new article to an already existing publication via EM,
and reintialized it. When the snapshot agent ran the change was
replicated, but the snapshot agent took the snapshot of all the
articles in the publication about 85 of them, instead of just
replicating the change.
IS there a way to reintialize an article in the publication, so
snapshot agent would only snap that one article instead of the whole
publication?
Does this also apply to adding a new column or dropping a column from a
publication?
Thanks for your help.
GG
for sql 2000 replicating to sql 2000 subscribers, only the article should be
replicated. I am not sure what happened in your case.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GG" <gdabbara@.gmail.com> wrote in message
news:1117574970.901699.85980@.f14g2000cwb.googlegro ups.com...
> Hi,
> I added a new article to an already existing publication via EM,
> and reintialized it. When the snapshot agent ran the change was
> replicated, but the snapshot agent took the snapshot of all the
> articles in the publication about 85 of them, instead of just
> replicating the change.
> IS there a way to reintialize an article in the publication, so
> snapshot agent would only snap that one article instead of the whole
> publication?
> Does this also apply to adding a new column or dropping a column from a
> publication?
>
> Thanks for your help.
> GG
>
|||You mention that you reinitialized it - this is the reason why all articles
were created by the snapshot agent. After adding the article, you just need
to run the snapshot agent and then the distribution agent to get the single
article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your responses. I will try just starting the snapshot agent
instead of reintializing the whole publication. I was under the
impression that snapshot agent doesn't know about the change unless I
reintialize.
Thanks
GG
|||Adding an article is normally taken care of when running the snapshot. There
are some exceptions - if the initial publication was a nosync one, then the
snapshot of the new article won't generate anything by default. However
assuming this is niot the case you'll be fine.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a DTS package

Can a DTS package be added to a server via the command line using OSQL?
Thanks in advance
JohnI don't think this is possible.
Anithsql

add users in sql server using batch file

I am trying to add user via batch file.
Can anyone help me with a command?
Thanks,
Culam
You can use osql to execute your sql statements. sp_addlogin is used to add a
sql login, while sp_grantLogin is used to add a windows login.
sp_grantdbaccess can be used to add a user to a database. All are described
with examples in BOL.
"culam" wrote:

> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam
|||Are you using these?
sp_adduser
sp_addlogin
sp_grantdbaccess
use osql.exe to issue the appropriate commands within the appropriate
database.
It is probably best to create a script (that runs) that you can execute
within Query Analyzer. When you have it working then try using osql.exe.
Keith
"culam" <culam@.discussions.microsoft.com> wrote in message
news:872F105E-90B9-46E6-A8B7-DB5E1A0C9A2D@.microsoft.com...
> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam

add users in sql server using batch file

I am trying to add user via batch file.
Can anyone help me with a command?
Thanks,
CulamYou can use osql to execute your sql statements. sp_addlogin is used to add
a
sql login, while sp_grantLogin is used to add a windows login.
sp_grantdbaccess can be used to add a user to a database. All are described
with examples in BOL.
"culam" wrote:

> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam|||Are you using these?
sp_adduser
sp_addlogin
sp_grantdbaccess
use osql.exe to issue the appropriate commands within the appropriate
database.
It is probably best to create a script (that runs) that you can execute
within Query Analyzer. When you have it working then try using osql.exe.
Keith
"culam" <culam@.discussions.microsoft.com> wrote in message
news:872F105E-90B9-46E6-A8B7-DB5E1A0C9A2D@.microsoft.com...
> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam

add total row to file via script component

Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like

ABC, 3

DEF, 5

FGH, 6

I have a variable in script component holding the running total I now want to add that to destination as a row e.g.

ABC, 3

DEF, 5

FGH, 6

Total, 14

This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.

Thanks

You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.

See this topic in Books Online:

"Creating an Asynchronous Transformation with the Script Component"

|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation worked

add total row to file via script component

Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like

ABC, 3

DEF, 5

FGH, 6

I have a variable in script component holding the running total I now want to add that to destination as a row e.g.

ABC, 3

DEF, 5

FGH, 6

Total, 14

This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.

Thanks

You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.

See this topic in Books Online:

"Creating an Asynchronous Transformation with the Script Component"

|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation worked

Add runtime rows to sql express database

Hi All,

I have a c# project with an sql express database which is bound to a datagridview via a dataset.

I would like to allow the users to import data into the database from a text file.

How does one go about adding rows and filling in the column data programatically? Do I add the to datagridview? the database? the bindingsource?

Thank you,

Paul

hi Paul,

it depends on your requirements.. you can direclty load the database via BULK INSERT statement like in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=723808&SiteID=1 ....
or you can read the flat file and parse it, loading a visual UI to be presented to the user for further manipulation, and finally load the destination database... if this is the case I'd just add the "imported" rows to the underlying DataView of the UI...

regards

|||

Thanks for the help and the links. I'll post back what I did.

Paul

|||

Wrote a class to convert the txt to xml but

it doens't work on a unbound grid. Oh well nice try...

Maybe I could unbin, set the data, and then

rebind. Looking into it now.

//this works fine on unbound grid.

dataSet1.ReadXml(filePath);

dataGridView1.DataSource = dataSet1;

dataGridView1.DataMember = "student";

Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

Add an alert/warning via report Builder

Hi !
Is it possible to add an alert/warning via reportBuilder ?
An alert is for example a value which is superior of a nominal value for example...
I saw in a demo, it is possible to have it by creating a function, but this way is for the developper, if the user wants to add an alert himself ? how can he do ?
and maybe how can the developper help him ?

thank you for your answerthank you for your answer.

Excuse me, I think I didn't good explain my need.
I want to display a grid with duration values in the last colum (for example), if one of my duration is superior of 1 second, I want the cell (where the value is ) become red (background color).
I saw in a demo, (webCast in french) it is possible to add a function in the code tab or the report to select the color of the cell is the value is superior, but I want to know if somethings similaire exist for a simple user via report Builder ?
I think no, but I want to be sure....

|||Don′t think that this is possible, never saw such an option.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Alineo wrote:

thank you for your answer.

Excuse me, I think I didn't good explain my need.
I want to display a grid with duration values in the last colum (for example), if one of my duration is superior of 1 second, I want the cell (where the value is ) become red (background color).
I saw in a demo, (webCast in french) it is possible to add a function in the code tab or the report to select the color of the cell is the value is superior, but I want to know if somethings similaire exist for a simple user via report Builder ?
I think no, but I want to be sure....

Hi I never did the duration before, but if you want to change the color of the cell, you can use an IIF statement in the background color property.

=IIF("Condition", "Red", "White")

Hope this helps

Bernard

|||Thank you for your help, I know what you explain but I ask to know, if it possible for a simple user via report Builder and not report designer...
But apparement it is not possible
|||

Alineo wrote:

Hi !
Is it possible to add an alert/warning via reportBuilder ?
An alert is for example a value which is superior of a nominal value for example...
I saw in a demo, it is possible to have it by creating a function, but this way is for the developper, if the user wants to add an alert himself ? how can he do ?
and maybe how can the developper help him ?

thank you for your answer

HI alineo,

It sounds like you are having the same problem or concept as I do. I am not sure whether this is the same problem as I am having, but here is the post that started this discussion. hope it might shed some light or else maybe someone can provide some enlightenment to us.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1450478&SiteID=1

Is there a way where you can show where this is working ? the demo you were speaking of?

thanks

Hope this helps.

Bernard

|||No, there is no way to place custom error messages in the user interface.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Add admin user to reporting services

Hi, we have a webpage which makes a call to the reporting services
function GetSystemPermissions via C# to get permissions for our
intranet.
My question is how to I add an admin user to this group? Is it done
via Active Directory and if so what rights does the person need?
We are running MSSQL 2005 and Windows Server 2003.
Thanks for any help
Regards
MarkusReporting Services by default is integrated with Windows integrated
security. But, you have to map a user or group to a role in RS. By default
any member of the local adminstrators group is an admin in RS.
What I do is add appropriate users to the local adminstrators group on the
server running RS for adminstrative rights. But you could just assign
another group to the appropriate role in RS. For browsing I add a local
group and then to that local group I add individual users and domain groups.
That local group is then assigned to the appropriate role.
Read up on roles in RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<MarkusJNZ@.gmail.com> wrote in message
news:1183531995.432312.93700@.m37g2000prh.googlegroups.com...
> Hi, we have a webpage which makes a call to the reporting services
> function GetSystemPermissions via C# to get permissions for our
> intranet.
> My question is how to I add an admin user to this group? Is it done
> via Active Directory and if so what rights does the person need?
> We are running MSSQL 2005 and Windows Server 2003.
> Thanks for any help
> Regards
> Markus
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
Woody
Chris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>
|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)[vbcol=seagreen]
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name='''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> >
> > Hello All, I've got a script that will Create a Database and install the
> > tables onto a SQL server.
> > what code is used to add a user/username and password to allow the user
to
> > login?
> > I am aware that I can do this from the SQL Server, However, I've been
> > asked
> > to do it in the code.
> > I don't want to add a user to the network - just into 1 database. I
> > think
> > this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> >
> > I've looked at CreateUser. However when I try to run this I get an error
> > saying, "Could not find stored procedure 'createuser'"
> >
> >
> > Please help
> > TIA
> > Woody
> >
> >
>

Add a default value to an existing column

How can I add a default value to an existing column via T-SQL? I tried the
following but not successful:
Alter Table MyTbl Alter Column MyCol Int Default 0.8
TIANote that it will not update previously stored null values
Madhivanan|||The statement fails even if the table is empty (no record). I get the error
message:
"Incorrect syntax near the keyword 'Default'."
The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default 0.8
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
> Note that it will not update previously stored null values
> Madhivanan
>|||Hi
create table #test
(
col decimal(18,3)
)
alter table #test add constraint my_def default 18.3 for col
"krygim" <krygim@.hotmail.com> wrote in message
news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> The statement fails even if the table is empty (no record). I get the
error
> message:
> "Incorrect syntax near the keyword 'Default'."
> The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default
0.8
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
>|||It works. Thanks Uri.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kQoOytKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi
> create table #test
> (
> col decimal(18,3)
> )
> alter table #test add constraint my_def default 18.3 for col
>
>
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> error
> 0.8
>

Add a column to published table without replicating to subscriber

I would like to add a column to a published table but not have that column replicated to subscribers. I can accomplish this via the UI by adding the column and then unchecking it. This adds the column to the publisher table but does not replicate it to the subscriber.

I am looking for a programmatic method to add a column to the base table and unmark it for replication.

Thanks for your assistance.

Here is a TSQL example (I suppose you mean SQL Server 2005):

1. First turn replication_ddl property off for your publication

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '0'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

2. Execute DDL statement to add a new column:

alter table authors add newcol8 int not null default 0

3. If you want to turn schema/DDL replication on for subsequent add columns, then turn it back on:

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '1'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another option you can have is vertical partitioning with which you can filter out which columns you want to publisher and which ones you do not want to.

When you mention that you could achieve this by doing in the UI, this is what is internally happening. The table is enabled for vertical partitioning and the newly added column is removed from the partition when you uncheck this column

Look in BOL for:sp_mergearticlecolumn, sp_articlecolumn, vertical_partition parameter of sp_addarticle/sp_addmergearticle

Activity Monitor - host entries blank for sql login ?

The activity monitor is not showing entries for hosts which connect via sql logins (odbc), is this normal? Where can I find a list of protocols in which the hostname/ip can be picked up. We're on SQL 2005 SP1/Build 2153

Thanks

The hostname should show up if the user is connecting from a Windows box using ODBC or OLE-DB. If you are using a JDBC driver or comming from a unix machine this field is typically blank. This can be adjusted by adding the "Workstation ID" paramater to the connection string and setting it to equal the client machines host name. This object probably doesn't exist on all drivers. The string name may also be slightly different so some trial and error may be required.|||

The connections in question are from Windows OS (2000/XP) machines, no JDBC or UNIX involved......