Transact SQL :: How To Determine Remote Server Is Shutdown By Agent

Sep 28, 2015

I create a job name test connection by SQL AGent

Background:

- Have server1 and server2
- The job has 3 steps:
 + Step1: check server1 is running, next step2
               Server1 is shutdown or can not ping, next step3
 + Step2: do anything, for Example: run batch exe on server1
 + Step3: do anything, for Example: run batch exe on server2

Step1, i am using ping server1 command,

My problem is if server1 is shutdown, my command (ping server1) is also return true

and in the fact, job is run by follow Step1 -> Step2.

Expectation the job is run by follow step1 -> Step3

View 5 Replies


ADVERTISEMENT

How To Determine When And If SQL Agent Job Will Run Again?

Dec 1, 2006

I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?

Thanks

View 3 Replies View Related

SQL Tools :: Server Agent - Remote Procedure Call Failed (0x800706be)

Jun 20, 2011

I can't access SQL Server 2008 R2 remotely on Windows 2008

1.  TCP/IP Enabled for SQL Server Network Configuration Protocols, SQL Native Client 10.0 configuration clients, and SQL Native Client 10.0 configuration clients(32 bit)

2.  Firewall disabled to make sure its not interferring with things.

I noticed the SQL Server Agent is Stopped.  Not sure if this is the issue.  When I try and turn this from disabled to Automatic or Manual, I get this error:

Remote procedure call failed (0x800706be)

It shouldn't be this difficult.

View 23 Replies View Related

Transact SQL :: Bulk Insert From Remote Server?

Nov 20, 2015

SQL Server 2012

I want to be able to run the following command from SSMS (as an ad-hoc query).

BULK INSERT Database_Name.dbo.Table_Name FROM 'serverfile.txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', MAXERRORS = 0);

When I do I get:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "serverfile.txt" could not be opened. Operating system error code 5(Access is denied.).

I have full access to the file.I can do the same command successfully if the file is stored on a local drive on the server.

According to my DBA I can not run it with a remote file location because I don't have the SA permission. His solution is for me to create a job that runs the command. I have done so and the job works correctly.

Is he correct that there is no way for me to be able to run it from SSMS without SA permissions?

View 5 Replies View Related

Transact SQL :: How To Determine Index Create Date

Nov 7, 2012

Is it possible to determine an index create date in SQL Server 2008 R2?  The index in question is not a primary key.

View 8 Replies View Related

Transact SQL :: DATEADD - Determine Prior Month

Jul 8, 2015

My overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.

To get the prior month, I use the following code;

SELECT
(
SELECT
RIGHT('00' + CAST(
CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
WHEN 0 THEN 12
ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
END AS varchar(2)), 2)
)--, -- Month
--CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4)) -- Year
FROM
Current_Membership;

This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.'
But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'

It did. Worked fine. Then, to make really sure, I put in '201502.'

Instead of the single record of '01,' it shot out 177,209 rows of all '02's!

I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.

Like I said, I can use DATEADD - Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.

Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!

View 6 Replies View Related

Transact SQL :: Move Data From Linked Remote Server?

Jun 18, 2015

I have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db 

Copying a backup file is not an option either, and Mailing it on media make it a significant delay.

View 2 Replies View Related

Transact SQL :: Stored Procedure To Determine Which Databases To Restore?

Sep 27, 2015

I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.

View 3 Replies View Related

Transact SQL :: Determine When Database Was Backed Up Without Using MSDB Tables?

Apr 22, 2015

I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.

Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?

View 4 Replies View Related

Transact SQL :: Query To Determine Overlapping Date Ranges (by Category)

May 12, 2015

Given the data below, I have a couple needs:

1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)

2) Query to determine if any date ranges of the same category overlap

declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'

[code]....

View 7 Replies View Related

Right Procedure For Shutdown Production Web Db Server

Feb 15, 2001

hi,

What is the right procedure for shutdown a production web sqlserver for maintenace purpose. Checking users by sp_who?
Any other monitoring for current connections? lockings?

thanks
ram

View 4 Replies View Related

SQL 2012 :: Server Shutdown Process

Feb 13, 2014

I am just trying to find a good article on the process SQL goes through when shutting down and starting up, so far I have not found anything definitive on Google. I am assuming a checkpoint is invoked and committed transactions are written to disk, while uncommitted are rolled back, but I would like an official textual description of what happens.

View 0 Replies View Related

SQL SERVER 2005 SERVICE SHUTDOWN

Feb 22, 2008

