Metadata Services: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

No Mail icon in control panel?

This is too weird.
I'm following
http://support.microsoft.com/Default.aspx?scid=http://support.microsoft.com/servicedesks/webcasts/en/wc091301/wct091301.asp
to troubleshoot why SQLAgentMail does not send notifications on our SQL 2000
server. Looks like I need to logon as the startup user account to get mail
profile configured. But after I logon the SQL server using the startup
domain account that runs both the server and the agent, I don't see any Mail
icon in the control panel. Logging on as other users does not have this
problem.
Any ideas?
BingHave you considered other alternatives?
http://www.aspfaq.com/2403
--
http://www.aspfaq.com/
(Reverse address to reply.)
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3B45529C-D273-4B72-9DEA-B01E13BC6F2C@.microsoft.com...
> This is too weird.
> I'm following
>
http://support.microsoft.com/Default.aspx?scid=http://support.microsoft.com/servicedesks/webcasts/en/wc091301/wct091301.asp
> to troubleshoot why SQLAgentMail does not send notifications on our SQL
2000
> server. Looks like I need to logon as the startup user account to get
mail
> profile configured. But after I logon the SQL server using the startup
> domain account that runs both the server and the agent, I don't see any
Mail
> icon in the control panel. Logging on as other users does not have this
> problem.
> Any ideas?
> Bing|||That indicates that mail is not configured for the service
account you are logging in as. Follow the steps in the
following:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
-Sue
On Tue, 18 Jan 2005 14:57:02 -0800, "bing"
<bing@.discussions.microsoft.com> wrote:
>This is too weird.
>I'm following
>http://support.microsoft.com/Default.aspx?scid=http://support.microsoft.com/servicedesks/webcasts/en/wc091301/wct091301.asp
>to troubleshoot why SQLAgentMail does not send notifications on our SQL 2000
>server. Looks like I need to logon as the startup user account to get mail
>profile configured. But after I logon the SQL server using the startup
>domain account that runs both the server and the agent, I don't see any Mail
>icon in the control panel. Logging on as other users does not have this
>problem.
>Any ideas?
>Bing|||Yup, you said it right. Looks like Microsoft office outlook was upgraded
from 2000 to 2003 on our SQL server at some point. But the mail
configuration for the SQL service account was not maintained at the same
time. I just went into outlook 2003 and get mail configured for the service
account. SQLAgentMail works fine now. Thanks all who replied.
Bing
"Sue Hoegemeier" wrote:
> That indicates that mail is not configured for the service
> account you are logging in as. Follow the steps in the
> following:
> INF: How to Configure SQL Mail
> http://support.microsoft.com/?id=263556
> -Sue
> On Tue, 18 Jan 2005 14:57:02 -0800, "bing"
> <bing@.discussions.microsoft.com> wrote:
> >This is too weird.
> >
> >I'm following
> >http://support.microsoft.com/Default.aspx?scid=http://support.microsoft.com/servicedesks/webcasts/en/wc091301/wct091301.asp
> >to troubleshoot why SQLAgentMail does not send notifications on our SQL 2000
> >server. Looks like I need to logon as the startup user account to get mail
> >profile configured. But after I logon the SQL server using the startup
> >domain account that runs both the server and the agent, I don't see any Mail
> >icon in the control panel. Logging on as other users does not have this
> >problem.
> >
> >Any ideas?
> >
> >Bing
>|||Glad it was that easy to sort out. In my experience, it usually involves a
lot more hair- and teeth-pulling. :-)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"bing" <bing@.discussions.microsoft.com> wrote in message
news:382E13A2-F995-4FE0-86F4-124CA6BB3210@.microsoft.com...
> Yup, you said it right. Looks like Microsoft office outlook was upgraded
> from 2000 to 2003 on our SQL server at some point. But the mail
> configuration for the SQL service account was not maintained at the same
> time. I just went into outlook 2003 and get mail configured for the
service
> account. SQLAgentMail works fine now. Thanks all who replied.
>

Wednesday, March 28, 2012

No entries in Database Mail log (sysmail_event_log)

Hi. I've reviewed the following webpage http://msdn2.microsoft.com/en-us/library/ms189959.aspx and didn't get the answer I was looking for. (Service Broker is running in msdb.)

Essentially, Database Mail isn't logging entries. Using the following example script:

EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'someone@.somewhere.com',
@.body='Message Body',
@.subject ='Message Subject'

I get the result "Mail queued," and I confirm I receive the email.

However, there are no records in the following tables:

select * from sysmail_event_log
select * from sysmail_faileditems
select * from sysmail_unsentitems

There are records that the send-off was a success:

select * from sysmail_sentitems

Database Mail's "Logging Level" to normally set to Normal. I've changed to Verbose and see no change.

Anyone see something I'm missing?

There should not be any records in

sysmail_event_log
sysmail_faileditems
sysmail_unsentitems

for a sucessfully sent email.

There should be a record in

sysmail_sentitems

for a sucessfully sent email.

Search Books Online for information on

sysmail_event_log
sysmail_faileditems
sysmail_unsentitems

|||I don't know... the other servers log messages in sysmail_event_log such as "Activation successful," "DatabaseMail process is started," and "Mail successfully sent" .|||

Contains one row for each Windows or SQL Server message returned by the Database Mail system. (Message in this context refers to a message such as an error message, not an e-mail message.) As suggested Use sysmail_sentitems when you want to see which messages were successfully sent.

BOL confirms:

When troubleshooting Database Mail, search the sysmail_event_log view for events related to e-mail failures. Some messages, such as the failure of the Database Mail external program, are not associated with specific e-mails. To search for errors related to specific e-mails, look up the mailitem_id of the failed e-mail in the sysmail_faileditems view and then search the sysmail_event_log for messages related to that mailitem_id. When an error is returned from sp_send_dbmail, the e-mail is not submitted to the Database Mail system and the error is not displayed in this view.

