Determine what server i'm running on

I've generated a report that will need to display different information
based upon where it is being run. For instance, if the report is executing
on our DVLP server, i need a title to say "Developement". Is there is simple
way to determine what environment the report is being run in? Check the URL?
Server variables?
Thanks,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com=System.Environment.MachineName
Warning: You'll need to give expression host FullTrust in order to use this
expression on report server, which may be a security risk.
If your SQL Server is on the same machine, creating a dataset against it and
using SELECT @.@.servername would be a better approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:uZDVm2lbEHA.3988@.tk2msftngp13.phx.gbl...
> I've generated a report that will need to display different information
> based upon where it is being run. For instance, if the report is executing
> on our DVLP server, i need a title to say "Developement". Is there is
simple
> way to determine what environment the report is being run in? Check the
URL?
> Server variables?
> Thanks,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>sql

Determine rowguid in column and edit column information ASP.NET 1.1 C#

What query should I run?

1. To determine whether a column is a rowguid or not using C# .NET 1.1

2. To add/modify column information and be able to set/change:

- Primary key

- Column Name

- Data Type

- Length

- Allow Null

- Default value

- Precision

- Scale

- Identity

- Identity Seed

- Identity Increment

- Row guid

Thanks a lot!

(1) SELECT OBJECTPROPRTTY(<tableId>,TableHasRowGuidCol) will tell you if the table has uniqueidentifier column.

(2) Please refer BOL.

|||

Hi, thanks for the answer but I tried to run this:

SELECT

OBJECTPROPERTY(OBJECT_ID('temp_aspnet_Permissions'),TableHasRowGuidCol)

and it gives me error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'TableHasRowGuidCol'.

|||

Never mind, I forgot about the quote:

SELECT

OBJECTPROPERTY(OBJECT_ID('temp_aspnet_Permissions'),'TableHasRowGuidCol')

Thanks again!

|||Btw, it only returning number 1 or 0, I need to know which column that has rowguid, so it still hasn't answer my question :(|||

I found it, you need to use columnproperty. Thanks again for the guidance!

SELECTCOLUMNPROPERTY(OBJECT_ID('testcol2'),'col2','IsRowGuidCol')

|||

SELECT

COLUMNPROPERTY(id,name,'IsRowGuidCol'),*

FROMsyscolumnsWHEREid=Object_Id('yourtable')

andCOLUMNPROPERTY(id,name,'IsRowGuidCol') = 1

Determine Replication Folder or Data Folder via SQL?

I have an application that has to do the following
1. Disable Replication
2. Run Database Changes Script
3. Enable Replication again.
I have the scripts to enable and/or disable replication, but I was wondering
if there was anyway to retrieve the data, log, and replication folder
directories via sql so that I can use them in my replication sql script
without having to hard code these values.
L Anthony Johnson
Newbie
sp_browsesnapshotfolder @.publication = 'PublicationName' will tell you the
snapshot folder name (as a UNC).
sp_helpdb 'DBName' will tell you the name of the db.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"L Anthony Johnson" <ppsyco2@.nospamm4m3bellsouth.net> wrote in message
news:%23BsT8$McEHA.3016@.tk2msftngp13.phx.gbl...
> I have an application that has to do the following
> 1. Disable Replication
> 2. Run Database Changes Script
> 3. Enable Replication again.
> I have the scripts to enable and/or disable replication, but I was
wondering
> if there was anyway to retrieve the data, log, and replication folder
> directories via sql so that I can use them in my replication sql script
> without having to hard code these values.
> --
> L Anthony Johnson
> Newbie
>

detaching db and stopping services

