Category: SQL Server 2012
Sage Line 500/1000 Period End Email Check
As part of Sage Line 500/1000 Period End we found it easier and more audit friendly to have an automated email fire via SQL. My colleague Jim wrote the below (Hey Bob!), I then changed it around to match the sections in which we ran period end. This made it easier to determine if period…
Use TSQL to extract V1 DBArchive Blobs
Using TSQL it is very easy to automate extraction from Version One’s DBArchive product with its dbremoteblob program (It does require a username and password to access DBArchive and the program comes with DBArchive). With a 4 core CPU I extracted 72000 documents a day, a limitation found was the programs used did not return…
Check scheme.plitempm for any orphaned entries
Check scheme.plitempm for any orphaned entries against plpy tables, found to be caused by third party app not Sage Line 500.
Sage Line 500 password policy implementation and force password reset
I wrote a simple script to aid users moving to a new password policy, users were put on a policy which would expire a password after a day. Once they had changed their password the policy is amended to the new policy. I now use this for forcing password resets. Replace and with your Sage…
Setup MS SQL Server Alerts via TSQL
Amend TSQL to your environment, run the TSQL, restart the agent service and test (I find it easy to enter an incorrect username and password). USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, @databasemail_profile=N'<TSQL_MAIL_PROFILE>’, @use_databasemail=1 GO USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N’Login Mismatch’, @message_id=18456, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @job_id=N’00000000-0000-0000-0000-000000000000’GO USE [msdb]…
Powershell issue with output characters changing to ?
On PowerShell command pipe to export CSV add Encoding Unicode if characters change to ? Credit: Idera 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…
Sage EDI IN Intermediary Email
I have found users don’t always read the logs from the Adaptus EDI Module anymore in Sage Line 500. There are a few reasons why orders get stuck, the most common is a customer has sent a duplicate EDI file although one was caused by incorrect product supersession setup causing an endless loop. USE <Affected…
Use T-Sql to find triggers on specific database
Credit: Joe Stefanelli on Stack Overflow I wanted a simple way to find triggers on a specific database, Joe Stefanelli posted SQL which did exactly what I wanted. SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name ,OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’) AS isupdate ,OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’) AS isdelete ,OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’) AS…
Find unused MSSQL indexs on a database
Credit: Basit’s SQL Server Tips The following is very handy to check for any unused index’s on a MSSQL database, this should only be run once the database has been up for at least a week or when all scheculed jobs have been allowed to run at least once. The only change I made is…
SQL Server 2008 – Find which stored procedure is part of a scheduled
Find a stored procedure (or any text) inside a scheduled on on SQL Server 2008 and 2012 by querying the sysjobs and sysjobsteps system tables. The following provides the name of the job and the step name. It uses the standard % wildcard so replace myjob in “%myjob%” below SELECT name, “description”, “enabled”, database_name,step_name,step_id,command …