Category Archives: Uncategorized

I like to run this against a server group in SSMS to check multiple servers at once.

SELECT  job.Name,
        job.job_ID,
        job.Originating_Server,
        activity.run_requested_Date,
        DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) AS ElapsedMinutes,
        activity.last_executed_step_date,
        activity.last_executed_step_id
FROM    msdb.dbo.sysjobs_view job
JOIN    msdb.dbo.sysjobactivity activity
ON      job.job_id = activity.job_id
JOIN    msdb.dbo.syssessions sess
ON      sess.session_id = activity.session_id
JOIN    (
         SELECT MAX(agent_start_date) AS max_agent_start_date
         FROM   msdb.dbo.syssessions
        ) sess_max
ON      sess.agent_start_date = sess_max.max_agent_start_date
WHERE   run_Requested_date IS NOT NULL
        AND stop_execution_date IS NULL
    GO

Here is what I use to prevent one job from trying to restart another job.

EXEC msdb.dbo.sp_help_job @execution_status = 1,
 @job_aspect = 'JOB',
 @job_name = 'DBA - Daily Database Maintenance'

IF (@@ROWCOUNT = 0)
 PRINT 'Start job'
ELSE
 PRINT 'Job already running ... Nothing to do'

Here's a trusted connection example.

SELECT *
FROM 	OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQLDEV;Integrated Security=SSPI'
).[pubs].dbo.[authors]

Does this work?

https://sourceforge.net/projects/blogexporter/

Not yet:

2008-08-03 19:27:13,968 DEBUG [BlogExporter.Core.PostParser] Parsing blog 4855217953238557838, post 4841212145285365256: Welcome.
2008-08-03 19:27:13,984 ERROR [BlogExporter.Console.Program] Error while exporting blog
BlogExporter.Core.ExportException: Value not not found: atom:content/text()
   at BlogExporter.Core.XPathResolver.GetValue(XPathNavigator node, String xpath, String defaultValue) in H:\Development\Blog\BlogExporter\BlogExporter.Core\XPathResolver.cs:line 55
   at BlogExporter.Core.PostParser.ParsePost(XPathNavigator blogPost) in H:\Development\Blog\BlogExporter\BlogExporter.Core\PostParser.cs:line 186

(Source)

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows=200000,
@jobhistory_max_rows_per_job=10000
GO

USE master

CREATE LOGIN [$(UserName)] FROM WINDOWS WITH DEFAULT_DATABASE=[$(DatabaseName)]
GO
USE [$(DatabaseName)]
GO
CREATE USER [$(UserName)] FOR LOGIN [$(UserName)]
GO
EXEC sp_addrolemember N'$(RoleName)', N'$(UserName)'
GO

For any login in an NT group, if you want to reduce, or increase more permissions, just add the login by itself. The individual’s permissions wins.

Supported SQLCMD Syntax
Query editor supports the following SQLCMD script keywords:

[!!:]GO[count]

!!

:exit(statement)

:Quit

:r

:setvar

:connect server[\instance] [-l login_timeout] [-U user [-P password]]

:on error [ignore|exit]

:error |stderr|stdout

:out |stderr|stdout

Note:
For both :error and :out, stderr and stdout send output to the messages tab.

SQLCMD commands not listed above are not supported in Query Editor. When a script containing SQLCMD keywords that are not supported is executed, the Query Editor will send an “Ignoring command ” message to the destination for each unsupported keyword. The script will execute successfully, but the unsupported commands will be ignored.

Caution:
Because you are not starting SQLCMD from the command line, there are some limitations when running Query Editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

EXEC master.sys.xp_dirtree N’E:\MSSQL\MSSQL.1\’

Subdirectory depth
MSSQL 1
Backup 2
Data 2
JOBS 2
LOG 2
repldata 2

Server: Msg 8501, Level 16, State 3, Line 15
MSDTC on server ‘YourSQLServerName’ is unavailable.

Resolution:
You must start the Support Service (e.g. in Enterprise Manager) called DTC Distributed Transaction Coordinator.