Is the state of SQL Server 2000 files the same:
1) when you detach them (run sp_detach-db); and
2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
In other words, when you stop the service, are the db files utomatically
put in the detached state, as if you had run sp_detach-db on them ?
If not, what is the difference between the files when the service is
stopped, and when they are in the detached state ?
Thanks
-- MikeNo, they are not the same.
sp_detach_db ensures that your files can be attached.
Simply grabbing the files from a stopped instance will not guarantee that
you can recover them.
Are you trying to implement a backup routine? Do you want 100% availability
during the backups? Look into the Transact-SQL BACKUP command. Information
and examples can be found within Books Online (within the SQL Server program
group).
--
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||I do not think the state MUST be the same... When you detach a file, there
can be no in-process transactions.
I believe that stopping the service is more 'rude'... I don't think it waits
for transactions to complete. Stopping the service through SEM or the
Service manager does wait, stopping through control panel services or net
stop does not...
Even if the state were the same, remember that detach removes all references
from master as well..
--
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
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||When you detach a database, a checkpoint happens in the database. That means
that all changed that have been made to datapages but that haven't been
written to the data file yet, are written to disk. All these changes are of
course already in the transaction log.
Shutting down SQL Services should perform a check point as well, but IIRC
the documentation in Books Online (see SHUTDOWN) is not complete at this
point. If the server or service fails, you will in most cases not have a
properly check pointed data file, and you will need the transaction log to
recover your database.
It is definitely recommended that if you want to attach a database to
another server, that you use sp_detachdb.
--
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||Wayne Snyder wrote:
> I do not think the state MUST be the same... When you detach a file, there
> can be no in-process transactions.
> I believe that stopping the service is more 'rude'... I don't think it waits
> for transactions to complete. Stopping the service through SEM or the
> Service manager does wait, stopping through control panel services or net
> stop does not...
> Even if the state were the same, remember that detach removes all references
> from master as well..
>
Thanks Wayne, that is very interesting.
Are you saying that IF the service MSSQL$SQL2000 is stopped using SQL
Server Service Manager, then the db files ARE in exactly the same state
as if they were detached using sp_detach_db '
I would appreciate your confirming this.
-- Mike|||Jacco Schalkwijk wrote:
> When you detach a database, a checkpoint happens in the database. That means
> that all changed that have been made to datapages but that haven't been
> written to the data file yet, are written to disk. All these changes are of
> course already in the transaction log.
> Shutting down SQL Services should perform a check point as well, but IIRC
> the documentation in Books Online (see SHUTDOWN) is not complete at this
> point. If the server or service fails, you will in most cases not have a
> properly check pointed data file, and you will need the transaction log to
> recover your database.
> It is definitely recommended that if you want to attach a database to
> another server, that you use sp_detachdb.
>
Thanks Jacco. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db '
I understand that using sp_detach_db is the proper way to do it, and we
are doing it that way, but this is a peculiar configuration I am trying
to work with and I need to know if using SQL Server Service Manager to
stop the service is actually the same as using sp_detach_db, like Wayne
suggests.
Many thanks for your time.
-- Mike|||Keith Kratochvil wrote:
> No, they are not the same.
> sp_detach_db ensures that your files can be attached.
> Simply grabbing the files from a stopped instance will not guarantee that
> you can recover them.
> Are you trying to implement a backup routine? Do you want 100% availability
> during the backups? Look into the Transact-SQL BACKUP command. Information
> and examples can be found within Books Online (within the SQL Server program
> group).
>
Thanks Keith. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db '
I am not doing a backup routine (or at least, I am the recommended way
using sp_detach_db) but I am working with a peculiar configuration with
some odd restraints, and and I need to know if using SQL Server Service
Manager to stop the service is actually the same as using sp_detach_db,
like Wayne suggests.
Many thanks for your time.
-- Mike|||I believe the safest option is to issue sp_detach_db.
I have read a few posts within the newsgroups along the lines of "I did not
detach my database (I copied the mdf and ldf files when the services were
stopped) and now I cannot attach my database. Help!"
I know that it should be possible to attach databases that have not been
explicitly detached, but if I wanted to make sure that I could recover
(attach) my databases I would issue a detach statement first.
--
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:%23R1$2CufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Keith Kratochvil wrote:
> > No, they are not the same.
> > sp_detach_db ensures that your files can be attached.
> > Simply grabbing the files from a stopped instance will not guarantee
that
> > you can recover them.
> >
> > Are you trying to implement a backup routine? Do you want 100%
availability
> > during the backups? Look into the Transact-SQL BACKUP command.
Information
> > and examples can be found within Books Online (within the SQL Server
program
> > group).
> >
> Thanks Keith. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I am not doing a backup routine (or at least, I am the recommended way
> using sp_detach_db) but I am working with a peculiar configuration with
> some odd restraints, and and I need to know if using SQL Server Service
> Manager to stop the service is actually the same as using sp_detach_db,
> like Wayne suggests.
> Many thanks for your time.
> -- Mike
>|||Practically speaking shutting down the database (in whatever way) and using
sp_detach_db are NOT the same. Theoretically they more or less should be the
same, but attaching databases that haven't been explicitly detached has been
a hit-and-miss experience for a lot of users.
--
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
>> When you detach a database, a checkpoint happens in the database. That
>> means that all changed that have been made to datapages but that haven't
>> been written to the data file yet, are written to disk. All these changes
>> are of course already in the transaction log.
>> Shutting down SQL Services should perform a check point as well, but IIRC
>> the documentation in Books Online (see SHUTDOWN) is not complete at this
>> point. If the server or service fails, you will in most cases not have a
>> properly check pointed data file, and you will need the transaction log
>> to recover your database.
>> It is definitely recommended that if you want to attach a database to
>> another server, that you use sp_detachdb.
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying to
> work with and I need to know if using SQL Server Service Manager to stop
> the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>|||IMO, it doesn't matter what anyone of us say (no disrespect to anyone here, I'm just making a point). What
matter is what the documentation say. And the documentation say that you are guaranteed to be able to attach a
db only if you detached it first. (Look up BOL for exact wording.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MikeF" <mrf@.sent.com> wrote in message news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
> > When you detach a database, a checkpoint happens in the database. That means
> > that all changed that have been made to datapages but that haven't been
> > written to the data file yet, are written to disk. All these changes are of
> > course already in the transaction log.
> >
> > Shutting down SQL Services should perform a check point as well, but IIRC
> > the documentation in Books Online (see SHUTDOWN) is not complete at this
> > point. If the server or service fails, you will in most cases not have a
> > properly check pointed data file, and you will need the transaction log to
> > recover your database.
> >
> > It is definitely recommended that if you want to attach a database to
> > another server, that you use sp_detachdb.
> >
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying
> to work with and I need to know if using SQL Server Service Manager to
> stop the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>|||MikeF,
I absolutely agree with all of the other posters... It was not my intention
to suggest that shutting down the service by any means is a replacement for
a proper detach...
It is possible that on some occasions you can attach a db file from a
properly shutdown server... However it is risky at best, and something you
try when you have no other alternative...
I though you were just in trouble, and had no options...
Sorry for the mini-controversy...if you INTEND to attach a database, then
ALWAYS properly detach, and NEVER assume that the shutdown is good enough -
it might be, and it might not be...!
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUlgeXvfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> IMO, it doesn't matter what anyone of us say (no disrespect to anyone
here, I'm just making a point). What
> matter is what the documentation say. And the documentation say that you
are guaranteed to be able to attach a
> db only if you detached it first. (Look up BOL for exact wording.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MikeF" <mrf@.sent.com> wrote in message
news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> > Jacco Schalkwijk wrote:
> >
> > > When you detach a database, a checkpoint happens in the database. That
means
> > > that all changed that have been made to datapages but that haven't
been
> > > written to the data file yet, are written to disk. All these changes
are of
> > > course already in the transaction log.
> > >
> > > Shutting down SQL Services should perform a check point as well, but
IIRC
> > > the documentation in Books Online (see SHUTDOWN) is not complete at
this
> > > point. If the server or service fails, you will in most cases not have
a
> > > properly check pointed data file, and you will need the transaction
log to
> > > recover your database.
> > >
> > > It is definitely recommended that if you want to attach a database to
> > > another server, that you use sp_detachdb.
> > >
> >
> > Thanks Jacco. What do you think of Wayne's point that if the service
> > MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> > files ARE in exactly the same state as if they were detached using
> > sp_detach_db '
> >
> > I understand that using sp_detach_db is the proper way to do it, and we
> > are doing it that way, but this is a peculiar configuration I am trying
> > to work with and I need to know if using SQL Server Service Manager to
> > stop the service is actually the same as using sp_detach_db, like Wayne
> > suggests.
> >
> > Many thanks for your time.
> >
> > -- Mike
> >
>|||In article <u8crS8sfEHA.2028@.tk2msftngp13.phx.gbl>,
jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> When you detach a database, a checkpoint happens in the database. That means
> that all changed that have been made to datapages but that haven't been
> written to the data file yet, are written to disk. All these changes are of
> course already in the transaction log.
> Shutting down SQL Services should perform a check point as well, but IIRC
> the documentation in Books Online (see SHUTDOWN) is not complete at this
> point. If the server or service fails, you will in most cases not have a
> properly check pointed data file, and you will need the transaction log to
> recover your database.
> It is definitely recommended that if you want to attach a database to
> another server, that you use sp_detachdb.
What about my case:
I have a standby server that I constantly restore logs to. If I detach
the database in order to back it up, it gets recovered when I re-attach
and the log synchronization gets broken. The database never has any
transactions other than the ones that come in from the production
server's logs as I just use it for large custom queries. Every night I
stop the service, add the data and log to a zip and restart the service.
Isn't this effectively a backup?|||I am confused. Why not backup your live (production) database? After all,
you are almost there with your log-shipping routines? All you need to do is
backup the file(s) created by that process and you should have your database
backup.
--
Keith
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b83f83cb16eeba0989692@.news...
> In article <u8crS8sfEHA.2028@.tk2msftngp13.phx.gbl>,
> jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> > When you detach a database, a checkpoint happens in the database. That
means
> > that all changed that have been made to datapages but that haven't been
> > written to the data file yet, are written to disk. All these changes are
of
> > course already in the transaction log.
> >
> > Shutting down SQL Services should perform a check point as well, but
IIRC
> > the documentation in Books Online (see SHUTDOWN) is not complete at this
> > point. If the server or service fails, you will in most cases not have a
> > properly check pointed data file, and you will need the transaction log
to
> > recover your database.
> >
> > It is definitely recommended that if you want to attach a database to
> > another server, that you use sp_detachdb.
> What about my case:
> I have a standby server that I constantly restore logs to. If I detach
> the database in order to back it up, it gets recovered when I re-attach
> and the log synchronization gets broken. The database never has any
> transactions other than the ones that come in from the production
> server's logs as I just use it for large custom queries. Every night I
> stop the service, add the data and log to a zip and restart the service.
> Isn't this effectively a backup?|||In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
sqlguy.back2u@.comcast.net says...
> I am confused. Why not backup your live (production) database? After all,
> you are almost there with your log-shipping routines? All you need to do is
> backup the file(s) created by that process and you should have your database
> backup.
My standby server is only connected by a T1 and I have about 2GB of log
per day (DB size=50GB). In two weeks of log shipping I have already
blown the standby database once. I basically want a backup of the local
copy (standby) that I can go back to if it gets corrupted. I already
backup the production database, but keep the copy at the datacenter.
I can't backup the standby database through SQL server because it is in
read-only mode. I can't detach and re-attach because it will recover
the database on the re-attach.|||Understood. I was assuming that the two servers were in the same building
connected by a fast network. That assumption was incorrect!
I suggest that you email your wish/request to the SQL Server development
team. sqlwish@.microsoft.com They might not respond to you, but they do
read the emails and our suggestions can make a difference!
--
Keith
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b8402b4afd7ebef989694@.news...
> In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
> sqlguy.back2u@.comcast.net says...
> > I am confused. Why not backup your live (production) database? After
all,
> > you are almost there with your log-shipping routines? All you need to
do is
> > backup the file(s) created by that process and you should have your
database
> > backup.
> My standby server is only connected by a T1 and I have about 2GB of log
> per day (DB size=50GB). In two weeks of log shipping I have already
> blown the standby database once. I basically want a backup of the local
> copy (standby) that I can go back to if it gets corrupted. I already
> backup the production database, but keep the copy at the datacenter.
> I can't backup the standby database through SQL server because it is in
> read-only mode. I can't detach and re-attach because it will recover
> the database on the re-attach.|||In that case I would consider your options such as "It will probably work, but it isn't guaranteed".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad" <brad@.seesigifthere.com> wrote in message news:MPG.1b8402b4afd7ebef989694@.news...
> In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
> sqlguy.back2u@.comcast.net says...
> > I am confused. Why not backup your live (production) database? After all,
> > you are almost there with your log-shipping routines? All you need to do is
> > backup the file(s) created by that process and you should have your database
> > backup.
> My standby server is only connected by a T1 and I have about 2GB of log
> per day (DB size=50GB). In two weeks of log shipping I have already
> blown the standby database once. I basically want a backup of the local
> copy (standby) that I can go back to if it gets corrupted. I already
> backup the production database, but keep the copy at the datacenter.
> I can't backup the standby database through SQL server because it is in
> read-only mode. I can't detach and re-attach because it will recover
> the database on the re-attach.|||That should have been "the attach option" instead of "options".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ea6AWAKgEHA.3416@.TK2MSFTNGP09.phx.gbl...
> In that case I would consider your options such as "It will probably work, but it isn't guaranteed".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brad" <brad@.seesigifthere.com> wrote in message news:MPG.1b8402b4afd7ebef989694@.news...
> > In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
> > sqlguy.back2u@.comcast.net says...
> > > I am confused. Why not backup your live (production) database? After all,
> > > you are almost there with your log-shipping routines? All you need to do is
> > > backup the file(s) created by that process and you should have your database
> > > backup.
> >
> > My standby server is only connected by a T1 and I have about 2GB of log
> > per day (DB size=50GB). In two weeks of log shipping I have already
> > blown the standby database once. I basically want a backup of the local
> > copy (standby) that I can go back to if it gets corrupted. I already
> > backup the production database, but keep the copy at the datacenter.
> >
> > I can't backup the standby database through SQL server because it is in
> > read-only mode. I can't detach and re-attach because it will recover
> > the database on the re-attach.
>

detaching db and stopping services

Is the state of SQL Server 2000 files the same:
1) when you detach them (run sp_detach-db); and
2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
In other words, when you stop the service, are the db files utomatically
put in the detached state, as if you had run sp_detach-db on them ?
If not, what is the difference between the files when the service is
stopped, and when they are in the detached state ?
Thanks
-- Mike
No, they are not the same.
sp_detach_db ensures that your files can be attached.
Simply grabbing the files from a stopped instance will not guarantee that
you can recover them.
Are you trying to implement a backup routine? Do you want 100% availability
during the backups? Look into the Transact-SQL BACKUP command. Information
and examples can be found within Books Online (within the SQL Server program
group).
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>
|||I do not think the state MUST be the same... When you detach a file, there
can be no in-process transactions.
I believe that stopping the service is more 'rude'... I don't think it waits
for transactions to complete. Stopping the service through SEM or the
Service manager does wait, stopping through control panel services or net
stop does not...
Even if the state were the same, remember that detach removes all references
from master as well..
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
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>
|||When you detach a database, a checkpoint happens in the database. That means
that all changed that have been made to datapages but that haven't been
written to the data file yet, are written to disk. All these changes are of
course already in the transaction log.
Shutting down SQL Services should perform a check point as well, but IIRC
the documentation in Books Online (see SHUTDOWN) is not complete at this
point. If the server or service fails, you will in most cases not have a
properly check pointed data file, and you will need the transaction log to
recover your database.
It is definitely recommended that if you want to attach a database to
another server, that you use sp_detachdb.
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>
|||Wayne Snyder wrote:

> I do not think the state MUST be the same... When you detach a file, there
> can be no in-process transactions.
> I believe that stopping the service is more 'rude'... I don't think it waits
> for transactions to complete. Stopping the service through SEM or the
> Service manager does wait, stopping through control panel services or net
> stop does not...
> Even if the state were the same, remember that detach removes all references
> from master as well..
>
Thanks Wayne, that is very interesting.
Are you saying that IF the service MSSQL$SQL2000 is stopped using SQL
Server Service Manager, then the db files ARE in exactly the same state
as if they were detached using sp_detach_db ?
I would appreciate your confirming this.
-- Mike
|||Jacco Schalkwijk wrote:

> When you detach a database, a checkpoint happens in the database. That means
> that all changed that have been made to datapages but that haven't been
> written to the data file yet, are written to disk. All these changes are of
> course already in the transaction log.
> Shutting down SQL Services should perform a check point as well, but IIRC
> the documentation in Books Online (see SHUTDOWN) is not complete at this
> point. If the server or service fails, you will in most cases not have a
> properly check pointed data file, and you will need the transaction log to
> recover your database.
> It is definitely recommended that if you want to attach a database to
> another server, that you use sp_detachdb.
>
Thanks Jacco. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db ?
I understand that using sp_detach_db is the proper way to do it, and we
are doing it that way, but this is a peculiar configuration I am trying
to work with and I need to know if using SQL Server Service Manager to
stop the service is actually the same as using sp_detach_db, like Wayne
suggests.
Many thanks for your time.
-- Mike
|||Keith Kratochvil wrote:

> No, they are not the same.
> sp_detach_db ensures that your files can be attached.
> Simply grabbing the files from a stopped instance will not guarantee that
> you can recover them.
> Are you trying to implement a backup routine? Do you want 100% availability
> during the backups? Look into the Transact-SQL BACKUP command. Information
> and examples can be found within Books Online (within the SQL Server program
> group).
>
Thanks Keith. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db ?
I am not doing a backup routine (or at least, I am the recommended way
using sp_detach_db) but I am working with a peculiar configuration with
some odd restraints, and and I need to know if using SQL Server Service
Manager to stop the service is actually the same as using sp_detach_db,
like Wayne suggests.
Many thanks for your time.
-- Mike
|||I believe the safest option is to issue sp_detach_db.
I have read a few posts within the newsgroups along the lines of "I did not
detach my database (I copied the mdf and ldf files when the services were
stopped) and now I cannot attach my database. Help!"
I know that it should be possible to attach databases that have not been
explicitly detached, but if I wanted to make sure that I could recover
(attach) my databases I would issue a detach statement first.
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:%23R1$2CufEHA.904@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Keith Kratochvil wrote:
that[vbcol=seagreen]
availability[vbcol=seagreen]
Information[vbcol=seagreen]
program
> Thanks Keith. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db ?
> I am not doing a backup routine (or at least, I am the recommended way
> using sp_detach_db) but I am working with a peculiar configuration with
> some odd restraints, and and I need to know if using SQL Server Service
> Manager to stop the service is actually the same as using sp_detach_db,
> like Wayne suggests.
> Many thanks for your time.
> -- Mike
>
|||Practically speaking shutting down the database (in whatever way) and using
sp_detach_db are NOT the same. Theoretically they more or less should be the
same, but attaching databases that haven't been explicitly detached has been
a hit-and-miss experience for a lot of users.
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
>
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db ?
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying to
> work with and I need to know if using SQL Server Service Manager to stop
> the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>
|||IMO, it doesn't matter what anyone of us say (no disrespect to anyone here, I'm just making a point). What
matter is what the documentation say. And the documentation say that you are guaranteed to be able to attach a
db only if you detached it first. (Look up BOL for exact wording.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MikeF" <mrf@.sent.com> wrote in message news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
>
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db ?
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying
> to work with and I need to know if using SQL Server Service Manager to
> stop the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>

detaching db and stopping services

Is the state of SQL Server 2000 files the same:
1) when you detach them (run sp_detach-db); and
2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
In other words, when you stop the service, are the db files utomatically
put in the detached state, as if you had run sp_detach-db on them ?
If not, what is the difference between the files when the service is
stopped, and when they are in the detached state ?
Thanks
-- MikeNo, they are not the same.
sp_detach_db ensures that your files can be attached.
Simply grabbing the files from a stopped instance will not guarantee that
you can recover them.
Are you trying to implement a backup routine? Do you want 100% availability
during the backups? Look into the Transact-SQL BACKUP command. Information
and examples can be found within Books Online (within the SQL Server program
group).
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||I do not think the state MUST be the same... When you detach a file, there
can be no in-process transactions.
I believe that stopping the service is more 'rude'... I don't think it waits
for transactions to complete. Stopping the service through SEM or the
Service manager does wait, stopping through control panel services or net
stop does not...
Even if the state were the same, remember that detach removes all references
from master as well..
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
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||When you detach a database, a checkpoint happens in the database. That means
that all changed that have been made to datapages but that haven't been
written to the data file yet, are written to disk. All these changes are of
course already in the transaction log.
Shutting down SQL Services should perform a check point as well, but IIRC
the documentation in Books Online (see SHUTDOWN) is not complete at this
point. If the server or service fails, you will in most cases not have a
properly check pointed data file, and you will need the transaction log to
recover your database.
It is definitely recommended that if you want to attach a database to
another server, that you use sp_detachdb.
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||Wayne Snyder wrote:

> I do not think the state MUST be the same... When you detach a file, the
re
> can be no in-process transactions.
> I believe that stopping the service is more 'rude'... I don't think it wai
ts
> for transactions to complete. Stopping the service through SEM or the
> Service manager does wait, stopping through control panel services or net
> stop does not...
> Even if the state were the same, remember that detach removes all referenc
es
> from master as well..
>
Thanks Wayne, that is very interesting.
Are you saying that IF the service MSSQL$SQL2000 is stopped using SQL
Server Service Manager, then the db files ARE in exactly the same state
as if they were detached using sp_detach_db '
I would appreciate your confirming this.
-- Mike|||Jacco Schalkwijk wrote:

> When you detach a database, a checkpoint happens in the database. That mea
ns
> that all changed that have been made to datapages but that haven't been
> written to the data file yet, are written to disk. All these changes are o
f
> course already in the transaction log.
> Shutting down SQL Services should perform a check point as well, but IIRC
> the documentation in Books Online (see SHUTDOWN) is not complete at this
> point. If the server or service fails, you will in most cases not have a
> properly check pointed data file, and you will need the transaction log to
> recover your database.
> It is definitely recommended that if you want to attach a database to
> another server, that you use sp_detachdb.
>
Thanks Jacco. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db '
I understand that using sp_detach_db is the proper way to do it, and we
are doing it that way, but this is a peculiar configuration I am trying
to work with and I need to know if using SQL Server Service Manager to
stop the service is actually the same as using sp_detach_db, like Wayne
suggests.
Many thanks for your time.
-- Mike|||Keith Kratochvil wrote:

> No, they are not the same.
> sp_detach_db ensures that your files can be attached.
> Simply grabbing the files from a stopped instance will not guarantee that
> you can recover them.
> Are you trying to implement a backup routine? Do you want 100% availabili
ty
> during the backups? Look into the Transact-SQL BACKUP command. Informati
on
> and examples can be found within Books Online (within the SQL Server progr
am
> group).
>
Thanks Keith. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db '
I am not doing a backup routine (or at least, I am the recommended way
using sp_detach_db) but I am working with a peculiar configuration with
some odd restraints, and and I need to know if using SQL Server Service
Manager to stop the service is actually the same as using sp_detach_db,
like Wayne suggests.
Many thanks for your time.
-- Mike|||I believe the safest option is to issue sp_detach_db.
I have read a few posts within the newsgroups along the lines of "I did not
detach my database (I copied the mdf and ldf files when the services were
stopped) and now I cannot attach my database. Help!"
I know that it should be possible to attach databases that have not been
explicitly detached, but if I wanted to make sure that I could recover
(attach) my databases I would issue a detach statement first.
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:%23R1$2CufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Keith Kratochvil wrote:
>
that[vbcol=seagreen]
availability[vbcol=seagreen]
Information[vbcol=seagreen]
program[vbcol=seagreen]
> Thanks Keith. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I am not doing a backup routine (or at least, I am the recommended way
> using sp_detach_db) but I am working with a peculiar configuration with
> some odd restraints, and and I need to know if using SQL Server Service
> Manager to stop the service is actually the same as using sp_detach_db,
> like Wayne suggests.
> Many thanks for your time.
> -- Mike
>|||Practically speaking shutting down the database (in whatever way) and using
sp_detach_db are NOT the same. Theoretically they more or less should be the
same, but attaching databases that haven't been explicitly detached has been
a hit-and-miss experience for a lot of users.
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
>
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying to
> work with and I need to know if using SQL Server Service Manager to stop
> the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>|||IMO, it doesn't matter what anyone of us say (no disrespect to anyone here,
I'm just making a point). What
matter is what the documentation say. And the documentation say that you are
guaranteed to be able to attach a
db only if you detached it first. (Look up BOL for exact wording.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MikeF" <mrf@.sent.com> wrote in message news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...[vbcol
=seagreen]
> Jacco Schalkwijk wrote:
>
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying
> to work with and I need to know if using SQL Server Service Manager to
> stop the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>[/vbcol]

