Automating DBCC Checkdb

Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there a
n alert that I can monitor for which will inform me of any problems discover
ed? Or is there a method to get the output of the DBCC Checkdb to a text fi
le? When running this command as a scheduled job, I don't see the output I
am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!Here's what I'd do:
Define two jobsteps, "at the end" of the job. One has subject "success", the
other "error". In these, you use xp_sendmail or xp_smtp_sendmail (I
recommend this: www.sqldev.net) In the "real" jobsteps, define "next step"
on success and on failure you go to the one where you send the email with
"failure".
In the DBCC jobstep, you define an output file, and in the mail jobsteps,
you specify that file as an attachment.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OiAUHxC5DHA.2776@.TK2MSFTNGP09.phx.gbl...
Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there
an alert that I can monitor for which will inform me of any problems
discovered? Or is there a method to get the output of the DBCC Checkdb to a
text file? When running this command as a scheduled job, I don't see the
output I am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!|||Use sqlmaint.exe utility as described in BOL. It can sends out put to a file
. The file could be in text and /or html format.
EXEC xp_sqlmaint '-S SQLSERVER -PlanName "DBMAINTPLAN 1" -CkDB -Rpt D:\Dbm
aint\DBCC.out -DelTxtRpt 2months -HtmlRpt D:\Dbmaint\DBCC_result.htm'
Richard
"JLS" <jlshoop@.hotmail.com> wrote in message news:OiAUHxC5DHA.2776@.TK2MSFTNG
P09.phx.gbl...
Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there a
n alert that I can monitor for which will inform me of any problems discover
ed? Or is there a method to get the output of the DBCC Checkdb to a text fi
le? When running this command as a scheduled job, I don't see the output I
am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!|||Thanx! Great advise, I like being pointed in the right direction instead of
spinning my wheels all over BOL.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message news:e8DcWfE5DHA.24
32@.TK2MSFTNGP10.phx.gbl...
Use sqlmaint.exe utility as described in BOL. It can sends out put to a file
. The file could be in text and /or html format.
EXEC xp_sqlmaint '-S SQLSERVER -PlanName "DBMAINTPLAN 1" -CkDB -Rpt D:\Dbm
aint\DBCC.out -DelTxtRpt 2months -HtmlRpt D:\Dbmaint\DBCC_result.htm'
Richard
"JLS" <jlshoop@.hotmail.com> wrote in message news:OiAUHxC5DHA.2776@.TK2MSFTNG
P09.phx.gbl...
Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there a
n alert that I can monitor for which will inform me of any problems discover
ed? Or is there a method to get the output of the DBCC Checkdb to a text fi
le? When running this command as a scheduled job, I don't see the output I
am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!|||Thanx! Exactly the information I was looking for!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCrGg$C5DHA.2344@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Here's what I'd do:
> Define two jobsteps, "at the end" of the job. One has subject "success",

---

the

quote:

---

> other "error". In these, you use xp_sendmail or xp_smtp_sendmail (I
> recommend this: www.sqldev.net) In the "real" jobsteps, define "next step"
> on success and on failure you go to the one where you send the email with
> "failure".
> In the DBCC jobstep, you define an output file, and in the mail jobsteps,
> you specify that file as an attachment.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "JLS" <jlshoop@.hotmail.com> wrote in message
> news:OiAUHxC5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> Sql Server 2000
> If I ran a weekend job to perform a DBCC Checkdb on my databases, is there
> an alert that I can monitor for which will inform me of any problems
> discovered? Or is there a method to get the output of the DBCC Checkdb to

---

a

quote:

---

> text file? When running this command as a scheduled job, I don't see the
> output I am in the habit of perusing for errors.
> Please advise. Or tell me how you approach executing database

---

maintenance.

quote:

---

> Thanx!
>
>

---

Automating a Backup and Restore across Servers

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

MariaHi Maria,
try if putting the statement

SET XACT_ABORT OFF

in the TSQL-stream of the step solves your problem.

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
Tram
If you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegr oups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>
|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.
|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegr oups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
--
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>sql

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?
Hi
"Kristen" wrote:

> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John
|||No....I will look into that. Thanks!
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John
|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John
|||Hi
"Kristen" wrote:

> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John
|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:
> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:
> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>> Can you think of another way that does not entail alot of programming?
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:

> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL
.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins
,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:

> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure y
ou verify this. Based on
for what purpose you want this script, it might be very important for the lo
gins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DM
O does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQ
L
> Server version!
> Why not just backup the system databases?
> John
>|||If you contact me, I will share a script. It will script logins,
users, system and database role membership, and indiviudal grants with
passwords preserved in SQL Server 2005.
Terry

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jens
Take a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/
|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegr oups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>
|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/
|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegr oups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>
|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
KlausAre the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name ='Your_job_name_goes_here', @.server_name ='The_Name_Of_The_Standby_Server_Goes_Here'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
--
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>sql

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
Klaus
Are the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name =
'Your_job_name_goes_here', @.server_name =
'The_Name_Of_The_Standby_Server_Goes_Here'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
KlausAre the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name =
'Your_job_name_goes_here', @.server_name =
'The_Name_Of_The_Standby_Server_Goes_Her
e'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>

automatic or manual ROLLBACK

Hi,
I have a job that executes the TSQL below. Do I need to do the
errorchecking myself (as below) or does the transaction automaticaly
rollback if a statement fails?
Feel free to comment on the script. (Its never too late to learn).
Yours sincerely,
Jo
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @.MYERROR
BEGIN TRANSACTION
INSERT INTO sequencetoolingbak
(
orderid,
partno,
jobno,
deliverydate,
time_of_creation
)
SELECT orderid, partno, jobno, deliverydate, time_of_creation
FROM dbo.sequencetooling AS seq
WHERE deliverydate < DATEADD(d, -14, GETDATE())
AND NOT EXISTS
(
SELECT orderid
FROM dbo.sequencetoolingbak AS bak
WHERE bak.orderid = seq.orderid
)
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM dbo.sequencetooling
WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Jo SegersHi
Relying on default error handling to be the same in future versions of SQL
Server is not good at all as it could change.
You should always handle the error yourself (like in your code) as the
execution will be predictable.
Regards
Mike
"Jo Segers" wrote:

> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers
>|||The transaction will not be automatically rolled back, unless you SET
XACT_ABORT ON first. Some errors will terminate the batch, which means that
your error handler won't be executed though.
For a complete explanation of errorhandling in SQL Server, see the articles
by SQL Server MVP Erland Sommarskog on www.sommarskog.se .
Jacco Schalkwijk
SQL Server MVP
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eSgVzgsAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Relying on default error handling to be the same in future versions of SQL
> Server is not good at all as it could change.
> You should always handle the error yourself (like in your code) as the
> execution will be predictable.
> Regards
> Mike
OK,
Thanks for your reply.
Jo Segers

Automatic Job Monitoring

I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day.

Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors.

How do you do your checks every morning?

Thanks,

MarcoYou can look up master SQL Server Agent job in SQL BOL. I personally have never used it except for a long time ago when I was experimenting with it early on.

You can also use SQL Agent Mail to flag when a job has failed and send out an alert. SQL Agent Mail is a pain to set up (have to install Outlook), is not fully supported by MS in an clustered environment (Outlook is not cluster aware) and when it goes South in can really go south in a bad way.

I personally manage about a dozen SQL servers. I use a combination of SQL Agent mail on a "management" SQL server (to which all the other servers forward their alerts) and a web site that pulls from a database on the "management" database. This database is in turn populated by a DTS package that pulls from the sysjobhistory table in the other servers. It was a fair amount of work to set up, but it's been running for almost three years now with very few changes.

Regards,

hmscott|||u can build query on system tables for job history in msdb database like : sysjobs, sysjobhistory, sysjobschedules. to make it automated, just schedule the query, then send a notification email, or store to a table, or however you want.
to make a complete check, notice the schedule, coz only already run steps (succeeded, failed, or cancelled) are logged in sysjobhistory.

Automatic content expiry

Hi All,

I have a job coming up which involves me creating a vacancy system for our website.

I (think) I'll only need one table:

ID (PK)
JobTitle
JobLocation
Salary
Hours
JobDescription
ValidFrom (Date/Time)
ValidTo (Date/Time)

I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?

Thanks in advance,
Brett

Add a where condition to your select statement to bring only the valid Jobs e.g.:

select * from Jobs where getDate() < ValidTo

Hope this was helpful

Eyad Salamin

|||

Thanks Eyad,

I didn't think it would be that simple!

Brett

Automated restore of a user database

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

Automated restore of a user database

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

automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!