Difference between Partial Backup (SQL 2005) and File Group Backup

What is the difference between "Files aned File Groups" backup and Partial Backup?

Looks like both are same.. Please comment.

A File Group backup is a backup which only saves the data from the named file group(s).

A Partial backup is also a backup which only saves the data from some file groups, but it usually refers to the syntax:

BACKUP DATABASE foo READ_WRITE_FILEGROUPS TO....

which backs up only the primary filegroup and any read-write filegroups.

If you have a good backup of the read-only filegroups, you don't need to save them at every backup, which limits volume.

So, a Partial backup is a special case of a File Group backup.

Diff. performance in Query Analyzer than when using stored procedure

Hi group,

I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored procedure
instead, calling the stored proc from query analyzer, then it takes
12-17 seconds.

Here is what I execute in Query Analyzer when bypassing the stored
procedure:

USE Verizon
GO
DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Here is what I execute in Query Analyzer when calling the stored
procedure:

DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
EXEC Verizon.dbo.baddebt_phonelookup @.phonenumber

Here is the script that created the stored procedure itself:

CREATE PROCEDURE dbo.baddebt_phonelookup @.phonenumber varchar(15)
AS

SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Using SQL Profiler, I also have the execution trees for each of these
two different ways of running the same query.

Here is the Execution tree when running the whole query in the
analyzer, bypassing the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Verizon].[dbo].[BadDebt]))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),
SEEK:([BadDebt].[Telephone_Number]=[@.phonenumber]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),
SEEK:([BadDebt].[Telephone_Number_Redef]=[@.phonenumber]) ORDERED
FORWARD)
------------

Finally, here is the execution tree when calling the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
------------

Thanks for any help on my path to optimizing this query for our
production environment.

Regards,

Warren Wright
Scorex Development Teamwarren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0308051401.2e65bb80@.posting.google.com>...
> Hi group,
> I have a select statement that if run against a 1 million record
> database directly in query analyzer takes less than 1 second.
> However, if I execute the select statement in a stored procedure
> instead, calling the stored proc from query analyzer, then it takes
> 12-17 seconds.

<snip
One possible reason is parameter sniffing - see here:

http://groups.google.com/groups?sel...7&output=gplain

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0308060118.46c12f2e@.posting.google.com>...
> One possible reason is parameter sniffing - see here:
> http://groups.google.com/groups?sel...7&output=gplain
> Simon

Wow. Thats a bit of an eye opener. It makes me wonder how best to
make sure a decent plan is chosen by SQL Server, and the answer seems
to be to make it recompile the stored procedure every time ?

or is there a way to make SQL simply use the index at all times? I'd
hate to spend a lot of time on my dev machine getting the stored
procedure to run correctly on a million record table, only to port it
to my production machine and have it take forever on the 33 million
record database because of some magically crafted execution plan :-)

Thanks,

Warren|||Here is something else that I don't understand. The stored procedure
I listed above compares a phone number that is passed in against a
Telephone_Number column that is 15 digits long, and against a computed
column (Telephone_Number_Redef), that is the left 10 digits of the
Telephone_Number column.

