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...
>
Showing posts with label particular. Show all posts
Showing posts with label particular. Show all posts
Monday, March 19, 2012
Auditing table data changes
Hi,
Is there any RDBMS concept ,If i do any DML operations in any of table i need to know how many rows are inserted/updated/deleted in particular table. I dont want write any trigger to get those information.
because the table count nearly 142.
Moving to Transact-SQL from SSIS.|||Nope. Triggers are the only current method in 2005 and earlier.
You could pretty easily write a code generator using the system tables (sys.tables, sys.columns) once you determine how to do one of them, then just follow a common pattern.
|||Yes..My last question. i have done work around solution.
just loading data into table using bulk insert concept. after that used substring function to fixed the problem.
Thanks - Phil Brammer.
Sunday, March 11, 2012
auditing logins
SQL 2k, Windows authentication only
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
auditing logins
SQL 2k, Windows authentication only
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
auditing logins
SQL 2k, Windows authentication only
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
Thursday, March 8, 2012
Audit User Activity
Hi All,
I'm an Oracle DBA that has inherited some SQL Server 2000 databases.
Can you audit a particular user in Sql Server 2000. We need to know exactly what a particular user is doing, (i.e. creating/dropping objects, and what data he is accessing)
what is the best way to do this??
Thanks,
DaveSQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_2xiq.asp).
-PatP
I'm an Oracle DBA that has inherited some SQL Server 2000 databases.
Can you audit a particular user in Sql Server 2000. We need to know exactly what a particular user is doing, (i.e. creating/dropping objects, and what data he is accessing)
what is the best way to do this??
Thanks,
DaveSQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_2xiq.asp).
-PatP
Saturday, February 25, 2012
Attributes of Database
Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Does anybody know how to get the following information for a particular
>> database in SQL 2000
>> 1. Whether it is a System Object or not.
>> 2. Create for Attach
>> 3. Replication Status
>> We can get this values through SQL-DMO, but can I get these values from
>> some system tables or in-built functions
>> TIA
>> Prasad
>>
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
--
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
--
http://milambda.blogspot.com/|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>> Isn't there a more cleaner way of finding the system databases,
>> comparing the names would mean hard-coding the stuff.
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>> and regarding the "created for attach" field bcoz SQL-DMO returns this
>> value
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Does anybody know how to get the following information for a particular
>> database in SQL 2000
>> 1. Whether it is a System Object or not.
>> 2. Create for Attach
>> 3. Replication Status
>> We can get this values through SQL-DMO, but can I get these values from
>> some system tables or in-built functions
>> TIA
>> Prasad
>>
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
--
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
--
http://milambda.blogspot.com/|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>> Isn't there a more cleaner way of finding the system databases,
>> comparing the names would mean hard-coding the stuff.
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>> and regarding the "created for attach" field bcoz SQL-DMO returns this
>> value
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>
Subscribe to:
Posts (Atom)