Automating Capacity planning of the disks

I will like to write a short code to automate the
procedure of checking the total space available on the
disks on the server toavoid running out of space and to
avoid manual intervention. Any help please.See if this helps: http://www.sqldbatips.com/showcode.asp?ID=4
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c24301c47a34$4a90d3f0$a301280a@.phx.gbl...
I will like to write a short code to automate the
procedure of checking the total space available on the
disks on the server toavoid running out of space and to
avoid manual intervention. Any help please.

Automating backups with TSM

I was wondering if anyone can share the procedure(s) used in setting up an automated MS SQL database backup through tivoli Storage Manager
( 5.2.7 )........?I'd settle for getting Tivoli to back up files 50% or more of the time. I'd consider getting Tivoli to do reliable, repeatable backups to be a miracle.

-PatPsql

automatically update another field based on other parts of the record

Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS
UPDATE CartItems Set quantity = @.newQuantity
WHERE cartItemID = @.cartItemID
GO

sure. you can update as many fields as you want within the UPDATE statement.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS

SET NOCOUNT ON

UPDATE
CartItems
Set
quantity = @.newQuantity
,lineTotal =pounds * itemSell
WHERE
cartItemID = @.cartItemID

SET NOCOUNT OFF

GO

|||Or make the lineTotal a computed column. Just modify the table and tell it the lineTotal column is equal to pounds*itemSell.sql

Automatically run query plan.

Is there a way to tell the SQL Server 2005 to automatically generate
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.Can you elaborate? A plan *is* created when you execute the procedure. You c
an't create a plan
without executing the procedure since the plan is dependent on a number of t
hings like environment
settings (like SET) and also parameter values.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...

> Is there a way to tell the SQL Server 2005 to automatically generate query
plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea,
but still).
> Thanks.|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:

> Tibor Karaszi wrote:
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/pr...5/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).|||You can a column called "plan_handle". You feed the value from this into the
function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gb
l...
> Kalen Delaney wrote:
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan i
n SQL Server Management
> Studio (for inspection).
>|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Automatically run query plan.

Is there a way to tell the SQL Server 2005 to automatically generate
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.
Can you elaborate? A plan *is* created when you execute the procedure. You can't create a plan
without executing the procedure since the plan is dependent on a number of things like environment
settings (like SET) and also parameter values.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is there a way to tell the SQL Server 2005 to automatically generate query plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea, but still).
> Thanks.
|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.
|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.
|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:

> Tibor Karaszi wrote:
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com
|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com
|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).
|||You can a column called "plan_handle". You feed the value from this into the function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney wrote:
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan in SQL Server Management
> Studio (for inspection).
>
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Automatically run query plan.

Is there a way to tell the SQL Server 2005 to automatically generate
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.Can you elaborate? A plan *is* created when you execute the procedure. You can't create a plan
without executing the procedure since the plan is dependent on a number of things like environment
settings (like SET) and also parameter values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is there a way to tell the SQL Server 2005 to automatically generate query plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea, but still).
> Thanks.|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
>> Understood. However, after the proc is ran, I'd love to have SQL Server
>> save somewhere that execution plan that it ran the proc with.
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).|||You can a column called "plan_handle". You feed the value from this into the function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney wrote:
>> SQL Server 2005 automatically captures this information and the plan is available in
>> sys.dm_exec_cached_plans. (Unless the plan is never cached at all, as when you create or exec the
>> proc WITH RECOMPILE). You don't need profiler.
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan in SQL Server Management
> Studio (for inspection).
>|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql

Automatically run a stored procedure at 6am every day?

Hi All,

I'm wondering if anyone can point me at some online resources that
demonstrate how to run a stored procedure against an SQL Server 2000
server at 6am each day?
Any help much appreciated!

Much warmth,

planetthoughtfulhttp://msdn.microsoft.com/library/e...tomate_4v1v.asp

--
David Portas
SQL Server MVP
--|||http://msdn.microsoft.com/library/d...p_adda_9tbk.asp

Automatically Fire Trigger Or Procedure in sql server 2000

Hi Guys

