Diff. transactional publications ; synchronizing at diif. times

Is it possible to have more than one transactional replication publications
which will be containing differenr list of tables and will be synchronizing
at different times.
If yes pls. tell me how ?
Thanks in advance.
I'm not too sure I follow the question, the process to create a second
publication is much the same as the first as far as the selection of tables
is concerned. Each one will have different jobs which can be scheduled. Make
sure the second publication is configured to have an independant
distribution agent before creating the subscription so the synchronization
times can be different.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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

Diff. between using XML DOM and SQL Server 2000

Please Can I know the difference between using XML DOM(in any application language) and using the XML services of SQL Server 2000 to create XML data from SQL Server 2000 database?
The DOM is a specific API to access data that is already XML or has an XML
representation. It is useful in close-coupled programming environments and
where navigational access is more important than data storage and data
access cost.
SQL Server 2000 generates the XML in a streaming way that then can be
processed in a streaming pipeline or can be loaded into a DOM. The streaming
generation provides a more memory-efficient, more performant and scalable
way to generate XML than buffering all the data in a DOM and then passing
the DOM.
Best regards
Michael
"Rani Ponmathi" <ranibackup@.yahoo.com> wrote in message
news:0DC00FE5-67FB-4B08-BD83-84DF0809EE0D@.microsoft.com...
> Please Can I know the difference between using XML DOM(in any application
> language) and using the XML services of SQL Server 2000 to create XML
> data from SQL Server 2000 database?

diff. between simple and full recovery

