Automating "picture" of server performance

Hi all

I need to do the following using the performance monitor tool.

Im monitoring the %cpu used, transaction/sec and page/sec of a server using the perfomance monitor.

What i would need to do is to monitor all these information in a log that will reset himself once it has reached its maximum size limit. I also need to be able to take a "screenshot" of what was in the log when it became full but i cannot simply save the file because it would use too much space in the long run.

Is there a way for me to automating this information and make it so that each time the log become full i can take a screenshot (By that i mean getting a image of the information under the form of a graphic) of what was inside the log before it restart?

Ask if you need more precision

Dale

If you can't save the file, you probably can't save a graphic that would include everything and all the details in the file.

You can automate perf mon and dump it to a CSV. But if you tried to create a screenshot that shows all the details of all the info in the CSV and save all of those, it's likely not going to save space. Maybe you should look at the interval you are using in perf mon. Or save the file somewhere off the server if it's just space on the server itself. Or load the csv files into a table in a database. I just am not following the whole screenshot concept. It's not going to be that useful in analysis to have a ton of screenshots to look through.

-Sue

|||

I need those to help determine the best moment of the week to monitor the system through a server sided trace.

I think the best will be to just keep all the log and extract the information i want from it at the end of the week.

automatically send a notice after 6 months (was "Date Question")

Hello,

I'm trying to write a query that will do the following... I'll give a quick background:

When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.

Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.

I'm not sure how to reference this situation... or how to start addressing the problem.

Basically:

Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)

Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).

;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old

that way you can prepare the notices manually

sorry, no idea how to set up access to do this automatically

perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.

Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.

There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:

PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)

Automatic rollback inside using statement?

If I start a transaction using the following approach ...

using (SqlTransaction trans = destConn.BeginTransaction())
{

...do some transfers using SqlBulkCopy
}

...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?

No.If an error occurs,Rollback the transaction in acatch statement to void out any changes and thenrethrow the error so that the application can deal with it accordingly.

http://davidhayden.com/blog/dave/archive/2005/10/14/2515.aspx

|||Is it really necessary to close the connection inside ausing (SqlConnection connection1 =new SqlConnection(connectString1)) block like David Hayden does?|||

They don't do it here:

http://msdn2.microsoft.com/en-us/library/tchktcdk.aspx

Also look at TransactionScopes they're pretty interesting:

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

|||

Let me tell you the internals of transaction it is mathmatically defined to be one operation at a time by the people who created it but SQL Server langauge T-SQL let you create more than one at a time through what we call transaction savepoints you may have seen them in David's blog entry. That is called nested transaction but you must use savepoint or the whole thing will rollback to the first if there is an error. Those savepoints can be rolled back as of service pack 3 of SQL Server 2000 if needed. And you must use the second Using statement before you use them so the system can call dispose for you automatically, that is good practice so resources like transaction is not running when you are through with them. And if you are not hosting the application in your local intranet you cannot use transaction scope because transaction scope performs illegal none atomic transactions and SQL Server does the right thing and promotes the operation to distributed transaction, if you use transaction scope you must call a T-SQL stored procedure doing the correct atomic transaction to finish your transaction block. There is an auto commit mode that is the default unless you change the definition in SQL Server and automatic roll back is if you don't use the savepoint and there is an error in your code. Post again if you still have questions. Try the thread below and read the code and the info in the links in it and you will understand what I have explained here. Hope this helps.

http://forums.asp.net/thread/1284424.aspx

|||

Thanx!

I'll read the thread

Automatic Replication Trigger