I want to automatically fire a trigger or Procedure every day 8:00 P.m. How can we achieve this? is there any way in sql server which checks the time regularly and can fire the trigger or we have to write a batch which runs at the specified time and try to create a situation so that the trigger is automatically fired?
plssssss get back with solution.. this is very urgent....
thanks in advance...

Krishna

In Enterprise Manager, <server name> -> Management -> SQL Server agent -> Jobs

Here you can create your job.

|||

when i execute a procedure it will display error like this

Server: Msg 217, Level 16, State 1, Procedure sp_PPS_send_cdontsmail, Line 12
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Thanks

Krishna

|||Then you need to debug your stored proc and figure out what you're doing wrong. Looks like you have an inadvertent loop that's causing something to continuously call itself.|||

Hi Greg

it working i changed my procedure name then it will be working sucessfully message. but i mail was not sending. it will take more time. i am executing procedure in queryanalyzer it's work fine. i have a created a job it's won't work.

if you don't mind are you online in yahoo please response me

i am in rama_krishna76@.yahoo.com.

Krishna

|||I don't know anything about sending mail, but could it have to do with the login account that's executing the stored proc? What account is used to start the sql server agent service? When you logged into query analyzer, did you log in with the same account, or did you use a different one?

automatic transaction - Stored procedure - @@identity

Hi, i am having a hard time understanding the concept maybe .

i am using automatic transaction as such:

[AutoComplete]

public void SaveInvoiceAndCharges()

{

//try

//{

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

// DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

//}

//catch (Exception e)

//{

// throw e;

//}

}

and my sp :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Invoice_New]

(

@.DateIssued datetime,

@.Reference nvarchar(50),

@.Note nvarchar(50),

@.DocumentType int,

@.CompanyCode int,

@.Valid nchar(1),

@.Fees int,

@.isDistributed nchar(1)

)

AS

SET NOCOUNT OFF;

Begin try

INSERT INTO [Invoice].[Invoice] ([DateIssued], [Reference], [Note],

[DocumentType], [CompanyCode], [Valid], [Fees], [isDistributed])

VALUES (@.DateIssued, @.Reference, @.Note, @.DocumentType, @.CompanyCode,

@.Valid, @.Fees, @.isDistributed);

return @.@.identity

End try

begin catch

raiserror('Test - Error', 16, 1 )

End catch

.. the code being run is not actually throwing any error.

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

updates but actually just does nothing and i lose the invalid row.

what is happening here please?

thank you ,

hrub

hrubesh:

I am not sure what else is going on but most likely you should be returning SCOPE_IDENTITY() instead of @.@.identity. Also, what are you getting for a return code?

|||

Hi, thanks for your reply,

i did not understand what u mean by what am i getting for a return code.

|||

Hrubesh:

You have this line in your code:

Code Snippet

return @.@.identity

How are you invoking the stored procedure? And after the execution of the stored procedure completes, what is the value returned by the stored procedure? Or are you just ignoring the return value? Finally, what do you mean by "it just does nothing." And if you don't know what the return value is, how do you know that "it is just doing nothing."

|||

hi,

i have been searching on the issue, and this is what i believe now it is not an issue with the sql part of it.

exception unhandled by user code in serviced components is what i am looking into now.

thanks for your help,

btw why the difference between @.@.identity and SCOPE_IDENTITY() ..

i have been using @.@.identity so that it automatically updates my parent row and child row with the new id , i am using

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

invoice is the parent table and invoicecharge the child, and it works.

i will try the scope_identity asa i get the exception unhandled workaround. ..

yep so how i am invoking the sp is that Tainvoice is the sql data adapter that i generated using the wizard in my dataset, that created stored procedures on an sql command, and it automatically calls the corresponding insert/upd/del sp.

thanks a lot.

|||

@.@.identity will frequently work but it is not technically the correct choice. I gave an example of how @.@.identity can go wrong in this thread last year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880725&SiteID=1

Another thread in which I discussed with my friend Craig was here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=793185&SiteID=1

|||

thanks kent.

AUtomatic mail alert

HI,

I have to send an automatic e-mail based on database table .Could any body help me

how to write stored procedure and where to execute it.How to send an automatic e-mail through sql server?

Thanks in advance.

Regards,

Raja.

Hi Raja,

To send an email in a stored procedure in SQL Server, you can call xp_sendmail directly.

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

Before this, you have to configure an extended MAPI mail profile. Here is a link for how to do it.

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

However, you can also read data from the database table and do this in your .net application, using the System.Net.Mail.MailMessage class.

http://msdn2.microsoft.com/en-us/library/system.net.mail.mailmessage.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

automatic data update from SERVER to all CLIENTS connected

Hi,
i have a problem about the CLIENT-SERVER architecture procedure.
Well , i have an application in VB with ADO connection to a table in a
database on a SQLSERVER 7.0 .
Is possible to do that when a client updates a data in a field of my table ,
the SERVER communicates to all clients connected to my table that this data
are updated , without the client do anything , for example without a
client-timer to control the data in the server ?
thanksYou might want to read about "notification services" on the MS web site for this. I haven't used it
myself, but it is designed for this type of scenario.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Filippo" <rude_Fil@.yahoo.it> wrote in message news:O%uhb.24255$vO5.872426@.twister1.libero.it...
> Hi,
> i have a problem about the CLIENT-SERVER architecture procedure.
> Well , i have an application in VB with ADO connection to a table in a
> database on a SQLSERVER 7.0 .
> Is possible to do that when a client updates a data in a field of my table ,
> the SERVER communicates to all clients connected to my table that this data
> are updated , without the client do anything , for example without a
> client-timer to control the data in the server ?
> thanks
>
>

automatic data update from SERVER to all CLIENTS connected

Hi,
i have a problem about the CLIENT-SERVER architecture procedure.
Well , i have an application in VB with ADO connection to a table in a
database on a SQLSERVER 7.0 .
Is possible to do that when a client updates a data in a field of my table ,
the SERVER communicates to all clients connected to my table that this data
are updated , without the client do anything , for example without a
client-timer to control the data in the server ?
thanks"Filippo" <rude_Fil@.yahoo.it> wrote in message
news:c2vhb.26030$e6.883003@.twister2.libero.it...
> Hi,
> i have a problem about the CLIENT-SERVER architecture procedure.
> Well , i have an application in VB with ADO connection to a table in a
> database on a SQLSERVER 7.0 .
> Is possible to do that when a client updates a data in a field of my table
,
> the SERVER communicates to all clients connected to my table that this
data
> are updated , without the client do anything , for example without a
> client-timer to control the data in the server ?
> thanks
>

In theory, you could use a trigger with xp_cmdshell to call some sort of
program to notify the clients, but in practice that wouldn't be a very good
solution. It would have serious performance implications, and if the
external program failed or hung, you could block access from other clients.

A better option is probably to poll the table to see if the data has
changed, either based on a datetime column, or perhaps a 'ModifiedFlag'
column. Clients could poll directly, or use a scheduled job at regular
intervals - the job could then call your notification program, and that
would not impact the database in case of communications or other issues.

Simon|||Filippo (rude_Fil@.yahoo.it) writes:
> i have a problem about the CLIENT-SERVER architecture procedure.
> Well , i have an application in VB with ADO connection to a table in a
> database on a SQLSERVER 7.0 .
> Is possible to do that when a client updates a data in a field of my
> table , the SERVER communicates to all clients connected to my table
> that this data are updated , without the client do anything , for
> example without a client-timer to control the data in the server ?

As Simon said, there is no direct support for this in SQL Server.

For a simple solution, polling is probably best. Note here that you
could make use of a timestamp column. Such a column is automatically
updated each time you update the row, and the value is monotonically
increasing on a database-wide basis. Thus, a client can save the last
fecthed timestamp value, and then get the new one.

A more sophisticated solution would be to write an extended stored
procedure to alert the clients. As Simon pointed out, such an operation
could be detrimental to performance, if you are not careful. Best is
to alert a local process, and this process then alerts the clients
asynchronusly.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option fro
m
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:

> Hi:
> Somebody knows how I can program a stored procedure for automatic backup t
o
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale |||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
[vbcol=seagreen]
> In SQL Server Management Studio, Go to Management, Maintenance Plans, righ
t
> click the node and select New. Choose the "Back Up Database Task" option f
rom
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
>|||You can create an Agent Performance Condition Alert that triggers on log ful
l percent and trigger
your job that does a backup of the log. If prefer to just schedule my backup
s regularly, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...[vbcol=seagreen]
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic fo
rm
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
>|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale
"Tibor Karaszi" wrote:

> You can create an Agent Performance Condition Alert that triggers on log f
ull percent and trigger
> your job that does a backup of the log. If prefer to just schedule my back
ups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>
>|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Perc
ent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert i
s fired.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...[vbcol=seagreen]
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale
> "Tibor Karaszi" wrote:
>|||It′s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:

> When the condition is true, yes. For instance, you can for the counter "Pe
rcent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert
is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> .
>

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale
In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option from
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:

> Hi:
> Somebody knows how I can program a stored procedure for automatic backup to
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale
|||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
[vbcol=seagreen]
> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> click the node and select New. Choose the "Back Up Database Task" option from
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
|||You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...[vbcol=seagreen]
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic form
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale
"Tibor Karaszi" wrote:

> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>
>
|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...[vbcol=seagreen]
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale
> "Tibor Karaszi" wrote:
|||It′s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:

> When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> .
>

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale :)In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option from
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:
> Hi:
> Somebody knows how I can program a stored procedure for automatic backup to
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale :)|||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> click the node and select New. Choose the "Back Up Database Task" option from
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
> > Hi:
> > Somebody knows how I can program a stored procedure for automatic backup to
> > disk in SQL Server 2005 checking the transaction log?
> > Thanks in advance
> > Ale :)|||You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic form
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
>> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> click the node and select New. Choose the "Back Up Database Task" option from
>> the toolbox. Configure the backup wizard as needed.
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "Ale" wrote:
>> > Hi:
>> > Somebody knows how I can program a stored procedure for automatic backup to
>> > disk in SQL Server 2005 checking the transaction log?
>> > Thanks in advance
>> > Ale :)|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale :)
"Tibor Karaszi" wrote:
> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> > ok, I checked this but I have one doubt: how can I check my log? if I want
> > that my backup init when my log is at 80 %, how can I send an automatic form
> > the backup process to work? it exists a manner for programming this in SQL
> > Server 2005? (like a cron in UNIX)
> > Thanks again
> > Ale
> >
> > "AndyP" wrote:
> >
> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> the toolbox. Configure the backup wizard as needed.
> >>
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "Ale" wrote:
> >>
> >> > Hi:
> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> > disk in SQL Server 2005 checking the transaction log?
> >> > Thanks in advance
> >> > Ale :)
>
>|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale :)
> "Tibor Karaszi" wrote:
>> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
>> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ale" <Ale@.discussions.microsoft.com> wrote in message
>> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>> > ok, I checked this but I have one doubt: how can I check my log? if I want
>> > that my backup init when my log is at 80 %, how can I send an automatic form
>> > the backup process to work? it exists a manner for programming this in SQL
>> > Server 2005? (like a cron in UNIX)
>> > Thanks again
>> > Ale
>> >
>> > "AndyP" wrote:
>> >
>> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> >> click the node and select New. Choose the "Back Up Database Task" option from
>> >> the toolbox. Configure the backup wizard as needed.
>> >>
>> >>
>> >> --
>> >> AndyP,
>> >> Sr. Database Administrator,
>> >> MCDBA 2003
>> >>
>> >>
>> >> "Ale" wrote:
>> >>
>> >> > Hi:
>> >> > Somebody knows how I can program a stored procedure for automatic backup to
>> >> > disk in SQL Server 2005 checking the transaction log?
>> >> > Thanks in advance
>> >> > Ale :)
>>|||It´s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:
> > This Agent triggers when the condition is completed?
> When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> > Ok, I'll try it!
> > This Agent triggers when the condition is completed?
> > Thanks!
> > Ale :)
> >
> > "Tibor Karaszi" wrote:
> >
> >> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> >> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> >> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> >> > ok, I checked this but I have one doubt: how can I check my log? if I want
> >> > that my backup init when my log is at 80 %, how can I send an automatic form
> >> > the backup process to work? it exists a manner for programming this in SQL
> >> > Server 2005? (like a cron in UNIX)
> >> > Thanks again
> >> > Ale
> >> >
> >> > "AndyP" wrote:
> >> >
> >> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> >> the toolbox. Configure the backup wizard as needed.
> >> >>
> >> >>
> >> >> --
> >> >> AndyP,
> >> >> Sr. Database Administrator,
> >> >> MCDBA 2003
> >> >>
> >> >>
> >> >> "Ale" wrote:
> >> >>
> >> >> > Hi:
> >> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> >> > disk in SQL Server 2005 checking the transaction log?
> >> >> > Thanks in advance
> >> >> > Ale :)
> >>
> >>
> >>
> .
>

Automatic backup of MSDE database.

Hi,

How to automate database backup (MSDE server v8.0)? Is some free tool
which can help on this or can I use some stored procedure? Plan:
Complete - 1 per week
Differential - 1 per day
--
*Best regards,*
Klaudiusz BryjaOn 2004-08-03, bryja_klaudiusz[at]poczta[dot]fm wrote:
> Hi,
> How to automate database backup (MSDE server v8.0)? Is some free tool
> which can help on this or can I use some stored procedure? Plan:
> Complete - 1 per week
> Differential - 1 per day
Some time ago MS published an Enterprise Manager 120 Days Trial. There you
can define the roles for SQL Agent - jobs that will be started some day at
specified hour.

Trociu

--
Plain-text over all!!!
/**********/
write me : trociu@.autonom ict pwr wroc pl
search me : gg: 1382729|||"bryja_klaudiusz[at]poczta[dot]fm" <dla@.zmyly.pl> wrote in message
news:cenq7p$kfh$1@.nemesis.news.tpi.pl...
> Hi,
> How to automate database backup (MSDE server v8.0)? Is some free tool
> which can help on this or can I use some stored procedure? Plan:
> Complete - 1 per week
> Differential - 1 per day
> --
> *Best regards,*
> Klaudiusz Bryja

You could use sp_add_job (with sp_add_jobstep and sp_add_jobschedule) to add
a scheduled job to run the BACKUP command when you want. Alternatively, you
could create the job with SQLDMO, if prefer a COM-based approach. The
simplest approach might be to pay USD50 for the Developer Edition, or
download the trial version and get the full client tools that way, but I
have no idea if the licensing allows that or not, so you should check first.

Also see this link:

http://www.aspfaq.com/show.asp?id=2442

Simon|||"bryja_klaudiusz[at]poczta[dot]fm" <dla@.zmyly.pl> wrote in message news:<cenq7p$kfh$1@.nemesis.news.tpi.pl>...
> Hi,
> How to automate database backup (MSDE server v8.0)? Is some free tool
> which can help on this or can I use some stored procedure? Plan:
> Complete - 1 per week
> Differential - 1 per day

Here's a skeleton process you can alter to suit your needs, with the
database up.

MSDE doesn't have maint. plans, but it comes with SQL Agent to
schedule things. Connect from Ent. Mgr. on another server and create
a job with two steps (if you don't have EM at all somwhere in your
shop, this won't work for you I guess). First step fires off an OS
command batch file on your target server that looks like this :

rem this file is used for the MSDE backup process zjec june '03
ren d:\mssql7\backup\master.002 master.003
ren d:\mssql7\backup\master.001 master.002
ren d:\mssql7\backup\master.bak master.001
del d:\mssql7\backup\master.003

Second step is a T-SQL step that looks like :

BACKUP DATABASE master TO DISK = 'd:\mssql7\backup\master.bak'

You can see I keep 3 versions. You will have to manually step through
the process as you are setting up the process if you want versions.
If you don't want versions, just issue the delete.

You would probably want two jobs, one for full, other for incr, or you
could get fancy with the scheduling on one job.

Alternately, use a free crontab scheduler to shut your db instance
down and use a similar process to copy the .mdb / .ldb to another
location.

HTH

cold_ronald

Automate SQL login account creation?

Hello. Does anyone know if it's possible to write a stored procedure or
script that will create new SQL Server logins, passwords, and assign role
membership based on a table that contains all of the potential users? I was
hoping this would be possible for it to go through a table and automate this
instead of creating all of the SQL login accounts manually. Thanks in
advance.
JYou can use several system stored procedures below example.
EXEC sp_addlogin 'loginid', 'password', 'default database'
"J"?? ??? ??:

> Hello. Does anyone know if it's possible to write a stored procedure or
> script that will create new SQL Server logins, passwords, and assign role
> membership based on a table that contains all of the potential users? I w
as
> hoping this would be possible for it to go through a table and automate th
is
> instead of creating all of the SQL login accounts manually. Thanks in
> advance.
> J
>
>|||Thanks for your quick reply Hongju :-)
"hongju" <hongjujung@.hotmail.com.korea> wrote in message
news:D35AB19B-86E3-4A9E-A8E2-5CE0C157BC70@.microsoft.com...[vbcol=seagreen]
> You can use several system stored procedures below example.
> EXEC sp_addlogin 'loginid', 'password', 'default database'
>
> "J"' ? ':
>

Automate "Week Ending" Date

How do i automate, by stored procedure, generating the "week ending" date using a field data.

------------------

CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-----------------

What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ? , StartTime) . I have no idea where to go!

--------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
------------------If you insist on performing an UPDATE on the field, then some variation of the statement below should suffice:


...SET WeekEnding =dateadd(day, 8-datepart(dw, StartTime), StartTime)...


I would make WeekEnd a computed column like this:

alter table add Weekend as dateadd(day, 8-datepart(dw, StartTime), StartTime)|||Should be in here somewhere

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx|||thanks a bunch

autogrow of log file possibly cause timeout?

i had a problem with a server instance where an app processing a file and putting the data into the database using a stored procedure was timing out. the app is a service and in the case of errors, will try to process the file until it has been succesfully added data to the database. in the event viewer i keep seeing this message during this timeout period:

"Autogrow of file 'mydatabase_log' in database 'mydatabase' was cancelled by user or timed out after 15687 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."

is there a possibility that this process was causing my SP to timeout? are there any other log or debug files that SQL Express creates that might be helpful in discovering what caused the timeout? currently the log is at... ouch 10gigs restricted... maybe trying to autogrow from this while restricted is hogging resources?

As SQL Server Express databases are limited to 4GB per database, I just wonder why you need 10GB of logs ? Did you consider backing up your database and shrink your logfiles ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||when we created the database, we used the default setting for the log file, which was "By 10 percent, restricted growth ". currently we backed up the database, detached the DB, removed the old log file, and reattached the DB. it created a new log file and we set it to a smaller size. as i understand it the log file holds the uncommitted transactions, is this true? if we want the log file to stay at a certain size maybe i need to uncheck AutoGrowth? and going back to the original question, if SQL was trying to autogrow a 10 gig file, could this have used all the resources at the moment and timeout any stored procedures running during this autogrowth process?|||

Hi nattylife,

for an instance assump your Log File size is 12 GB, you have set up Auto Growth option in 20% , now when ever your T-Log file need to grow it will grow 12 GB * 20 % means it will grow 2.4 GB in size and while this process is running it occupy the server resource (yes it will effect while this happens in pick time).

If you stop/uncheck/disabled Auto Grow , you may be in trouble because if your T-log need to grow and Auto Grow is disabled it might be resulted in SUSPECT status of your database, so don't disabled Auto Grow set it in appropriate % / MB as per your *requirement/perdiction* in growth of your database. BTW what is your Recovery Model of your database?

Refer T-Log architecture in BOL to understand more, BOL is your best friend.

Hemantgiri S. Goswami

|||

Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

we backed up the database before we do any questionable practices. the database is filled with 3 text files that our app recieves every day. inside each file are the records for the database. we archive these every day for 90 days in case we have any database issues. after doing some more research, i found how to shrink the log in the CTP which was the approach i was looking for, just didnt find until after the fact. i didnt realize that this log file would grow coninuously, i was under the assumption it would eventually get written over. so we are discussing a way to archive the ldf file now too.

|||

Hi,

you should consider using the backup functions of SQL Server. You are able to do either full / differential or transaction log backups. A combination of those will help you to make your databases disaster-recoverable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||i checked the backup options in the CTP. i see where i can do a log backup, but is this a manual process or when will it do it on a regular basis since i dont see any option to automate it. if i wanted to automate it, would i need to implement this with my own service or such?|||Hi,

SQL Server Agent is not shipped with SQL Server Express. An approach could be to script out the backup command (using the functionality of the management studio) and schedule the execution of the script using an AT command (or any other scheduler) by executing the script with SQLCMD.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

auto recompile in sql server

Hi,
I have a question in SQL Server 2K, I use SQL Profile to trace, and
find Stored Procedure was auto recompiled, like this row in the
trace:
SP:Recompile151680762004-02-27 16:01:11.610

How can I stop the auto recompile.

Thanks
Harold"Harold" <chen1999@.hotmail.com> wrote in message
news:6f2bbaed.0402271309.37a4b477@.posting.google.c om...
> Hi,
> I have a question in SQL Server 2K, I use SQL Profile to trace, and
> find Stored Procedure was auto recompiled, like this row in the
> trace:
> SP:Recompile 15 1680 76 2004-02-27 16:01:11.610
> How can I stop the auto recompile.
> Thanks
> Harold

http://support.microsoft.com/defaul...7&Product=sql2k

Simon|||Harold (chen1999@.hotmail.com) writes:
> I have a question in SQL Server 2K, I use SQL Profile to trace, and
> find Stored Procedure was auto recompiled, like this row in the
> trace:
> SP:Recompile 15 1680 76 2004-02-27 16:01:11.610
> How can I stop the auto recompile.

Simon posted a very useful link. But permit me some short notes.

Recompiles are a mixed blessing. Sometimes they kill your performance,
sometimes they save the day. Not at all knowing your situation, it is
difficult to tell.

The most common reason for recompiles are temp tables that are filled
with data in the procedure. There are two remedies: use table variables
instead, or use OPTION (KEEPFIXED PLAN).

Table variables does not have statistics, which is why they cannot cause
recompile. But that also means that SQL Server has less information about
them when building a plan. Also, be aware of that insering data into a
table variable precludes parallellism. This can also be a performance
killer.

If you litter your code with OPTION (KEEPFIXED PLAN) you can prevent
recompiles, but forget it in one place, and you are in for it again.

A war story: some time back, I fought with a procedure that took 1
minute to run. I figured I try to cut that down, and since there
were several recompiles, I tried to switching to table variables
plus a few more tricks. Sure, the recompiles went away - but I lost
on the roundabouts, so the execution time was still 1 minute.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949CF41F3DA21Yazorman@.127.0.0.1...
> Harold (chen1999@.hotmail.com) writes:
> > I have a question in SQL Server 2K, I use SQL Profile to trace, and
> > find Stored Procedure was auto recompiled, like this row in the
> > trace:
> > SP:Recompile 15 1680 76 2004-02-27 16:01:11.610
> > How can I stop the auto recompile.
> Simon posted a very useful link. But permit me some short notes.

I'm going to jump in with my own real life experience here.

We went through the steps MS recommended... big first step was making sure
we called all of our stored procs fully qualified with names, etc.

i.e. dbo.stored_proc as opposed to just stored_proc.

This helped. We got about 10% improvement in throughput.

Just last week, we found another issue (which I think is partly related to
the ODBC drivers, but that's beyond the scope here.)

In any case, rather than doing: exec stored_proc @.foo=123 @.bar='xyz' etc.

we wrapped the stored_proc call in a sp_executesql... exec
sp_executesql(stored_proc, etc etc.)

What used to take two sql boxes to handle the load for (and even then we
were getting a lot of problems) we now run on ONE box and have processing
power to spare.

One thing that helped us track this down was a recent article in SQL Server
Magazine that provided some stored procs on tracking wait states.

Whereas over a 2 minute period we'd get MILLIONS of LCK_MX_I (I think I got
that right off the top of my head) we now get hundreds to thousands.)

So, in this case, making the change made a major difference for us. (in our
case this stored proc gets called millions of times a day.)

So, in our case, the savings was HUGE.

> Recompiles are a mixed blessing. Sometimes they kill your performance,
> sometimes they save the day. Not at all knowing your situation, it is
> difficult to tell.
> The most common reason for recompiles are temp tables that are filled
> with data in the procedure. There are two remedies: use table variables
> instead, or use OPTION (KEEPFIXED PLAN).
> Table variables does not have statistics, which is why they cannot cause
> recompile. But that also means that SQL Server has less information about
> them when building a plan. Also, be aware of that insering data into a
> table variable precludes parallellism. This can also be a performance
> killer.
> If you litter your code with OPTION (KEEPFIXED PLAN) you can prevent
> recompiles, but forget it in one place, and you are in for it again.
> A war story: some time back, I fought with a procedure that took 1
> minute to run. I figured I try to cut that down, and since there
> were several recompiles, I tried to switching to table variables
> plus a few more tricks. Sure, the recompiles went away - but I lost
> on the roundabouts, so the execution time was still 1 minute.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> Just last week, we found another issue (which I think is partly related to
> the ODBC drivers, but that's beyond the scope here.)
> In any case, rather than doing: exec stored_proc @.foo=123 @.bar='xyz' etc.
> we wrapped the stored_proc call in a sp_executesql... exec
> sp_executesql(stored_proc, etc etc.)
> What used to take two sql boxes to handle the load for (and even then we
> were getting a lot of problems) we now run on ONE box and have processing
> power to spare.

Interesting.

But I don't really understand. Did you change from using sp_executesql,
or to using it?

Assuming that changed to, how did you call the procedures before that?
Did you send EXEC statements over the wire, or did you call the procedures
through RPC? How do you call sp_executesql now? EXEC statement or RPC?
How are you passing the parameters to the procedures? Just part of the
EXEC string, or as parameters to sp_executesql?

Intuitively, I would think that wrapping the call in sp_executesql would
just be overhead, but I've been wrong before.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank all of you!

Erland,
The OPTION (KEEPFIXED PLAN) only affect when Auto Update
Statistics(the properties of the database) is true, I turned of the
Auto Update Statistics.

This is from SQL 2K BOOK:
Forces the query optimizer not to recompile a query due to changes in
statistics or to the indexed column (update, delete, or insert).
Specifying KEEPFIXED PLAN ensures that a query will be recompiled only
if the schema of the underlying tables is changed or sp_recompile is
executed against those tables.

I think it may other cause the recompiler.

Thanks
Harold|||Harold (chen1999@.hotmail.com) writes:
> The OPTION (KEEPFIXED PLAN) only affect when Auto Update
> Statistics(the properties of the database) is true, I turned of the
> Auto Update Statistics.

Have you done that for tempdb as well? (Which I am not sure that I would
recommend.)

> This is from SQL 2K BOOK:
> Forces the query optimizer not to recompile a query due to changes in
> statistics or to the indexed column (update, delete, or insert).
> Specifying KEEPFIXED PLAN ensures that a query will be recompiled only
> if the schema of the underlying tables is changed or sp_recompile is
> executed against those tables.
> I think it may other cause the recompiler.

Of course. If you have a procedure outer_sp that creates a temp
table and then calls inner_sp to operate on that table, then inner_sp
will be create at least once for each call to outer_sp, since it is a
new temp table each time.

But that's a common problem in these newsgroups. People post to little
information, so it often becomes a guessing game.

By the way, to identify why a procedure is being recompiled, this link
is useful.
http://support.microsoft.com/defaul...b;EN-US;q308737.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
I did not turn of the Auto Update Statistics config at tempdb, you
are right, so the temp table still cause the recompiler.

Thank you so much!
Harold