Power BI Gateway Service – “The on-premises data gateway service” is offline

Background

Application Subject Matter Expert ( SME ) called this morning to report that his application is down.

Error Message

Here is the error message


Couldn't load the data for this visual
The gateway is either offline or could not be reached.
Please try again later or contact support. If you contact support, please provide these details.
Error CodeDM_GWPipeline_Client_GatewayUnreachable
Activity ID5a763a2d-d357-34ab-5396-ec568baae17a
Correlation ID7b1ee57b-c957-d313-125a-08b550e3ab1b
Request ID4bcbe543-5cd3-9787-8ad4-47cdb93842f4
Time    Tue Jan 17 2017 09:57:16 GMT-0800 (Pacific Standard Time)
Version13.0.1700.1003
Cluster URI

Diagnostic

Control Panel – Services Applet

Looked for the Gateway Service and in our case it is on the same box as Our SQL Server Engine.

Using the services applet in Control Panel, confirmed that the service is indeed stopped.

Quickly restarted it.

relevantservices

 

Why is it down?

MS SQL Server

ErrorLog

We checked MS SQL Server and know that the errorlogs were recycled on Jan 14th, 2017.

 

Event Viewer

System Log

Filter
Filter – Custom Range

Let us set our date range to encompass Jan 14th, 2017.

And, so 12 AM to 12:55 PM on Jan 14th, 2017.

customrange

 

Filter – Filter Current Log

Here is what our filter looks like once we narrowed on Jan 14th.

filtercurrentlogforjune142017

Explanation

  1. Filter Out events that are causing a lot of noises.
    • In our case most of the noises were Events 140 & 157
    • Place a minus in front of an event to filter them out
    • Event ID = 140
      • Event ID :- 140 NTFS Warning
        The system failed to flush data to the transaction log. Corruption may occur in VolumeId:<> DeviceName: \Device\HarddiskVolume<>.(A device which does not exist was specified.).
    • Event ID = 157
      • Event ID :- 157 “Disk # has been surprise removed”

 

Events
Events Image

Here are the events ordered by Level..

eventssystemforjune142017-20170117-0615pm

Events Image
Level Date and Time Source  Event ID  General
 Error  1/14/2017 1:25:31 AM  Service Control Manager  7000  The SQL Server Integration Services 12.0 service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
 Error  1/14/2017 1:26:51 AM  Service Control Manager  7000 The On-premises data gateway service service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
 Error  1/14/2017 1:25:31 AM  Service Control Manager  7009 A timeout was reached (30000 milliseconds) while waiting for the On-premises data gateway service service to connect.

 

 

Explanation:

  1. Two of our services are timing out.  And, those are:
    1. SQL Server Integration Services
    2. And, On-Premises Data Gateway Service
  2. Two types of events are listed
    • Event ID :- 7000
      • Service Start Timeout
    • Event ID :- 7009
      • Timeout was reached

 

Remediation

  1. Change Service Mode from Automatic to “Automatic Delayed Start
  2. Change Service Dependency
  3. Write a start service Script and schedule through “Task Scheduler

 

Change Start Mode from Automatic to “Automatic Delayed Start”

servicestartuptype

 

Add Service Dependency

servicedependecy

SQL Server – Error Log – Parsing – FindStr

Background

A need to parse all available SQL Server Error Logs for a SQL Server Instance and find I/O issues.

Code

Stored Procedure – dbo.sp_errorLog_findstr




use [master]
go

if object_id('dbo.sp_errorLog_findstr') is null
begin

	exec('create procedure [dbo].[sp_errorLog_findstr] as ')

end
go

alter procedure [dbo].[sp_errorLog_findstr]
(
	  @stringSought	varchar(255)
	, @stringSought2	varchar(255) = null
)
as

begin

	declare @FileID			int
	declare @FileMaxID		int
	declare @Subsystem		int

	SET NOCOUNT ON

	DECLARE @maxLog      INT
	DECLARE @searchStr   VARCHAR(256)

	DECLARE @errorLogs   TABLE 
	(
		  LogID    INT
		, LogDate  DATETIME
		, LogSize  BIGINT   
	);

	DECLARE @logData  TABLE 
	(
		  FileID   INT default (-1) NULL
		, LogDate     DATETIME
		, ProcInfo    VARCHAR(64)
		, LogText     VARCHAR(MAX)   
	);

	INSERT INTO @errorLogs
	(
		  LogID 
		, LogDate
		, LogSize
	)
	EXEC sys.sp_enumerrorlogs
	;

	SET @FileID = 0
	set @Subsystem = 1

	SELECT @FileMaxID = max([LogID])
	FROM   @errorLogs

	WHILE (@FileID <= @FileMaxID )
	BEGIN

		INSERT INTO @logData
		(
			  [LogDate] 
			, [ProcInfo]
			, [LogText]
		)
		EXEC sys.sp_readerrorlog 
				  @FileID
				, 1
				, @stringSought 
				, @stringSought2
				;

		update  tblLD
		set     tblLD.FileID = @FileID
		from    @logData tblLD
		where   (
					   ( tblLD.FileID is null )
					or ( tblLD.FileID = -1 )
				)

		SET @FileID = @FileID + 1

	END

	SELECT FileID, [LogDate], [LogText]
	FROM @logData
	ORDER BY
			 [LogDate] desc
			;

end
go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_errorLog_findstr]'
go




Sample


	declare @stringSought varchar(255)
	declare @stringSought2 varchar(255)

	set @stringSought  = 'SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file'
	set @stringSought2 = 'tempdb'

	exec [dbo].[sp_errorLog_findstr]
		  @stringSought = @stringSought
		, @stringSought2 = @stringSought2




Output

sqlserver-error-iostalls-20170117-0300pm

 

Explanation

  1. Part of the problem in our case is that all files are using our system drive ( Drive C:)

 

Source Code Repository

GitHub

Placed here

 

Referenced Work

Here are the code that I used:

  1. Mike Eastland
    • Search multiple SQL Server Error Logs at the same time
      Link

 

Twins :- Adopted Or Just Different

 

Videos & Write Ups

  1. Mia and Alexandra
    • Independent Lens | Twin Sisters | Sibling Love | PBS
      Published on :- 2015-Sept-18th
      Link
    • The Amazing Story of Twin Sisters
      Link
    • The Movie
      Link
  2. Lucy and Maria Aylmer
    • Good Morning Britain
      • Non-Identical Twins | Good Morning Britain
        Link
  3. Anais Bordier & Samantha Futerman
    • ABC News
      • Twin Sisters Separated at Birth Reunite
        Link
  4. Audrey Doering & Gracie Rainsberry
    • On Good Morning America
      • Twin Sisters Separated at Birth Reunite on ‘GMA’
        Link
      • Identical Twins Reunited on ‘GMA’ Explore NYC Together
        Link
    • ABC News
      1. Identical Twin Sisters Separated at Birth Reunite
        Link

 

 

 

Indepth

Mia & Alexandra

The Story

Link

In this heartwarming documentary, identical twins Mia and Alexandra were found as babies in a cardbox in China in 2003. They were separated and put up for adoption to two families from opposite corners of the world: One of them from a small village in Norway, surrounded by high mountains and deep fjords, and the other from the major American city of Sacramento, California.

The twins are reunited by destiny when, incredibly, their adoptive mothers brought identical red gingham dresses for them to wear on adoption day. This startling coincidence made the mothers take note of each other and start talking. That’s when they noticed that the girls looked very much alike… but the orphanage denied that the girls were related.

Six months later, established a world apart, truth has its day when DNA tests confirm that the girls are identical twins.

A true story of inspiration, Twin Sisters follows Mia and Alexandra through an uplifting parallel journey until they reunite in Norway when they are 8 years old. This is one of those feel-good stories that will make you cry, laugh, and think about the people you miss in your life. Among the top documentaries in the world, this award-winning film has been seen by an estimated 20 million people so far, and broadcast by 30 TV channels around the world.

eRightSoft – Super – Bundled with Toolbars & Addons

Background

I was looking for a free Video Converter and tried eRightSoft free version.

I really should not have.

 

Download Page

Here is what the download advisor page looks like:

Image

download

 

Textual

  1. SUPER © setup file installer
    •  Strongly Recommended.
    • WITHOUT any additional 3rd party Bundle Offers, Toolbars or Addons.
  2. SUPER © setup file installer
    • Totally free to download and install.
    • Comes with various Bundle Offers, Toolbars, Addons.

Download Selection

Being on the cheap side, I chose the free version with the Bundled Offers, Toolbars, and AddOns.

 

Installation

 

I tried my best to pay attention and only chose to install the main product.

 

But, to no avail

Upon launching by browsers, I saw that I was being taking to No-No Land.

 

Browser Hijack

HomePage & Search Hijack

My browsers Home Page is being changed to:

  1. newssci.com
  2. simolesr.com/credit

 

newssci.com

sear2

newssci-com-sear2-20170115-1159am

 

 

sear3

newssci-com-sear3-20170115-0111pm

 

 

simolesr.com/credit

simolesr-com-credit

 

 

Remediation

To remediate, I downloaded three tools and they are:

  1. Spybot Search & Destroy
  2. MalwareBytes
  3. Unhackme

 

Review

Here is a quick  review:

Both Spybot Search & Destroy and Malwarebytes are tools I have used for a while.  And, I am thankful to both of them.

But, the product that took my day is Unhackme by Dmitry Sokolov.

 

Dmitry Sokolov – Unhackme

Download

Downloaded it from here.

The free version has  a 30-days grace period.

 

Installation

Keep in mind that you have to be an Administrator to run.

mustbeanadministrator

 

Usage

 

Processes

processes-20170115-0126pm

PUPS

removepups-20170115-0126pm

 

Browser Plugins

browserplugins_20170115_0445pm

 

Explanation:
  1. You pretty much want to stay with the checked entries
  2. Which in our case
    • Fast Search
    • And, another Browser Helper Object

 

Search Settings

searchsettings-20170115-0132pm

Explanation:
  1. Current Home Page
    • For us:
      • pesonal-spage.com/sen

Terminology

Terminology Meaning
PUPS Potentially unwanted Programs

 

Summary

Learnt a new term, PUPS.

Again, PUPS mean potentially unwanted programs.

And, learnt to Say No and Never Again to eRightSoft Super/Bundled application.

Keeping Time Worked Using myhours.com

Background

Ever so often you end up supporting somebody, a project, or a company.

And, you need to keep hours worked on a project or task level.

I sought out free websites that will let me do so.

 

myhours.com

Here is my experience using myhours.com

 

fee

Here is what the fee structure look like:

fee-20170114

 

 

Product Fee Additional Use
 Individual Free Only the one user is allowed
 Pro Version  $3 per month base  $2 per additional user

 

 

Maintenance

We will setup the following entities:

  1. Clients
  2. Projects
    • Assign Tasks
    • Assign Team Members
  3. Tasks
  4. People

 

Clients

To list registered Clients and maintain them click on the menu items – Manage / Clients

List Clients

listclients_20170114_0915pm

Manage Client

Add New Client

To add Clients, click on the “Create New Client” button.

manageclient_macy

 

Choose Existing Client

Choose a client to edit from the list of Clients

 

listclients_20170114_0920pm

 

Projects

With the same ease you can add Projects and assign them to Clients.

List Projects

listprojects-20170114-0923pm

 

Maintain Project

manageproject_softwareengineering_20170114_0927pm

 

Assign Tasks

assigntasks_20170114_0932pm

 

Assign Team Members

assignteammembers_20170114_0934pm

 

Tasks

List Tasks

listtasks-20170114-0936pm

 

Manage Task

managetask-databasedevelopment_20170114_0939pm

 

People

List People

managepeople_list_20170114_0941pm

Explanation

  1. As we using the free package:
    • We can not more than one registered user
    • To add users so that they can use the system, we have to upgrade to the Pro Package

 

Track

Outline

Clicking the Track Menu item allows us enter hours works for each day

  1. Add Track Log
  2. List Previous Tracked Entries
  3. Edit Previous Entries

 

Add Track Log

addtracklog-20170114-1000pm

 

Listing previously log Time Entry

trackedhours-list-20170114-0956pm

 

Editing previously log Time Entry

trackedhours-edit-20170114-0950pm

 

Unavailable Options

As we are using a free version, there are some functionalities that are not available to us.

Here are some of the tooling that is not available:

  1. Invoices
  2. Approvals
    • No need for Approval as only the Admin is using the tool
  3. Task Delegation
    • You can delegate tasks for multiple days to yourself or your team
  4. Imports
    • Import legacy data from other tools

Invoices

invoices

Approvals

approvals

Task Delegation

taskdelegation

Imports

imports

 

Reports

My Hours has a very rich Report utensils.

  1. List
    • List hours worked displayed within five distinct Tabs
      • By Days
      • Clients
      • Projects
      • Tasks
      • People
  2. Dashboard
    • Rich Charts aggregated datasets

Reports – Activity

report_activity_2017jan_20170114_1032pm

 

Reports – Dashboard

report-dashboard-20170114-1037am

 

SQL Server – Running SQL Server Agent Jobs with least privilege accounts – TroubleShooting – Day 1

 

Background

Here are some of the errors that we ran into while configuring our SQL Server Agent Jobs to run with an Account that has streamlined permission sets.

 

Errors

ActiveX Scripting

Error – Script does not destroy all the objects

Error Image

ScriptDoesNotDestroyAllTheObjects

 

Error Text

 
The command script does not destroy all the objects that it creates. Revise the command script
 

 

Remediation

Look through your code and please make sure that you have corresponding release statements for all your create objects.

We had a lone createActiveXObject and we forgot the corresponding set object to Nothing.

Error – Error authenticating proxy …. The user name or password is incorrect

Error Image

userOrPasswordIsIncorrect - 20170113 - 0737PM

 

Error Text

 
Unable to start execution of step 1 (reason: Error authenticating proxy … system error: The user or password is incorrect)
 

Remediation

Specify valid user credentials for your credential

USE [master]
GO

ALTER CREDENTIAL [credentialBISSQL]
WITH
        IDENTITY = N'HRDBMirr\BISDBSQLSvc'
      , SECRET = N'786544'
GO

 

 

Error – Proxy is not allowed for subsystem “ActiveScripting”

Error Image

grantloginisnotallowed_2017013_0422pm

 

Error Text


Proxy (37) is not allowed for subsystem "ActiveScripting" and user "LABDB\DBSQLSvc". 
Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. (.Net SqlClient Data Provider) 

 

Remediation

In our case we already granted our proxy access to the ActiveScripting subsystem.

Additionally, we needed to grant access our login access to the Proxy, as well.


USE msdb ;  
GO  

declare @loginName     sysname
declare @proxy	       sysname
declare @commit	       bit

set @loginName = 'LABDB\BISDBSQLSvc'
set @proxy = 'proxyBISSQL'

 
EXEC dbo.sp_grant_login_to_proxy  
          @login_name = @loginName
	, @proxy_name = @proxy

;

 

 

Error – ActiveScripting

Error Image

permissiondeniedonline89_20170113_0436pm

Error Text


Executed as user.  Error code: 0.  Error Source = Microsoft VBScript runtime error.  Error Description: Permission Denied.  Error on line 89.
 

Remediation

While running our ActiveXObject Script, the system ran into a couple of potholes.

In our case, it was File System permissions.

File System – Permission – Original

Here is our original File System Permission set

fspermissions_before_20170113_0451am

 

File System – Permission – Revised

fspermissions_after_20170114_0457am

Explanation
  1. As the folder is a log folder, we granted write full permissions to it.
  2. A more careful Admin will likely only grant create, modify, list folder contents, and Read

 

SQL Server – Running SQL Server Agent Jobs with least privilege accounts

Background

We have a need to create a pretty complete SQL Server BI Stack that allows developers to develop SQL Objects, create cubes ( Analysis Services ), design workflows ( Integration Services) and integrate code ( ActiveX, Batch files, and PowerShell).

 

Disclaimer

I will not advocate that SQL Server Agent should be your work engine, but if your job management needs are simple and limited, I will say it is a good “Jump Off.

Glossary

Here are some of the terms that we will use:

Credentials

  1. Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server.
    • Usage Profile
      • Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.
    • Mapping
      • While a credential can be mapped to several SQL Server logins at the same time,  a SQL Server login can only be mapped to one credential at a time.
      • Once a credential is created, use the Login Property page to map Logins to the Credential

 

Goal

We need to import some data files into SQL Server tables.

And, to tighten our surface area, we will not use the Account that the SQL Server Engine or Agent is using.

But, to use a very specific and least favored OS or AD Account.

Outline

  1. Create Local Computer Account or Active Directory Account
  2. Create  Credential
  3. Create Proxy
  4. Grant Login access to Proxy
  5. Grant Proxy access to subsystems
  6. Create & Edit SQL Server Agent Job to use the Proxy
  7. Run SQL Server Agent Jobs a few times

 

Tasks

Create Local Computer Account or Active Directory Account

We will go with a local Computer Account and create one while we wait for our OS Admin group to provision an Active Directory Account.

Code

set _SAMAccount=DBSQLSvc
set _SAMAccountPassword=buddy6187#!

net user %_SAMAccount% %_SAMAccountPassword% /ADD

Credential

Create Credential

Let us create a credential.

Code



use [master]
go

/*
	Create a Credential
	https://msdn.microsoft.com/en-us/library/ms190703.aspx
	CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',   
		SECRET = '<EnterStrongPasswordHere>';
*/

declare @commit bit

set @commit = 0
--set @commit = 1

begin tran

	--credentialBISSQL
	if not exists
	(
		select *
		from    sys.credentials tblSC
		where   tblSC.[name] = 'credentialBISSQL'
	)
	begin

		CREATE CREDENTIAL [credentialBISSQL]
			WITH  IDENTITY = 'HRDBLab\DBSQLSvc'
			    , SECRET = 'buddy6187#!'
	end

while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end

Alter Credential

If you need to change the account you are using as your Credential or re-enter is password, please use the Alter Statement.

Code


USE [master]
GO

ALTER CREDENTIAL [credentialBISSQL] 
	   WITH 
			  IDENTITY = N'LABDOMAIN\BISDBSQLSvc'
			, SECRET = N'cookingMONSTER81712'
GO

Proxy

Let us create a proxy.

Code


use [msdb]
go

set nocount on
go

declare @proxy		sysname
declare @credential sysname
declare @commit		bit


set @proxy = 'proxyBISSQL'
set @credential = 'credentialBISSQL'

set @commit = 0
--set @commit = 1

begin tran

	if not exists
	(
		select *
		from    dbo.sysproxies tblSP
		where   tblSP.[name] = @proxy 
	)
	begin

		EXEC dbo.sp_add_proxy  
				  @proxy_name = @proxy
				, @enabled = 1
				, @description = 'Proxy Account for interfacing with other services' 
				, @credential_name = @credential 
				;
	
	end


	-- select * from master.dbo.syslogins
	-- select * from dbo.sysproxylogin
	select  
			   [proxy] = tblSP.[name]

			 --, tblSP.[user_sid]

			 , [proxyAccount]
				= suser_sname(tblSP.user_sid)

			 , tblSSS.[subsystem]

			 , tblSSS.[subsystem_dll]


	from    dbo.sysproxies tblSP

	left outer join dbo.sysproxysubsystem tblSPSS 

			on tblSP.proxy_id = tblSPSS.proxy_id

	left outer join dbo.syssubsystems tblSSS

			on tblSPSS.subsystem_id = tblSSS.subsystem_id



while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end


Grant Login access to Proxy

Let us grant Login Access to Proxy

Code



USE msdb ;  
GO  

declare @loginName     sysname
declare @proxy		   sysname
declare @commit		   bit

set @loginName = 'HRDBLab\DBSQLSvc'
set @proxy = 'proxyBISSQL'

set @commit = 0
--set @commit = 1


begin tran
  
	EXEC dbo.sp_grant_login_to_proxy  
		    @login_name = @loginName
		  , @proxy_name = @proxy
		;

while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end
go


Grant Proxy access to subsystems

Grant Proxy access to specific subsystems

Code



use [msdb]
go

set nocount on;
go

declare @proxy		   sysname

declare @id			   int
declare @idMax		   int
declare @subsystemID   int
declare @subsystemName sysname

declare @tblSubsystem  TABLE
(
	  [id]			int not null identity(1,1)
	, [subsystemID] int not null
	, [subsystem]	varchar(60)
)

declare @iProcessed int
declare @commit		bit

declare @CHAR_SEPARATOR   varchar(30)
declare @listofSubSystems varchar(4000)
declare @log		varchar(255)

set @iProcessed = 0
set @listofSubSystems = ''
set @CHAR_SEPARATOR = '; '

set @proxy = 'proxyBISSQL'

/*

	select * from dbo.syssubsystems tblSSS

*/
insert into @tblSubsystem
(
	  [subsystemID]
	, [subsystem]
)
select 2, 'ActiveScripting'
union
select 3, 'CmdExec'	
union
select 9, 'ANALYSISQUERY'	
union
select 10, 'ANALYSISCOMMAND'	
union
select 11, 'SSIS'	
union
select 12, 'PowerShell'	

set @id = 1
set @idMax = ( select max([id]) from @tblSubsystem)

set @commit = 0
--set @commit = 1


begin tran


	while (@id <= @idMax)
	begin

			select 
					  @subsystemID = tblS.subsystemID
					, @subsystemName = tblS.subsystem
			from  @tblSubsystem tblS
			where [id] = @id

		/*
			Grants the proxy proxyBISSQL access to the ActiveX Scripting subsystem.  
		*/
		if not exists
		(
			select *

			from    dbo.sysproxies tblSP

			inner  join dbo.sysproxysubsystem tblSPSS 

				on tblSP.proxy_id = tblSPSS.proxy_id

			inner join dbo.syssubsystems tblSSS

				on tblSPSS.subsystem_id = tblSSS.subsystem_id

			where   tblSP.[name] = @proxy 

			and     tblSSS.subsystem = @subsystemName

		)
		begin

			EXEC dbo.sp_grant_proxy_to_subsystem  
					  @proxy_name = @proxy
					, @subsystem_id = @subsystemID
				;  

			set @listofSubSystems = @listofSubSystems
										+ ' '
										+ @subsystemName
										+ @CHAR_SEPARATOR

			set @iProcessed = @iProcessed + 1

		end

		set @id = @id + 1

	end

	set @log = 'For Proxy Account '
					+ @proxy 
					+ ', '
					+ cast(@iProcessed as varchar(10))
					+ ' subsystems processed!'
					+ ' - List of Subsystems '
					+ ' :-'
					+ @listofSubSystems 

	print @log

while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end
go