Currently i am using SQL server 2005. Since the begining i am facing sqlserver service shutdown problem. Normally it happens in every 2-3 days and sometimes happens twice a day. It halts all the banking operation. After starting MSSQL SERVER service, the system goes online.

Following is the error message recorded in the event viewer.
---------------
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
---------------

Please tell me what steps i should take to resolve it.

Thanks

Umesh Raghubanshi
Kathmandu,Nepal

View 1 Replies View Related

Force Shutdown Of SQL Server Instance

Jul 20, 2005

I have a problem with an instance of SQL Server that refuses torespond to a shutdown request. I've managed to shutdown the SQLManager and DTC services but the sqlservr.exe process is permanentlyin a "Stopping" state.I cannot logon to the instance to issue a SHUTDOWN WITH NOWAITcommand. Short of rebooting the entire server, is there a way I canforce the process to end?Tony

View 3 Replies View Related

Error 3449 SQL Server Shutdown

Jun 27, 2006

SQL Server 2005 X64 Standard running on Windows Server 2003 X64 Htper Threaded Quad processors with 16 GB RAM.

I have had two production servers shut down this week due to Error 3449: SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

Previous to the shutdown I received multiple Error 3314 & 602: Could not find an entry for table or index with partition ID 491897996509184 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

Also, Error 9001: During undoing of a logged operation in database 'msdb', an error occurred at log record ID (838:208:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.


Does anyone have an idea why I have begun receiving these error messages? I have run dbcc checkdb on Master and msdb and they do not show any problems.

View 3 Replies View Related

DB Engine :: Need DDL Trigger Before Server Instance Shutdown

Nov 24, 2015

I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?

View 3 Replies View Related

Database 4 Cannot Be Autostarted During Server Shutdown Or Startup. (Microsoft SQL Server, Error: 904)

May 5, 2008

Hi,

I'm from Argentina, I'm running SQL Server 2005 Express. I can connect to SQL Server via Management Studio and see all my db but I don't have acces to them !!

The system gives me the following error when trying to expand all folders:

Database 4 cannot be autostarted during server shutdown or startup. (Microsoft SQL Server, Error: 904)

Any ideas??

Thanks from Argentina

View 3 Replies View Related

AlwaysOn Availability Does Not Support Server Shutdown / Power Off?

Sep 24, 2015

I have Configured always on Availability groups between Server 1 Primary Replica(Active), server 2 Secondary Replica(Passive) on top of WCFS...

Listener Name: AGListner( CLIENTS/APPLICATIONS connect using this Name)

Testing Scenario 1(on Virtual Servers):

I have turned network down on Server 1(primary) , then secondary server (Passive ) one came ONLINE and this is now the primary and i was able to connect to AGListner.now When i try to SHUTDOWN/POWER OFF PRIMARY(current Active server),failover happened to Secondary successfully but lost cluster and lost AGListner and was not able to connect ....now applications which are trying to connect using AGListenr name will loose all connections..does AlwaysOn Availability does not support Server SHUTDOWN/POWER OFF?or is there a way to resolve this? or am i doing wrong somewhere?

View 2 Replies View Related

How To Verify The Agent Is Up On A Remote Host?

Jul 20, 2005

Hi,Somtimes the agent will just die on some computers...I'd like to have a job monitoring the agent every 20 minutes or so..Do you know of a way to tell if the agent is up or no using plain Tsql?ThanksGaspar

View 1 Replies View Related

Running Ssis Package With A Remote Sql Agent?

Jan 18, 2007

hi all,

thanks for the valuable information all the time!!! saved me a lot of time...

our team developes a system for text data improvement using ssis .

we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .

my question is:

what is the best way to do this?

if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?

or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.

do we need to install sqlserver on these "ssis servers"?

thanks for your replies!

View 9 Replies View Related

Transact SQL :: How To Create Stored Procedure Using Agent Job

May 8, 2015

I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.

View 10 Replies View Related

Transact SQL :: Send Email If Agent Job Fails

Aug 8, 2015

Is it possible for to send an email if a job fails as an alert? Can the email tell me which step failed?

I know how the code to send an email, but unsure how to trigger this for an agent job failure.

View 8 Replies View Related

Execute Sql Server Agent Job Task - Job Immediately Returns Success... However Agent Job Is Still Running???

Nov 30, 2006

when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds



However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.



Thanks,

Larry

View 1 Replies View Related

Transact SQL :: Agent Jobs - Running More Than 3 Hours Based On Job Name?

Sep 24, 2015

I need a sql script for Jobs is running more-than 3 hours in a current server, based on a particular jobs.

View 2 Replies View Related

SQL Server Agent Could Not Access Replication Agent

Feb 19, 2007

We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?

View 4 Replies View Related

Shutdown Command

Jan 3, 2000

Hi,
Is there any shutdown command in sql that shutdowns the sql server with a single command or statement, as of now I am using the windows shutdown command.

thanks in advance.

bye,
Madhu.

View 2 Replies View Related

About Shutdown The Database

Mar 14, 2008

hi friends,
i am new to sqlserver. can anyone tell me how to shutdown the database.

View 2 Replies View Related

Remote Access Server Configuration Option And Remote Query Timeout?

Jun 2, 2015

- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?

- what is the impact of changing the remote query timeout (on the same page) from default value?

View 4 Replies View Related

OS Shutdown With Running MSSQL

Mar 10, 2008

I have my suspicions, but I need some proof ...

Is restarting a Windows OS box without first stopping the MSSQL service comparable to a shutdown with nowait command? I know that MSSQL will recover, but wouldn't it recover more nicely if it was shutdown on its own terms prior to the box shutting down? OR. Since it's a Windows service does the OS *know* to stop the service gently, allow the engineto place checkpoints on every db and then shut down the OS?

I've googled plenty and cannot find an exact answer. Lots say it's fine to do - doesn't mean it's right ;)