Detach and Attach for Backup and Deployment?

Hello,
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus

Design of 'age' ( dimension ? )

my reports are run over a period of time of certain dates, let s say :

Period 1) DateStartPeriod1 - DateEndPeridod1

Period 2) DateStartPeriod1 - DateEndPeridod1

Period n ) .....

The subject is born in DBORN, so when i need to rollup to the age at the end of the period 1 it is going to be

DateEndPeriod1 - DBORN and the same when the second date is used, i.e. DateEndPeridod1 - DBORN.

QUESTION : How do i model this in Analasys services ? In other words how do i explain AS that when i use Period 1

on the columns i want the age DateEndPeriod1 - DBORN on the rows

I tried to use calculated memebrs AgePeriod1 and AgePeriod2 ... something like

MEMBER AgendPeriod1 as 'DateEndPeriod1 - DBORN'

but it does not seem to work.

I m pretty sure that the answer is straightforward but because i m new to OLAP i just can t think of it.

Thanks

Lui

One possible solution: define a calculated measure.

Formula could be the following:

'[Period].CurrentMember.MemberValue - [Subject].[LastLevel].CurrentMember.Properties("DBORN")'

When designing your cube, make sure ValueColumn for the [Period] hierarchy points to a relational column that has 'date' type, and actually contains the date for the end of each period.

I am also assuming you will have a hierarchy named [Subject], and each member at the last level of this hierarchy has a member property called DBORN, that is also of type date.