When individual account delivery attempts fail, Database Mail holds the error messages during retry attempts until the mail item delivery either succeeds or fails. In case of ultimate success, all of the accumulated errors get logged as separate warnings including the account_id. This can cause warnings to appear, even though the e-mail was sent. In case of ultimate delivery failure, all previous warnings get logged as one error message without an account_id, since all accounts have failed.

No entries in Database Mail log (sysmail_event_log)

Hi. I've reviewed the following webpage http://msdn2.microsoft.com/en-us/library/ms189959.aspx and didn't get the answer I was looking for. (Service Broker is running in msdb.)

Essentially, Database Mail isn't logging entries. Using the following example script:

EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'someone@.somewhere.com',
@.body='Message Body',
@.subject ='Message Subject'

I get the result "Mail queued," and I confirm I receive the email.

However, there are no records in the following tables:

select * from sysmail_event_log
select * from sysmail_faileditems
select * from sysmail_unsentitems

There are records that the send-off was a success:

select * from sysmail_sentitems

Database Mail's "Logging Level" to normally set to Normal. I've changed to Verbose and see no change.

Anyone see something I'm missing?

There should not be any records in

sysmail_event_log
sysmail_faileditems
sysmail_unsentitems

for a sucessfully sent email.

There should be a record in

sysmail_sentitems

for a sucessfully sent email.

Search Books Online for information on

sysmail_event_log
sysmail_faileditems
sysmail_unsentitems

|||I don't know... the other servers log messages in sysmail_event_log such as "Activation successful," "DatabaseMail process is started," and "Mail successfully sent" .|||

BOL confirms:

sql

Monday, March 26, 2012

No disk in drive a:

Good morning
We ask for help please for the following:
From a Windows XP SP2 via Terminal Services (mstsc.exe) we were accessing a
Windows Server 2003 SP1/SQL 2000 SP4.
After remote instruction to be restarted the SQL Server Service, this
service aparentely stops OK, but does not start again
with an error on the master Database.
Note: in Task Manager the SQL Process was still active, not possible to
eliminate
After some attempts to restart the service, we note that other DBs were also
alocated. Allways working via Terminal.
When finally we went to the phisical Server, to restart the machine, we note
the dialog message: There is no disk in drive a:
Note: this dialog does not appear on the terminal side
When this msg was accepted the situation was reestablished.
Is this a known error?
How to solve?
How to be sure that the dialog msg is really mapped to the Terminal
environment?
Thanks
Regards
Rui MendonçaHi RM,
Have a look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281345&sd=tech
Ray
"RM" wrote:
> Good morning
> We ask for help please for the following:
> From a Windows XP SP2 via Terminal Services (mstsc.exe) we were accessing a
> Windows Server 2003 SP1/SQL 2000 SP4.
> After remote instruction to be restarted the SQL Server Service, this
> service aparentely stops OK, but does not start again
> with an error on the master Database.
> Note: in Task Manager the SQL Process was still active, not possible to
> eliminate
> After some attempts to restart the service, we note that other DBs were also
> alocated. Allways working via Terminal.
>
> When finally we went to the phisical Server, to restart the machine, we note
> the dialog message: There is no disk in drive a:
> Note: this dialog does not appear on the terminal side
> When this msg was accepted the situation was reestablished.
>
> Is this a known error?
> How to solve?
> How to be sure that the dialog msg is really mapped to the Terminal
> environment?
> Thanks
> Regards
> Rui MendonÃ§a
>
>|||Hello Rui,
I agree with Ray this issue is related to default error mode handling. Some
settings in terminal session blocks the error dialog which causes the
process of SQL Server to hang. You may want to check if you log on to
Windows with same domain user account and if the issue occurs with other
user account.
Also, please change the registry key ErrorMode Value = 2 to see if there is
any side effect. Usually this error message "There is no disk in drive a:"
does not cause real issue and can be ignored. You may still see the
information logged in event log though the dialog does not prompt.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||I Thank you all for the help.
I have posted a very urgent situation in "microsoft.public.sqlserver.server"
with the name "Event ID: 17052 Urgent"
Please Help
Thanks a lot
Rui Mendonça
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:sJNUcJalGHA.5164@.TK2MSFTNGXA01.phx.gbl...
> Hello Rui,
> I agree with Ray this issue is related to default error mode handling.
> Some
> settings in terminal session blocks the error dialog which causes the
> process of SQL Server to hang. You may want to check if you log on to
> Windows with same domain user account and if the issue occurs with other
> user account.
> Also, please change the registry key ErrorMode Value = 2 to see if there
> is
> any side effect. Usually this error message "There is no disk in drive a:"
> does not cause real issue and can be ignored. You may still see the
> information logged in event log though the dialog does not prompt.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Rui,
Please note I'v replied in that in that thread. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Friday, March 23, 2012

No Custom Controls in 2003?