Thanks!

View 3 Replies View Related

Low Virtual Memory Shutdown

Jul 6, 2006

hi,

for the last six months or so my pc has been shutting down all applications for no apparent reason when a 'low virtual memory' bubble appears. I have removed dozens of items, such as games, image editos; all programmes that require a lot of memory but it is no good. Every 40mins or so the pc decides to shut everything down and where it is impossible to start any further applications, unless I log off and on or shut down the pc myself. I really am fed up with this, its so annoying. Is it because of a virus or do I still have too much on my pc?



insaneolly

View 1 Replies View Related

Timed Shutdown Of Process

May 3, 2007

I have an SSIS package that looks for the existence of a file (using Konesans file watcher) on the network to begin processing. I am trying to find a way to terminate/shutdown the entire process if that file is not out there by say 7:30 AM. I think I could set a timeout on the filewatcher task, but was hoping there was another tool I could use. (If the process had to be restarted late a timeout would still countdown the same period wouldn't it? We'd still need the job to stop by 7:30.)



More background. The package, once deployed, will be called from a command line script that is started by a scheduled task normally, or manually in the event of late availability.



Thanks,

Rog

View 3 Replies View Related

SQL 2012 :: Installing SP2 - Instance Shutdown

Dec 9, 2014

I want to install SP2 for SQL 2012, but I am not sure how my instance would be affected.

I want to know if the instance will go down for any amount of time during the update? I can schedule a server reboot after hours, but don't want to apply the service pack if it will take the instance offline at any point.

View 2 Replies View Related

How To Shutdown SQL 2005 Express Properly

Oct 21, 2007

While working on my Visual C# 2005 Express programs, I will sometimes open SQL 2005 Express to look at the database. Then I will disconnect the database and exit the SQL Server Management Studio Express.

Then when I run my Visual C# 2005 program the next time, I will always get this error message.
"Cannot open user default database. Login failed...."

Here is a copy of my connection string

string cs = "Data Source=.\SQLEXPRESS;AttachDBFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\hdcTemperatureCableReadings.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True";

string strSelect = "Select * from CableReading";

SqlConnection tcrConn = new SqlConnection(cs);

SqlCommand cmd = new SqlCommand("Select * from CableReading", tcrConn);

tcrConn.Open();

The error is being generated when "tcrConn.Open()" is being executed. This is line 27 of my program.

My question is: Am I shutting SQL 2005 Express down wrong when I go in and look at the database. The SQL 2005 Express is located on the same computer that I am writing my program on.

I copied the error message to the clipboard and copied into this thread.

System.Data.SqlClient.SqlException was unhandled
Message="Cannot open user default database. Login failed.
Login failed for user 'HDC\jfeeney'."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=11
LineNumber=65536
Number=4064
Procedure=""
Server="\\.\pipe\8A52A6B8-493D-45\tsql\query"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in C: empTestCableReadingTestCableReadingForm1.cs:line 27
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at WindowsFormsApplication1.Program.Main() in C: empTestCableReadingTestCableReadingProgram.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved