We are using SQL 2K with sp4 and push subscription.
If I add a trigger or a Check contraint (or other contraints) for a column
in a table that is already published in the publishing database, do or don't
I need to do anything in the subscriber database? Please explain the
consequence if I do or don't do anything in the subscriber database.
Wing
That depends on what you are trying to do. If you don't use the check
constraint and there could be DML occurring on the subscriber this check
constraint will not be enforced as it is not there.
Regarding the trigger. Do you need it fired on the subscriber as well? If
the trigger cause DML on tables which are also being replication the DML
will be replicated there as well. If not, you might want to put and fire the
trigger on the subscriber as long as all objects are there as well and the
required data is.
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
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:9F33E6B3-86D1-428E-8F9B-05F3707AEFB7@.microsoft.com...
> We are using SQL 2K with sp4 and push subscription.
> If I add a trigger or a Check contraint (or other contraints) for a column
> in a table that is already published in the publishing database, do or
> don't
> I need to do anything in the subscriber database? Please explain the
> consequence if I do or don't do anything in the subscriber database.
> Wing
|||Ok, I guess I have two scenerios.
1 Say I don't need any check contraints or triggers happening on the
subscriber server, does it mean I don't need to do anything to the subscriber
table(s). Even thought the table between the publisher and subscriber is
defferent because of the trigger and contraints, it won't cause any problem.
Am I right?
2. Say if I do want the check contraints and triggers happening on teh
subscriber server, then I will have to manually create them plus any
depending objects in the subscriber server. This is a manual process unless
I reinitialize and do a snap shot again. Am I right?
Another question too, is there a setting somewhere in replication in EM or
using sp_ stored procedure to replicate triggers or contraints?
"Wingman" wrote:
> We are using SQL 2K with sp4 and push subscription.
> If I add a trigger or a Check contraint (or other contraints) for a column
> in a table that is already published in the publishing database, do or don't
> I need to do anything in the subscriber database? Please explain the
> consequence if I do or don't do anything in the subscriber database.
> Wing
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
Adding new node to SQL SP4 cluster
Hi all,
I'm trying to add a new node to our SQL Server 2000 SP4 cluster. Cluster
node is Windows 2003 SP1. I can add the node to the SQL cluster, but when I
try to add SP4 to the new node, I get an error message when I enter in the
service password for the instance.
Error message : Setup cannot verify/grant logon as service right to the SQL
Server service logon account: 5000 (0x1388)
However, this error didn't come up when I was adding the node to the SQL
cluster and it prompted me for the same password. We're running an
active/active configuration and I can't do anything with the other instance
(the default one) as it says that this instance is still waiting for the
service pack!
Any ideas anyone? Thanks in advance
Dave
Dave Wall
Dave,
It looks like a permission issue. Does the Windows domain a/c that you are using the setup have local admin privileges on both the cluster nodes? Try running the setup using the virtual SQL Server service a/c or the
cluster service a/c ?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Dave,
Are you using the NEED_SP_KEY in the registry. If yes, then remove this key and re-run SP4 from the original node (i.e the node that is already on SP4 and owns the SQL resources) and see if this helps?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thanks Uttam,
removed the reg key and it all went through without any problems
Dave
Dave Wall
"Uttam Parui[MS]" wrote:
> Dave,
> Are you using the NEED_SP_KEY in the registry. If yes, then remove this key and re-run SP4 from the original node (i.e the node that is already on SP4 and owns the SQL resources) and see if this helps?
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
|||Dave,
Glad to hear that the issue is resolved. I appreciate your update.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Hello, I'm having the same problem, but I don't know what you mean by this
NEED_SP_KEY I can't find any information on it. If you could shed some light
on this I would greatly appreciate it!
--Gene
"Uttam Parui[MS]" <uttamkp@.online.microsoft.com> wrote in message
news:KA3IUo6kFHA.944@.TK2MSFTNGXA01.phx.gbl...
> Dave,
> Are you using the NEED_SP_KEY in the registry. If yes, then remove this
key and re-run SP4 from the original node (i.e the node that is already on
SP4 and owns the SQL resources) and see if this helps?
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||Depending on certain parameters, the Need_SP_Key is added to the new node by the setup process when the node is
added to the SQL failover cluster.
Check (on all nodes) if you have the following key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Need_SP_Key
If yes, then delete it. Then try to install the service pack 4 from the active node where SQL Server is currently running.
If no, then you might be having a different issue. Give more details for us to help you.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Uttam Parui[MS] wrote:
> Dave,
> Are you using the NEED_SP_KEY in the registry. If yes, then remove this key and re-run SP4 from the original node (i.e the node that is already on SP4 and owns the SQL resources) and see if this helps?
For me this does not realy help. The node needs to be a possible owner of
the sql resources or else, the node is not updated. When you have just added
the node by the advanced sql setup option, this is not the case yet.
The second problem is, that this way sp4 updates all the nodes and takes the
SQL resources OFFLINE.
I tried installing SP3 first, which is not a goed idea. The sqlsetup runs
fine, the node becomes a possible owner but can't be used (sql wil not come
online), the NEED_SP_KEY dissapears, with means SP4 wants to reapply totaly.
Restoring the NEED_SP_KEY solves this but then the "Setup cannot
verify/grant logon as service right..." is back.
So adding a SP4 node without takeing SQL temporarily offline is not possible.
Hans
p.s.
SP4 was installed with a enterprise admin account.
|||There is a hotfix available from Microsoft support which fixed this for me.
See http://support.microsoft.com/?kbid=905286
BB.
"Hans de Bruin" wrote:
> Uttam Parui[MS] wrote:
> For me this does not realy help. The node needs to be a possible owner of
> the sql resources or else, the node is not updated. When you have just added
> the node by the advanced sql setup option, this is not the case yet.
> The second problem is, that this way sp4 updates all the nodes and takes the
> SQL resources OFFLINE.
> I tried installing SP3 first, which is not a goed idea. The sqlsetup runs
> fine, the node becomes a possible owner but can't be used (sql wil not come
> online), the NEED_SP_KEY dissapears, with means SP4 wants to reapply totaly.
> Restoring the NEED_SP_KEY solves this but then the "Setup cannot
> verify/grant logon as service right..." is back.
> So adding a SP4 node without takeing SQL temporarily offline is not possible.
> --
> Hans
> p.s.
> SP4 was installed with a enterprise admin account.
>
I'm trying to add a new node to our SQL Server 2000 SP4 cluster. Cluster
node is Windows 2003 SP1. I can add the node to the SQL cluster, but when I
try to add SP4 to the new node, I get an error message when I enter in the
service password for the instance.
Error message : Setup cannot verify/grant logon as service right to the SQL
Server service logon account: 5000 (0x1388)
However, this error didn't come up when I was adding the node to the SQL
cluster and it prompted me for the same password. We're running an
active/active configuration and I can't do anything with the other instance
(the default one) as it says that this instance is still waiting for the
service pack!
Any ideas anyone? Thanks in advance
Dave
Dave Wall
Dave,
It looks like a permission issue. Does the Windows domain a/c that you are using the setup have local admin privileges on both the cluster nodes? Try running the setup using the virtual SQL Server service a/c or the
cluster service a/c ?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Dave,
Are you using the NEED_SP_KEY in the registry. If yes, then remove this key and re-run SP4 from the original node (i.e the node that is already on SP4 and owns the SQL resources) and see if this helps?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thanks Uttam,
removed the reg key and it all went through without any problems
Dave
Dave Wall
"Uttam Parui[MS]" wrote:
> Dave,
> Are you using the NEED_SP_KEY in the registry. If yes, then remove this key and re-run SP4 from the original node (i.e the node that is already on SP4 and owns the SQL resources) and see if this helps?
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
|||Dave,
Glad to hear that the issue is resolved. I appreciate your update.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Hello, I'm having the same problem, but I don't know what you mean by this
NEED_SP_KEY I can't find any information on it. If you could shed some light
on this I would greatly appreciate it!
--Gene
"Uttam Parui[MS]" <uttamkp@.online.microsoft.com> wrote in message
news:KA3IUo6kFHA.944@.TK2MSFTNGXA01.phx.gbl...
> Dave,
> Are you using the NEED_SP_KEY in the registry. If yes, then remove this
key and re-run SP4 from the original node (i.e the node that is already on
SP4 and owns the SQL resources) and see if this helps?
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||Depending on certain parameters, the Need_SP_Key is added to the new node by the setup process when the node is
added to the SQL failover cluster.
Check (on all nodes) if you have the following key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Need_SP_Key
If yes, then delete it. Then try to install the service pack 4 from the active node where SQL Server is currently running.
If no, then you might be having a different issue. Give more details for us to help you.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Uttam Parui[MS] wrote:
> Dave,
> Are you using the NEED_SP_KEY in the registry. If yes, then remove this key and re-run SP4 from the original node (i.e the node that is already on SP4 and owns the SQL resources) and see if this helps?
For me this does not realy help. The node needs to be a possible owner of
the sql resources or else, the node is not updated. When you have just added
the node by the advanced sql setup option, this is not the case yet.
The second problem is, that this way sp4 updates all the nodes and takes the
SQL resources OFFLINE.
I tried installing SP3 first, which is not a goed idea. The sqlsetup runs
fine, the node becomes a possible owner but can't be used (sql wil not come
online), the NEED_SP_KEY dissapears, with means SP4 wants to reapply totaly.
Restoring the NEED_SP_KEY solves this but then the "Setup cannot
verify/grant logon as service right..." is back.
So adding a SP4 node without takeing SQL temporarily offline is not possible.
Hans
p.s.
SP4 was installed with a enterprise admin account.
|||There is a hotfix available from Microsoft support which fixed this for me.
See http://support.microsoft.com/?kbid=905286
BB.
"Hans de Bruin" wrote:
> Uttam Parui[MS] wrote:
> For me this does not realy help. The node needs to be a possible owner of
> the sql resources or else, the node is not updated. When you have just added
> the node by the advanced sql setup option, this is not the case yet.
> The second problem is, that this way sp4 updates all the nodes and takes the
> SQL resources OFFLINE.
> I tried installing SP3 first, which is not a goed idea. The sqlsetup runs
> fine, the node becomes a possible owner but can't be used (sql wil not come
> online), the NEED_SP_KEY dissapears, with means SP4 wants to reapply totaly.
> Restoring the NEED_SP_KEY solves this but then the "Setup cannot
> verify/grant logon as service right..." is back.
> So adding a SP4 node without takeing SQL temporarily offline is not possible.
> --
> Hans
> p.s.
> SP4 was installed with a enterprise admin account.
>
Sunday, February 19, 2012
adding datafile to database
sql server 2000 sp4
Hi
I have a database that was initially created for 70MB. It has a application
associated with it and can not be deleted. I need to increase size of the
database to 440 GB. What is the best way of doing it.
--
ontario, canadaDo you want to add another file or increase the size of an existing file? Anyhow, both operations
are very straight forward:
ALTER DATABASE db ADD FILE (...)
ALTER DATABASE db MODIFY FILE (...)
You find exact syntax in Books Online. It will take a while to add or grow a file close to half a
terabyte...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:0030A5B4-3B73-440A-9710-9A210DBBD9D9@.microsoft.com...
> sql server 2000 sp4
> Hi
> I have a database that was initially created for 70MB. It has a application
> associated with it and can not be deleted. I need to increase size of the
> database to 440 GB. What is the best way of doing it.
>
> --
> ontario, canada
Hi
I have a database that was initially created for 70MB. It has a application
associated with it and can not be deleted. I need to increase size of the
database to 440 GB. What is the best way of doing it.
--
ontario, canadaDo you want to add another file or increase the size of an existing file? Anyhow, both operations
are very straight forward:
ALTER DATABASE db ADD FILE (...)
ALTER DATABASE db MODIFY FILE (...)
You find exact syntax in Books Online. It will take a while to add or grow a file close to half a
terabyte...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:0030A5B4-3B73-440A-9710-9A210DBBD9D9@.microsoft.com...
> sql server 2000 sp4
> Hi
> I have a database that was initially created for 70MB. It has a application
> associated with it and can not be deleted. I need to increase size of the
> database to 440 GB. What is the best way of doing it.
>
> --
> ontario, canada
Thursday, February 9, 2012
Adding a user to a Read-Only database
Hey guys,
I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.
The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:
exec sp_change_users_login 'Update_One', 'YYY','YYY'
Except for I get the following error:
Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.
Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?Easiest would be to drop the login on your server, and re-create it with the SID of the user in the database. Alternatively, you can try to have the vendor drop the user and login on their end, and recreate it with your login's SID. The new SID will be brought to your server with the appropriate TLog restore.
I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.
The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:
exec sp_change_users_login 'Update_One', 'YYY','YYY'
Except for I get the following error:
Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.
Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?Easiest would be to drop the login on your server, and re-create it with the SID of the user in the database. Alternatively, you can try to have the vendor drop the user and login on their end, and recreate it with your login's SID. The new SID will be brought to your server with the appropriate TLog restore.
Subscribe to:
Posts (Atom)