If i'm understanding some previous posts by Robert Bruckner, you really can't
do extensive or fancy custom controls in RS.
Is the following to fancy? I have a integer field which tracks contact
method (Email, Fax, Mail, or any combination of the 3). Depending on what
this number is, will determine if there are 5 address fields or an email
address or an fax number printed on a report. I want a control to do this. Is
such a thing possible with RS 2003?
Thanks!What you can do is to have standard controls on the report and hide or show
the control based on an expression.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CraigZello" <CraigZello@.discussions.microsoft.com> wrote in message
news:7F8F4F7D-F50C-4C7B-9C06-36804596427D@.microsoft.com...
> If i'm understanding some previous posts by Robert Bruckner, you really
can't
> do extensive or fancy custom controls in RS.
> Is the following to fancy? I have a integer field which tracks contact
> method (Email, Fax, Mail, or any combination of the 3). Depending on what
> this number is, will determine if there are 5 address fields or an email
> address or an fax number printed on a report. I want a control to do this.
Is
> such a thing possible with RS 2003?
> Thanks!|||You can certainly do fancy custom controls - even in RS 2000. However, you
need to write your own custom rendering extension (which can be a lot of
implementation work if you want to do more than just output a simple text
file and support complex reportitems any layout).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eRHJk5aUFHA.3840@.tk2msftngp13.phx.gbl...
> What you can do is to have standard controls on the report and hide or
> show
> the control based on an expression.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "CraigZello" <CraigZello@.discussions.microsoft.com> wrote in message
> news:7F8F4F7D-F50C-4C7B-9C06-36804596427D@.microsoft.com...
>> If i'm understanding some previous posts by Robert Bruckner, you really
> can't
>> do extensive or fancy custom controls in RS.
>> Is the following to fancy? I have a integer field which tracks contact
>> method (Email, Fax, Mail, or any combination of the 3). Depending on what
>> this number is, will determine if there are 5 address fields or an email
>> address or an fax number printed on a report. I want a control to do
>> this.
> Is
>> such a thing possible with RS 2003?
>> Thanks!
>

No current backup?

Hello All,
The hourly sheduled appends to the trasnaction log file start genereating
the following in the output log.
There is no current database backup. This log backup cannot be used to roll
forward a preceding database backup. [SQLSTATE 01000].
This is happening on one database on SQL 2000 machine running three
instances of SQL Server. This happening to a database that is on the defaul
t
instance. Does anyone know why this is happening? Other than that, there i
s
one transaction log device and four data devices with the same [Primary]
filegroup defined for each data device in the database. Since I took over a
s
the admin for this particular database, I have not been able to trucnate the
transaction log. The Dbcc produces the following:
Cannot shrink log file 2 ('') because total number of logical log files
cannot be fewer than 2. [SQLSTATE 01000].
I have tried following the instructions as per MSKB 324432 but have'nt had
any luck. The only thing that I have not tried is recreate the database wit
h
only one log and one data device and transfer all the data. Actually I did
start it once and ran out of diskspace when the indexes were being generated
,
so I had to scrap that idea. If you have any clue as to what's going on,
please help.
Thanks.
Bilal Abbasi
Chadbourne & Parke LLP
30 Rockefeller Plaza
New York, NY 10112Hi
It looks like you don't have a full database backup? Backing up the log will
allow the log to be re-used and it won't change the size. Shrinking the log
file can make it fragmented on the disc and should only be carried out after
significant abnormal growth.
John
"Bilal Abbasi" wrote:

> Hello All,
> The hourly sheduled appends to the trasnaction log file start genereating
> the following in the output log.
> There is no current database backup. This log backup cannot be used to rol
l
> forward a preceding database backup. [SQLSTATE 01000].
> This is happening on one database on SQL 2000 machine running three
> instances of SQL Server. This happening to a database that is on the defa
ult
> instance. Does anyone know why this is happening? Other than that, there
is
> one transaction log device and four data devices with the same [Primar
y]
> filegroup defined for each data device in the database. Since I took over
as
> the admin for this particular database, I have not been able to trucnate t
he
> transaction log. The Dbcc produces the following:
> Cannot shrink log file 2 ('') because total number of logical log files
> cannot be fewer than 2. [SQLSTATE 01000].
> I have tried following the instructions as per MSKB 324432 but have'nt had
> any luck. The only thing that I have not tried is recreate the database w
ith
> only one log and one data device and transfer all the data. Actually I di
d
> start it once and ran out of diskspace when the indexes were being generat
ed,
> so I had to scrap that idea. If you have any clue as to what's going on,
> please help.
> Thanks.
>
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112|||see this..from Books Online
Virtual Log Files
Each transaction log file is divided logically into smaller segments called
virtual log files. Virtual log files are the unit of truncation for the
transaction log. When a virtual log file no longer contains log records for
active transactions, it can be truncated and the space becomes available to
log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The minimum
size for a transaction log is 512 KB, which provides two 256-KB virtual log
files. The number and size of the virtual log files in a transaction log
increase as the size of the log file increases. A small log file can have a
small number of small virtual log files (for example, a 5-MB log file that
comprises five 1-MB virtual log files). A large log file can have larger
virtual log files (for example, a 500-MB log file that comprises ten 50-MB
virtual log files).
Microsoft SQL ServerT 2000 tries to avoid having many small virtual log
files. The number of virtual log files grows much more slowly than the size.
If a log file grows in small increments, it tends to have many small virtual
log files. If the log file grows in larger increments, SQL Server creates a
smaller number of larger virtual log files. For example, if the transaction
log is growing by 1-MB increments, the virtual log files are smaller and
more numerous compared to a transaction log growing at 50-MB increments. A
large number of virtual log files can increase the time taken to perform
database recovery.
As records are written to the log, the end of the log grows from one virtual
log file to the next. If there is more than one physical log file for a
database, the end of the log grows through each virtual log file in each
physical file before circling back to the first virtual log file in the
first physical file. Only when all log files are full will the log begin to
grow automatically.
"Bilal Abbasi" <BilalAbbasi@.discussions.microsoft.com> wrote in message
news:A8FE0793-E2B4-47E6-B176-9F78E78D6BFB@.microsoft.com...
> Hello All,
> The hourly sheduled appends to the trasnaction log file start genereating
> the following in the output log.
> There is no current database backup. This log backup cannot be used to
> roll
> forward a preceding database backup. [SQLSTATE 01000].
> This is happening on one database on SQL 2000 machine running three
> instances of SQL Server. This happening to a database that is on the
> default
> instance. Does anyone know why this is happening? Other than that, there
> is
> one transaction log device and four data devices with the same [Primar
y]
> filegroup defined for each data device in the database. Since I took over
> as
> the admin for this particular database, I have not been able to trucnate
> the
> transaction log. The Dbcc produces the following:
> Cannot shrink log file 2 ('') because total number of logical log files
> cannot be fewer than 2. [SQLSTATE 01000].
> I have tried following the instructions as per MSKB 324432 but have'nt had
> any luck. The only thing that I have not tried is recreate the database
> with
> only one log and one data device and transfer all the data. Actually I
> did
> start it once and ran out of diskspace when the indexes were being
> generated,
> so I had to scrap that idea. If you have any clue as to what's going on,
> please help.
> Thanks.
>
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112|||That is exactly the point. The full dump is done and transaction log is
initialized as a scheduled process. Subsequent appends work so the log file
s
increase to around 15 when the output from the append job starts writing
"There is no current backup", just out of the blue. And it's not something
that I can predict either as to at what point this will start to fail.
Another thing that I noticed is that there are 5 rows in the sysfiles table
and 6 in sysfiles1. The sixth row in sysfiles1 is pointing to a physical LD
F
file that does not exist. I have not found a way to get rid of this
obviously orphanned record. It's a mess I need to cleanup somehow.
--
Bilal Abbasi
Chadbourne & Parke LLP
30 Rockefeller Plaza
New York, NY 10112
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> It looks like you don't have a full database backup? Backing up the log wi
ll
> allow the log to be re-used and it won't change the size. Shrinking the lo
g
> file can make it fragmented on the disc and should only be carried out aft
er
> significant abnormal growth.
> John
> "Bilal Abbasi" wrote:
>|||Hi
You could code around this by issuing a full backups if you hit this error
to restart the log backup sequence, or it would probably better to use SQL
profiler to get to the root of this and find what is issuing the statement
that is breaking the log backup sequence.
What does sp_helpfiles return?
Can you take a full backup and restore it on a different machine? If so you
could try ALTER DATABASE <db> REMOVE FILE <logical_file>
John
"Bilal Abbasi" wrote:
[vbcol=seagreen]
> That is exactly the point. The full dump is done and transaction log is
> initialized as a scheduled process. Subsequent appends work so the log fi
les
> increase to around 15 when the output from the append job starts writing
> "There is no current backup", just out of the blue. And it's not somethin
g
> that I can predict either as to at what point this will start to fail.
> Another thing that I noticed is that there are 5 rows in the sysfiles tabl
e
> and 6 in sysfiles1. The sixth row in sysfiles1 is pointing to a physical
LDF
> file that does not exist. I have not found a way to get rid of this
> obviously orphanned record. It's a mess I need to cleanup somehow.
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112
>
> "John Bell" wrote:
>sql

