I recently had fun with being unable to upgrade to version 1909 for Window 10 failing with a BSOD and an error which pointed to a memory issue. After using Nirsoft’s BSOD ViewNirsoft’s BSOD View it pointed to ArcSec.sys. I uninstalled Arcsoft Totalmedia Extreme. however the uninstaller left the drivers installed. I then used SysInternals […]
On PowerShell command pipe to export CSV add Encoding Unicode if characters change to ? Credit: Idera
1 | execute xp_cmdshell 'powershell -Command "Get-ChildItem -File -Path @path -filter *.* -Recurse | ForEach-Object {[PsCustomObject]@{Fullname = "$($_.FullName)"; LastWriteTime = "$($_.LastWriteTime)"}} | Sort Fullname -Descending | Export-Csv -Path @CSVFile -NoTypeInformation -Encoding unicode"' |
If using BULK INSERT ensure widechar is used (It will work but TSQL will add notices which may add pointless text to log files)
1 2 3 | BULK INSERT #bcp_file_mem FROM @CSVFile WITH (FORMAT = 'CSV', DATAFILETYPE = 'widechar', FIELDQUOTE = '"', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK); |
First we need to enable logging for the Windows Task Scheduler, open Event Viewer (Put “eventvwr.msc” in Run) and browse to TaskScheduler as below. Right click the Operational log and click properties and ensure the “Enable Logging” is checked Windows is now setup to log Scheduled tasks to the Event Viewer, now we need to […]
Ensure a mail profile is setup and make a note of its name. Run the following in Microsoft Query Analyser replacing the mail profile and email address.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | USE [msdb] GO /* Credit https://sqljunkieshare.com/2016/08/01/variable-wmi-not-found/ for issue with Variable WMI Not Found on newer versions of SQL Server */ EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 GO USE [msdb] GO /****** Object: Job [Mail on login failed: State 5] Script Date: 29/07/2019 10:40:56 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 29/07/2019 10:40:56 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Mail on login failed: State 5', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Send e-mail on WMI event', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Step 1 - send e-mail] Script Date: 29/07/2019 10:40:56 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1 - send e-mail', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @msg NVARCHAR(MAX) = ''From job: Login failed for $(ESCAPE_SQUOTE(WMI(LoginName))). Full error message follows: $(ESCAPE_SQUOTE(WMI(TextData)))''; EXEC msdb.dbo.sp_send_dbmail @recipients = ''<email address>'', @profile_name = ''<mail profile>'', @body = @msg, @subject = ''There was a login failed event on $(ESCAPE_SQUOTE(A-SVR)).'';', @database_name=N'msdb', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO |
Recently we had this issue where after windows patches were applied, after a reboot the Sage Line 500 v7.1 Windows services no longer worked on our development and live servers. It was due to the Visual Studio dll msvcr71.dll being removed SYSWOW64 (32 Bit systems will be System32 and you can see in process monitor […]
The below stored proc is used to populate two Sage fields shipper_code2 and shipper_code3 in the Order Header Table. Obiously if this is used already by another Sage Project do not use, as the fields won’t be updated. Here I look 30 days backwards and at particular invoice customers (There is no point updating every […]
If you get the following error on scheduled stored procedures, force the dateformat (The Microsoft SQL Server scheduler will probably be running the default mdy), you can use Set Language but I tend to change the dateformat. [SQLSTATE 01000] (Message 0) The conversion of a nvarchar data type to a datetime data type resulted in […]
I find this useful for using validating spreadsheet imports, using ISDATE can just blank the field if crap data is provided
1 2 3 4 5 6 7 | --Use Case to make sure a data field is a date --Will Work and produce the inserted field select CASE WHEN ISDATE(t1.datefield) =1 THEN t1.datefield ELSE '' END as datefield_full from (select '14/12/2054' as datefield) t1 --Won't Work and will produce Blank select CASE WHEN ISDATE(t1.datefield) =1 THEN t1.datefield ELSE '' END as datefield_full from (select '14/122054' as datefield) t1 |
This is useful to see who is using their licenses which are very expensive. I tend to check every few months and look at 90 days. Its a simple count excluding the System Account and look at a date range. I have only run this against XIR3 SP7 where auditing has been enabled on an […]
A simple way to strip out characters which can cause TSQL Code to break. Assumes the SQL commannd is in sqlstatement and you are passing a variable in me.comment, before executing the code.
1 2 3 4 | 'Allows Characters such as , and ' which may break TSQL Code and Cause Injection sqlstatement = RTrim(sqlstatement) + ",'Me.comment & "'" 'Strips out characters such as , and ' sqlstatement = RTrim(sqlstatement) + ",'Replace(Replace(Me.comment, ",", ""), "'", "") & "'" |