Question about sp_addscriptexec

Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
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
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:

> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> 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
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
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
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. Th
e
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:

> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP
4.
> I have to install a new application which will reside on this sql server.
The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. The
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:
> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
> I have to install a new application which will reside on this sql server. The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>sql

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. The
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.
Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:

> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
> I have to install a new application which will reside on this sql server. The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>
|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>

Question about AWE.

SQL2K SP3
From reading BOL, to enable AWE, I should simply need to:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', MyMax
RECONFIGURE
GO
And then start the SQL services. But a coworker of mine seems to think that
there's more to it then that. That SQL won't really be using AWE until we do
this something else. The best part is he's not sure what that something else
is. I think there's nothing else, but wanted to verify if I could.
TIA, ChrisRHi,Chris
http://www.sql-server-performance.com/awe_memory.asp
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:312ACADD-F513-4D8F-B5D8-F69C2D2E9138@.microsoft.com...
> SQL2K SP3
> From reading BOL, to enable AWE, I should simply need to:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', MyMax
> RECONFIGURE
> GO
> And then start the SQL services. But a coworker of mine seems to think
> that
> there's more to it then that. That SQL won't really be using AWE until we
> do
> this something else. The best part is he's not sure what that something
> else
> is. I think there's nothing else, but wanted to verify if I could.
> TIA, ChrisR|||And also http://www.sqlservercentral.com/columnists/jsack/aweadventures.asp
See Lock Pages in memory priv.
Don't forget that if you install Sql2000sp4 you must get a hotfix otherwise
SQL will use half the memory (bug in the base SP4)
Paul
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eW7yW6TPGHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi,Chris
> http://www.sql-server-performance.com/awe_memory.asp
>
>
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:312ACADD-F513-4D8F-B5D8-F69C2D2E9138@.microsoft.com...
>> SQL2K SP3
>> From reading BOL, to enable AWE, I should simply need to:
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', MyMax
>> RECONFIGURE
>> GO
>> And then start the SQL services. But a coworker of mine seems to think
>> that
>> there's more to it then that. That SQL won't really be using AWE until we
>> do
>> this something else. The best part is he's not sure what that something
>> else
>> is. I think there's nothing else, but wanted to verify if I could.
>> TIA, ChrisR
>