HI All,
Using SQL Server 2000 SP3.
Replication is set up on a few databases, on each of the tables that are
replicated the following trigger appears:
create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
update as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[Voucher]
set msrepl_tran_version = newid()
from [dbo].[Voucher], inserted
where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
This trigger is causing a significant slowdown when called and I would
like to remove it. However I do not want to break the replication.
I need to understand what has created this trigger and why. I belive
that this has been implemented as a workaround for some problem that was
encountered by the replication process but this is merely speculation on
my behalf.
Your help is very much appreciated.
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
It looks like you are using immediate or queued updating which puts these
triggers in place on the publisher. I can't comment on Microsoft's logic
behind this proc. Basically what it does is assigns a new value to the guid
column if the guid column it self is not modified/supplied.
I would look at whether you really need immediate updating or bi-directional
replication in general as bi-directional transactional replication offers
some of the functionality but will not cause the performance degradation.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:uU1y0aSSGHA.4956@.TK2MSFTNGP09.phx.gbl...
>
> HI All,
> Using SQL Server 2000 SP3.
> Replication is set up on a few databases, on each of the tables that are
> replicated the following trigger appears:
> create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
> update as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [dbo].[Voucher]
> set msrepl_tran_version = newid()
> from [dbo].[Voucher], inserted
> where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
>
> This trigger is causing a significant slowdown when called and I would
> like to remove it. However I do not want to break the replication.
> I need to understand what has created this trigger and why. I belive
> that this has been implemented as a workaround for some problem that was
> encountered by the replication process but this is merely speculation on
> my behalf.
> Your help is very much appreciated.
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for your response here. What is required is one-way replication
as we have a primary server that needs to replicate information to a
backup server.
I presume that this is a different configuration from immediate/queued
updating. I thought that transactional replication is what was set up
but perhaps this is not the case?
Can the specific kind of replication be altered in situ or would it need
to be dropped and recreated?
Again many thanks for any help!
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
|||Use transactional replication if you have no requirement for
bi-directionality. to fix this drop the subscriber, and then disable the
triggers, then redeploy the subscriber as a plain vanilla subscriber.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:OarBgsZSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
> Thanks for your response here. What is required is one-way replication
> as we have a primary server that needs to replicate information to a
> backup server.
> I presume that this is a different configuration from immediate/queued
> updating. I thought that transactional replication is what was set up
> but perhaps this is not the case?
> Can the specific kind of replication be altered in situ or would it need
> to be dropped and recreated?
> Again many thanks for any help!
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that executes the following command "restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101 "unable to get exclusive use".
Absolutely no one is on this server when I do this
From which database are you executing the restore command? Make sure it is the master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this server when I do this
|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that exe
cutes the following command "restore database sqleligibilitydatabase from di
sk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101 "un
able to get exclusive use".
Absolutely no one is on this server when I do thisFrom which database are you executing the restore command? Make sure it is t
he master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine
that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes
the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibil
itydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this serv
er when I do this|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this

Automated backup failing

I keepp getting the following error in my application log for SQL Server 2000:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan master, msdb, model''
(0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
Does anyone know what this means?
--
John SchusterSpecify a report file for the plan and check that file for detailed information about errors,. My
guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
can't do log backup for master even if you set it to full recovery (master is special).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>I keepp getting the following error in my application log for SQL Server 2000:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan master, msdb, model''
> (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> Does anyone know what this means?
> --
> John Schuster|||Tibor,
Thank you very much. I will try doing what you suggest.
--
John Schuster
"Tibor Karaszi" wrote:
> Specify a report file for the plan and check that file for detailed information about errors,. My
> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> can't do log backup for master even if you set it to full recovery (master is special).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >I keepp getting the following error in my application log for SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Does anyone know what this means?
> > --
> >
> > John Schuster
>|||I do have reporting set up. This is the message I see for the maintenance
plan in question:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
9/27/2007 12:00:06 AM
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database model: Transaction Log Backup...
Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
9/27/2007 12:00:06 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
--
John Schuster
"Tibor Karaszi" wrote:
> Specify a report file for the plan and check that file for detailed information about errors,. My
> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> can't do log backup for master even if you set it to full recovery (master is special).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >I keepp getting the following error in my application log for SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Does anyone know what this means?
> > --
> >
> > John Schuster
>|||Yep, as I suspected. You can't do log backups on databases that are in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>I do have reporting set up. This is the message I see for the maintenance
> plan in question:
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 9/27/2007 12:00:06 AM
> Backup can not be performed on database 'master'. This sub task is ignored.
> [1] Database model: Transaction Log Backup...
> Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 9/27/2007 12:00:06 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> Specify a report file for the plan and check that file for detailed information about errors,. My
>> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
>> can't do log backup for master even if you set it to full recovery (master is special).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >I keepp getting the following error in my application log for SQL Server 2000:
>> >
>> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
>> > 'DB Maintenance Plan master, msdb, model''
>> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
>> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >
>> > Does anyone know what this means?
>> > --
>> >
>> > John Schuster|||Tibor,
How, then, do I back up the master and msdb databases? Is it possible?
--
John Schuster
"Tibor Karaszi" wrote:
> Yep, as I suspected. You can't do log backups on databases that are in simple recovery model.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >I do have reporting set up. This is the message I see for the maintenance
> > plan in question:
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 9/27/2007 12:00:06 AM
> > Backup can not be performed on database 'master'. This sub task is ignored.
> >
> > [1] Database model: Transaction Log Backup...
> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [2] Database model: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Backup can not be performed on database 'msdb'. This sub task is ignored.
> >
> > Deleting old text reports... 1 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 9/27/2007 12:00:06 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Specify a report file for the plan and check that file for detailed information about errors,. My
> >> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> >> can't do log backup for master even if you set it to full recovery (master is special).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >I keepp getting the following error in my application log for SQL Server 2000:
> >> >
> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> >> > 'DB Maintenance Plan master, msdb, model''
> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >
> >> > Does anyone know what this means?
> >> > --
> >> >
> >> > John Schuster
> >>
>|||John,
You can only perform the Full backup, don't check for the Transaction log
backups.
Chris
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> Tibor,
> How, then, do I back up the master and msdb databases? Is it possible?
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> Yep, as I suspected. You can't do log backups on databases that are in
>> simple recovery model.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>> >I do have reporting set up. This is the message I see for the
>> >maintenance
>> > plan in question:
>> >
>> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> > Server
>> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
>> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> > 9/27/2007 12:00:06 AM
>> > Backup can not be performed on database 'master'. This sub task is
>> > ignored.
>> >
>> > [1] Database model: Transaction Log Backup...
>> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > [2] Database model: Verifying Backup...
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Backup can not be performed on database 'msdb'. This sub task is
>> > ignored.
>> >
>> > Deleting old text reports... 1 file(s) deleted.
>> >
>> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> > 9/27/2007 12:00:06 AM
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> > --
>> > John Schuster
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Specify a report file for the plan and check that file for detailed
>> >> information about errors,. My
>> >> guess is that you try to do log backups for databases that are in
>> >> simple recovery mode. Also, you
>> >> can't do log backup for master even if you set it to full recovery
>> >> (master is special).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >> >I keepp getting the following error in my application log for SQL
>> >> >Server 2000:
>> >> >
>> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
>> >> > Maintenance Plan
>> >> > 'DB Maintenance Plan master, msdb, model''
>> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked
>> >> > by
>> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >> >
>> >> > Does anyone know what this means?
>> >> > --
>> >> >
>> >> > John Schuster
>> >>|||Thank you.
--
John Schuster
"Chris Wood" wrote:
> John,
> You can only perform the Full backup, don't check for the Transaction log
> backups.
> Chris
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> > Tibor,
> >
> > How, then, do I back up the master and msdb databases? Is it possible?
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Yep, as I suspected. You can't do log backups on databases that are in
> >> simple recovery model.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >> >I do have reporting set up. This is the message I see for the
> >> >maintenance
> >> > plan in question:
> >> >
> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> >> > Server
> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> > 9/27/2007 12:00:06 AM
> >> > Backup can not be performed on database 'master'. This sub task is
> >> > ignored.
> >> >
> >> > [1] Database model: Transaction Log Backup...
> >> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >> >
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > [2] Database model: Verifying Backup...
> >> >
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > Backup can not be performed on database 'msdb'. This sub task is
> >> > ignored.
> >> >
> >> > Deleting old text reports... 1 file(s) deleted.
> >> >
> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> > 9/27/2007 12:00:06 AM
> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> > --
> >> > John Schuster
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Specify a report file for the plan and check that file for detailed
> >> >> information about errors,. My
> >> >> guess is that you try to do log backups for databases that are in
> >> >> simple recovery mode. Also, you
> >> >> can't do log backup for master even if you set it to full recovery
> >> >> (master is special).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >> >I keepp getting the following error in my application log for SQL
> >> >> >Server 2000:
> >> >> >
> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
> >> >> > Maintenance Plan
> >> >> > 'DB Maintenance Plan master, msdb, model''
> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked
> >> >> > by
> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >> >
> >> >> > Does anyone know what this means?
> >> >> > --
> >> >> >
> >> >> > John Schuster
> >> >>
> >>
>
>|||Recommended backup strategy for system databases (such as master, msdb,
model) is taking full backups of them when you make a change in your SQL
Server system like changing SQL Server configuration, adding new logins,
adding new jobs, adding linked servers etc.
Transaction Log backup is unnecessary for system databases.
--
Ekrem Ã?nsoy
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:B4858CF1-97B9-4A03-A365-802CFB2C92E9@.microsoft.com...
> Thank you.
> --
> John Schuster
>
> "Chris Wood" wrote:
>> John,
>> You can only perform the Full backup, don't check for the Transaction log
>> backups.
>> Chris
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
>> > Tibor,
>> >
>> > How, then, do I back up the master and msdb databases? Is it possible?
>> > --
>> > John Schuster
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Yep, as I suspected. You can't do log backups on databases that are in
>> >> simple recovery model.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>> >> >I do have reporting set up. This is the message I see for the
>> >> >maintenance
>> >> > plan in question:
>> >> >
>> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> >> > Server
>> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
>> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model'
>> >> > on
>> >> > 9/27/2007 12:00:06 AM
>> >> > Backup can not be performed on database 'master'. This sub task is
>> >> > ignored.
>> >> >
>> >> > [1] Database model: Transaction Log Backup...
>> >> > Destination:
>> >> > [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >> >
>> >> > [2] Database model: Verifying Backup...
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >> >
>> >> > Backup can not be performed on database 'msdb'. This sub task is
>> >> > ignored.
>> >> >
>> >> > Deleting old text reports... 1 file(s) deleted.
>> >> >
>> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> >> > 9/27/2007 12:00:06 AM
>> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> > --
>> >> > John Schuster
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Specify a report file for the plan and check that file for detailed
>> >> >> information about errors,. My
>> >> >> guess is that you try to do log backups for databases that are in
>> >> >> simple recovery mode. Also, you
>> >> >> can't do log backup for master even if you set it to full recovery
>> >> >> (master is special).
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >> >> >I keepp getting the following error in my application log for SQL
>> >> >> >Server 2000:
>> >> >> >
>> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
>> >> >> > Maintenance Plan
>> >> >> > 'DB Maintenance Plan master, msdb, model''
>> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked
>> >> >> > on:
>> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was
>> >> >> > invoked
>> >> >> > by
>> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step
>> >> >> > 1).
>> >> >> >
>> >> >> > Does anyone know what this means?
>> >> >> > --
>> >> >> >
>> >> >> > John Schuster
>> >> >>
>> >>
>>|||Thank you.
--
John Schuster
"Ekrem Ã?nsoy" wrote:
> Recommended backup strategy for system databases (such as master, msdb,
> model) is taking full backups of them when you make a change in your SQL
> Server system like changing SQL Server configuration, adding new logins,
> adding new jobs, adding linked servers etc.
> Transaction Log backup is unnecessary for system databases.
> --
> Ekrem Ã?nsoy
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:B4858CF1-97B9-4A03-A365-802CFB2C92E9@.microsoft.com...
> > Thank you.
> > --
> > John Schuster
> >
> >
> > "Chris Wood" wrote:
> >
> >> John,
> >>
> >> You can only perform the Full backup, don't check for the Transaction log
> >> backups.
> >>
> >> Chris
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> >> > Tibor,
> >> >
> >> > How, then, do I back up the master and msdb databases? Is it possible?
> >> > --
> >> > John Schuster
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Yep, as I suspected. You can't do log backups on databases that are in
> >> >> simple recovery model.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >> >> >I do have reporting set up. This is the message I see for the
> >> >> >maintenance
> >> >> > plan in question:
> >> >> >
> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> >> >> > Server
> >> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> >> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model'
> >> >> > on
> >> >> > 9/27/2007 12:00:06 AM
> >> >> > Backup can not be performed on database 'master'. This sub task is
> >> >> > ignored.
> >> >> >
> >> >> > [1] Database model: Transaction Log Backup...
> >> >> > Destination:
> >> >> > [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >> >
> >> >> > [2] Database model: Verifying Backup...
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >> >
> >> >> > Backup can not be performed on database 'msdb'. This sub task is
> >> >> > ignored.
> >> >> >
> >> >> > Deleting old text reports... 1 file(s) deleted.
> >> >> >
> >> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> >> > 9/27/2007 12:00:06 AM
> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> >> > --
> >> >> > John Schuster
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Specify a report file for the plan and check that file for detailed
> >> >> >> information about errors,. My
> >> >> >> guess is that you try to do log backups for databases that are in
> >> >> >> simple recovery mode. Also, you
> >> >> >> can't do log backup for master even if you set it to full recovery
> >> >> >> (master is special).
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >> >> >I keepp getting the following error in my application log for SQL
> >> >> >> >Server 2000:
> >> >> >> >
> >> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
> >> >> >> > Maintenance Plan
> >> >> >> > 'DB Maintenance Plan master, msdb, model''
> >> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked
> >> >> >> > on:
> >> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was
> >> >> >> > invoked
> >> >> >> > by
> >> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step
> >> >> >> > 1).
> >> >> >> >
> >> >> >> > Does anyone know what this means?
> >> >> >> > --
> >> >> >> >
> >> >> >> > John Schuster
> >> >> >>
> >> >>
> >>
> >>
> >>
>

auto update/create statistics

For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
--
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon

auto save query result to a text file

Is there an easy way to automate the following steps
from SQL server:
1. run a sql-query script (like "select * from ..." )
2. save the query result to a .txt or .rpt file in a
designated folder.
-i know i can write some c++ application to do this
but is there an easy way (like some simple script to be
run as scheduled task)
to do it ?
THANKS!The osql utility will do this for you. It can take a query as an input
parameter, and send its output to a file which is also specified as a
parameter.
The osql command can be called from a command file (.bat) so it can be
automated fully.
See the full osql syntax in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"AMY" <anonymous@.discussions.microsoft.com> wrote in message
news:2bb7601c39331$37fc0650$a601280a@.phx.gbl...
> Is there an easy way to automate the following steps
> from SQL server:
> 1. run a sql-query script (like "select * from ..." )
> 2. save the query result to a .txt or .rpt file in a
> designated folder.
> -i know i can write some c++ application to do this
> but is there an easy way (like some simple script to be
> run as scheduled task)
> to do it ?
> THANKS!
>|||thank you Kalen, it's big help !
-amy
>--Original Message--
>The osql utility will do this for you. It can take a
query as an input
>parameter, and send its output to a file which is also
specified as a
>parameter.
>The osql command can be called from a command file (.bat)
so it can be
>automated fully.
>See the full osql syntax in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"AMY" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2bb7601c39331$37fc0650$a601280a@.phx.gbl...
>> Is there an easy way to automate the following steps
>> from SQL server:
>> 1. run a sql-query script (like "select * from ..." )
>> 2. save the query result to a .txt or .rpt file in a
>> designated folder.
>> -i know i can write some c++ application to do this
>> but is there an easy way (like some simple script to be
>> run as scheduled task)
>> to do it ?
>> THANKS!
>>
>
>.
>

Auto run report

Hello

Anyone know if any of the following are possible:

Change the icon for a report in the report manager. I want a report to have a folder icon, so it therefore looks like a folder. When a user opens a folder, have a report automatically run and display. Have a report automatically open when the user opens the report manager.

Cheers

**Bump** - Anyone?

Auto Number PROBLEM

I have a table namely Product with the following identity modifiers:-
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1

My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:

Num Name
30 Apple
31 Orange
32 Pineapple

If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.

Num Name
30 Apple
32 Pineapple

What can I do to ensure that the Num column (Num 32) will change to Num 31?

Thank you.what you could do is think very hard about what you seem to want to do

suppose you have an Orders table, which contains customer orders for all products you've sold

if you renumber the products in the products table, you must therefore renumber the Orders table too

this could shut your database down for a few hours

and why? what is the benefit?

autonumbers should never be reassigned

it's not as though you're going to run out of numbers

you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table

RowNumber

Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102

If you add a new fruit
RowNumber = Count(*) of the table

If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit

If you delete the Banana

Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)

Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.

But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)

also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table

and i repeat: what exactly is the benefit of doing it?

if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!

Auto join in SQL Query

I've got the following table :
CREATE TABLE M6_CHECK
(
ID_QUOTE VARCHAR(20),
ID_ITEM VARCHAR(20),
BEGIN_DATE DATETIME,
END_DATE DATETIME
)
This table contains the following data :
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I1','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I2','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I3','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I1','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I4','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I2','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I5','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I3','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I6','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q5','I4','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q6','I5','01/12/2000','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q7','I6','01/31/2001','12/31/2001')
I am trying to find a SQL query which would return the following recordset :
ID_ITEM ID_QUOTE_1 END_DATE_1 ID_QUOTE_2 BEGIN_DATE_2
----
I5 Q3 12/31/2000 Q6 01/12/2000
I6 Q4 12/31/2000 Q7 01/31/2001
Let me explain :
The SQL Query should return every ID_ITEM for which 2 consecutives quotes
don't verify the following rule :
end date previous quote = begin date next quote - 1 day
It looks like I should make an auto join in the query but I couldn't make it
:(
Thanks in advance for your help,
AlexSELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t1.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_QUOTE,t1.ID_ITEM,t1.BEGIN_DATE|||typo..
t1.BEGIN_DATE as BEGIN_DATE_2
should be
t2.BEGIN_DATE as BEGIN_DATE_2|||This doesn't show up in the test data, but
more correctly I think it should be this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
AND t2.BEGIN_DATE=(SELECT MIN(t3.BEGIN_DATE)
FROM M6_CHECK t3
WHERE t3.ID_ITEM=t1.ID_ITEM
AND
t3.BEGIN_DATE>t1.BEGIN_DATE)
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE|||It's almost the good result. The problem is that if you add the following
record
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q9','I3','01/01/2001','12/31/2001');
This record should appear in the result, and with your query it does not :(
Damn it !!
Thanks a lot for your help !!
Alex|||What result are you expecting? Q1 -> Q9 or Q9 ->Q1 or both?
Try this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.ID_QUOTE<t2.ID_QUOTE
AND t1.BEGIN_DATE<=t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE

Auto insert a variable number of records?

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?

Thanks in advance,

Sunny Guam

Can't you put all operations in a trigger on tblA? Put the SELECT command to tblC before INSERT to tblB so that you can dynamically build INSERT commands to tblB.|||

Thanks for the advice, but at the risk of sounding dumb; I'm not familiar with triggers and how to set them up within SQL 2005. Can you give me an example perhaps?

|||

Here is an example:http://forums.asp.net/thread/1281234.aspx

You can start from hereEnforcing Business Rules with Triggers