No current backup?

Hello All,
The hourly sheduled appends to the trasnaction log file start genereating
the following in the output log.
There is no current database backup. This log backup cannot be used to roll
forward a preceding database backup. [SQLSTATE 01000].
This is happening on one database on SQL 2000 machine running three
instances of SQL Server. This happening to a database that is on the default
instance. Does anyone know why this is happening? Other than that, there is
one transaction log device and four data devices with the same [Primary]
filegroup defined for each data device in the database. Since I took over as
the admin for this particular database, I have not been able to trucnate the
transaction log. The Dbcc produces the following:
Cannot shrink log file 2 ('') because total number of logical log files
cannot be fewer than 2. [SQLSTATE 01000].
I have tried following the instructions as per MSKB 324432 but have'nt had
any luck. The only thing that I have not tried is recreate the database with
only one log and one data device and transfer all the data. Actually I did
start it once and ran out of diskspace when the indexes were being generated,
so I had to scrap that idea. If you have any clue as to what's going on,
please help.
Thanks.
--
Bilal Abbasi
Chadbourne & Parke LLP
30 Rockefeller Plaza
New York, NY 10112Hi
It looks like you don't have a full database backup? Backing up the log will
allow the log to be re-used and it won't change the size. Shrinking the log
file can make it fragmented on the disc and should only be carried out after
significant abnormal growth.
John
"Bilal Abbasi" wrote:
> Hello All,
> The hourly sheduled appends to the trasnaction log file start genereating
> the following in the output log.
> There is no current database backup. This log backup cannot be used to roll
> forward a preceding database backup. [SQLSTATE 01000].
> This is happening on one database on SQL 2000 machine running three
> instances of SQL Server. This happening to a database that is on the default
> instance. Does anyone know why this is happening? Other than that, there is
> one transaction log device and four data devices with the same [Primary]
> filegroup defined for each data device in the database. Since I took over as
> the admin for this particular database, I have not been able to trucnate the
> transaction log. The Dbcc produces the following:
> Cannot shrink log file 2 ('') because total number of logical log files
> cannot be fewer than 2. [SQLSTATE 01000].
> I have tried following the instructions as per MSKB 324432 but have'nt had
> any luck. The only thing that I have not tried is recreate the database with
> only one log and one data device and transfer all the data. Actually I did
> start it once and ran out of diskspace when the indexes were being generated,
> so I had to scrap that idea. If you have any clue as to what's going on,
> please help.
> Thanks.
>
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112|||see this..from Books Online
Virtual Log Files
Each transaction log file is divided logically into smaller segments called
virtual log files. Virtual log files are the unit of truncation for the
transaction log. When a virtual log file no longer contains log records for
active transactions, it can be truncated and the space becomes available to
log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The minimum
size for a transaction log is 512 KB, which provides two 256-KB virtual log
files. The number and size of the virtual log files in a transaction log
increase as the size of the log file increases. A small log file can have a
small number of small virtual log files (for example, a 5-MB log file that
comprises five 1-MB virtual log files). A large log file can have larger
virtual log files (for example, a 500-MB log file that comprises ten 50-MB
virtual log files).
Microsoft® SQL ServerT 2000 tries to avoid having many small virtual log
files. The number of virtual log files grows much more slowly than the size.
If a log file grows in small increments, it tends to have many small virtual
log files. If the log file grows in larger increments, SQL Server creates a
smaller number of larger virtual log files. For example, if the transaction
log is growing by 1-MB increments, the virtual log files are smaller and
more numerous compared to a transaction log growing at 50-MB increments. A
large number of virtual log files can increase the time taken to perform
database recovery.
As records are written to the log, the end of the log grows from one virtual
log file to the next. If there is more than one physical log file for a
database, the end of the log grows through each virtual log file in each
physical file before circling back to the first virtual log file in the
first physical file. Only when all log files are full will the log begin to
grow automatically.
"Bilal Abbasi" <BilalAbbasi@.discussions.microsoft.com> wrote in message
news:A8FE0793-E2B4-47E6-B176-9F78E78D6BFB@.microsoft.com...
> Hello All,
> The hourly sheduled appends to the trasnaction log file start genereating
> the following in the output log.
> There is no current database backup. This log backup cannot be used to
> roll
> forward a preceding database backup. [SQLSTATE 01000].
> This is happening on one database on SQL 2000 machine running three
> instances of SQL Server. This happening to a database that is on the
> default
> instance. Does anyone know why this is happening? Other than that, there
> is
> one transaction log device and four data devices with the same [Primary]
> filegroup defined for each data device in the database. Since I took over
> as
> the admin for this particular database, I have not been able to trucnate
> the
> transaction log. The Dbcc produces the following:
> Cannot shrink log file 2 ('') because total number of logical log files
> cannot be fewer than 2. [SQLSTATE 01000].
> I have tried following the instructions as per MSKB 324432 but have'nt had
> any luck. The only thing that I have not tried is recreate the database
> with
> only one log and one data device and transfer all the data. Actually I
> did
> start it once and ran out of diskspace when the indexes were being
> generated,
> so I had to scrap that idea. If you have any clue as to what's going on,
> please help.
> Thanks.
>
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112|||That is exactly the point. The full dump is done and transaction log is
initialized as a scheduled process. Subsequent appends work so the log files
increase to around 15 when the output from the append job starts writing
"There is no current backup", just out of the blue. And it's not something
that I can predict either as to at what point this will start to fail.
Another thing that I noticed is that there are 5 rows in the sysfiles table
and 6 in sysfiles1. The sixth row in sysfiles1 is pointing to a physical LDF
file that does not exist. I have not found a way to get rid of this
obviously orphanned record. It's a mess I need to cleanup somehow.
--
Bilal Abbasi
Chadbourne & Parke LLP
30 Rockefeller Plaza
New York, NY 10112
"John Bell" wrote:
> Hi
> It looks like you don't have a full database backup? Backing up the log will
> allow the log to be re-used and it won't change the size. Shrinking the log
> file can make it fragmented on the disc and should only be carried out after
> significant abnormal growth.
> John
> "Bilal Abbasi" wrote:
> > Hello All,
> >
> > The hourly sheduled appends to the trasnaction log file start genereating
> > the following in the output log.
> > There is no current database backup. This log backup cannot be used to roll
> > forward a preceding database backup. [SQLSTATE 01000].
> >
> > This is happening on one database on SQL 2000 machine running three
> > instances of SQL Server. This happening to a database that is on the default
> > instance. Does anyone know why this is happening? Other than that, there is
> > one transaction log device and four data devices with the same [Primary]
> > filegroup defined for each data device in the database. Since I took over as
> > the admin for this particular database, I have not been able to trucnate the
> > transaction log. The Dbcc produces the following:
> >
> > Cannot shrink log file 2 ('') because total number of logical log files
> > cannot be fewer than 2. [SQLSTATE 01000].
> >
> > I have tried following the instructions as per MSKB 324432 but have'nt had
> > any luck. The only thing that I have not tried is recreate the database with
> > only one log and one data device and transfer all the data. Actually I did
> > start it once and ran out of diskspace when the indexes were being generated,
> > so I had to scrap that idea. If you have any clue as to what's going on,
> > please help.
> >
> > Thanks.
> >
> >
> > --
> > Bilal Abbasi
> > Chadbourne & Parke LLP
> > 30 Rockefeller Plaza
> > New York, NY 10112|||Hi
You could code around this by issuing a full backups if you hit this error
to restart the log backup sequence, or it would probably better to use SQL
profiler to get to the root of this and find what is issuing the statement
that is breaking the log backup sequence.
What does sp_helpfiles return?
Can you take a full backup and restore it on a different machine? If so you
could try ALTER DATABASE <db> REMOVE FILE <logical_file>
John
"Bilal Abbasi" wrote:
> That is exactly the point. The full dump is done and transaction log is
> initialized as a scheduled process. Subsequent appends work so the log files
> increase to around 15 when the output from the append job starts writing
> "There is no current backup", just out of the blue. And it's not something
> that I can predict either as to at what point this will start to fail.
> Another thing that I noticed is that there are 5 rows in the sysfiles table
> and 6 in sysfiles1. The sixth row in sysfiles1 is pointing to a physical LDF
> file that does not exist. I have not found a way to get rid of this
> obviously orphanned record. It's a mess I need to cleanup somehow.
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112
>
> "John Bell" wrote:
> > Hi
> >
> > It looks like you don't have a full database backup? Backing up the log will
> > allow the log to be re-used and it won't change the size. Shrinking the log
> > file can make it fragmented on the disc and should only be carried out after
> > significant abnormal growth.
> >
> > John
> >
> > "Bilal Abbasi" wrote:
> >
> > > Hello All,
> > >
> > > The hourly sheduled appends to the trasnaction log file start genereating
> > > the following in the output log.
> > > There is no current database backup. This log backup cannot be used to roll
> > > forward a preceding database backup. [SQLSTATE 01000].
> > >
> > > This is happening on one database on SQL 2000 machine running three
> > > instances of SQL Server. This happening to a database that is on the default
> > > instance. Does anyone know why this is happening? Other than that, there is
> > > one transaction log device and four data devices with the same [Primary]
> > > filegroup defined for each data device in the database. Since I took over as
> > > the admin for this particular database, I have not been able to trucnate the
> > > transaction log. The Dbcc produces the following:
> > >
> > > Cannot shrink log file 2 ('') because total number of logical log files
> > > cannot be fewer than 2. [SQLSTATE 01000].
> > >
> > > I have tried following the instructions as per MSKB 324432 but have'nt had
> > > any luck. The only thing that I have not tried is recreate the database with
> > > only one log and one data device and transfer all the data. Actually I did
> > > start it once and ran out of diskspace when the indexes were being generated,
> > > so I had to scrap that idea. If you have any clue as to what's going on,
> > > please help.
> > >
> > > Thanks.
> > >
> > >
> > > --
> > > Bilal Abbasi
> > > Chadbourne & Parke LLP
> > > 30 Rockefeller Plaza
> > > New York, NY 10112