Hi all,
do anyone know what is the different between simple and full recovery mode
in database properties.
thanksI suggest you search for the terms in Books Online. They are well described
there. Very short:
Full recovery mode allow you to perform transaction log backups. When you pe
rform transaction log
backup, the transaction log will be emptied.
In simple recovery mode, SQL Server will empty ("truncate) the transaction l
og for you, and
transaction log backup is not possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BCat" <elin@.abcmultiactive.com> wrote in message news:ePUzahQCFHA.2384@.TK2MSFTNGP14.phx.gb
l...
> Hi all,
> do anyone know what is the different between simple and full recovery mode
> in database properties.
> thanks
>
>

diff. between named pipe & TCP-IP

Hi
Is there any diff. between Named Pipe& TCP-IP protocol from performance
point of view there by I can force all the users connecting to server with
TCP-IP proctocol only .
Regards
Ajay RengunthwarFrom BOL:
Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control
Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are
comparable in terms of performance. However, the performance difference
between the TCP/IP Sockets and Named Pipes clients becomes apparent with
slower networks, such as across wide area networks (WANs) or dial-up
networks. This is because of the different ways the interprocess
communication (IPC) mechanisms communicate between peers.
For named pipes, network communications are typically more interactive. A
peer does not send data until another peer asks for it using a read command.
A network read typically involves a series of peek named pipes messages
before it begins to read the data. These can be very costly in a slow
network and cause excessive network traffic, which in turn affects other
network clients.
It is also important to clarify if you are talking about local pipes or
network pipes. If the server application is running locally on the computer
running an instance of Microsoft® SQL ServerT 2000, the local Named Pipes
protocol is an option. Local named pipes runs in kernel mode and is
extremely fast.
For TCP/IP Sockets, data transmissions are more streamlined and have less
overhead. Data transmissions can also take advantage of TCP/IP Sockets
performance enhancement mechanisms such as windowing, delayed
acknowledgements, and so on, which can be very beneficial in a slow network.
Depending on the type of applications, such performance differences can be
significant.
TCP/IP Sockets also support a backlog queue, which can provide a limited
smoothing effect compared to named pipes that may lead to pipe busy errors
when you are attempting to connect to SQL Server.
In general, sockets are preferred in a slow LAN, WAN, or dial-up network,
whereas named pipes can be a better choice when network speed is not the
issue, as it offers more functionality, ease of use, and configuration
options.
"AJAY R" <dba_pune@.hotmail.com> wrote in message
news:er$fIigRDHA.3132@.tk2msftngp13.phx.gbl...
> Hi
> Is there any diff. between Named Pipe& TCP-IP protocol from performance
> point of view there by I can force all the users connecting to server with
> TCP-IP proctocol only .
> Regards
> Ajay Rengunthwar
>
>

Diff. b/w Stored Procedures and Function?

Diff. b/w Stored Procedures and Function??

When any of them is appropriate to use?

Here|||

What is the difference between a Sub and a Function in VB?

Diff versus Log

I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?
Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>
|||So either solution should work for me since I've got a one
hour max?

>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from[vbcol=seagreen]
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or
>
>.
>
|||I'm not sure what you mean by 'one hour max'. How often are you making backups? If a backup spans more than one hour, but you want to restore to a certain point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy for you; amount of acceptable work loss is only one of the questions. Start by reading everything you can in Books Online about backup and restore, and then take a look at "Microsoft SQL Server 2000 High Availability" http://www.amazon.com/exec/obidos/tg...349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is the best part of the book!
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a401280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or
|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
sql

Diff versus Log

I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>|||So either solution should work for me since I've got a one
hour max?

>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or[vbcol=seagreen]
>
>.
>|||I'm not sure what you mean by 'one hour max'. How often are you making backu
ps? If a backup spans more than one hour, but you want to restore to a certa
in point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy
for you; amount of acceptable work loss is only one of the questions. Start by reading
everything you can in Books Online about backup and restore, and then take a look at "
Microsoft SQL Server 2000 High Availability" detail/-/0735619204/104-2349825-6773545" target="_blank">http://www.amazon.com/exec/obidos/t...2349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is th
e best part of the book!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a40
1280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
>
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?

Diff versus Log

I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
> > I'm confused. Let's say I want to be able to back up to
> > one hour ago on several critical databases, i.e. I'm
> > willing to accept a maxiumum of 1 hour of data loss. Is
> > it better to use Differential backups or Log Backups or
> > does it matter? What are some of the basic pros/cons?
>|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
>> Differentials only look at the changes in the data. If
you make UPDATES,
>> then this may work for you. If you do a lot of INSERTS
and DELETEs, then
>it
>> may not.
>> A differential is good from the last time it was backed
up.
>> A transaciton log allows you to restore to a point in
time. So you could
>> recover up to 20 mins ago when something bad
happened. The Diff backup
>> won't allow you to do this. You can apply the diff
backup from 1 hour ago
>> and lose everything up to the last 20 mins. (Unless
you are doing log
>> backups as well. Then simply backup the log and
restore it to 20 minutes
>> ago.)
>>
>> Diff recoveries are faster than TLog recoveries in that
you only have to
>> apply the last diff backup. With logs, you restore
your last good
>database
>> backup and then run all the logs in between.
>> Sooo.. It depends on what you are after.
>> HTH
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>> "G2SL" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>> > I'm confused. Let's say I want to be able to back up
to
>> > one hour ago on several critical databases, i.e. I'm
>> > willing to accept a maxiumum of 1 hour of data loss.
Is
>> > it better to use Differential backups or Log Backups
or
>> > does it matter? What are some of the basic pros/cons?
>>
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm not sure what you mean by 'one hour max'. How often are you making =backups? If a backup spans more than one hour, but you want to restore =to a certain point in time, you need log backups.
There are many questions that must be answered to come up with the best =backup strategy for you; amount of acceptable work loss is only one of =the questions. Start by reading everything you can in Books Online about =backup and restore, and then take a look at "Microsoft SQL Server 2000 =High Availability" =http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104-2349825-6773=
545
The chapter on Backup and Restore was written by Kimberly Tripp and it =is the best part of the book!
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message =news:02f101c48c7e$357192a0$a401280a@.phx.gbl...
> So either solution should work for me since I've got a one > hour max?
> > >--Original Message--
> >Hi Rick
> >
> >I'm not sure what you're saying here. As far as a > differential backup is
> >concerned, updates are the same as inserts or deletes. > Any change to an
> >extent is backed up in the differential, whether that > changes comes from
> >updating existing rows, or from adding or removing rows.
> >
> >-- > >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> >news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> >> Differentials only look at the changes in the data. If > you make UPDATES,
> >> then this may work for you. If you do a lot of INSERTS > and DELETEs, then
> >it
> >> may not.
> >>
> >> A differential is good from the last time it was backed > up.
> >>
> >> A transaciton log allows you to restore to a point in > time. So you could
> >> recover up to 20 mins ago when something bad > happened. The Diff backup
> >> won't allow you to do this. You can apply the diff > backup from 1 hour ago
> >> and lose everything up to the last 20 mins. (Unless > you are doing log
> >> backups as well. Then simply backup the log and > restore it to 20 minutes
> >> ago.)
> >>
> >>
> >> Diff recoveries are faster than TLog recoveries in that > you only have to
> >> apply the last diff backup. With logs, you restore > your last good
> >database
> >> backup and then run all the logs in between.
> >>
> >> Sooo.. It depends on what you are after.
> >>
> >> HTH
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >>
> >>
> >>
> >> "G2SL" <anonymous@.discussions.microsoft.com> wrote in > message
> >> news:184301c48c72$89430e10$a601280a@.phx.gbl...
> >> > I'm confused. Let's say I want to be able to back up > to
> >> > one hour ago on several critical databases, i.e. I'm
> >> > willing to accept a maxiumum of 1 hour of data loss. > Is
> >> > it better to use Differential backups or Log Backups > or
> >> > does it matter? What are some of the basic pros/cons?
> >>
> >>
> >
> >
> >.
> >
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'm not sure what you mean by 'one hour =max'. How often are you making backups? If a backup spans more than one hour, but =you want to restore to a certain point in time, you need log backups.
There are many questions that must be =answered to come up with the best backup strategy for you; amount of acceptable work =loss is only one of the questions. Start by reading everything you can in Books =Online about backup and restore, and then take a look at "Microsoft SQL Server =2000 High Availability" http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104=-2349825-6773545
The chapter on Backup and Restore was =written by Kimberly Tripp and it is the best part of the book!
-- =HTH--Kalen DelaneySQL Server MVPwww.SolidQualityLearning.com
"G2SL" wrote in message news:02f101c48c7e$357192a0$a401280a@.phx.gbl...> So either =solution should work for me since I've got a one > hour max?> > >--Original Message--> >Hi Rick> >> =>I'm not sure what you're saying here. As far as a > differential =backup is> >concerned, updates are the same as inserts or deletes. => Any change to an> >extent is backed up in the differential, =whether that > changes comes from> >updating existing rows, or =from adding or removing rows.> >> >-- > =>HTH> >--> >Kalen Delaney> >SQL Server MVP> >www.SolidQualityLearning.com> >> =>> >"Rick Sawtell" wrote in message> >news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...> >> =Differentials only look at the changes in the data. If > you make UPDATES,> >> then this may work for you. If you do a =lot of INSERTS > and DELETEs, then> >it> >> may not.> >>> >> A differential is good from the =last time it was backed > up.> >>> >> A =transaciton log allows you to restore to a point in > time. So you =could> >> recover up to 20 mins ago when something bad > happened. The Diff backup> >> won't allow you =to do this. You can apply the diff > backup from 1 hour =ago> >> and lose everything up to the last 20 mins. (Unless => you are doing log> >> backups as well. Then simply backup =the log and > restore it to 20 minutes> >> ago.)> >>> >>> >> Diff recoveries are faster =than TLog recoveries in that > you only have to> >> apply the =last diff backup. With logs, you restore > your last =good> >database> >> backup and then run all the logs in between.> >>> >> Sooo.. It depends =on what you are after.> >>> >> HTH> =>>> >> Rick Sawtell> >> MCT, MCSD, MCDBA> =>>> >>> >>> >>> >>> =>> "G2SL" wrote in > message> >> news:184301c48c72$89430e10$a601280a@.phx.gbl...> >> > I'm confused. Let's say I want =to be able to back up > to> >> > one hour ago on several =critical databases, i.e. I'm> >> > willing to accept a maxiumum =of 1 hour of data loss. > Is> >> > it better to use Differential backups or Log Backups > or> >> > =does it matter? What are some of the basic pros/cons?> =>>> >>> >> >> >.> >

---

--=_NextPart_000_0D00_01C48C67.9ED536F0--|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?

Diff Result Between SQL 7 & SQL 2000

Hi.

When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.

The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.

Update SAPvsSQL set [Sales Organisation] = sales_org,
[Value Out By] = val_diff, [Qty Out By] = qty_diff

Error message:

Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ...

SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.

Best regardsYou sure you just didn't execute all of the code in the QA window, and you're executing something you don't see?|||And also, what are you setting the columns to?

Those aren't local variables...is there more to the statement?|||Originally posted by Brett Kaiser
And also, what are you setting the columns to?

Those aren't local variables...is there more to the statement?

Hi,

Thank you for your email reply.

Those in [ ] were actual field names in my database table. I used them for display purposes so instead of showing Sales_organation, I display Sales Organisation, Value Out By instead of val_diff and finally Qty Out By instead of qty_diff (look more meanful to the users).

You are right that I didn't show all the script in my previous message because they worked ok until this line. I am just being curious why the same script work PERFECT in SQL 7 but not in SQL 2000. I have been running this same set of script for more than a year and still running fine as at this morning. Reason why I am trying it on SQL 2000 because we will be moving to SQL 2000 totally by end this month.|||No problems here...your doing something else wrong...

USE Northwind
GO

CREATE TABLE SAPvsSQL (
[Sales Organisation] varchar(10)
, [Value Out By] varchar(10)
, [Qty Out By] varchar(10)
)
GO

DECLARE @.Sales_Org varchar(10), @.val_diff varchar(10), @.qty_diff varchar(10)

SELECT @.Sales_Org = 'A', @.val_Diff = 'B', @.qty_diff = 'C'

INSERT INTO SAPvsSQL ([Sales Organisation], [Value Out By], [Qty Out By])
SELECT 'X', 'Y', 'Z'

SELECT * FROM SAPvsSQL

UPDATE SAPvsSQL
SET [Sales Organisation] = @.sales_org
, [Value Out By] = @.val_diff
, [Qty Out By] = @.qty_diff

SELECT * FROM SAPvsSQL
GO

DROP TABLE SAPvsSQL
GO

DIFF Objects

Does anybody know of a tool or script that will tell the difference between
two tables including objects such as indexes?Give Red Gate SQLCompare
(http://www.red-gate.com/products/SQL_Compare/index.htm) a try
Denis the SQL Menace
http://sqlservercode.blogspot.com/
morphius wrote:
> Does anybody know of a tool or script that will tell the difference between
> two tables including objects such as indexes?

Diff header / footer for diff section?

I have an report and it's structure like:

PAGE HEADER A
PAGE HEADER B
PAGE HEADER C
---
DETAIL A
DETAIL B
DETAIL C
---
PAGE FOOTER A
PAGE FOOTER B
PAGE FOOTER C

DETAIL B and DETAIL C will begin on a new page (As I have checked "New Page Before" for this two section...), but the content for DETAIL B and DETAIL C will be 2 or 3 pages, not static content...

My question is, can the PAGE HEADER and PAGE FOOTER show up according to the DETAIL?

The target output is:

(let say, Detail A will begin on page 1, detail B will begin on page 2, and detail C will begin on page 4...)

(page 1)
============
PAGE HEADER A
-------
DETAIL A
-------
PAGE FOOTER A
============

(page 2,...)
============
PAGE HEADER B
-------
DETAIL B
-------
PAGE FOOTER B
============

(page 4,.....)
============
PAGE HEADER C
-------
DETAIL C
-------
PAGE FOOTER C
============

Can this be done on crystal report 10...?

Thanks for the help.wy don't you put the header and footer in the group header /footer instead of page header / footersql

Diff for tables *structures* rather than data

If I have two sql server databases that started out with identical
table/key/index structures, but were not properly kept in sync, is
there any way I can generate a table change script to essentially
'diff' the two databases and come up with table change scripts to
bring one in line with the other?

An answer to this age-old question of mine would make me very
happy...!

BrianERwin will do this easily for you (reverse engineer one database and then do
a compare with the other). PowerDesigner and ER/Studio will probably also
do it.

Downside? They're not free...

To find out what's different, you could do something like this:

select so.name, sc.name, sc.type from sysobject so inner join syscolumns sc
on so.id = sc.id
order by so.name, sc.name

in both databases, paste the output into .TXT files and then do a compare on
the .TXT files (WINDIFF utility) to get started. You'll have to generate
the change scripts by hand, of course.

You could completely script the databases and then WINDIFF the scripts.
However, the scripting order might be different between the two databases
and this may muddy the waters (you could rearrange the scripts by hand to
resolve some ordering problems).

If you have a little money, see if you can find a database consultant with
access to ERwin or one of the other tools to come in for a couple of hours
and use his tools to generate the scripts for you. It might save a lot of
time. You could ask him to print diagrams, too, which might be helpful down
the road.

If you have a fair amount of cash, consider buying one of these tools
yourself - they're very, very handy. ER/Studio used to offer a freely
downloadable demo; don't know about ERwin or PowerDesigner. It seems to me
that ERwin is something like $4000. I think ER/Studion was less, don't
recall about PowerDesigner.

DesktopDBA, if it's still around, may also offer some capability this way.

I suppose you could check C|Net, SQLServerCentral or some of the other
SQL-oriented group sites for freely downloadable utilities, too.

"Brian McGee" <brian.mcgee@.Sentrio.com> wrote in message
news:831a513c.0309110332.2184b751@.posting.google.c om...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?
> An answer to this age-old question of mine would make me very
> happy...!
> Brian|||In article <831a513c.0309110332.2184b751@.posting.google.com>,
brian.mcgee@.Sentrio.com says...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?

I like Red-gate Softwares "SQL Tools" product for that.
(http://www.red-gate.com) You can get a single-user license for the SQL
Compare portion of the product for about $200. That would bring the
table definitions in line. If you also want scripts to modify the
contents of the tables, that's another $200. Of course, at that point
you're better off with the bundle, which is $350 and includes DTS
Compare which diffs server settings, DTS packages, jobs and logins.

-- Rick

P.S. No affiliation at all with Red-Gate software but their product
saved my cojones once, so I'm just passing on my experience.

Diff Datatypes used doubt int and bigint ........SQL SERVER 2005......Any useful links ?

Hello Frdz,

I have doubt regarding the datatypes fields used in SQL SERVER 2005.

The value for bigint Int64 is 18

The value of int Int32 is 9/10

Now,if in int i write : 1234567890 (accepted)

This gives error : 9874565656 (not accepted........why is it so ? )

Why is it so ??

I want to know the perfect size of all the datatypes used in SQLSERVER 2005.

There are also smallint,tinyint....

What's the main difference with all of them ??

Can anyone provide me the nice links which can explain me what m i asking in this post...

Please help me...I want to know all the datatypes used differences...

you cannot store 9874565656 in an int as it exceeds the max value for an int which is: 2,147,483,647

http://msdn2.microsoft.com/en-us/library/ms187745(SQL.90).aspx

|||

thanxs...i think it's helpful link..

I want to also know that in SQL SERVER 2005 can we assign or fix manually the values upto the limit...like,

Id1 int - 4

Id2 bigint - 10

like in varchar we can do

Name Varchar(20) if we take varchar(50) as datatype...

Hope u understand what i ask...this questions are not solved in my mind...

Please help me...

Thanxs again....

|||

varchar is a variable length datatype and you are allowed to set its length

you cannot do that with the integer datatypes.

|||

If you really intend to limit the value <= 4 digits ( = 9999) you can create a constraing on the column to make sure the value <= 9999.

|||

You can also create your own datatype that is based on an integer value type that can store all your possible values and has a constraint.

This may help:

http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/07/426930.aspx

More complex datatype needs can be done via a CLR UDT, but I'm not sure how well they perform. Like:

http://www.devx.com/dotnet/Article/22644

|||

thanxs all of...

I think it's better to make a constraint......

Nice answers to clear my doubt...

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
|||I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
sql

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
--|||I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between SQL server and SQL server Personal edition

Hi All,
I would like to install SQL server 2000 personal edition
on a workstation as a backup to my server. (disaster
recovery scenario) I run a backup to this machine already
every 30 minutes, all I would have to do is turn on SQL
services, restore the latest backup and I am back up and
running within a few minutes. The workstaion has a 2 GIG
processor and 256 Ram which I am planning on upgrading to
512.
Are there any downsides to running 6 clients hitting this
personal edition server? Is the PE the same as the server
edition? Does the personal edition have limitations that
the server edition does not?
Thanks,
GP
Hi
Just remember that a worstation edition of Windows (XP or 2000 Pro) both
have a limit of 10 concurrent client connections.
Regards
Mike
"GeorgeP" wrote:

> Hi All,
> I would like to install SQL server 2000 personal edition
> on a workstation as a backup to my server. (disaster
> recovery scenario) I run a backup to this machine already
> every 30 minutes, all I would have to do is turn on SQL
> services, restore the latest backup and I am back up and
> running within a few minutes. The workstaion has a 2 GIG
> processor and 256 Ram which I am planning on upgrading to
> 512.
> Are there any downsides to running 6 clients hitting this
> personal edition server? Is the PE the same as the server
> edition? Does the personal edition have limitations that
> the server edition does not?
> Thanks,
> GP
>
|||Thanks,
Yes I know Windows prof. has a conection limitation, but
are there any SQL limitations running on a workstation?
Thanks again for your input.
GP
|||The technical differences between editions are listed in Books Online (see the architecture
section). I suggest you call MS to make sure you aren't violating the licensing agreement:
To speak to someone regarding licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"georgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:493c01c49f24$18fc1830$a601280a@.phx.gbl...
> Thanks,
> Yes I know Windows prof. has a conection limitation, but
> are there any SQL limitations running on a workstation?
> Thanks again for your input.
> GP