Create & Edit SQL Server Agent Job to use the Proxy

Job Properties

jobproperties-20170113-0616pm

Job Step

Screen

jobstep-listfiles-20170113-0612pm

Explanation
  1. Type :- ActiveX Script
  2. Run as :- proxyBISSQL

Code



OPTION EXPLICIT

Function rightAlign(ValIn, WidthOut) REM As String

	Dim l
	Dim dataOut
	
	l = len(ValIn)
	
	dataOut = space(widthOut - l) + " "

    rightAlign= dataOut
	
End Function
		

Dim strFolder

Dim strLogFolder
Dim strLogFile
DIm strLogFileFullName

Dim objFolder
Dim objFile
Dim objFSO
Dim objTextFile

Dim bFolderExists
Dim bFileExists

Dim strLog
Dim objListFiles
Dim iFile

' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8

strFolder = "E:\Vendor\Import\BackupFiles"
strLogFolder = "E:\Vendor\Import\Log"
strLogFile = "\backupFilesProcessing.log"
strLogFileFullName = strLogFolder & strLogFile

Set objFSO = CreateObject("Scripting.FileSystemObject")

bFolderExists = objFSO.FolderExists(strFolder)

If (bFolderExists) Then

else

	strLog = "Creating Folder " + strFolder
	
	Rem Wscript.Echo strLog

    objFSO.CreateFolder(strFolder)
	
End If

bFolderExists = objFSO.FolderExists(strLogFolder)

If (bFolderExists) Then

else

	strLog = "Creating Folder " + strLogFolder
	
	rem Wscript.Echo strLog

    objFSO.CreateFolder(strFolderLog)
	
End If

bFileExists = objFSO.FileExists(strLogFileFullName)


If (bFileExists) Then

else

	strLog = "Creating File " + strLogFileFullName
	
	rem Wscript.Echo strLog

    objFSO.CreateTextFile(strLogFileFullName)
	
End If


Set objTextFile = objFSO.OpenTextFile (strLogFileFullName, ForAppending, True)

Set objFolder = objFSO.GetFolder(strFolder)

Set objListFiles = objFolder.Files

iFile = 0

strLog = "@ " & CSTR(Now) 
objTextFile.WriteLine(strLog)

strLog = "====================="
objTextFile.WriteLine(strLog)

For Each objFile in objListFiles

	iFile = iFile + 1

	strLog = "File " + rightAlign(iFile, 4) + CSTR(iFile) + ") " + objFile.Name
	
	objTextFile.WriteLine(strLog)

Next

objTextFile.Close

Set objFSO = Nothing
set objTextFile = Nothing

To Do

Will come back and identify some of the errors we encountered.

 

References

 

  1. Transact-SQL Reference (Database Engine) > System Stored Procedures (Transact-SQL) > SQL Server Agent Stored Procedures (Transact-SQL)
    • sp_grant_login_to_proxy (Transact-SQL)
      • sp_grant_login_to_proxy
        Link
    • msdb.dbo.sp_delete_proxy ( Transact-SQL )
      • sp_delete_proxy
        Link
  2. Transact-SQL Reference (Database Engine) > System Tables (Transact-SQL)  > SQL Server Agent Tables (Transact-SQL)
    • dbo.sysproxies
      • dbo.sysproxies
        Link
    • dbo.sysproxylogin (Transact-SQL)
      • dbo.sysproxylogin
        Link
    • dbo.sysproxysubsystem  (Transact-SQL)
      • dbo.sysproxysubsystem
        Link
    • dbo.syssubsystems (Transact-SQL)
      • dbo.syssubsystems
        Link
  3. VBScript Example
    • ScriptingGuy1
      • How Can I Get a List of All the Files in a Folder and Its Subfolders?
        Link
    • VB Script to File
      • VBScript Write File
        Link
  4. System Passwords
    • Secured Passwords
      • Symantec Password Generator
        Link