Monday, March 19, 2012

Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!
SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegr oups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>
|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegr oups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>
|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!
|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegr oups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>
|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:

>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!

Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "d
atabase snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy
database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few
seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modi
fied are first copied to
the Copy databases' database files. This way, the copy database always has t
he data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE
DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database fil
es) are copied back to
the Orig database' files and you are back where you began. Concept is called
"Copy on write".
Same-same (concept) but different (implementation). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a
"database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Cop
y database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a f
ew seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be mo
dified are first copied to
> the Copy databases' database files. This way, the copy database always has
the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTOR
E DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database f
iles) are copied back to
> the Orig database' files and you are back where you began. Concept is call
ed "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). B
ut if you want to
restore from any of them, you need for first DROP DATABASE for all those sna
pshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, y
ou break the log backup
chain sequence for your database. So, if you are doing log backups for that
database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:

>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!

Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
--
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
> >I have become involved with an assessment of a client's nightly update
> > procedures, and I keep running into the following practice.
> > The client consistently copies databases off to separate locations and
> > performs updates upon the new copy. After successful update, the
> > updated database is then copied back to the origin location and
> > becomes the new version. All of this, obviously, to protect
> > themselves in case of error. I can't help but suspect that SQL Server
> > provides an easier way to do it.
> > In the mainframe universe, this used to be accomplished via
> > generational datasets which would be promoted as the baseline when the
> > update completes. I guess what I'm asking is if SQL Server provides
> > any kind of "checkpoint-fallback" capability that the client could use
> > instead of this monstrous time-consuming practice. When talking
> > thousands of updates in a batch, I'm not sure transactioning would be
> > the best way to do it. That would be one big honking rollback.
> > Thanks in advance to any of you architects out there!- Hide quoted text -
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
>> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are
>> initially
>> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take.
>> Then
>> modify your Orig database. As you modify Orig database, the pages to be modified are first copied
>> to
>> the Copy databases' database files. This way, the copy database always has the data as it was
>> when
>> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
>> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back
>> to
>> the Orig database' files and you are back where you began. Concept is called "Copy on write".
>> Same-same (concept) but different (implementation). :-)
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "BizEd" <EdMathia...@.gmail.com> wrote in message
>> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>>
>> >I have become involved with an assessment of a client's nightly update
>> > procedures, and I keep running into the following practice.
>> > The client consistently copies databases off to separate locations and
>> > performs updates upon the new copy. After successful update, the
>> > updated database is then copied back to the origin location and
>> > becomes the new version. All of this, obviously, to protect
>> > themselves in case of error. I can't help but suspect that SQL Server
>> > provides an easier way to do it.
>> > In the mainframe universe, this used to be accomplished via
>> > generational datasets which would be promoted as the baseline when the
>> > update completes. I guess what I'm asking is if SQL Server provides
>> > any kind of "checkpoint-fallback" capability that the client could use
>> > instead of this monstrous time-consuming practice. When talking
>> > thousands of updates in a batch, I'm not sure transactioning would be
>> > the best way to do it. That would be one big honking rollback.
>> > Thanks in advance to any of you architects out there!- Hide quoted text -
>> - Show quoted text -
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:
>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!

