Applying SP on SQL Cluster (active/passive) after new nodes Added

Ran into a problem in scheduling applying the Service Pack to our SQL
2000 active/passive cluster when we add new nodes. 50 plus databases and an
unknown number of apps.
I'm wondering what people do to ensure that no apps/people are trying to
access the databases when the service pack is applied.
Our application staff says there is no way they can guarantee that an
app or person will not try to access one of the databases while the service
pack is being installed.
My plan is to evict one of the existing nodes then add the two new nodes
one at a time and set up the heartbeat. Then install SQL 2000; then apply
SP3a. Yes I know SP4 is available; however, one of the apps will not run
with SP4.
The service pack brings the database engine up in single-user mode during
the SP install. Unless your applications are connecting as SA or
equivalent, there should be no conflict.
If the apps are connecting as SA, change the password before applying the SP
and change it back when you are done. Then fix that glaring security and
managability hole.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
news:FA6E93F9-78FC-497E-A4F0-E4D459F85CA9@.microsoft.com...
> Ran into a problem in scheduling applying the Service Pack to our SQL
> 2000 active/passive cluster when we add new nodes. 50 plus databases and
> an
> unknown number of apps.
> I'm wondering what people do to ensure that no apps/people are trying
> to
> access the databases when the service pack is applied.
> Our application staff says there is no way they can guarantee that an
> app or person will not try to access one of the databases while the
> service
> pack is being installed.
> My plan is to evict one of the existing nodes then add the two new
> nodes
> one at a time and set up the heartbeat. Then install SQL 2000; then
> apply
> SP3a. Yes I know SP4 is available; however, one of the apps will not run
> with SP4.
|||I thought in single-user mode, that any ID could be used and the 1st one
wins. SA or not. I also thought that the service pack brings the database
engine up and down several times while it is applying the service pack.
The other restriction (db_owner, sysadmin, bd_creator) isn't an option for
us either since many of the app IDs are db_owner in their database.
"Geoff N. Hiten" wrote:

> The service pack brings the database engine up in single-user mode during
> the SP install. Unless your applications are connecting as SA or
> equivalent, there should be no conflict.
>
> If the apps are connecting as SA, change the password before applying the SP
> and change it back when you are done. Then fix that glaring security and
> managability hole.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
> news:FA6E93F9-78FC-497E-A4F0-E4D459F85CA9@.microsoft.com...
>
|||There is single-user mode for a database, and then there is single-user mode
for the entire database service. The SP starts the entire service in
single-user mode and immediately claims the connection.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
news:98107803-0953-4B12-986E-FB82CDCBA45B@.microsoft.com...[vbcol=seagreen]
>I thought in single-user mode, that any ID could be used and the 1st one
> wins. SA or not. I also thought that the service pack brings the
> database
> engine up and down several times while it is applying the service pack.
> The other restriction (db_owner, sysadmin, bd_creator) isn't an option for
> us either since many of the app IDs are db_owner in their database.
> "Geoff N. Hiten" wrote:
|||Thanks. So do I even need to get all the apps down before I start applying
a service pack? I would think so, so they close out of what they are doing
in an orderly fashion. Just not worry about them trying to get in while the
service pack is running.
"Geoff N. Hiten" wrote:

> There is single-user mode for a database, and then there is single-user mode
> for the entire database service. The SP starts the entire service in
> single-user mode and immediately claims the connection.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
> news:98107803-0953-4B12-986E-FB82CDCBA45B@.microsoft.com...
>
|||You need a standard practice for maintenance, regardless of whether you are
applying a hotfix or an internal update. This includes procedures for
stopping and starting the entire system.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
news:D497CBD2-637D-490C-9BAD-6AA49F145000@.microsoft.com...[vbcol=seagreen]
> Thanks. So do I even need to get all the apps down before I start
> applying
> a service pack? I would think so, so they close out of what they are
> doing
> in an orderly fashion. Just not worry about them trying to get in while
> the
> service pack is running.
> "Geoff N. Hiten" wrote:

Application Role behaves differently in 2005?

