integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
..
You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>|||Thanks! I changed my integrity check to not attempt to repair any minor problems, and it doesn't require single user mode to do the check. The integrity check came back fine, so I assume it didn't need to do any repairing anyway.
Regards,
Mike Walraven
"Andrew J. Kelly" wrote:
> You can use sp_who2 to see who is in a particular database but you should
> not be running this in Single User mode anyway. Single User is only
> required to fix issues not to do the initial checks.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > When I try to run an integrity check on my project Server
> > 2002 database, I get the following error:
> >
> > [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 'ProjectServer'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > DATABASE statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > sp_dboption command failed.
> > [31] Database ProjectServer: 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.
> >
> > This same error comes up if I try to manually change the
> > database to single user mode.
> >
> > Is there any way to see which users are using the
> > database, close their sessions and then do the integrity
> > check.
> >
> > This is the only database on my SQL server that I have
> > any issues with.
> >
> > Any help would be appreciated.
> >
> > Regards,
> > Mike Walraven
> > .
> >
> >
>
>|||That is usually the case. If there is an error you can then decide the best
approach to handle it instead of letting the wizard make those decisions.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <Mike Walraven@.discussions.microsoft.com> wrote in message
news:23F9018D-52F4-4BA4-8A23-05AD4A83D080@.microsoft.com...
> Thanks! I changed my integrity check to not attempt to repair any minor
problems, and it doesn't require single user mode to do the check. The
integrity check came back fine, so I assume it didn't need to do any
repairing anyway.
> Regards,
> Mike Walraven
> "Andrew J. Kelly" wrote:
> > You can use sp_who2 to see who is in a particular database but you
should
> > not be running this in Single User mode anyway. Single User is only
> > required to fix issues not to do the initial checks.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> > news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > > When I try to run an integrity check on my project Server
> > > 2002 database, I get the following error:
> > >
> > > [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 'ProjectServer'
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > > DATABASE statement failed.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > > sp_dboption command failed.
> > > [31] Database ProjectServer: 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.
> > >
> > > This same error comes up if I try to manually change the
> > > database to single user mode.
> > >
> > > Is there any way to see which users are using the
> > > database, close their sessions and then do the integrity
> > > check.
> > >
> > > This is the only database on my SQL server that I have
> > > any issues with.
> > >
> > > Any help would be appreciated.
> > >
> > > Regards,
> > > Mike Walraven
> > > .
> > >
> > >
> >
> >
> >

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx
.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>

Integration services Globla variable and scheduling issue

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

How can i schedule package in sql server?

Thanks

Priyank Gajera wrote:

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

You can use package configuration in your children packages based on a parent package variable to set the connection strings.

Priyank Gajera wrote:

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

You meant, how the children packages can 'see' the value of a variable in the parent package? Foe that use package configuration as well as described in the previos answer.

Priyank Gajera wrote:

How can i schedule package in sql server?

Using SQL Agent; create a job to run your package. There are a lot of information around that in this forum.

Rafael Salas

|||

Parent Package configurations can get a little tricky. The easiest thing to do is set up an environment variable with your connection string, and then configure your packages to use that environment variable.

To configure your packages go to the SSIS-->Configuration menu, and then in the drop down choose Environment Variable. Map that to the connection string of the connection manager you are using.

Sql Agent will schedule your package to run. You can access it through the Sql Management Studio.

|||

Instead of using variable, Can I use the same connection for child packages which I am using in Main package ?

And if there is no other way then how can I asign a variable value(Connection Staring) to a Ole DB connection manager?

|||

Hi Rafael

Can we share Connections( Ole DB or FTP Comnnection) between packages?

Can you give one example of using parent package variable or if not possible, then please describe me the procedure.

Thanks

Priyank Gajera

|||Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?|||

Priyank Gajera wrote:

Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?

The procedure is pretty much described in Sean's post. Basically you need to place the connection string in some place is accessible for package configurations; this is Env. variable, registry key, xml file, etc. and the set the package configuration in you package to go there and 'grab' the connection string and assigned it to the connection string property of you connection manager.

Rafael Salas

Integration services Globla variable and scheduling issue

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

How can i schedule package in sql server?

Thanks

Priyank Gajera wrote:

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

You can use package configuration in your children packages based on a parent package variable to set the connection strings.

Priyank Gajera wrote:

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

You meant, how the children packages can 'see' the value of a variable in the parent package? Foe that use package configuration as well as described in the previos answer.

Priyank Gajera wrote:

How can i schedule package in sql server?

Using SQL Agent; create a job to run your package. There are a lot of information around that in this forum.

Rafael Salas

|||

Parent Package configurations can get a little tricky. The easiest thing to do is set up an environment variable with your connection string, and then configure your packages to use that environment variable.

To configure your packages go to the SSIS-->Configuration menu, and then in the drop down choose Environment Variable. Map that to the connection string of the connection manager you are using.

Sql Agent will schedule your package to run. You can access it through the Sql Management Studio.

|||

Instead of using variable, Can I use the same connection for child packages which I am using in Main package ?

And if there is no other way then how can I asign a variable value(Connection Staring) to a Ole DB connection manager?

|||

Hi Rafael

Can we share Connections( Ole DB or FTP Comnnection) between packages?

Can you give one example of using parent package variable or if not possible, then please describe me the procedure.

Thanks

Priyank Gajera

|||Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?|||

Priyank Gajera wrote:

Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?

The procedure is pretty much described in Sean's post. Basically you need to place the connection string in some place is accessible for package configurations; this is Env. variable, registry key, xml file, etc. and the set the package configuration in you package to go there and 'grab' the connection string and assigned it to the connection string property of you connection manager.

Rafael Salas