The installation of SQL Server 2005 (Express) has an option
for default instance or named instance (SQLExpress as default)
What are the advantages of setting up named instance and default instance?
Thanks for answering...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1"pedestrian via SQLMonster.com" <u16758@.uwe> wrote in
news:64c9478703e41@.uwe:
> The installation of SQL Server 2005 (Express) has an option
> for default instance or named instance (SQLExpress as default)
> What are the advantages of setting up named instance and default
> instance? Thanks for answering...
There can only be one default instance, but multipe named instances with
different names. Advantage of the default instance is that you (or the
user) do not have to remember an instance name.
One of the major reasons for using multiple instances will be complete
separation of database solutions. Each instance has it's own service, and
thus you can set memory constraints per instance. Furthermore, remember
that logins are defined in the master database, so logins are only
available within that instance, which can be a good security measure.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks for replying... I'm currently using SQL Server 2005 Express...
I have set up with Named Instance = SQLExpress, that is, the default name.
So, does that mean I can still set up another copy of Express server
using Default Instance?
Ole Kristian Bangås wrote:
>> The installation of SQL Server 2005 (Express) has an option
>> for default instance or named instance (SQLExpress as default)
>> What are the advantages of setting up named instance and default
>> instance? Thanks for answering...
>There can only be one default instance, but multipe named instances with
>different names. Advantage of the default instance is that you (or the
>user) do not have to remember an instance name.
>One of the major reasons for using multiple instances will be complete
>separation of database solutions. Each instance has it's own service, and
>thus you can set memory constraints per instance. Furthermore, remember
>that logins are defined in the master database, so logins are only
>available within that instance, which can be a good security measure.
>
--
Pedestrian, Penang.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Yes. There can be one default instance on a machine and a number of named instances (each need a
unique name, of course). Installation order doesn't matter (excluding prior versions of SQL
Server/MSDE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pedestrian via SQLMonster.com" <u16758@.uwe> wrote in message news:64d4235358c63@.uwe...
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
> Ole Kristian Bangås wrote:
>> The installation of SQL Server 2005 (Express) has an option
>> for default instance or named instance (SQLExpress as default)
>> What are the advantages of setting up named instance and default
>> instance? Thanks for answering...
>>There can only be one default instance, but multipe named instances with
>>different names. Advantage of the default instance is that you (or the
>>user) do not have to remember an instance name.
>>One of the major reasons for using multiple instances will be complete
>>separation of database solutions. Each instance has it's own service, and
>>thus you can set memory constraints per instance. Furthermore, remember
>>that logins are defined in the master database, so logins are only
>>available within that instance, which can be a good security measure.
> --
> Pedestrian, Penang.
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||I haven't tried to install two SQL Server 2005 Express instances on the
same computer, but since multiple applications may install their instance
of MSDE I cannot see any reason why this should not work.
"pedestrian via SQLMonster.com" <u16758@.uwe> wrote in
news:64d4235358c63@.uwe:
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default
> name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks to Tibor and Ole... I shall try it out.
--
Pedestrian, Penang.
Message posted via http://www.sqlmonster.com
Showing posts with label default. Show all posts
Showing posts with label default. Show all posts
Tuesday, March 20, 2012
Advantages of SQL Server 2005 named instance?
The installation of SQL Server 2005 (Express) has an option
for default instance or named instance (SQLExpress as default)
What are the advantages of setting up named instance and default instance?
Thanks for answering...
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1"pedestrian via droptable.com" <u16758@.uwe> wrote in
news:64c9478703e41@.uwe:
> The installation of SQL Server 2005 (Express) has an option
> for default instance or named instance (SQLExpress as default)
> What are the advantages of setting up named instance and default
> instance? Thanks for answering...
There can only be one default instance, but multipe named instances with
different names. Advantage of the default instance is that you (or the
user) do not have to remember an instance name.
One of the major reasons for using multiple instances will be complete
separation of database solutions. Each instance has it's own service, and
thus you can set memory constraints per instance. Furthermore, remember
that logins are defined in the master database, so logins are only
available within that instance, which can be a good security measure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks for replying... I'm currently using SQL Server 2005 Express...
I have set up with Named Instance = SQLExpress, that is, the default name.
So, does that mean I can still set up another copy of Express server
using Default Instance?
Ole Kristian Bang?s wrote:
>There can only be one default instance, but multipe named instances with
>different names. Advantage of the default instance is that you (or the
>user) do not have to remember an instance name.
>One of the major reasons for using multiple instances will be complete
>separation of database solutions. Each instance has it's own service, and
>thus you can set memory constraints per instance. Furthermore, remember
>that logins are defined in the master database, so logins are only
>available within that instance, which can be a good security measure.
>
Pedestrian, Penang.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Yes. There can be one default instance on a machine and a number of named in
stances (each need a
unique name, of course). Installation order doesn't matter (excluding prior
versions of SQL
Server/MSDE).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pedestrian via droptable.com" <u16758@.uwe> wrote in message news:64d4235358c63@.uwe...[vbco
l=seagreen]
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
> Ole Kristian Bang?s wrote:
> --
> Pedestrian, Penang.
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||I haven't tried to install two SQL Server 2005 Express instances on the
same computer, but since multiple applications may install their instance
of MSDE I cannot see any reason why this should not work.
"pedestrian via droptable.com" <u16758@.uwe> wrote in
news:64d4235358c63@.uwe:
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default
> name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks to Tibor and Ole... I shall try it out.
Pedestrian, Penang.
Message posted via http://www.droptable.comsql
for default instance or named instance (SQLExpress as default)
What are the advantages of setting up named instance and default instance?
Thanks for answering...
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1"pedestrian via droptable.com" <u16758@.uwe> wrote in
news:64c9478703e41@.uwe:
> The installation of SQL Server 2005 (Express) has an option
> for default instance or named instance (SQLExpress as default)
> What are the advantages of setting up named instance and default
> instance? Thanks for answering...
There can only be one default instance, but multipe named instances with
different names. Advantage of the default instance is that you (or the
user) do not have to remember an instance name.
One of the major reasons for using multiple instances will be complete
separation of database solutions. Each instance has it's own service, and
thus you can set memory constraints per instance. Furthermore, remember
that logins are defined in the master database, so logins are only
available within that instance, which can be a good security measure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks for replying... I'm currently using SQL Server 2005 Express...
I have set up with Named Instance = SQLExpress, that is, the default name.
So, does that mean I can still set up another copy of Express server
using Default Instance?
Ole Kristian Bang?s wrote:
>There can only be one default instance, but multipe named instances with
>different names. Advantage of the default instance is that you (or the
>user) do not have to remember an instance name.
>One of the major reasons for using multiple instances will be complete
>separation of database solutions. Each instance has it's own service, and
>thus you can set memory constraints per instance. Furthermore, remember
>that logins are defined in the master database, so logins are only
>available within that instance, which can be a good security measure.
>
Pedestrian, Penang.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Yes. There can be one default instance on a machine and a number of named in
stances (each need a
unique name, of course). Installation order doesn't matter (excluding prior
versions of SQL
Server/MSDE).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pedestrian via droptable.com" <u16758@.uwe> wrote in message news:64d4235358c63@.uwe...[vbco
l=seagreen]
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
> Ole Kristian Bang?s wrote:
> --
> Pedestrian, Penang.
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||I haven't tried to install two SQL Server 2005 Express instances on the
same computer, but since multiple applications may install their instance
of MSDE I cannot see any reason why this should not work.
"pedestrian via droptable.com" <u16758@.uwe> wrote in
news:64d4235358c63@.uwe:
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default
> name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks to Tibor and Ole... I shall try it out.
Pedestrian, Penang.
Message posted via http://www.droptable.comsql
Sunday, March 11, 2012
Advanced Index Statistics Query
I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pro.../qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pro.../qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>
|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pro.../qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pro.../qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>
|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
Advanced Index Statistics Query
I have noticed that running UPDATE STATISTICS without specifying any samplin
g
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UP
DATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pr...5/qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampli
ng
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBC
C
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hen
ce
> lessening the usefulness of the statistics. The values for EQ_ROWS are al
so
> much less accurate, which I suppose you'd expect with a smaller sample siz
e.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling.
UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pr...5/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE
option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>
g
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UP
DATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pr...5/qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampli
ng
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBC
C
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hen
ce
> lessening the usefulness of the statistics. The values for EQ_ROWS are al
so
> much less accurate, which I suppose you'd expect with a smaller sample siz
e.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling.
UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pr...5/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE
option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>
Advanced Index Statistics Query
I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
> >I have noticed that running UPDATE STATISTICS without specifying any sampling
> > options, thus accepting the default selected by SQL Server, can reduce the
> > level of detail contained within the index distribution statistics.
> >
> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
> > much less accurate, which I suppose you'd expect with a smaller sample size.
> > However, I do not understand what the optimizer gains from reducing the
> > number of steps during an UPDATE STATISTICS execution.
> >
> > Kind Regards
> >
> > Andrew Pike
> > --
> > SQL Server DBA
> > UBS IB
> >
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS
>> without
>> specifying sampling options *will* sample:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
>> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>> >I have noticed that running UPDATE STATISTICS without specifying any sampling
>> > options, thus accepting the default selected by SQL Server, can reduce the
>> > level of detail contained within the index distribution statistics.
>> >
>> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
>> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
>> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
>> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
>> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
>> > much less accurate, which I suppose you'd expect with a smaller sample size.
>> > However, I do not understand what the optimizer gains from reducing the
>> > number of steps during an UPDATE STATISTICS execution.
>> >
>> > Kind Regards
>> >
>> > Andrew Pike
>> > --
>> > SQL Server DBA
>> > UBS IB
>> >
>>
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
> >I have noticed that running UPDATE STATISTICS without specifying any sampling
> > options, thus accepting the default selected by SQL Server, can reduce the
> > level of detail contained within the index distribution statistics.
> >
> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
> > much less accurate, which I suppose you'd expect with a smaller sample size.
> > However, I do not understand what the optimizer gains from reducing the
> > number of steps during an UPDATE STATISTICS execution.
> >
> > Kind Regards
> >
> > Andrew Pike
> > --
> > SQL Server DBA
> > UBS IB
> >
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS
>> without
>> specifying sampling options *will* sample:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
>> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>> >I have noticed that running UPDATE STATISTICS without specifying any sampling
>> > options, thus accepting the default selected by SQL Server, can reduce the
>> > level of detail contained within the index distribution statistics.
>> >
>> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
>> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
>> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
>> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
>> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
>> > much less accurate, which I suppose you'd expect with a smaller sample size.
>> > However, I do not understand what the optimizer gains from reducing the
>> > number of steps during an UPDATE STATISTICS execution.
>> >
>> > Kind Regards
>> >
>> > Andrew Pike
>> > --
>> > SQL Server DBA
>> > UBS IB
>> >
>>
Thursday, March 8, 2012
aduit connections to SQL Server
On sql 2005 is there any audit logs on by default that log connections to sq
l
server and what database the connection was to.
If not on, is there one that you can turn on.
Not to long ago we had some records deleted out of the sql database
mysteriously. I am trying to find out how that happened and to keep it from
happening again.There is a default trace in SQL Server 2005 that is enabled
by default. You can find information about the default trace
in books online and this link has more info:
http://www.mssqltips.com/tip.asp?tip=1111
-Sue
On Wed, 14 Feb 2007 08:49:05 -0800, Andrew
<Andrew@.discussions.microsoft.com> wrote:
>On sql 2005 is there any audit logs on by default that log connections to s
ql
>server and what database the connection was to.
>If not on, is there one that you can turn on.
>Not to long ago we had some records deleted out of the sql database
>mysteriously. I am trying to find out how that happened and to keep it from
>happening again.|||Andrew
In addition on Sue's reply
> Not to long ago we had some records deleted out of the sql database
> mysteriously. I am trying to find out how that happened and to keep it
> from
> happening again.
You can use
1) DDL Triggers
2) Event notifications
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:8B1D1BB3-9EC2-4C31-87FA-064A1FB5C2E0@.microsoft.com...
> On sql 2005 is there any audit logs on by default that log connections to
> sql
> server and what database the connection was to.
> If not on, is there one that you can turn on.
> Not to long ago we had some records deleted out of the sql database
> mysteriously. I am trying to find out how that happened and to keep it
> from
> happening again.
>
l
server and what database the connection was to.
If not on, is there one that you can turn on.
Not to long ago we had some records deleted out of the sql database
mysteriously. I am trying to find out how that happened and to keep it from
happening again.There is a default trace in SQL Server 2005 that is enabled
by default. You can find information about the default trace
in books online and this link has more info:
http://www.mssqltips.com/tip.asp?tip=1111
-Sue
On Wed, 14 Feb 2007 08:49:05 -0800, Andrew
<Andrew@.discussions.microsoft.com> wrote:
>On sql 2005 is there any audit logs on by default that log connections to s
ql
>server and what database the connection was to.
>If not on, is there one that you can turn on.
>Not to long ago we had some records deleted out of the sql database
>mysteriously. I am trying to find out how that happened and to keep it from
>happening again.|||Andrew
In addition on Sue's reply
> Not to long ago we had some records deleted out of the sql database
> mysteriously. I am trying to find out how that happened and to keep it
> from
> happening again.
You can use
1) DDL Triggers
2) Event notifications
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:8B1D1BB3-9EC2-4C31-87FA-064A1FB5C2E0@.microsoft.com...
> On sql 2005 is there any audit logs on by default that log connections to
> sql
> server and what database the connection was to.
> If not on, is there one that you can turn on.
> Not to long ago we had some records deleted out of the sql database
> mysteriously. I am trying to find out how that happened and to keep it
> from
> happening again.
>
ADP dont shows table from MSDE
Hi,
I created one blank ADP to connect default tempdb database on MSDE on server ASHFAQUEPC (name of my msde server). Tested the connection was successful.
I created a table called Testtable from command prompt (MSSQL/Binn) in default tempdb of MSDE using following lines:
Binn>osql/U sa mypassword
1> use tempdb
1> go
1> Create table Testtable (Cnum integer NOT NULL PRIMARY KEY, City Varchar(15))
1> go
Table Created successfully then I added 2-3 records using following lines;
1> INSERT INTO testable (cnum, city)
1> values (1, abc)
1> go
1 Record affected and I did same to add 2-3 more records. Then I used following lines to see all the records I entered.
1> select * from testable
1> go
And a list of records displayed at command prompt. After exit from command prompt I found a Testtable appeared in the above ADP.
But next day when I opened my pc and opened ADP, I did not found the Testtable even though I re-connected to tempdb on MSDE from File menu. I reached then to command prompt to see whether the table itself is existing. Strange it was not there.
Can somebody tell me how can I get connected to testable automatically when I open my ADP? Where might have gone wrong? Do I need to create table again and input records?
I want to make sure before I do anything with my real dbs.
Any help would be highly appreciated.
With kind regards,
Ashfaque :confused:tempdb is recreated everytime sql server restarts. msde restarts when the last connection is closed. create a database with the create database command and create your table in there.|||Thanks Thrasymachus,
IC,
I will create my own db from scratch.
Thanks again....
Wiith kind regards,
Ashfaque|||You should definitely not be mucking around in TEMPDB, MASTER, or MSDB. Even MODEL should not be modified without good cause. Leave the system databases alone, and I'd advise you to read the manual BEFORE trying to fly the plane.|||What the blind dude is saying (with his usual grace and elegance), is that you really need to create a datbase for your purposes, and create all of you objects in there.
tempdb is a critical part of the sql server architecture that allows it to manage your "temporary" objects you can create. Temp tables, derived tables, etc. You should not interfere with it's operation.
btw, have you had a look at SQL Server Express and XM Manager?
It's still beta, but it should be coming out full time soon.
http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm|||Thanks blindman & Brett Kaiser for your advice.
I was not aware of behavior of these 4 default models on MSDE.
I upsized my MDB and it successfully created ADP and found steady. I can now go on to take further actions over it. I am close to mid-level carrier in handling MDBs in form of FE and BE. But as a little advance step, I used MSDE and I hope I could deal with it successfully with the help you Genius guys.
btw, have you had a look at SQL Server Express and XM Manager?
No, I did not take a look at SQL Server Express and XM Manager but I will do so.
Thanks again for you people..
With kind regards,
Ashfaque|||One more question...
I upsized the tables on MSDE successfully keeping the current db as MDB.
Why I dont know , the record in my subform is appearing but I can not enter new records any more..(Be is on MSDE). If my tables are on local (Not on MSDE server) it works fine.
Any idea ...?
With kind regards,
Ashfaque|||Could be a permissions issue with your login. Do you get any error messages?
I created one blank ADP to connect default tempdb database on MSDE on server ASHFAQUEPC (name of my msde server). Tested the connection was successful.
I created a table called Testtable from command prompt (MSSQL/Binn) in default tempdb of MSDE using following lines:
Binn>osql/U sa mypassword
1> use tempdb
1> go
1> Create table Testtable (Cnum integer NOT NULL PRIMARY KEY, City Varchar(15))
1> go
Table Created successfully then I added 2-3 records using following lines;
1> INSERT INTO testable (cnum, city)
1> values (1, abc)
1> go
1 Record affected and I did same to add 2-3 more records. Then I used following lines to see all the records I entered.
1> select * from testable
1> go
And a list of records displayed at command prompt. After exit from command prompt I found a Testtable appeared in the above ADP.
But next day when I opened my pc and opened ADP, I did not found the Testtable even though I re-connected to tempdb on MSDE from File menu. I reached then to command prompt to see whether the table itself is existing. Strange it was not there.
Can somebody tell me how can I get connected to testable automatically when I open my ADP? Where might have gone wrong? Do I need to create table again and input records?
I want to make sure before I do anything with my real dbs.
Any help would be highly appreciated.
With kind regards,
Ashfaque :confused:tempdb is recreated everytime sql server restarts. msde restarts when the last connection is closed. create a database with the create database command and create your table in there.|||Thanks Thrasymachus,
IC,
I will create my own db from scratch.
Thanks again....
Wiith kind regards,
Ashfaque|||You should definitely not be mucking around in TEMPDB, MASTER, or MSDB. Even MODEL should not be modified without good cause. Leave the system databases alone, and I'd advise you to read the manual BEFORE trying to fly the plane.|||What the blind dude is saying (with his usual grace and elegance), is that you really need to create a datbase for your purposes, and create all of you objects in there.
tempdb is a critical part of the sql server architecture that allows it to manage your "temporary" objects you can create. Temp tables, derived tables, etc. You should not interfere with it's operation.
btw, have you had a look at SQL Server Express and XM Manager?
It's still beta, but it should be coming out full time soon.
http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm|||Thanks blindman & Brett Kaiser for your advice.
I was not aware of behavior of these 4 default models on MSDE.
I upsized my MDB and it successfully created ADP and found steady. I can now go on to take further actions over it. I am close to mid-level carrier in handling MDBs in form of FE and BE. But as a little advance step, I used MSDE and I hope I could deal with it successfully with the help you Genius guys.
btw, have you had a look at SQL Server Express and XM Manager?
No, I did not take a look at SQL Server Express and XM Manager but I will do so.
Thanks again for you people..
With kind regards,
Ashfaque|||One more question...
I upsized the tables on MSDE successfully keeping the current db as MDB.
Why I dont know , the record in my subform is appearing but I can not enter new records any more..(Be is on MSDE). If my tables are on local (Not on MSDE server) it works fine.
Any idea ...?
With kind regards,
Ashfaque|||Could be a permissions issue with your login. Do you get any error messages?
Tuesday, March 6, 2012
ADOMD.Connection error
We are getting this error: 'ADODB.Connection: Provider is not specified
and there is no designated default provider' on a 64 bit machine with
both Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript
that backs up our Olap databases. The script (below) works fine on a 32
bit server with Sql 2K installed.
Function QueryDatabaseList()
Dim oConnectionServer
Dim oRS
Dim arrDatabase
Set oConnectionServer =CreateObject("ADODB.Connection")
oConnectionServer.ConnectionString = "Data Source=" & ServerName &
"; Provider=MSOLAP.2"
oConnectionServer.Open
Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
arrDatabase = oRs.GetRows
oConnectionServer.Close
Set oConnectionServer = Nothing
QueryDatabaseList = arrDatabase
End Function
I am pretty sure the problem has to do with the fact that the right
driver can not be found. But which one?
Thanks in advance,
Koni.
MSOLAP.2 is for AS2000
so try MSOLAP.3 for SSAS2005
"Koni" <kkogan@.haiint.com> wrote in message
news:uAgqmPoPHHA.1152@.TK2MSFTNGP03.phx.gbl...
> We are getting this error: 'ADODB.Connection: Provider is not specified
> and there is no designated default provider' on a 64 bit machine with both
> Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript that
> backs up our Olap databases. The script (below) works fine on a 32 bit
> server with Sql 2K installed.
> Function QueryDatabaseList()
> Dim oConnectionServer
> Dim oRS
> Dim arrDatabase
>
> Set oConnectionServer =CreateObject("ADODB.Connection")
> oConnectionServer.ConnectionString = "Data Source=" & ServerName & ";
> Provider=MSOLAP.2"
> oConnectionServer.Open
> Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
> arrDatabase = oRs.GetRows
> oConnectionServer.Close
> Set oConnectionServer = Nothing
> QueryDatabaseList = arrDatabase
> End Function
> I am pretty sure the problem has to do with the fact that the right driver
> can not be found. But which one?
>
> Thanks in advance,
> Koni.
and there is no designated default provider' on a 64 bit machine with
both Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript
that backs up our Olap databases. The script (below) works fine on a 32
bit server with Sql 2K installed.
Function QueryDatabaseList()
Dim oConnectionServer
Dim oRS
Dim arrDatabase
Set oConnectionServer =CreateObject("ADODB.Connection")
oConnectionServer.ConnectionString = "Data Source=" & ServerName &
"; Provider=MSOLAP.2"
oConnectionServer.Open
Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
arrDatabase = oRs.GetRows
oConnectionServer.Close
Set oConnectionServer = Nothing
QueryDatabaseList = arrDatabase
End Function
I am pretty sure the problem has to do with the fact that the right
driver can not be found. But which one?
Thanks in advance,
Koni.
MSOLAP.2 is for AS2000
so try MSOLAP.3 for SSAS2005
"Koni" <kkogan@.haiint.com> wrote in message
news:uAgqmPoPHHA.1152@.TK2MSFTNGP03.phx.gbl...
> We are getting this error: 'ADODB.Connection: Provider is not specified
> and there is no designated default provider' on a 64 bit machine with both
> Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript that
> backs up our Olap databases. The script (below) works fine on a 32 bit
> server with Sql 2K installed.
> Function QueryDatabaseList()
> Dim oConnectionServer
> Dim oRS
> Dim arrDatabase
>
> Set oConnectionServer =CreateObject("ADODB.Connection")
> oConnectionServer.ConnectionString = "Data Source=" & ServerName & ";
> Provider=MSOLAP.2"
> oConnectionServer.Open
> Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
> arrDatabase = oRs.GetRows
> oConnectionServer.Close
> Set oConnectionServer = Nothing
> QueryDatabaseList = arrDatabase
> End Function
> I am pretty sure the problem has to do with the fact that the right driver
> can not be found. But which one?
>
> Thanks in advance,
> Koni.
ADOMD.Connection error
We are getting this error: 'ADODB.Connection: Provider is not specified
and there is no designated default provider' on a 64 bit machine with
both Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript
that backs up our Olap databases. The script (below) works fine on a 32
bit server with Sql 2K installed.
Function QueryDatabaseList()
Dim oConnectionServer
Dim oRS
Dim arrDatabase
Set oConnectionServer =CreateObject("ADODB.Connection")
oConnectionServer.ConnectionString = "Data Source=" & ServerName &
"; Provider=MSOLAP.2"
oConnectionServer.Open
Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
arrDatabase = oRs.GetRows
oConnectionServer.Close
Set oConnectionServer = Nothing
QueryDatabaseList = arrDatabase
End Function
I am pretty sure the problem has to do with the fact that the right
driver can not be found. But which one?
Thanks in advance,
Koni.MSOLAP.2 is for AS2000
so try MSOLAP.3 for SSAS2005
"Koni" <kkogan@.haiint.com> wrote in message
news:uAgqmPoPHHA.1152@.TK2MSFTNGP03.phx.gbl...
> We are getting this error: 'ADODB.Connection: Provider is not specified
> and there is no designated default provider' on a 64 bit machine with both
> Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript that
> backs up our Olap databases. The script (below) works fine on a 32 bit
> server with Sql 2K installed.
> Function QueryDatabaseList()
> Dim oConnectionServer
> Dim oRS
> Dim arrDatabase
>
> Set oConnectionServer =CreateObject("ADODB.Connection")
> oConnectionServer.ConnectionString = "Data Source=" & ServerName & ";
> Provider=MSOLAP.2"
> oConnectionServer.Open
> Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
> arrDatabase = oRs.GetRows
> oConnectionServer.Close
> Set oConnectionServer = Nothing
> QueryDatabaseList = arrDatabase
> End Function
> I am pretty sure the problem has to do with the fact that the right driver
> can not be found. But which one?
>
> Thanks in advance,
> Koni.
and there is no designated default provider' on a 64 bit machine with
both Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript
that backs up our Olap databases. The script (below) works fine on a 32
bit server with Sql 2K installed.
Function QueryDatabaseList()
Dim oConnectionServer
Dim oRS
Dim arrDatabase
Set oConnectionServer =CreateObject("ADODB.Connection")
oConnectionServer.ConnectionString = "Data Source=" & ServerName &
"; Provider=MSOLAP.2"
oConnectionServer.Open
Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
arrDatabase = oRs.GetRows
oConnectionServer.Close
Set oConnectionServer = Nothing
QueryDatabaseList = arrDatabase
End Function
I am pretty sure the problem has to do with the fact that the right
driver can not be found. But which one?
Thanks in advance,
Koni.MSOLAP.2 is for AS2000
so try MSOLAP.3 for SSAS2005
"Koni" <kkogan@.haiint.com> wrote in message
news:uAgqmPoPHHA.1152@.TK2MSFTNGP03.phx.gbl...
> We are getting this error: 'ADODB.Connection: Provider is not specified
> and there is no designated default provider' on a 64 bit machine with both
> Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript that
> backs up our Olap databases. The script (below) works fine on a 32 bit
> server with Sql 2K installed.
> Function QueryDatabaseList()
> Dim oConnectionServer
> Dim oRS
> Dim arrDatabase
>
> Set oConnectionServer =CreateObject("ADODB.Connection")
> oConnectionServer.ConnectionString = "Data Source=" & ServerName & ";
> Provider=MSOLAP.2"
> oConnectionServer.Open
> Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
> arrDatabase = oRs.GetRows
> oConnectionServer.Close
> Set oConnectionServer = Nothing
> QueryDatabaseList = arrDatabase
> End Function
> I am pretty sure the problem has to do with the fact that the right driver
> can not be found. But which one?
>
> Thanks in advance,
> Koni.
Thursday, February 9, 2012
Admin password for new installtion
Hi,
I just installed VS.NET 2005 on my computer and along the way SQL Server Express edition got installed too. What is the default sa password right out of the box? I actually want to use Windows authentication but was not able to set my domain account in there w/ administrative rights. I'd appreciate some help here.
Thanks,
Sam
My guess is that the default installation here uses Windows authentication and does not set an sa password. You can use any of the management tools (Management Tools Express in Express Advanced) or T-SQL to set the sa password and change the authentication method.
Thanks,
Sam Lester (MSFT)
Subscribe to:
Posts (Atom)