Attach remote DB file: jobs

Showing posts with label jobs. Show all posts

Tuesday, March 27, 2012

auto backup jobs wont run - not in single user mode

Hello!
I am trying to get a SQL server that was configured by someone else up
and running the backup and maintainance jobs from within SQL server
2000. when the job runs it backs up all but 3 of the databases, the 3
it doesnt is our ASDB (our arcserv) database, master and model.
the error that shows for the asdb database is ..
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
The one that shows for the others is this...
12] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[13] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Database state cannot be changed
while other users are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
[14] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Can anyone advise me on how to resolve this issue?
Thanks in advance!
/ehrenWingnut wrote:
> Hello!
> I am trying to get a SQL server that was configured by someone else up
> and running the backup and maintainance jobs from within SQL server
> 2000. when the job runs it backs up all but 3 of the databases, the 3
> it doesnt is our ASDB (our arcserv) database, master and model.
> the error that shows for the asdb database is ..
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> The one that shows for the others is this...
>
> 12] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [13] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> while other users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> failed.
> [14] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Can anyone advise me on how to resolve this issue?
> Thanks in advance!
> /ehren
>
In your maintenance plan, turn off the "repair problems" option...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I will give that a try.
On Jan 29, 9:38 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> Wingnut wrote:
> > Hello!
> > I am trying to get a SQL server that was configured by someone else up
> > and running the backup and maintainance jobs from within SQL server
> > 2000. when the job runs it backs up all but 3 of the databases, the 3
> > it doesnt is our ASDB (our arcserv) database, master and model.
> > the error that shows for the asdb database is ..
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > The one that shows for the others is this...
> > 12] Database master: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> > Database needs to be in single user mode.
> > The following errors were found:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > [13] Database model: Check Data and Index Linkage...
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> > while other users are using the database 'msdb'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> > statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> > failed.
> > [14] Database msdb: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> > Database needs to be in single user mode.
> > The following errors were found:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > Can anyone advise me on how to resolve this issue?
> > Thanks in advance!
> > /ehrenIn your maintenance plan, turn off the "repair problems" option...
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com- Hide quoted text -- Show quoted text -

auto backup jobs wont run - not in single user mode

Sunday, March 25, 2012

author of package affects login?

This is a very frustrating problem.

Let me break it down for the reader.

I have an agent service account that works fine - the account that jobs run under.

I know it works fine because this is the same account all my SQL Backup jobs run under - without a hitch.

I also have a valid sql server account that I use for connection string logins when building packages -

that account works fine. I know it works fine because I can login to the SQL Server Manager with this account.

So, I build a package with the sql server account and password for the OLE DB connection manager in a package. All the package does is a simple query on a table and outputs to a flat file.

I create a job, with 'sa' as the owner. in step 1 I select SSIS package, run as SQL Agent Service Account, package source is file system and I point to the package. The package itself uses the sql server login account to execute.

If I run the package it works fine. I close the solution, someone else comes along and opens the solution - THEY CHANGE ABSOLUTELY NOTHING ABOUT THE LOGIN OR SQL AGENT SERVICE ACCOUNT, but apparently just the fact that they opened the solution and looked at the package, breaks the login. If they try to run the package, they get a login failure, whereas I did not. After they close it, it breaks for me as well. If they open the solution, open the connection manager, re-enter the sql login's password, the package works fine for them, until I come along and open the solution, then it is broken again.

This makes no sense to me. Why would an 'author' opening a solution impact the connection manager when ABOLUTELY NOTHING ABOUT THE CONNECTION MANAGER WAS CHANGED?!

Seems like a bug to me. Anyone seen something like this?

Thanks in advance.

randy

Read up on the package ProtectionLevel property in this forum and in Books-Online.

I'd bet the package is set to EncryptSensitiveWithUserKey, which would prevent others from being able to run the package. You can try EncryptSensitiveWithPassword, but then all developers would need to know the package's password and would be required to enter it to open the package and be able to use it. Either way, Microsoft did not want to be responsible for package security and made sure that the packages are secure.

You could also try DontSaveSensitive, but then you'd have to use a package configuration file to pass in the passwords to the connection managers. It's a touch cumbersome, but in the end it's worth the extra security.|||

Thanks very much. I'll take a look.

Regards

|||

Randyvol,

What you described in your first post is just the normal behavior of SSIS. SSIS does not save sensitive information like connection manager's passwords unless you use an encrypted protection level in your package. If the protection level of the package is set to DonSaveSensitive; then you have to provide the connection credentials every time you open the package. That is what seems to be happening.

As a good practice you can use package configuration to set the connection string of connection managers at run time; that way the package will always get the connection credential when is executed. That will not change the behavior of the package when editing it.

Sunday, March 11, 2012

Auditing changes to Scheduled Jobs

Hello,

Is there a way that I can create an audit trail of changes that are made to scheduled jobs in SQL Server 2000?

Thanks

This question is not directly related to SSIS, so I would try posting in the SQL Server Tools General forum to get more responses.

Bob

Saturday, February 25, 2012

Audit history for scheduled jobs (SQL Server 2000)

Hello,

I would like to be able to set up an audit of changes that are made to scheduled jobs, so that a history can be built up of of these changes. Anyone any ideas on how to do this?

Thanks

You can create a query to do a SELECt on the sysjobs table in the MSDB database

Tuesday, March 27, 2012

auto backup jobs wont run - not in single user mode

auto backup jobs wont run - not in single user mode

auto backup jobs wont run - not in single user mode

Sunday, March 25, 2012

author of package affects login?

Sunday, March 11, 2012

Auditing changes to Scheduled Jobs

Saturday, February 25, 2012

Audit history for scheduled jobs (SQL Server 2000)

Attach remote DB file

Blog Archive

About Me