Attach remote DB file: procedure

Showing posts with label procedure. Show all posts

Monday, March 19, 2012

Auotmated Stored Procedure

Hi
Is there is any way to run the stored procedure automatically from master
database on daily at particular time.
Thanks is advance
NizhamYes, create a SQL Server Agent job with a TSQL jobstep which executes the pr
ocedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Junkmn" <junkmn@.gmail.com> wrote in message news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl..
.
> Hi
> Is there is any way to run the stored procedure automatically from master
> database on daily at particular time.
> Thanks is advance
> Nizham
>|||Yes. Create a job and schedule it to run at a specific time. From EM, you
can do this by using the Jobs node under Management. ( Make sure the SQL
Agent service is enabled & started )
Anith|||Thanks Tibor Karaszi
I hope you are expert on that If possible will you please send me the sample
code to run the stored procedure sp_XYZ to be run at 1.00 am
Regards
Nizham
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Yes, create a SQL Server Agent job with a TSQL jobstep which executes the
> procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Junkmn" <junkmn@.gmail.com> wrote in message
> news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl...|||It's much easier to use the GUI than to write the code for you. Have a look
at SQL Server Agent topics in Books Online. You can also see a brief
example here:
http://www.aspfaq.com/2403
"Junkmn" <junkmn@.gmail.com> wrote in message
news:O03vlkLMGHA.668@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor Karaszi
> I hope you are expert on that If possible will you please send me the
> sample code to run the stored procedure sp_XYZ to be run at 1.00 am
> Regards
> Nizham
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
>

Thursday, March 8, 2012

Audit Table

Wednesday, March 7, 2012

Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
ThanksYou probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:
> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy foor
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
> > Hi all gurus
> >
> > I want to create a store procedure which will generate an audit table and
> > track all the changes done in any of my tables in any database of my
> > server.
> >
> > Please guide me where to start
> >
> > Any guidance will be highly appreciated
> >
> > Thanks
> >
> >
> >|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
> > You probably won;t want a stored proc, but a trigger on each of the
tables
> > you want to audit.. I believe there's a MS design pattern you can copy
foor
> > this. Check out MSDN... In general,
> > 1) decide what you want to record.. . some ideas are
> > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> >
> > 2) build a table to store the data
> > 3) write trigger in each table that inserts new record in audit table
for
> > each row being modified...
> >
> > hth,
> > Charles
> >
> > "AM" wrote:
> >
> > > Hi all gurus
> > >
> > > I want to create a store procedure which will generate an audit table
and
> > > track all the changes done in any of my tables in any database of my
> > > server.
> > >
> > > Please guide me where to start
> > >
> > > Any guidance will be highly appreciated
> > >
> > > Thanks
> > >
> > >
> > >|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > hi,
> > You can add to the strategic or central production's tables fields such
> as:
> >
> > -LastDML ('u': update, 'i': insert)
> > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> >
> >
> > "CBretana" wrote:
> >
> > > You probably won;t want a stored proc, but a trigger on each of the
> tables
> > > you want to audit.. I believe there's a MS design pattern you can copy
> foor
> > > this. Check out MSDN... In general,
> > > 1) decide what you want to record.. . some ideas are
> > > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > >
> > > 2) build a table to store the data
> > > 3) write trigger in each table that inserts new record in audit table
> for
> > > each row being modified...
> > >
> > > hth,
> > > Charles
> > >
> > > "AM" wrote:
> > >
> > > > Hi all gurus
> > > >
> > > > I want to create a store procedure which will generate an audit table
> and
> > > > track all the changes done in any of my tables in any database of my
> > > > server.
> > > >
> > > > Please guide me where to start
> > > >
> > > > Any guidance will be highly appreciated
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
>
>|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > hi,
> > You can add to the strategic or central production's tables fields such
> as:
> >
> > -LastDML ('u': update, 'i': insert)
> > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> >
> >
> > "CBretana" wrote:
> >
> > > You probably won;t want a stored proc, but a trigger on each of the
> tables
> > > you want to audit.. I believe there's a MS design pattern you can
copy
> foor
> > > this. Check out MSDN... In general,
> > > 1) decide what you want to record.. . some ideas are
> > > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > >
> > > 2) build a table to store the data
> > > 3) write trigger in each table that inserts new record in audit table
> for
> > > each row being modified...
> > >
> > > hth,
> > > Charles
> > >
> > > "AM" wrote:
> > >
> > > > Hi all gurus
> > > >
> > > > I want to create a store procedure which will generate an audit
table
> and
> > > > track all the changes done in any of my tables in any database of
my
> > > > server.
> > > >
> > > > Please guide me where to start
> > > >
> > > > Any guidance will be highly appreciated
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
>|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > I cannot change anykind on tables , so I can not add triggers on the
> tables
> >
> > Thanks
> > AM
> > "Enric" <Enric@.discussions.microsoft.com> wrote in message
> > news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > > hi,
> > > You can add to the strategic or central production's tables fields
such
> > as:
> > >
> > > -LastDML ('u': update, 'i': insert)
> > > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> > >
> > >
> > > "CBretana" wrote:
> > >
> > > > You probably won;t want a stored proc, but a trigger on each of the
> > tables
> > > > you want to audit.. I believe there's a MS design pattern you can
> copy
> > foor
> > > > this. Check out MSDN... In general,
> > > > 1) decide what you want to record.. . some ideas are
> > > > Table, RecordIdentifier, Column, OldValue, NewValue,
WHoChangedit,
> > > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > > >
> > > > 2) build a table to store the data
> > > > 3) write trigger in each table that inserts new record in audit
table
> > for
> > > > each row being modified...
> > > >
> > > > hth,
> > > > Charles
> > > >
> > > > "AM" wrote:
> > > >
> > > > > Hi all gurus
> > > > >
> > > > > I want to create a store procedure which will generate an audit
> table
> > and
> > > > > track all the changes done in any of my tables in any database of
> my
> > > > > server.
> > > > >
> > > > > Please guide me where to start
> > > > >
> > > > > Any guidance will be highly appreciated
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > >
> >
> >
>

Saturday, February 25, 2012

Audit Delete Statements

Hi

I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.

Any help will be greatly appreciated!

Thanks,
Alla

CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/

-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
declare @.StatusMsg varchar
declare @.ServerTraceFile varchar
set @.ServerTraceFile = 'E:\Program Files\Microsoft SQL Server\MSSQL\Trace\Audit_Info'
set @.maxfilesize = 1024

-- Client side File and Table cannot be scripted

-- Set the events
declare @.on bit
set @.on = 1

exec @.rc = sp_trace_create @.TraceID OUTPUT, 0, N'\\hostname\dbauditlog\my_dir', @.maxfilesize, NULL
print @.TraceID

if (@.rc != 0) goto error
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
--SELECT @.StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @.TraceID
-- display trace id for future references
select TraceID=@.TraceID

goto noCursor

error:
select ErrorCode=@.rc

noCursor:
return

GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GOTake a look at tirggers (http://doc.ddart.net/mssql/sql70/create_8.htm) :)|||I see...

I just noticed that there is an event to audit TSQL via SQL Profiler. I spooled the script to a SQL file. however, is there any way of filtering it that it would capture DELETE statements only?

Thanks,
Alla|||Spool SQL Profiler output to a table so you can query it.

Sunday, February 19, 2012

Attempt to catch error using @@ERROR in SQL 2000 is failing

We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.

UPDATE PERSONS

SET FLOORNR = dbo.FloorNR(OFFICELOCATION)

WHERE OFFICELOCATION IS NOT NULL

IF(@.@.ERROR <> 0 OR @.@.ROWCOUNT = 0)

BEGIN

RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait

END

However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.

How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.

Is it possible for you to try to handle some of those situations inside of the function and have it not return any values in the case of an error?|||We already handle a number of situations inside the functions. But once every week a user finds a new way to enter his office location and our procedure stops responding. At the moment, we have no way of controlling the input by our users, but we don't want to keep on expanding the procedure. In fact, we just want users to enter their office location in a standard way. We will change the application which handles that, but that change is still some months away. Untill then, we just want the catch any errors and log them.

Attachment file is invalid.

Hello,

I have a stored procedure with this syntax

declare @.filename varchar(100)

set @.filename='c:/A.pdf'

EXEC msdb.dbo.sp_send_dbmail

@.profile_name =@.p_profile

@.recipients = @.p_address,

@.body = @.p_body,

@.subject = @.p_subject,

@.file_attachments = @.filename

but when I execute it i have an error

Attachment file c:/A.pdf is invalid.

What's happen? Thanks in advance

hi,

It seems you are trying to load the file from invalid path. Change to

@.filename='C:\A.pdf' instead of 'c:/A.pdf'

Also make sure that A.pdf file is availed in C Drive.

|||Moving to the Transact-SQL forum.

Monday, March 19, 2012

Auotmated Stored Procedure

Thursday, March 8, 2012

Audit Table

Audit Table

Wednesday, March 7, 2012

Audit Table

Audit Table

Saturday, February 25, 2012

Audit Delete Statements

Sunday, February 19, 2012

Attempt to catch error using @@ERROR in SQL 2000 is failing

Attachment file is invalid.

Attach remote DB file

Blog Archive

About Me