Database start trigger 2005

Hi,

I have searched high and low, and can not find any mention of SQL2005 and mount/start database DDL triggers.

I need a trigger/job/event to update a column in a table to indicate that the database has been restarted. I can not have an application connect to the database and set this value as I am afraid one of my other computers may connect first and get the wrong state from the database.

Is there any way to have SQL2005 update a table on MOUNT/Service Start/DB start?

Regards,
Derek

I solved it on my own. I use a StoredProcedure in the MASTER database and set it to start automatically.

~Derek

Database script creation

In the paste I have used SQL2000 to create db from the Query Analyser. In SQL2005 I can't see Query Analzyer but found under programs --> Microsoft SQL Server 2005 --> Configuration Tools --> Reporting Services Configuration --> Database Setup. It creates a database and I can connect to it from VS.NET. Here is my dilema, I was provided SQL scripts to create tables, stored procs etc. Where and how do I run these scripts to create these things in my newly create db.

Also how do I remove a db in SQL2005?

You can run your scripts from the command line via sqlcmd.exe or what I suggest you really do is download SQL Server Management Studio - Express Edition, that is a lightweight replacement for QA and EM, but for Express|||

Excellent suggestion... SQL Server Management Studio was exactly what I was looking for.

Thanks!

Database restoring problem..HELP!! SOS

I am a noob of SQL2005, when i try to restore my database, it jz doesn't work and i've got this error..any expert here please lend me your hand..thx!!

http://img.photobucket.com/albums/v617/mv2squall_sx/restoredberror.png"

You want to override an existing database which tail has not been yet backuped, which measn that you will lose any changes that were made after the last transaction backup. If you know about that you will either have to confirm it or use another database name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Database Replication

we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:

> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
ChrisFor tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
Chris
For tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
ChrisFor tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>