Friday, March 9, 2012

next 30 days or all values

Hi,
I have a dataset and I need to be able to filter that data based on
Completion Date.
I have created a user parameter is SSRS with the following values:
0 = All Dates
1 = Next 30 days
I am trying to filter my dataset but I don't know how to filter this
based on my user parameter.
Help!
JasonI figured it out
if the user selects "All" then Parameter!Completion.Value = 0
if the user selects "Next 30 Days" then Parameter!Completion.Value = 1
so my filter looks like:
=iif (Parameters!Completion.Value=1, date.Today.AddDays(30),
date.MaxValue)

Wednesday, March 7, 2012

newline in pdf lost

I'd like to format a textfield with newline. In Preview everything looks ok. After exporting to pdf all newlines are gone. I use the following text inside the textbox:
+++
=controlchars.Newline &
controlchars.Newline &
controlchars.Newline &
controlchars.Newline &
First(Fields!NameKunde.Value, "Absender") & vbnewline &
First(Fields!Ort.Value, "Absender") & vbnewline &
First(Fields!BenutzerAnmeldename.Value, "Absender") & vbnewline &
First(Fields!Vorname.Value, "Absender") & " " & First(Fields!Name.Value, "Absender") & vbnewline &
today
+++
As you see, I tried controlchars.Newline, vbnewline. No differenc in pdf.
Thanks for help
NickiThis looks like a bug. As a workaround you could handle the formatting
using a rectangle that contains a series of textboxes. The sample report at
the end of this posting demonstrates how to accomplish this.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicki" <Nicki@.discussions.microsoft.com> wrote in message
news:4AF42D93-C526-4235-80A7-DF83133A1A48@.microsoft.com...
> I'd like to format a textfield with newline. In Preview everything looks
ok. After exporting to pdf all newlines are gone. I use the following text
inside the textbox:
> +++
> =controlchars.Newline &
> controlchars.Newline &
> controlchars.Newline &
> controlchars.Newline &
> First(Fields!NameKunde.Value, "Absender") & vbnewline &
> First(Fields!Ort.Value, "Absender") & vbnewline &
> First(Fields!BenutzerAnmeldename.Value, "Absender") & vbnewline &
> First(Fields!Vorname.Value, "Absender") & " " & First(Fields!Name.Value,
"Absender") & vbnewline &
> today
> +++
> As you see, I tried controlchars.Newline, vbnewline. No differenc in pdf.
> Thanks for help
> Nicki
>
RectangleTextboxSampleReport.rdl
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<Top>1.625in</Top>
<rd:DefaultName>textbox4</rd:DefaultName>
<Height>0.25in</Height>
<Width>1.875in</Width>
<CanGrow>true</CanGrow>
<Value>=today</Value>
<Left>0.125in</Left>
</Textbox>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Top>1.125in</Top>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.25in</Height>
<Width>1.875in</Width>
<CanGrow>true</CanGrow>
<Value>Last line of text</Value>
<Left>0.125in</Left>
</Textbox>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<Top>0.625in</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<Height>0.25in</Height>
<Width>1.875in</Width>
<CanGrow>true</CanGrow>
<Value>Second line of text</Value>
<Left>0.125in</Left>
</Textbox>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.375in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.25in</Height>
<Width>1.875in</Width>
<CanGrow>true</CanGrow>
<Value>First line of text</Value>
<Left>0.125in</Left>
</Textbox>
</ReportItems>
<Width>2.25in</Width>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Rectangle>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<Width>6.5in</Width>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b77866c3-877c-45bc-a5dc-88760f336daf</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

