Saturday, 14 November 2015
Wednesday, 11 November 2015
Using PowerShell-FTP-Client to download and delete files from an FTP server
- Configure PowerShell - out the scope of this blog entry, but here with Microsoft
- Download and install the PowerShell FTP Client Module by Michal Gajda.
- Use the code below which logs on to the FTP server and then gets a list of files
- Downloads each file in the list
- Deletes the file after download
$FtpServer = "ftp.server.com" $User = "username" $PWD = "cleverpassword" $Password = ConvertTo-SecureString $Pwd -AsPlainText -Force $FtpCredentials = New-Object System.Management.Automation.PSCredential ($User, $Password) Set-FTPConnection -Credentials $FtpCredentials -Server $FtpServer -Session MyFtpSession -UsePassive $FtpSession = Get-FTPConnection -Session MyFtpSession $ServerPath = "/backups/test/" $LocalPath = "C:\Data\test\" $fileList = Get-FTPChildItem -Session $FtpSession -Path $ServerPath -Filter *.txt foreach ($element in $fileList ) { $filename = $ServerPath + $element.name Get-FTPItem -Path $filename -Session $FtpSession -LocalPath $LocalPath -Overwrite $true Remove-FTPItem -Path $filename -Session $FtpSession }
Tuesday, 10 November 2015
How to allow a user to run a SQL Server Agent Job from a client application.
I wanted to allow users to run a SQL Server agent Job from a client application.
Short Answer
CALL msdb.[dbo].[sp_start_job]
I had a number of errors but the fix was to
1) Add their login as a user to the msdb database
2) Put the user into group: SQLAgentOperatorRole
Long answer I had a stored procedure like below
Then I got
Long answer I had a stored procedure like below
CREATE PROCEDURE [dbo].[startJob] @JobName nvarchar(255) as BEGIN declare @retval int EXEC @retval = msdb..sp_start_job @JobName return @retval ENDAnd runs OK for me but didnt work for a user. Problem 1 "the execute permission was denied on the object 'sp_start_job' database 'msdb'" To check this permission run this query
SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC FROM MSDB.SYS.DATABASE_PERMISSIONS DP JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID JOIN MSDB.SYS.DATABASE_PRINCIPALS PR ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID WHERE O.NAME = 'SP_START_JOB' Results NAME PERMISSION_NAME STATE_DESC TargetServersRole EXECUTE DENY SQLAgentUserRole EXECUTE GRANT USE [msdb] GO CREATE USER [domain\securitygroup] FOR LOGIN [domain\securitygroup] GO EXEC sp_addrolemember N'SQLAgentUserRole', N'domain\securitygroup' GO
Then I got
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61 The specified @job_name ('My Job X') does not exist.This is because the user did not have permission to run someone else's job. They need to be made a member of the SQLAgentOperatorRole to do that.
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'domain\securitygroup' GOReferences:
Monday, 15 April 2013
Amazing video
This is an live action short video from Russia (I believe). It's in the style of a first person shooter game.
Tuesday, 5 February 2013
How to stop Excel from automatically splitting a CSV
One of the annoying features added to Excel is that when you open a file with csv extension it automatically parses the file on the "," and butchers your file. This is annoying if the file uses another character to split or contains German numbers which use a comma for the decimal etc. So how do you fix this?
1) Open Excel (dont double click on the CSV)
2) Go to "Data" -> "Get External Data from Text"
3) Select the CSV file
4) Click "Import"
5) Go through the wizard and specify the delimiter and that all fields are text not numebrs etc
6) You are done.
Friday, 23 November 2012
Learning Silverlight
I recently had to take over a Silverlight application and had to remind myself how it hangs together. Here are some good blogs on the topic
How to blog Code
Here is a great blog on how to post code snippets.
- http://www.craftyfella.com/2010/01/syntax-highlighting-with-blogger-engine.html
- Copy and paste source into http://www.opinionatedgeek.com/dotnet/tools/htmlencode/encode.aspx
- encode the source then copy from above step and paste into blog
- Then wrap in tags
Subscribe to:
Posts (Atom)