This is because sometimes our client passes in a 10 digit number, and
sometimes a 15 digit version that includes some check digits on the
end (Don't ask).

Anyway, in the execution plan for when the stored proc is executing, I
see the following:

----------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
----------

It appears to be recomputing the Telephone_Number_Redef column values
on the fly, instead of using the values already present. The
Telephone_Number_Redef column is indexed specifically to allow that
second comparison in the WHERE statement to be a SARG, but it seems
this is being ignored.

Is it being ignored because SQL had already decided to do a table
scan, and so though it might as well speed things up by not scanning
both columns? or is SQL doing a table scan because it thinks it needs
to re-compute the values for Telephone_Number_Redef on the fly?

Argh.

Thanks,

Warren Wright
Scorex Development Team
Dallas|||More follow-up on this issue, to help you experts analyze what's going
on here.

I've spent the day trying various things, with no success. I tried
using hints to suggest that the index on Telephone_Number_Redef be
used, which results in an error stating the stored procedure couldn't
be executed due to an unworkable hint.

I've tried declaring a new variable in the stored proc with a value
set equal to the @.phonenumber input, so SQL couldn't optimize based on
the actual value being passed in.

I've tried changing the index for the computed column to be a
clustered index.

I only wish I could simply tell SQL to use the same execution plan it
uses when I run the query from the analyzer!! All problems would be
solved!

No matter what, if I run the query from query analyzer, the response
time is a few milliseconds. If I run the stored procedure, the
response time is at least 17 seconds due to a completely suboptimal
execution plan (where the Telephone_Number_Redef's index isn't used at
all).

Introducing the new version of the stored proc with the OR statement
that checks against the computed column as well bogs down the
production server, and results in timeouts and app errors for our
client.

Frustrated,

Warren|||[posted and mailed, please reply in news]

Warren Wright (warren.wright@.us.scorex.com) writes:
> Here is something else that I don't understand. The stored procedure
> I listed above compares a phone number that is passed in against a
> Telephone_Number column that is 15 digits long, and against a computed
> column (Telephone_Number_Redef), that is the left 10 digits of the
> Telephone_Number column.

Computed column? Which you have an index on? Aha!

While Bart's article on parameter sniffing is good reading it is not
the answer here. Index on computed columns (as well on views) can
only be used if these SET options are ON: ANSI_NULLS, QUOTED_IDENTIFIER,
ANSI_WARNINGS, ARITHABORT, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL.
And NUMERIC_ROUNDABORT be OFF.

The killer here is usually QUOTED_IDENTIFIER. That option, together
with ANSI_NULLS is saved with the procedure, so that the run-time
setting does not apply, but the setting saved with the procedure.
QUOTED_IDENTIFIER is ON by default with ODBC and OLE DB, as well
with Query Analyzer. But OSQL and Enterprise Manager turns it off.
So you need to make sure that the procedure is created with
QUOTED_IDENTIFIER on.

You can review the current setting with

select objectproperty(object_id('your_sp'), 'IsQuotedIdentOn')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Did Uninstall/Reinstall of SQL Server. Now databases are under WRONG servers

I have a Server Group with two servers; my "local" server (computer name)
and another "older" server that I was using for a particular purpose some
time ago. After doing an Uninstal/Reinstall the databases that WERE under
my "local" server are now attached to the other server! And the databases
from the older server are attached to my "local" server!
''?
TIA,
Larry WoodsDo you mean that the database file moved off to another machine and attached themselves to that
other machine? Or are you talking about instances? Perhaps there's some configuration in Client
Network Utility?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Larry Woods" <larry@.lwoods.com> wrote in message news:%23z3xmIEmDHA.3320@.tk2msftngp13.phx.gbl...
> I have a Server Group with two servers; my "local" server (computer name)
> and another "older" server that I was using for a particular purpose some
> time ago. After doing an Uninstal/Reinstall the databases that WERE under
> my "local" server are now attached to the other server! And the databases
> from the older server are attached to my "local" server!
> ''?
> TIA,
> Larry Woods
>|||Hi Larry,
Is this issue related to the problem in your previous post? If so, I would
like to gather and research them together. If I have misunderstood, please
feel free to let me know.
What is the versions of SQL Server (Local and Older), Service Pack? I
understand that there are two servers under the server group in the SQL
Server Enterprise Manager. I would like you to provide more information so
that I can narrow down this issue.
1. Is the "older" server named instance? Are the Local Server and Older
Server installed on the same machine?
2. What do you mean that "Uninstall/Reinstall" database? Do you mean
detach/attach database? Can you describe it in detail?
3. According to your description, I am not sure what your exact problem is.
Please feel free to let us know your accurate concerns.
Here is a useful article below:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Please read my response to myself.
Thanks.
Larry Woods
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ehno7ZGmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Do you mean that the database file moved off to another machine and
attached themselves to that
> other machine? Or are you talking about instances? Perhaps there's some
configuration in Client
> Network Utility?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
news:%23z3xmIEmDHA.3320@.tk2msftngp13.phx.gbl...
> > I have a Server Group with two servers; my "local" server (computer
name)
> > and another "older" server that I was using for a particular purpose
some
> > time ago. After doing an Uninstal/Reinstall the databases that WERE
under
> > my "local" server are now attached to the other server! And the
databases
> > from the older server are attached to my "local" server!
> >
> > ''?
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||I need to apologize for my ignorance. First, I DO have two servers in my
group. One of the servers is on another computer. When I reinstalled SQL
Server it looks like it included a "default" set of databases, "overwriting"
the databases that were in my default (local) SQL Server. I did find the
"old" databases in my Data folder so I guess that I have to reattached each
one individually, right? And, what I had mistaken for "copying" databases
was incorrect. The databases in the "foreign" server (the other computer)
were approximately the same ones as I had in my local server so I
misinterpretted what I saw.
Please verify that the reinstallation of SQL Server includes a default set
of databases. True? Also, does my explanation of what seems to have
happened make sense?
Thanks again.
Larry Woods
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23z3xmIEmDHA.3320@.tk2msftngp13.phx.gbl...
> I have a Server Group with two servers; my "local" server (computer name)
> and another "older" server that I was using for a particular purpose some
> time ago. After doing an Uninstal/Reinstall the databases that WERE under
> my "local" server are now attached to the other server! And the databases
> from the older server are attached to my "local" server!
> ''?
> TIA,
> Larry Woods
>|||Please read my response to myself.
Thanks.
Larry Woods
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:t6L%233TLmDHA.1408@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Is this issue related to the problem in your previous post? If so, I would
> like to gather and research them together. If I have misunderstood, please
> feel free to let me know.
> What is the versions of SQL Server (Local and Older), Service Pack? I
> understand that there are two servers under the server group in the SQL
> Server Enterprise Manager. I would like you to provide more information so
> that I can narrow down this issue.
> 1. Is the "older" server named instance? Are the Local Server and Older
> Server installed on the same machine?
> 2. What do you mean that "Uninstall/Reinstall" database? Do you mean
> detach/attach database? Can you describe it in detail?
> 3. According to your description, I am not sure what your exact problem
is.
> Please feel free to let us know your accurate concerns.
> Here is a useful article below:
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL
Server
> http://support.microsoft.com/?id=314546.
> I am standing by for your response.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. According to your description, I apologize that I
am still not sure what the problem is. I think we need to make something
clear firstly.
1. What is the version of the both SQL Server (Service Pack)? Please review
[Item 1] to get the information about obtaining the version of SQL Server
(Service Pack).
2. You wrote "When I reinstalled SQL Serve", which Server you
"reinstalled"? Also you wrote: "One of the servers is on another computer".
Do you mean the "reinstall" indicate register SQL Server using SQL Server
Enterprise Manager?
For additional information regarding register SQL Server, please refer to
the following article on SQL Server Books Online.
Topic: "How to register a server (Enterprise Manager)"
Or you mean that you reinstall the SQL Server on the machine where the
local SQL Server located? Create a new instance or update the original
instance? If you create a new instance, you need to specify the reinstalled
SQL Server as named instance. Did you do it?
This step-by-step guide describes how to install a SQL Server 2000 basic
local installation.
303747 HOW TO: Install SQL Server 2000 - Basic Local Installation
http://support.microsoft.com/?id=303747
If I have misunderstood, please feel free to let me know and describe how
you reinstall the SQL Server.
Thank you for collecting the above information. It will definitely speed up
our progress. When we make the above things clear, we are able to
troubleshoot this problem more efficiently. Thanks for your understanding.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
[Item 1]
How to obtain the version of SQL Server (Service Pack)?
Please perform the following SQL statements using Query Analyzer.
select @.@.version
go
select serverproperty('productlevel')
go
For additional information regarding obtaining the version of SQL Server
(Service Pack), please refer to the following article on SQL Server Books
Online:
Topic: "@.@.VERSION"
Topic: "SERVERPROPERTY"|||Hi Larry,
Thanks for your feedback. Now, I still do not know the version of SQL
Server you want to install and the original SQL Server version. I assume
that you want to install MSDE 2000 on your computer which already has SQL
Server 2000. If I have misunderstood, please feel free to let me know.
For additional information regarding identifying the version of SQL Server,
please refer to the following article:
321185 HOW TO: Identify Your SQL Server Service Pack Version and Edition
http://support.microsoft.com/?id=321185
To install MSDE 2000 onto a machine, which already has SQL Server, we need
to make it clear that this new instance is only able to be installed as a
named instance.
I performed a test trying to install the MSDE 2000 on my machine, which
already has SQL Server 2000.
1. Download SQL2KDeskSP3 at the following URL:
Microsoft SQL Server 2000 Service Pack 3
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=9032
f608-160a-4537-a2b6-4cb265b80766
SQL2kdesksp3.exe includes all of the files that are required to install a
new instance of Desktop Engine (.msi files), to upgrade all existing
instances of Desktop Engine (.msp files), as well as to consume merge
modules (.msm files) into applications.
2. Configure setup options for MSDE 2.0 in the file Setup.ini.
-- Setup.ini --
[Options]
TARGETDIR="C:\Program Files\Microsoft SQL Server\Mssql$MyInstance\Binn\"
DATADIR="C:\Program Files\Microsoft SQL Server\Mssql$MyInstance\Data\"
INSTANCENAME="MyInstance"
SAPWD="sytest"
--
"sytest" is the password of the sa account. "MyInstance" is the name of the
instance.
For additional information regarding Customizing Desktop Engine Setup.exe,
please refer to the following article at SQL Server Books Online.
Topic: "Customizing Desktop Engine Setup.exe"
3. Double click Setup.exe to install MSDE 2.0 (named instance).
We can check to see MSSQL$ MYINSTANCE service is in the Services list. We
can also register the new named instance using <Computer Name>\MYINSTANCE
as the instance name via SQL Server Enterprise Manager.
It works on my side. Does it work on your side?
Also, I found some related articles below:
317328 HOW TO: Troubleshoot a SQL Server Desktop Engine 2000 Installation
and
http://support.microsoft.com/?id=317328
324998 HOW TO: Obtain and Install SQL Server 2000 Desktop Engine (MSDE 2000)
http://support.microsoft.com/?id=324998
814463 HOWTO: Specify a Strong SA Password When You Install SQL Server 2000
http://support.microsoft.com/?id=814463
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I presently have SQL Server 2000 installed on my computer. I want to
install MSDE 2000. I will then be deploying MSDE 2000 with my application
at a later date. (Running XP Pro... The computer that will be receiving
MSDE will be running Win 2000 Server)
Thanks, again.
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:3eosgeSnDHA.2624@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. Now, I still do not know the version of SQL
> Server you want to install and the original SQL Server version. I assume
> that you want to install MSDE 2000 on your computer which already has SQL
> Server 2000. If I have misunderstood, please feel free to let me know.
> For additional information regarding identifying the version of SQL
Server,
> please refer to the following article:
> 321185 HOW TO: Identify Your SQL Server Service Pack Version and Edition
> http://support.microsoft.com/?id=321185
>
> To install MSDE 2000 onto a machine, which already has SQL Server, we need
> to make it clear that this new instance is only able to be installed as a
> named instance.
> I performed a test trying to install the MSDE 2000 on my machine, which
> already has SQL Server 2000.
> 1. Download SQL2KDeskSP3 at the following URL:
> Microsoft SQL Server 2000 Service Pack 3
>
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=9032
> f608-160a-4537-a2b6-4cb265b80766
> SQL2kdesksp3.exe includes all of the files that are required to install a
> new instance of Desktop Engine (.msi files), to upgrade all existing
> instances of Desktop Engine (.msp files), as well as to consume merge
> modules (.msm files) into applications.
>
> 2. Configure setup options for MSDE 2.0 in the file Setup.ini.
> -- Setup.ini --
> [Options]
> TARGETDIR="C:\Program Files\Microsoft SQL Server\Mssql$MyInstance\Binn\"
> DATADIR="C:\Program Files\Microsoft SQL Server\Mssql$MyInstance\Data\"
> INSTANCENAME="MyInstance"
> SAPWD="sytest"
> --
> "sytest" is the password of the sa account. "MyInstance" is the name of
the
> instance.
> For additional information regarding Customizing Desktop Engine Setup.exe,
> please refer to the following article at SQL Server Books Online.
> Topic: "Customizing Desktop Engine Setup.exe"
> 3. Double click Setup.exe to install MSDE 2.0 (named instance).
> We can check to see MSSQL$ MYINSTANCE service is in the Services list. We
> can also register the new named instance using <Computer Name>\MYINSTANCE
> as the instance name via SQL Server Enterprise Manager.
> It works on my side. Does it work on your side?
> Also, I found some related articles below:
> 317328 HOW TO: Troubleshoot a SQL Server Desktop Engine 2000 Installation
> and
> http://support.microsoft.com/?id=317328
> 324998 HOW TO: Obtain and Install SQL Server 2000 Desktop Engine (MSDE
2000)
> http://support.microsoft.com/?id=324998
> 814463 HOWTO: Specify a Strong SA Password When You Install SQL Server
2000
> http://support.microsoft.com/?id=814463
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||As far as downloading products, I have an MSDN Universal subscription. Are
all of these files on my CD's?
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:3eosgeSnDHA.2624@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. Now, I still do not know the version of SQL
> Server you want to install and the original SQL Server version. I assume
> that you want to install MSDE 2000 on your computer which already has SQL
> Server 2000. If I have misunderstood, please feel free to let me know.
> For additional information regarding identifying the version of SQL
Server,
> please refer to the following article:
> 321185 HOW TO: Identify Your SQL Server Service Pack Version and Edition
> http://support.microsoft.com/?id=321185
>
> To install MSDE 2000 onto a machine, which already has SQL Server, we need
> to make it clear that this new instance is only able to be installed as a
> named instance.
> I performed a test trying to install the MSDE 2000 on my machine, which
> already has SQL Server 2000.
> 1. Download SQL2KDeskSP3 at the following URL:
> Microsoft SQL Server 2000 Service Pack 3
>
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=9032
> f608-160a-4537-a2b6-4cb265b80766
> SQL2kdesksp3.exe includes all of the files that are required to install a
> new instance of Desktop Engine (.msi files), to upgrade all existing
> instances of Desktop Engine (.msp files), as well as to consume merge
> modules (.msm files) into applications.
>
> 2. Configure setup options for MSDE 2.0 in the file Setup.ini.
> -- Setup.ini --
> [Options]
> TARGETDIR="C:\Program Files\Microsoft SQL Server\Mssql$MyInstance\Binn\"
> DATADIR="C:\Program Files\Microsoft SQL Server\Mssql$MyInstance\Data\"
> INSTANCENAME="MyInstance"
> SAPWD="sytest"
> --
> "sytest" is the password of the sa account. "MyInstance" is the name of
the
> instance.
> For additional information regarding Customizing Desktop Engine Setup.exe,
> please refer to the following article at SQL Server Books Online.
> Topic: "Customizing Desktop Engine Setup.exe"
> 3. Double click Setup.exe to install MSDE 2.0 (named instance).
> We can check to see MSSQL$ MYINSTANCE service is in the Services list. We
> can also register the new named instance using <Computer Name>\MYINSTANCE
> as the instance name via SQL Server Enterprise Manager.
> It works on my side. Does it work on your side?
> Also, I found some related articles below:
> 317328 HOW TO: Troubleshoot a SQL Server Desktop Engine 2000 Installation
> and
> http://support.microsoft.com/?id=317328
> 324998 HOW TO: Obtain and Install SQL Server 2000 Desktop Engine (MSDE
2000)
> http://support.microsoft.com/?id=324998
> 814463 HOWTO: Specify a Strong SA Password When You Install SQL Server
2000
> http://support.microsoft.com/?id=814463
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback.
Firstly I would like to make the following things clear in my previous post:
SQL2kdesksp3.exe includes a MSDE 2000 with SP3. This MSDE 2000 is installed
in my test in the previous post. It can be downloaded at following URL:
Microsoft SQL Server 2000 Service Pack 3
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=9032
f608-160a-4537-a2b6-4cb265b80766
I just installed a MSDE 2000 with SP3 in my test and did not only install
Service Pack 3. If you have any further concerns, please feel free to let
me know.
Then, I understand you installed SQL Server 2000 and two MSDE instances on
your machine. I am not sure if they are installed successfully only
according to the screen shot. Please check to see if the related services
are in the service list (Start-->Control Panel-->Administrative
Tools-->Services), such as MSSQLSERVER, MSSQL$<INSTANCE NAME>. In this
issue, there should be 3 services (MSSQLSERVER service, 2 MSSQL$<INSTANCE
NAME> services).
If these services are in the services list, please start these services and
try to register the related instances in the SQL Server Enterprise Manager.
For named instance, please use <Computer Name>\<Instance Name> to register.
After registering these three instances, please check to see if the
databases under these services are normal.
If the databases under the instance are abnormal or you cannot see the
above service in the service list, I would like you to provide the
sqldiag.txt file so that I can understand the system environment on your
side. For additional information regarding sqldiag utility, please review
[Item 1]. You can send it to me at v-yshao@.microsoft.com.
Also, if you only want to setup MSDE 2000 on the machine that already has
SQL Server 2000. You can uninstall these 2 MSDE from your machine and try
to install MSDE 2000 mentioned in my test in the previous post.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
[Item 1]
How to obtain the Sqldiag.txt file
Please provide the Sqldiag.txt (by default located in \Mssql\Log) using
Sqldiag utility (Sqldiag.exe by default located in C:\Program
Files\Microsoft SQL Server\MSSQL\Binn). If you are unable to find the
Sqldiag.exe, please search the Sqldiag.exe file and run this utility. Then
please search the Sqldiag.txt file and provide it to me.|||Michael,
This is an important project for me but at the present time XP Pro is
"eating" my disk (6 Gig to 0 in less thatn 1 day--no reason!) and I am going
to be forced into reverting back to Windows 2000. This means rebuilding TWO
systems so I will be busy for a few days. I DEFINITELY will get back to
this as soon as I get rid of this "latest and greatest" of MS's operating
systems!!!!
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:sdCKEEunDHA.1548@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback.
> Firstly I would like to make the following things clear in my previous
post:
> SQL2kdesksp3.exe includes a MSDE 2000 with SP3. This MSDE 2000 is
installed
> in my test in the previous post. It can be downloaded at following URL:
> Microsoft SQL Server 2000 Service Pack 3
>
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=9032
> f608-160a-4537-a2b6-4cb265b80766
> I just installed a MSDE 2000 with SP3 in my test and did not only install
> Service Pack 3. If you have any further concerns, please feel free to let
> me know.
> Then, I understand you installed SQL Server 2000 and two MSDE instances on
> your machine. I am not sure if they are installed successfully only
> according to the screen shot. Please check to see if the related services
> are in the service list (Start-->Control Panel-->Administrative
> Tools-->Services), such as MSSQLSERVER, MSSQL$<INSTANCE NAME>. In this
> issue, there should be 3 services (MSSQLSERVER service, 2 MSSQL$<INSTANCE
> NAME> services).
> If these services are in the services list, please start these services
and
> try to register the related instances in the SQL Server Enterprise
Manager.
> For named instance, please use <Computer Name>\<Instance Name> to
register.
> After registering these three instances, please check to see if the
> databases under these services are normal.
> If the databases under the instance are abnormal or you cannot see the
> above service in the service list, I would like you to provide the
> sqldiag.txt file so that I can understand the system environment on your
> side. For additional information regarding sqldiag utility, please review
> [Item 1]. You can send it to me at v-yshao@.microsoft.com.
> Also, if you only want to setup MSDE 2000 on the machine that already has
> SQL Server 2000. You can uninstall these 2 MSDE from your machine and try
> to install MSDE 2000 mentioned in my test in the previous post.
> I am standing by for your response.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> [Item 1]
> How to obtain the Sqldiag.txt file
> Please provide the Sqldiag.txt (by default located in \Mssql\Log) using
> Sqldiag utility (Sqldiag.exe by default located in C:\Program
> Files\Microsoft SQL Server\MSSQL\Binn). If you are unable to find the
> Sqldiag.exe, please search the Sqldiag.exe file and run this utility. Then
> please search the Sqldiag.txt file and provide it to me.
>|||Hi Larry,
Thanks for your feedback. Your patience is appreciated.
For the problem with XP pro "eating" the disk, I would like you to check
the size of this folder (C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP). This folder usually contained all the SQL Server
backup files. According to your description, I suspected there are some
backup jobs performing on the background, which created the backup files
"eating" the disk. Does it resolve your problem?
If not, please check to see if there is worm virus on your computer.
Also, did you try my suggestions on your machine? I am standing by for your
response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I don't think there is ANY connection between SQL Server and the loss of
disk space...but maybe there is. In any case, I threw in the towel and
rebuilt the system with Win 2000. I now have a SQL Server Group with two
servers: (local) and DEVELOPMENT\VSdotNET. DEVELOPMENT is the name of my
computer. When I checked the Properties of the "VSdotNET" server I see that
it is MSDE. Does all of this sound right?
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:NAo8JWxnDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. Your patience is appreciated.
> For the problem with XP pro "eating" the disk, I would like you to check
> the size of this folder (C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP). This folder usually contained all the SQL Server
> backup files. According to your description, I suspected there are some
> backup jobs performing on the background, which created the backup files
> "eating" the disk. Does it resolve your problem?
> If not, please check to see if there is worm virus on your computer.
> Also, did you try my suggestions on your machine? I am standing by for
your
> response.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. It seems that you have rebuilt the system with
Win 2000 and the Windows XP has been removed. I understand that there are
two instances (Local and DEVELOPMENT\VSdotNET) in the SQL Server Group
using SQL Server Enterprise Manager. The DEVELOPMENT\VSdotNET instance's
version is MSDE. If I have misunderstood, please feel free to let me know.
Everything seems worked fine. According to your above description, I am
unable to find something abnormal. Currently, does any problem occur on
your side?
Please feel free to post it in the group if you need further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql

diamension relationship

Hi

I have a diamension called 'mechanism of injury' and measure injurycount situated in the injury measure group.

I defined a relation between mechanism of them using the regular relationship connecting using the mechanismofinjuryid key in both tables (mechanismofinjury and injury). I tried to deploy the cube and it gave me the following error.

Errors in the OLAP storage engine: The attribute key cannot be found: ....

I changed the error handling of cube by right clicking on the cube and going to processes and then customising the error handling to ignore the error. Now i could deploy the cube but the injurycount measure is not showing up correct results.

Before defining the relationship between the diamension i was getting injurycount=2541 which was correct.

After defining relationship between the diamensions i am getting injurycount=2526

When i carefully looked at the injury table i found out that in injury table there are 15 (2541 - 2526) records with mechanismofinjuryid=NULL. It seems like the analysis services do not take these records into account while calculating the measures.

I don't understand how do i handle the nulls without changing the error handling options.

Any help would be greatly appreciated.

-padu.

One way to resolve this problem is to create a missing member in the dimension like MissingDimMember.

When you load the fact table you use this missing member for fact table records with missing dimension members or dimension members that are not found in the dimension table.

It is also possible to configure this solution directly in SSAS2005.

Have a look in BOL: Defining the Unknown Member and Null Processing Properties

HTH

Thomas Ivarsson

Developpe axis on a graph as on a grid

Hi !
I have A question about the developpe of the axis on the graph.
I explain, when you use a matrix, you can developpe your group with a clic on (+) or (-)
, does it possible on a graph ?
For example, if I display on my axis few years, and if I want to developpe specially one, does it possible, the user clik on a (+) and my axis displays every month in my year...

I have another question, does it possible to have tooltip on a graph ?

Thank you for your answer...

Sorry this is currently not supported.

This kind of interactivity is considered for a future release.

-- Robert

Development Procedures

Hello,

Recently my partner in crime (so to speak) and I have been asked to develop procedures and/or methods for our software development group. Currently we're running SQL Server 8 with several Access apps and Crystal Reports. In a nutshell, I'm looking for resources.

We're supposed to develop a process (we're both DBA's) where our development team can formally submit new stored procedures, views, or whatever they've come up with to solve for buisiness requirements. It'll be our job to take thier scripts and execute them on the production database after ensuring someone has tested the logic and signed-off on it.

Unfortunately, I have been charged with developing this process (ie: development, sign-off, testing, etc..). Currently we have no procedures in place; until now my partner and I have been the primary deveopers (this is about to change). Are there any resources out there that could help me develop a formal process for our soon-comming developers?

Thanks,

Jason

Jason -

You can check out InformIT.com - they have lots of information there on a "professional" nature, not just technical info. I do the SQL Server "Guide", and I've got an article or two that might be useful.

Most of the Software Development articles will point towards a particular methodology or tool. I've found that most shops develop their own methods and call it an "SDLC" or Software Development Life Cycle. Also check out a book called "Code Complete".

Buck

Development Procedures

Hello,

Recently my partner in crime (so to speak) and I have been asked to develop procedures and/or methods for our software development group. Currently we're running SQL Server 8 with several Access apps and Crystal Reports. In a nutshell, I'm looking for resources.

We're supposed to develop a process (we're both DBA's) where our development team can formally submit new stored procedures, views, or whatever they've come up with to solve for buisiness requirements. It'll be our job to take thier scripts and execute them on the production database after ensuring someone has tested the logic and signed-off on it.

Unfortunately, I have been charged with developing this process (ie: development, sign-off, testing, etc..). Currently we have no procedures in place; until now my partner and I have been the primary deveopers (this is about to change). Are there any resources out there that could help me develop a formal process for our soon-comming developers?

Thanks,

Jason

Jason -

You can check out InformIT.com - they have lots of information there on a "professional" nature, not just technical info. I do the SQL Server "Guide", and I've got an article or two that might be useful.

Most of the Software Development articles will point towards a particular methodology or tool. I've found that most shops develop their own methods and call it an "SDLC" or Software Development Life Cycle. Also check out a book called "Code Complete".

Buck

developers cannot make databases

I need help with a permissions problem. I had my network admin create a AD
group with all my developers in it. I then went to the Enterprise Manager,
then to the Login screen and put that AD group into the the SQL Server. Nex
t, I gave that group the Se
rver Role of "Database Creators". There are no databases in the server othe
r than the standard Master, Model, MSDB and Tempdb. Non of these databases
are checked for access.
My developers are connecting to the SQL Server with the Server Explorer in .
NET2003 but cannot make any databases. We are using Windows Only security o
n the database server. The error message says SQLState 42000 and Error 4064
, Cannot open user default
database login failed.
There is no default database because they have not made one yet.Use Enterprise Manager to determine what the default database is for the
developers group. I'd set it to tempdb, to which everyone automatically has
access.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:A500F9D8-44C7-4488-A61E-96CFE5647932@.microsoft.com...
I need help with a permissions problem. I had my network admin create a AD
group with all my developers in it. I then went to the Enterprise Manager,
then to the Login screen and put that AD group into the the SQL Server.
Next, I gave that group the Server Role of "Database Creators". There are
no databases in the server other than the standard Master, Model, MSDB and
Tempdb. Non of these databases are checked for access.
My developers are connecting to the SQL Server with the Server Explorer in
.NET2003 but cannot make any databases. We are using Windows Only security
on the database server. The error message says SQLState 42000 and Error
4064, Cannot open user default database login failed.
There is no default database because they have not made one yet.

Determining windows grouop membership of a SQL login

Hello,
I'm trying to determine the windows group membership of a SQL login I've
created from a Windows login. Specifically, I'm trying to determine if the
login is in BUILTIN\Administrators or otherwise has admin permissions. For
example,
CREATE LOGIN <login> FROM WINDOWS WITH DEFAULT_DATABASE <database>
USE <database>
CREATE USER <login>
The CREATE USER call will fail if the login has admin privs because they
already have access under 'dbo'. So how do I go about determining the
membership of this login? sp_helplogins, sp_helpuser, sp_helpgroup don't
seem to provide the information I need.
Suggestions?
KeithKeith,
You could check with: is_srvrolemember ('sysadmin','Domain\Loginame')
RLF
"Keith" <keith@.alh.com> wrote in message
news:uBrsCyq2HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I'm trying to determine the windows group membership of a SQL login I've
> created from a Windows login. Specifically, I'm trying to determine if
> the login is in BUILTIN\Administrators or otherwise has admin permissions.
> For example,
> CREATE LOGIN <login> FROM WINDOWS WITH DEFAULT_DATABASE <database>
> USE <database>
> CREATE USER <login>
> The CREATE USER call will fail if the login has admin privs because they
> already have access under 'dbo'. So how do I go about determining the
> membership of this login? sp_helplogins, sp_helpuser, sp_helpgroup don't
> seem to provide the information I need.
> Suggestions?
> Keith
>|||Thanks Russell! That seems to do the trick!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:O5C3HQr2HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Keith,
> You could check with: is_srvrolemember ('sysadmin','Domain\Loginame')
> RLF
> "Keith" <keith@.alh.com> wrote in message
> news:uBrsCyq2HHA.2752@.TK2MSFTNGP06.phx.gbl...
>