NewLine and Carriage return issue in PDF format

If I insert the following expression in a textbox it will display
correctly in HTML format but not in PDF format. Can anyone show me how
to accomplish the same thing so that it will display correctly in both
HTML and and PDF format.
vbCrLf & vbCrLf & vbCrLf & "My Text"
In HTML format, it will display "My Text" on the 4th "row". Whereas in
PDF format, it displays "My Text" on the first row of the textbox.This has to be an issue (bug) with PDF and how it formats initial blank
space before the literal text. I am not sure if this is addressed in a
service pack for 2000 or in 2005 (I am using 2000 to test this) but here is
a workaround that I found that places an ASCII record seperator in the first
position before the CRLF's as:
=CHR(30)&vbCrLf & vbCrLf & vbCrLf & "My Text"
That should work.
Rodney Landrum
<Nergock@.gmail.com> wrote in message
news:1143588675.322590.215140@.u72g2000cwu.googlegroups.com...
> If I insert the following expression in a textbox it will display
> correctly in HTML format but not in PDF format. Can anyone show me how
> to accomplish the same thing so that it will display correctly in both
> HTML and and PDF format.
> vbCrLf & vbCrLf & vbCrLf & "My Text"
> In HTML format, it will display "My Text" on the 4th "row". Whereas in
> PDF format, it displays "My Text" on the first row of the textbox.
>

Newbie-Page Torn Detection - ambigious results

Hi All,
I had a database tornpage detected and at traced the table which is causing the problem and executed the following command:
dbcc checktable('inbox',REPAIR_ALLOW_DATA_LOSS).
The output was. CHECKTABLE fixed 0 allocation errors and 90 consistency errors in table 'inbox'
after that i made the db to single user and executed dbcc checkdb and the output was
CHECKDB found 0 allocation errors and 88 consistency errors in database 'HelloDB'.
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 37575172, index ID 0, page (1:96043). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 0, page (1:96045). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HelloDB', index 'Inbox.PK_Inbox' (ID 37575172) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:152032:187) with values (MessageID =3D C9C9BD2B-
C135-487C-B26E-006FDB45B523d=A2=EE ) points to the data row identified by (RID =3D (1:96045:20)).
.
.
.
.
I am confused why it is showing inconsistency errors for DBCC Checkdb whereas it showed all the errors are fixed when checktable was run.
The improvement I found was, even it shows errors on DBCC CheckDB, it is not affecting the Operatins on DB.( before dbcc checktable the db was non operational)
Can anyone suggest on what is happening? and how to overcome this situation?
Hoping for a Quick reply
Thanks in Advance
Regards
ThirumalHi All,
At last I could find the soultion. KB says that Error:Server: Msg 8939, Level 16, State 98, Line 1
CAUSE
The sysindexes.status field should have a value of 2097152 so that the nonclustered index column can allow the insertion of NULL values for both SQL Server 7.0 and SQL Server 2000.
In SQL Server 7.0, if the sysindexes.status value is 0 for the nonclustered index in a table, the nonclustered index also allows the insertion of NULL values. If you run a DBCC CHECKDB statement on the SQL Server 7.0 database, the problem is not reported. After you upgrade the table in SQL Server 2000, the DBCC CHECKDB statement reports the error message described in the "Symptoms" section because a NULL value is not allowed when the sysindexes.status value is not 2097152.
I have run the DBCC DBREINDEX as
DBCC DBREINDEX ('inbox','',0)
after this i have run
dbcc checkdb
no errors Reported
I request the experts to suggest what I have done is proper or not?
Regards
Thirumal
>--Original Message--
>Hi All,
>I had a database tornpage detected and at traced the table >which is causing the problem and executed the following >command:
>dbcc checktable('inbox',REPAIR_ALLOW_DATA_LOSS).
>The output was. CHECKTABLE fixed 0 allocation errors and >90 consistency errors in table 'inbox'
>after that i made the db to single user and executed dbcc >checkdb and the output was >CHECKDB found 0 allocation errors and 88 consistency >errors in database 'HelloDB'. >Server: Msg 8939, Level 16, State 98, Line 1
>Table error: Object ID 37575172, index ID 0, page >(1:96043). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>>berrcode) failed. Values are 2057 and -1.
>Server: Msg 8939, Level 16, State 1, Line 1
>Table error: Object ID 37575172, index ID 0, page >(1:96045). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>>berrcode) failed. Values are 2057 and -1.
>Server: Msg 8952, Level 16, State 1, Line 1
>Table error: Database 'HelloDB', index 'Inbox.PK_Inbox' >(ID 37575172) (index ID 2). Extra or invalid key for the >keys:
>Server: Msg 8956, Level 16, State 1, Line 1
>Index row (1:152032:187) with values (MessageID =3D C9C9BD2B-
>C135-487C-B26E-006FDB45B523d=A2=EE ) points to the data row >identified by (RID =3D (1:96045:20)).
>..
>..
>..
>..
>I am confused why it is showing inconsistency errors for >DBCC Checkdb whereas it showed all the errors are fixed >when checktable was run.
>The improvement I found was, even it shows errors on DBCC >CheckDB, it is not affecting the Operatins on DB.( before >dbcc checktable the db was non operational)
>Can anyone suggest on what is happening? and how to >overcome this situation?
>Hoping for a Quick reply
>Thanks in Advance
>Regards
>Thirumal
>.
>

