Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.
Showing posts with label fact. Show all posts
Showing posts with label fact. Show all posts
Friday, March 30, 2012
Relative Performance: Native SQL vs User Functions
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
Friday, March 23, 2012
Re-installing SQL Server
A remote user messed up my installation of SQL SP3 and now the SQL Server property shows it has SP3 but in fact it never went though the process. I'm planning to re-isntll the SQL server then apply the SP3.
Is there an article or can someone give me some pointers of preparation prior to removing the current SQL server. Also, currently I only know to back up the database file. Can I backup and restore the Master DB and it will retore all users' login and pa
sswords to a particular database now on the server?
Thanks,
Alpha
Have you tried reinstalling sp3? If it still fails without a good reason, I
highly recommend you starting reinstallation from scratch because the
previous failed sp install may have corrupted the SQL instance, or at least
changed system schemas etc, leaving you in a inconsistent state. You can run
sp_help_revlogin to script out all user logins and save them. Also detach
all user dbs for future attach. You need to script out all jobs, alerts,
etc. also to avoid restoring msdb.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> A remote user messed up my installation of SQL SP3 and now the SQL Server
property shows it has SP3 but in fact it never went though the process. I'm
planning to re-isntll the SQL server then apply the SP3.
> Is there an article or can someone give me some pointers of preparation
prior to removing the current SQL server. Also, currently I only know to
back up the database file. Can I backup and restore the Master DB and it
will retore all users' login and passwords to a particular database now on
the server?
> Thanks,
> Alpha
|||I tried reinstalling sp3 twice and both time it won't go through. The sql server status shows it has SP3 when in fact the installation never went through. It is in a confused state. I will be removing the sql server installation, deleted the folder and
then re-install from scratch. I search the on-line book for more detail of sp_help_revlogin but it is not found. Is there a typo?
Thanks,
Alpha
"Richard Ding" wrote:
> Have you tried reinstalling sp3? If it still fails without a good reason, I
> highly recommend you starting reinstallation from scratch because the
> previous failed sp install may have corrupted the SQL instance, or at least
> changed system schemas etc, leaving you in a inconsistent state. You can run
> sp_help_revlogin to script out all user logins and save them. Also detach
> all user dbs for future attach. You need to script out all jobs, alerts,
> etc. also to avoid restoring msdb.
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> property shows it has SP3 but in fact it never went though the process. I'm
> planning to re-isntll the SQL server then apply the SP3.
> prior to removing the current SQL server. Also, currently I only know to
> back up the database file. Can I backup and restore the Master DB and it
> will retore all users' login and passwords to a particular database now on
> the server?
>
>
|||Yes, you can backup and restore master, model, everything... But I think I
would open a PSS call to get MS support for this... Perhaps they can help
you get through without re-install... The cost is just $249 I think..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:58D9149D-F7B0-423F-9EE7-CA7E1AC62C13@.microsoft.com...
> I tried reinstalling sp3 twice and both time it won't go through. The sql
server status shows it has SP3 when in fact the installation never went
through. It is in a confused state. I will be removing the sql server
installation, deleted the folder and then re-install from scratch. I search
the on-line book for more detail of sp_help_revlogin but it is not found.
Is there a typo?[vbcol=seagreen]
> Thanks,
> Alpha
>
> "Richard Ding" wrote:
reason, I[vbcol=seagreen]
least[vbcol=seagreen]
run[vbcol=seagreen]
detach[vbcol=seagreen]
Server[vbcol=seagreen]
I'm[vbcol=seagreen]
preparation[vbcol=seagreen]
to[vbcol=seagreen]
it[vbcol=seagreen]
on[vbcol=seagreen]
Is there an article or can someone give me some pointers of preparation prior to removing the current SQL server. Also, currently I only know to back up the database file. Can I backup and restore the Master DB and it will retore all users' login and pa
sswords to a particular database now on the server?
Thanks,
Alpha
Have you tried reinstalling sp3? If it still fails without a good reason, I
highly recommend you starting reinstallation from scratch because the
previous failed sp install may have corrupted the SQL instance, or at least
changed system schemas etc, leaving you in a inconsistent state. You can run
sp_help_revlogin to script out all user logins and save them. Also detach
all user dbs for future attach. You need to script out all jobs, alerts,
etc. also to avoid restoring msdb.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> A remote user messed up my installation of SQL SP3 and now the SQL Server
property shows it has SP3 but in fact it never went though the process. I'm
planning to re-isntll the SQL server then apply the SP3.
> Is there an article or can someone give me some pointers of preparation
prior to removing the current SQL server. Also, currently I only know to
back up the database file. Can I backup and restore the Master DB and it
will retore all users' login and passwords to a particular database now on
the server?
> Thanks,
> Alpha
|||I tried reinstalling sp3 twice and both time it won't go through. The sql server status shows it has SP3 when in fact the installation never went through. It is in a confused state. I will be removing the sql server installation, deleted the folder and
then re-install from scratch. I search the on-line book for more detail of sp_help_revlogin but it is not found. Is there a typo?
Thanks,
Alpha
"Richard Ding" wrote:
> Have you tried reinstalling sp3? If it still fails without a good reason, I
> highly recommend you starting reinstallation from scratch because the
> previous failed sp install may have corrupted the SQL instance, or at least
> changed system schemas etc, leaving you in a inconsistent state. You can run
> sp_help_revlogin to script out all user logins and save them. Also detach
> all user dbs for future attach. You need to script out all jobs, alerts,
> etc. also to avoid restoring msdb.
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> property shows it has SP3 but in fact it never went though the process. I'm
> planning to re-isntll the SQL server then apply the SP3.
> prior to removing the current SQL server. Also, currently I only know to
> back up the database file. Can I backup and restore the Master DB and it
> will retore all users' login and passwords to a particular database now on
> the server?
>
>
|||Yes, you can backup and restore master, model, everything... But I think I
would open a PSS call to get MS support for this... Perhaps they can help
you get through without re-install... The cost is just $249 I think..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:58D9149D-F7B0-423F-9EE7-CA7E1AC62C13@.microsoft.com...
> I tried reinstalling sp3 twice and both time it won't go through. The sql
server status shows it has SP3 when in fact the installation never went
through. It is in a confused state. I will be removing the sql server
installation, deleted the folder and then re-install from scratch. I search
the on-line book for more detail of sp_help_revlogin but it is not found.
Is there a typo?[vbcol=seagreen]
> Thanks,
> Alpha
>
> "Richard Ding" wrote:
reason, I[vbcol=seagreen]
least[vbcol=seagreen]
run[vbcol=seagreen]
detach[vbcol=seagreen]
Server[vbcol=seagreen]
I'm[vbcol=seagreen]
preparation[vbcol=seagreen]
to[vbcol=seagreen]
it[vbcol=seagreen]
on[vbcol=seagreen]
Re-installing SQL Server
A remote user messed up my installation of SQL SP3 and now the SQL Server pr
operty shows it has SP3 but in fact it never went though the process. I'm p
lanning to re-isntll the SQL server then apply the SP3.
Is there an article or can someone give me some pointers of preparation prio
r to removing the current SQL server. Also, currently I only know to back u
p the database file. Can I backup and restore the Master DB and it will ret
ore all users' login and pa
sswords to a particular database now on the server?
Thanks,
AlphaHave you tried reinstalling sp3? If it still fails without a good reason, I
highly recommend you starting reinstallation from scratch because the
previous failed sp install may have corrupted the SQL instance, or at least
changed system schemas etc, leaving you in a inconsistent state. You can run
sp_help_revlogin to script out all user logins and save them. Also detach
all user dbs for future attach. You need to script out all jobs, alerts,
etc. also to avoid restoring msdb.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> A remote user messed up my installation of SQL SP3 and now the SQL Server
property shows it has SP3 but in fact it never went though the process. I'm
planning to re-isntll the SQL server then apply the SP3.
> Is there an article or can someone give me some pointers of preparation
prior to removing the current SQL server. Also, currently I only know to
back up the database file. Can I backup and restore the Master DB and it
will retore all users' login and passwords to a particular database now on
the server?
> Thanks,
> Alpha|||I tried reinstalling sp3 twice and both time it won't go through. The sql s
erver status shows it has SP3 when in fact the installation never went throu
gh. It is in a confused state. I will be removing the sql server installat
ion, deleted the folder and
then re-install from scratch. I search the on-line book for more detail of
sp_help_revlogin but it is not found. Is there a typo?
Thanks,
Alpha
"Richard Ding" wrote:
> Have you tried reinstalling sp3? If it still fails without a good reason,
I
> highly recommend you starting reinstallation from scratch because the
> previous failed sp install may have corrupted the SQL instance, or at leas
t
> changed system schemas etc, leaving you in a inconsistent state. You can r
un
> sp_help_revlogin to script out all user logins and save them. Also detach
> all user dbs for future attach. You need to script out all jobs, alerts,
> etc. also to avoid restoring msdb.
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> property shows it has SP3 but in fact it never went though the process. I
'm
> planning to re-isntll the SQL server then apply the SP3.
> prior to removing the current SQL server. Also, currently I only know to
> back up the database file. Can I backup and restore the Master DB and it
> will retore all users' login and passwords to a particular database now on
> the server?
>
>|||Yes, you can backup and restore master, model, everything... But I think I
would open a PSS call to get MS support for this... Perhaps they can help
you get through without re-install... The cost is just $249 I think..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:58D9149D-F7B0-423F-9EE7-CA7E1AC62C13@.microsoft.com...
> I tried reinstalling sp3 twice and both time it won't go through. The sql
server status shows it has SP3 when in fact the installation never went
through. It is in a confused state. I will be removing the sql server
installation, deleted the folder and then re-install from scratch. I search
the on-line book for more detail of sp_help_revlogin but it is not found.
Is there a typo?[vbcol=seagreen]
> Thanks,
> Alpha
>
> "Richard Ding" wrote:
>
reason, I[vbcol=seagreen]
least[vbcol=seagreen]
run[vbcol=seagreen]
detach[vbcol=seagreen]
Server[vbcol=seagreen]
I'm[vbcol=seagreen]
preparation[vbcol=seagreen]
to[vbcol=seagreen]
it[vbcol=seagreen]
on[vbcol=seagreen]
operty shows it has SP3 but in fact it never went though the process. I'm p
lanning to re-isntll the SQL server then apply the SP3.
Is there an article or can someone give me some pointers of preparation prio
r to removing the current SQL server. Also, currently I only know to back u
p the database file. Can I backup and restore the Master DB and it will ret
ore all users' login and pa
sswords to a particular database now on the server?
Thanks,
AlphaHave you tried reinstalling sp3? If it still fails without a good reason, I
highly recommend you starting reinstallation from scratch because the
previous failed sp install may have corrupted the SQL instance, or at least
changed system schemas etc, leaving you in a inconsistent state. You can run
sp_help_revlogin to script out all user logins and save them. Also detach
all user dbs for future attach. You need to script out all jobs, alerts,
etc. also to avoid restoring msdb.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> A remote user messed up my installation of SQL SP3 and now the SQL Server
property shows it has SP3 but in fact it never went though the process. I'm
planning to re-isntll the SQL server then apply the SP3.
> Is there an article or can someone give me some pointers of preparation
prior to removing the current SQL server. Also, currently I only know to
back up the database file. Can I backup and restore the Master DB and it
will retore all users' login and passwords to a particular database now on
the server?
> Thanks,
> Alpha|||I tried reinstalling sp3 twice and both time it won't go through. The sql s
erver status shows it has SP3 when in fact the installation never went throu
gh. It is in a confused state. I will be removing the sql server installat
ion, deleted the folder and
then re-install from scratch. I search the on-line book for more detail of
sp_help_revlogin but it is not found. Is there a typo?
Thanks,
Alpha
"Richard Ding" wrote:
> Have you tried reinstalling sp3? If it still fails without a good reason,
I
> highly recommend you starting reinstallation from scratch because the
> previous failed sp install may have corrupted the SQL instance, or at leas
t
> changed system schemas etc, leaving you in a inconsistent state. You can r
un
> sp_help_revlogin to script out all user logins and save them. Also detach
> all user dbs for future attach. You need to script out all jobs, alerts,
> etc. also to avoid restoring msdb.
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:3D37FDD1-7C11-4DAE-99A6-E2E12CC11A76@.microsoft.com...
> property shows it has SP3 but in fact it never went though the process. I
'm
> planning to re-isntll the SQL server then apply the SP3.
> prior to removing the current SQL server. Also, currently I only know to
> back up the database file. Can I backup and restore the Master DB and it
> will retore all users' login and passwords to a particular database now on
> the server?
>
>|||Yes, you can backup and restore master, model, everything... But I think I
would open a PSS call to get MS support for this... Perhaps they can help
you get through without re-install... The cost is just $249 I think..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:58D9149D-F7B0-423F-9EE7-CA7E1AC62C13@.microsoft.com...
> I tried reinstalling sp3 twice and both time it won't go through. The sql
server status shows it has SP3 when in fact the installation never went
through. It is in a confused state. I will be removing the sql server
installation, deleted the folder and then re-install from scratch. I search
the on-line book for more detail of sp_help_revlogin but it is not found.
Is there a typo?[vbcol=seagreen]
> Thanks,
> Alpha
>
> "Richard Ding" wrote:
>
reason, I[vbcol=seagreen]
least[vbcol=seagreen]
run[vbcol=seagreen]
detach[vbcol=seagreen]
Server[vbcol=seagreen]
I'm[vbcol=seagreen]
preparation[vbcol=seagreen]
to[vbcol=seagreen]
it[vbcol=seagreen]
on[vbcol=seagreen]
Subscribe to:
Posts (Atom)