Here is a very simple test that I ran on my 2000 and 2005 box and in
both cases I got different results. When I ran this command on 2000 it
worked fine but when I ran the same script on a 2005 box I got the
follwoing error.
sp_addapprole 'aaa','aa1'
exec sp_setapprole 'aaa','aa1'
exec sp_helpuser UserA
ERROR:
Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
The name supplied (UserA) is not a user, role, or aliased login.
I ran this script on a user database, where UserA has access to the DB.
Can anyone tell me why this same script works fine in 2000 and not in
2005? What do I need to do to get it to work in 2005?
ThanksIt looks like the user (UserA) is not a login in the SQL 2005 server. Check
the server logins.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shub" <shubtech@.gmail.com> wrote in message
news:1155743364.308040.173700@.74g2000cwt.googlegroups.com...
> Here is a very simple test that I ran on my 2000 and 2005 box and in
> both cases I got different results. When I ran this command on 2000 it
> worked fine but when I ran the same script on a 2005 box I got the
> follwoing error.
> sp_addapprole 'aaa','aa1'
> exec sp_setapprole 'aaa','aa1'
> exec sp_helpuser UserA
> ERROR:
> Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> The name supplied (UserA) is not a user, role, or aliased login.
> I ran this script on a user database, where UserA has access to the DB.
> Can anyone tell me why this same script works fine in 2000 and not in
> 2005? What do I need to do to get it to work in 2005?
> Thanks
>|||Hi
This was already logged at
http://connect.microsoft.com/SQLSer...=12581
4
The difference is a tightening up of security.
If you did sp_helpuser 'aaa' you would get information.
John
"shub" wrote:

> Here is a very simple test that I ran on my 2000 and 2005 box and in
> both cases I got different results. When I ran this command on 2000 it
> worked fine but when I ran the same script on a 2005 box I got the
> follwoing error.
> sp_addapprole 'aaa','aa1'
> exec sp_setapprole 'aaa','aa1'
> exec sp_helpuser UserA
> ERROR:
> Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> The name supplied (UserA) is not a user, role, or aliased login.
> I ran this script on a user database, where UserA has access to the DB.
> Can anyone tell me why this same script works fine in 2000 and not in
> 2005? What do I need to do to get it to work in 2005?
> Thanks
>|||Thanks John for your response. Its good to know that this issue was
acknowledged by Microsoft.
However I am still not sure what the work around is, how can I get the
sp_helpuser info for UserA
login. UserA has access to the server and the database.
John Bell wrote:[vbcol=seagreen]
> Hi
> This was already logged at
> http://connect.microsoft.com/SQLSer...=125
814
> The difference is a tightening up of security.
> If you did sp_helpuser 'aaa' you would get information.
> John
> "shub" wrote:
>|||Hi
Why are you wanting information about other users when you have set the
application role? If you want information about the original user then you
could gather that before setting the application role.
John
"shub" wrote:

> Thanks John for your response. Its good to know that this issue was
> acknowledged by Microsoft.
> However I am still not sure what the work around is, how can I get the
> sp_helpuser info for UserA
> login. UserA has access to the server and the database.
> John Bell wrote:
>|||John
The way the application works is after it enables the Application role,
it still tries to figure out what user should get access to what screen
within the application. In order to do that the application runs the
sp_helpuser for the login to detremine what roles the login belongs to,
depending on that it gives access to different screens in the
application.
I did find a work around that seems to work but I am not sure if that
is the best way. If I add the db users and all the db roles to the
secureables of the application role and grant "control", the
sp_helpuser seems to work fine then. But is this the right way to do
it?
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi
> Why are you wanting information about other users when you have set the
> application role? If you want information about the original user then you
> could gather that before setting the application role.
> John
> "shub" wrote:
>|||Hi
I am not sure why you can't get the information before you set the
application role, most applications I know have user permissions stored in
their own tables and are not dependent on the roles, therefore all you would
need to know is the userid (such as SYSTEM_USER).
Granting too many privileges to anyone is not a good idea, I think adding
the application role to the db_securityadmin or db_accessadmin database role
s
would give it privileges to get all user information back, but you don't
necessarily want to do that.
John
"shub" wrote:

> John
> The way the application works is after it enables the Application role,
> it still tries to figure out what user should get access to what screen
> within the application. In order to do that the application runs the
> sp_helpuser for the login to detremine what roles the login belongs to,
> depending on that it gives access to different screens in the
> application.
> I did find a work around that seems to work but I am not sure if that
> is the best way. If I add the db users and all the db roles to the
> secureables of the application role and grant "control", the
> sp_helpuser seems to work fine then. But is this the right way to do
> it?
> Thanks
> John Bell wrote:
>

Application Role behaves differently in 2005?

Here is a very simple test that I ran on my 2000 and 2005 box and in
both cases I got different results. When I ran this command on 2000 it
worked fine but when I ran the same script on a 2005 box I got the
follwoing error.
sp_addapprole 'aaa','aa1'
exec sp_setapprole 'aaa','aa1'
exec sp_helpuser UserA
ERROR:
Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
The name supplied (UserA) is not a user, role, or aliased login.
I ran this script on a user database, where UserA has access to the DB.
Can anyone tell me why this same script works fine in 2000 and not in
2005? What do I need to do to get it to work in 2005?
ThanksIt looks like the user (UserA) is not a login in the SQL 2005 server. Check
the server logins.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shub" <shubtech@.gmail.com> wrote in message
news:1155743364.308040.173700@.74g2000cwt.googlegroups.com...
> Here is a very simple test that I ran on my 2000 and 2005 box and in
> both cases I got different results. When I ran this command on 2000 it
> worked fine but when I ran the same script on a 2005 box I got the
> follwoing error.
> sp_addapprole 'aaa','aa1'
> exec sp_setapprole 'aaa','aa1'
> exec sp_helpuser UserA
> ERROR:
> Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> The name supplied (UserA) is not a user, role, or aliased login.
> I ran this script on a user database, where UserA has access to the DB.
> Can anyone tell me why this same script works fine in 2000 and not in
> 2005? What do I need to do to get it to work in 2005?
> Thanks
>|||Hi
This was already logged at
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
The difference is a tightening up of security.
If you did sp_helpuser 'aaa' you would get information.
John
"shub" wrote:
> Here is a very simple test that I ran on my 2000 and 2005 box and in
> both cases I got different results. When I ran this command on 2000 it
> worked fine but when I ran the same script on a 2005 box I got the
> follwoing error.
> sp_addapprole 'aaa','aa1'
> exec sp_setapprole 'aaa','aa1'
> exec sp_helpuser UserA
> ERROR:
> Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> The name supplied (UserA) is not a user, role, or aliased login.
> I ran this script on a user database, where UserA has access to the DB.
> Can anyone tell me why this same script works fine in 2000 and not in
> 2005? What do I need to do to get it to work in 2005?
> Thanks
>|||Thanks John for your response. Its good to know that this issue was
acknowledged by Microsoft.
However I am still not sure what the work around is, how can I get the
sp_helpuser info for UserA
login. UserA has access to the server and the database.
John Bell wrote:
> Hi
> This was already logged at
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> The difference is a tightening up of security.
> If you did sp_helpuser 'aaa' you would get information.
> John
> "shub" wrote:
> > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > both cases I got different results. When I ran this command on 2000 it
> > worked fine but when I ran the same script on a 2005 box I got the
> > follwoing error.
> >
> > sp_addapprole 'aaa','aa1'
> > exec sp_setapprole 'aaa','aa1'
> > exec sp_helpuser UserA
> >
> > ERROR:
> > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > The name supplied (UserA) is not a user, role, or aliased login.
> >
> > I ran this script on a user database, where UserA has access to the DB.
> >
> > Can anyone tell me why this same script works fine in 2000 and not in
> > 2005? What do I need to do to get it to work in 2005?
> >
> > Thanks
> >
> >|||Hi
Why are you wanting information about other users when you have set the
application role? If you want information about the original user then you
could gather that before setting the application role.
John
"shub" wrote:
> Thanks John for your response. Its good to know that this issue was
> acknowledged by Microsoft.
> However I am still not sure what the work around is, how can I get the
> sp_helpuser info for UserA
> login. UserA has access to the server and the database.
> John Bell wrote:
> > Hi
> >
> > This was already logged at
> > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> >
> > The difference is a tightening up of security.
> > If you did sp_helpuser 'aaa' you would get information.
> >
> > John
> >
> > "shub" wrote:
> >
> > > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > > both cases I got different results. When I ran this command on 2000 it
> > > worked fine but when I ran the same script on a 2005 box I got the
> > > follwoing error.
> > >
> > > sp_addapprole 'aaa','aa1'
> > > exec sp_setapprole 'aaa','aa1'
> > > exec sp_helpuser UserA
> > >
> > > ERROR:
> > > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > > The name supplied (UserA) is not a user, role, or aliased login.
> > >
> > > I ran this script on a user database, where UserA has access to the DB.
> > >
> > > Can anyone tell me why this same script works fine in 2000 and not in
> > > 2005? What do I need to do to get it to work in 2005?
> > >
> > > Thanks
> > >
> > >
>|||John
The way the application works is after it enables the Application role,
it still tries to figure out what user should get access to what screen
within the application. In order to do that the application runs the
sp_helpuser for the login to detremine what roles the login belongs to,
depending on that it gives access to different screens in the
application.
I did find a work around that seems to work but I am not sure if that
is the best way. If I add the db users and all the db roles to the
secureables of the application role and grant "control", the
sp_helpuser seems to work fine then. But is this the right way to do
it?
Thanks
John Bell wrote:
> Hi
> Why are you wanting information about other users when you have set the
> application role? If you want information about the original user then you
> could gather that before setting the application role.
> John
> "shub" wrote:
> > Thanks John for your response. Its good to know that this issue was
> > acknowledged by Microsoft.
> > However I am still not sure what the work around is, how can I get the
> > sp_helpuser info for UserA
> > login. UserA has access to the server and the database.
> > John Bell wrote:
> > > Hi
> > >
> > > This was already logged at
> > > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> > >
> > > The difference is a tightening up of security.
> > > If you did sp_helpuser 'aaa' you would get information.
> > >
> > > John
> > >
> > > "shub" wrote:
> > >
> > > > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > > > both cases I got different results. When I ran this command on 2000 it
> > > > worked fine but when I ran the same script on a 2005 box I got the
> > > > follwoing error.
> > > >
> > > > sp_addapprole 'aaa','aa1'
> > > > exec sp_setapprole 'aaa','aa1'
> > > > exec sp_helpuser UserA
> > > >
> > > > ERROR:
> > > > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > > > The name supplied (UserA) is not a user, role, or aliased login.
> > > >
> > > > I ran this script on a user database, where UserA has access to the DB.
> > > >
> > > > Can anyone tell me why this same script works fine in 2000 and not in
> > > > 2005? What do I need to do to get it to work in 2005?
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I am not sure why you can't get the information before you set the
application role, most applications I know have user permissions stored in
their own tables and are not dependent on the roles, therefore all you would
need to know is the userid (such as SYSTEM_USER).
Granting too many privileges to anyone is not a good idea, I think adding
the application role to the db_securityadmin or db_accessadmin database roles
would give it privileges to get all user information back, but you don't
necessarily want to do that.
John
"shub" wrote:
> John
> The way the application works is after it enables the Application role,
> it still tries to figure out what user should get access to what screen
> within the application. In order to do that the application runs the
> sp_helpuser for the login to detremine what roles the login belongs to,
> depending on that it gives access to different screens in the
> application.
> I did find a work around that seems to work but I am not sure if that
> is the best way. If I add the db users and all the db roles to the
> secureables of the application role and grant "control", the
> sp_helpuser seems to work fine then. But is this the right way to do
> it?
> Thanks
> John Bell wrote:
> > Hi
> >
> > Why are you wanting information about other users when you have set the
> > application role? If you want information about the original user then you
> > could gather that before setting the application role.
> >
> > John
> >
> > "shub" wrote:
> >
> > > Thanks John for your response. Its good to know that this issue was
> > > acknowledged by Microsoft.
> > > However I am still not sure what the work around is, how can I get the
> > > sp_helpuser info for UserA
> > > login. UserA has access to the server and the database.
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > This was already logged at
> > > > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> > > >
> > > > The difference is a tightening up of security.
> > > > If you did sp_helpuser 'aaa' you would get information.
> > > >
> > > > John
> > > >
> > > > "shub" wrote:
> > > >
> > > > > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > > > > both cases I got different results. When I ran this command on 2000 it
> > > > > worked fine but when I ran the same script on a 2005 box I got the
> > > > > follwoing error.
> > > > >
> > > > > sp_addapprole 'aaa','aa1'
> > > > > exec sp_setapprole 'aaa','aa1'
> > > > > exec sp_helpuser UserA
> > > > >
> > > > > ERROR:
> > > > > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > > > > The name supplied (UserA) is not a user, role, or aliased login.
> > > > >
> > > > > I ran this script on a user database, where UserA has access to the DB.
> > > > >
> > > > > Can anyone tell me why this same script works fine in 2000 and not in
> > > > > 2005? What do I need to do to get it to work in 2005?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>

appending to the end of current file

Hello all! I have a dts package exporting a text file. I would like for the dts job to append to the end of the file each time it is ran, rather than overwriting it. Is there a simple solution for this?
ThanksDarren Green suggested in earlier posts that :
The text file provider does not support this. The simplest thing to do is to export to a new file, then use the DOS copy command to merge the files.

This can be done with the Execute Process Task, and any dynamic filename stuff can be handled with an ActiveX Script Task to change the connection and the Exec Proc Task at the same time.

copy FileOriginal.txt+FileNew.txt FileResult.txt

HTH|||That is what I am currently doing. I was hoping for something within the dts package. Thanks for you help.

val|||As suggested its not available using TEXT provider with DTS.
May be this is also other way around, is to export to an excel sheet and save that as text file.:)