Saturday, February 25, 2012

Newbie:help on maximum of a series of columns

Hi,
I have the following table:
tblMeasurements
Date | value1| value2| ...|value24|
I need to find (and display) the maximum per day, i.e. the maximum number
among the value1...value24 in the same row (date).
I tried MAX(value1,....,value24) to no avail.
The examples I see on the net seem to find the max for all the entries of a
*specific* column. I want the max of a specific row. Can it be done and am i
looking at the right function ?
I know its a simple question but i cant seem to find the answer.
TIA
-steveSteve
Why not to store like this
CREATE TABLE #Test
(
[Id] Int not null primary key,
[Date] datetime not null,
Value int not null
)
SELECT MAX(Value),Date FROM #Test
GROUP BY Date
"Steve" <try@.this.com> wrote in message
news:xOz0e.19063$ZC6.78184@.wagner.videotron.net...
> Hi,
> I have the following table:
> tblMeasurements
> Date | value1| value2| ...|value24|
> I need to find (and display) the maximum per day, i.e. the maximum number
> among the value1...value24 in the same row (date).
> I tried MAX(value1,....,value24) to no avail.
> The examples I see on the net seem to find the max for all the entries of
a
> *specific* column. I want the max of a specific row. Can it be done and am
i
> looking at the right function ?
> I know its a simple question but i cant seem to find the answer.
> TIA
> -steve
>|||Try,
select
[Date]
max(value)
from
(
select
[date],
case n.colA
when 1 then value1
when 2 then value2
..
when 24 then value24
end as value
from
t
cross join
(
select 1
union all
select 2
union all
..
select 24
) as n(colA)
) as a
group by
[date]
go
AMB
"Steve" wrote:

> Hi,
> I have the following table:
> tblMeasurements
> Date | value1| value2| ...|value24|
> I need to find (and display) the maximum per day, i.e. the maximum number
> among the value1...value24 in the same row (date).
> I tried MAX(value1,....,value24) to no avail.
> The examples I see on the net seem to find the max for all the entries of
a
> *specific* column. I want the max of a specific row. Can it be done and am
i
> looking at the right function ?
> I know its a simple question but i cant seem to find the answer.
> TIA
> -steve
>
>

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:
I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.
I want to ask before i do something stupid.
- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?
Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.
-steveBackup the database on the "good" server, transfer the backup to the "bad"
server, and restore this
backup over the database on the "bad" server.
Refer to BACKUP DATABASE & RESTORE in BOL
"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
Server2.
> There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>|||"steve" wrote:

> I have the following simple situtation:
By far the easiest way to do this is to "move" the actual physical data
files. Doing this is easy with attach/detatch. See:
http://support.microsoft.com/defaul...kb;en-us;224071
Maury|||"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.
If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.
Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.
Simon

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:
I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.
I want to ask before i do something stupid.
- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?
Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.
-steveBackup the database on the "good" server, transfer the backup to the "bad"
server, and restore this
backup over the database on the "bad" server.
Refer to BACKUP DATABASE & RESTORE in BOL
"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
Server2.
> There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>|||"steve" wrote:
> I have the following simple situtation:
By far the easiest way to do this is to "move" the actual physical data
files. Doing this is easy with attach/detatch. See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
Maury|||"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.
If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.
Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.
Simon

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:
I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.
I want to ask before i do something stupid.
- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?
Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.
-steve
Backup the database on the "good" server, transfer the backup to the "bad"
server, and restore this
backup over the database on the "bad" server.
Refer to BACKUP DATABASE & RESTORE in BOL
"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
Server2.
> There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
|||"steve" wrote:

> I have the following simple situtation:
By far the easiest way to do this is to "move" the actual physical data
files. Doing this is easy with attach/detatch. See:
http://support.microsoft.com/default...b;en-us;224071
Maury
|||"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.
If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.
Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.
Simon

Friday, March 30, 2012

No Mail icon in control panel?

Wednesday, March 28, 2012

No entries in Database Mail log (sysmail_event_log)

No entries in Database Mail log (sysmail_event_log)

Monday, March 26, 2012

No disk in drive a:

Friday, March 23, 2012

No Custom Controls in 2003?

No current backup?

No current backup?

Monday, March 19, 2012

Nightly Update / Fallback protection

Nightly Update / Fallback protection

Nightly Update / Fallback protection

Friday, March 9, 2012

next 30 days or all values

Wednesday, March 7, 2012

newline in pdf lost

NewLine and Carriage return issue in PDF format

Newbie-Page Torn Detection - ambigious results

Saturday, February 25, 2012

Newbie:help on maximum of a series of columns

Newbie: Transfering databases from different servers

Newbie: Transfering databases from different servers

Newbie: Transfering databases from different servers

Metadata Services

Blog Archive

About Me