peer to peer replication

Hi,

I set up a peer to peer replication on servers, but only one side seem to be working. I am getting Log Reader Agent job error.

Unable to start execution of step 2 (reason: Error authenticating proxy domain\user, system error: Logon failure: unknown user name or bad password.). The step failed.

I am using the same windows account on both servers, why is one side working and the other side not?

thanks

doublecheck the account and password that starts the job, sqlserver and sqlagent service, and doublecheck their permissions on the machine.

Pausing SQL Server from transact SQL

We have a database that automatically restores at night from another server.
We use a kill all users job and place the database in single user mode.
Unfortunately there is an app on the network that will automatically
reconnect if it's spid is killed.
I'd like to be able to Pause the server from within transact SQL.
The only way I can see to do this is:
exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
Kill the spids >= 50
exec sp_dboption 'Database Name', 'dbo use only', true
exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
Is there a way of pausing without using xp_cmdshell?
PaulThis might work...
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE yourdb
GO
RESTORE ...
That way you should be the single_user before the application can reconnect.
--
HTH
Ryan Waight, MCDBA, MCSE
"Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> We have a database that automatically restores at night from another
server.
> We use a kill all users job and place the database in single user mode.
> Unfortunately there is an app on the network that will automatically
> reconnect if it's spid is killed.
> I'd like to be able to Pause the server from within transact SQL.
> The only way I can see to do this is:
> exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> Kill the spids >= 50
> exec sp_dboption 'Database Name', 'dbo use only', true
> exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> Is there a way of pausing without using xp_cmdshell?
> Paul
>|||Or set the db in restricted user mode, assuming that that nasty app doesn't
use a login that is a member of the db_owner, db_creator or sysadmin roles.
ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
Ryan, your code will not work. _no one_ can be in the database when you want
to restore it, not even the connection that is doing the restore, that
should be connected to a different database. You can do ALTER DATABASE...
and then RESTORE when you are connected to the master database and hope the
application won't be able to log back in between the statements, but that
won't always work.
--
Jacco Schalkwijk
SQL Server MVP
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
>
Since a database can't be restored while it's in use, the database
context needs to be changed to some other database:
USE master
GO
RESTORE ...
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user
mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||Apologies all. USE yourdb will of course use up the SINGLE_USER.
Should have read :-
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
RESTORE ...
--
HTH
Ryan Waight, MCDBA, MCSE
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>|||Thanks guys. Too busy looking at set dboption to remember alter database.
Paul
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>

password lost

Hello,

I import a package into Integration Services (it containt 3 ole db connections to SQL Server), then I schedule this into a job.

When I run the job, it failed !

For the connections, I use a sql user and, in the step that run package, I notice that the password isn't store

I add password in the connection string ("Password=password;") and when I rerun the job, it terminate with success.

My question is: Is it possible to store the password directly into the package and so avoid password encoding several times ?

Regards

Arnaud

Arnaud H wrote:

Hello,

I import a package into Integration Services (it containt 3 ole db connections to SQL Server), then I schedule this into a job.

When I run the job, it failed !

For the connections, I use a sql user and, in the step that run package, I notice that the password isn't store

I add password in the connection string ("Password=password;") and when I rerun the job, it terminate with success.

My question is: Is it possible to store the password directly into the package and so avoid password encoding several times ?

Regards

Arnaud

Sorry, I forgot saying you that the package come from a DTS package. It has been build with the DTS migration tool.

Regards

Arnaud

Passing status back from Command line application to SQL Job

Hi,

I have created a job in SQL Server 2005 in which one of the step executes a .NET console application which is created in .NET to update some status to database before the next step. i need some help in sending some status back to sql job when i come accross any problem in the console application for ex when there is a exception i need to send some status to the job, so the job gets failed permanently. i tried few other methods of updating some temporary status database with this error information and have another intermediate step in the job to check for the status... it worked but i dont like doing it. please let me know if there is any other method to do this.

Thanks in advance

Hariharan

Are you doing anything with SSIS, or is this a SQL Server Agent question only?|||I'ts in SSIS as one of the step.|||

Hari Haran wrote:

I'ts in SSIS as one of the step.

Please provide more details then... As it reads, it sounds like it has nothing to do with SSIS.

Thanks,
Phil|||

To make the to step fail, just ensure the applicaton returns a suitable exit code. Take a closer look at the Job Step dialog and note of the "Process exit code of a successful cvommand:" option. Match this to you exist code as required. If this is the SSIS task after all, then look at the properties SuccessValue and FailTaskIfRetrnCodeIsNotSuccessValue.

You have set an exit code? If not see -

Environment.Exit Method (System)
(http://msdn2.microsoft.com/en-us/library/system.environment.exit.aspx)

Passing status back from Command line application to SQL Job

Hi,

I have created a job in SQL Server 2005 in which one of the step executes a .NET console application which is created in .NET to update some status to database before the next step. i need some help in sending some status back to sql job when i come accross any problem in the console application for ex when there is a exception i need to send some status to the job, so the job gets failed permanently. i tried few other methods of updating some temporary status database with this error information and have another intermediate step in the job to check for the status... it worked but i dont like doing it. please let me know if there is any other method to do this.

Thanks in advance

Hariharan

Are you doing anything with SSIS, or is this a SQL Server Agent question only?|||I'ts in SSIS as one of the step.|||

Hari Haran wrote:

I'ts in SSIS as one of the step.

Please provide more details then... As it reads, it sounds like it has nothing to do with SSIS.

Thanks,
Phil|||

To make the to step fail, just ensure the applicaton returns a suitable exit code. Take a closer look at the Job Step dialog and note of the "Process exit code of a successful cvommand:" option. Match this to you exist code as required. If this is the SSIS task after all, then look at the properties SuccessValue and FailTaskIfRetrnCodeIsNotSuccessValue.

You have set an exit code? If not see -

Environment.Exit Method (System)
(http://msdn2.microsoft.com/en-us/library/system.environment.exit.aspx)