SQL Server - Dynamic Code Blocks

Tasks to think about when migrating SQL server for Dynamics GP to a new server

After performing SQL migration I had this top level list of things I had to think about, so this is just a private list put here of things that you might need to think about when migrating from one SQL server to another. This is for my environment, if you

SQL Server

SSMS Registered Servers–Key not valid for use in specified state

After exporting the registered servers from SQL Server Management Studio’s “Registered Servers window” and then importing the exported list into a new machine. You may encounter the following error if passwords were stored against the servers… Registered Servers Key not valid for use in specified state. (System.Security) This

Microsoft Dynamics GP

SQL indexed views are incompatible with Dynamics GP

Indexed views with Dynamics GP? – well I’ve been there and I have seen others attempt the same. The Dynamics GP database and application are not really compatible with SQL indexed views. To help out those searching around this subject I thought I should write up our experience. What is

SQL Server

Yes you can - SQL Server Table Compression and Dynamics GP

Today I attended SqlBits in Manchester UK, where there was a session “Performance tuning SQL server on crappy hardware” by Monica Rathbun. Monica has a the fast and punchy presentation style I enjoyed. Although I had already experienced or knew most of what was covered it was still a good

SQL Server

Failed to grant permission to execute error and Zen Barcode for SSRS 2016 Reporting Services

Copy assemblies into the correct reporting services folder When migrating to a new SQL server the barcodes were causing an issue, firstly because we lacked the barcode assemblies in the reporting services bin folder for the new version of Reporting Services that came with SQL server 2016. Finding the dlls

Microsoft Dynamics GP

SQL server Optimize for Ad hoc Workloads setting in server advanced properties window

TL;DR Turn this option on, from false to true Whilst looking at a problem SQL server instance, I was on the diagnostic journey looking at why the query plan cache was getting totally cleared every few minutes. It turned out the server had bad memory setup and SQL server

SQL Server

SQL Server upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25

Upgrading SQL server to SP4 or SQL Server 2016 encountered error 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25 Whilst performing a dummy run for a SQL Server instance upgrade, I encountered this error and it resulted in the SQL service not starting after the upgrade and the

SQL Server

Prevent SQL job running when another job is running

From SO post: https://stackoverflow.com/questions/200195/how-can-i-determine-the-status-of-a-job CREATE VIEW myschema.Admin_RunningSQLJobs AS SELECT job.NAME ,job.job_id ,job.originating_server ,activity.run_requested_date ,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed FROM msdb.dbo.sysjobs_view job JOIN msdb.dbo.sysjobactivity activity ON job.

SQL Server

SQL formatting and Power Query

Today I got caught out by using SSMSBoost to SQL format a query being used as the source for a Microsoft Power Query. On updating the stored procedure the query stopped working in Power Query, with the error that the column “Temp” was missing. Looking at the query output in

SQL Server

Recovering a deleted Reporting Services Report

Yes, I deleted the wrong report while housekeeping. This raised the question as to how to recover it once it has been deleted? There is no recycle bin to or undelete option in reporting services on the version I work with and at the time of writing (Nov 2017). It

SQL Server

SSMS SQL Hazard

Let me point out a hazard using SSMS, when developing a SQL delete query, especially more complex ones that take time to build up. I also think about some of the ways I work and how they minimise the chances of damage to data. Let us say we are developing

SQL Server

Uppercase SQL script using SSMS

I just realised I use this functionality a lot and thought that others might not know it was possible. Highlight a lower case bit of text in the SQL editor window of SSMS and press CTRL+SHIFT+U and the text will go to upper case (L for lower case)

SQL Server

Using SQL UNPIVOT operator to reconcile Dynamics GP inventory items

Working with the Dynamics GP inventory tables you will encounter the field named QTYTYPE a lot. This is usually seen with its partner LOCNCODE. Inventory in GP can reside in different locations (for example depots New York, London, Sidney). That location then is broken down further into five item types.

Microsoft Dynamics GP

Document attach feature Dynamics GP database and BusObjKey formats

I’ve been working on an archive project recently where I’ve been archiving records from GP into another archive database. This meant looking at how attachments are handled in Dynamics GP, in order to take any attachments related to records being moved into the archive. The Table CO00102 is

Microsoft Dynamics GP

SQL server FILESTREAM for Dynamics GP file attachments in coattachitems -is it possible?

In previous post (Managing document attachments in Dynamics GP) I looked at the attachments feature in Dynamics GP 2013-R2 and above. I talked about how files attached to GP in this manner are stored in the database, specifically in the coAttachmentItems table. This table can get to be a huge

SQL Server

Trying to update locked view

Trying to update a view with ALTER VIEW and it does not happen immediately, then there is most likely a contention for the table with another process (user). To find out what is blocking, start the alter statement again, then execute the following SQL, for SQL server, sp_who2 active

SQL Server

Old SQL habits

With each new version of SQL server some old SQL habits have to die. In Dynamics GP we often find document numbering sequences that are padded with zeros to facilitate sorting/ordering and to “look normal” on printed documentation. For example website orders may be imported via econnect into GP

SQL Server

Beware diacritic characters where integrating with SQL

I’m certain you all know the above and practice it regularly. First a little background… In Dynamics GP we wrote a very basic “CRM like” system using a .NET GP Addin, that lays over the top of the SOP module. It introduces the concept of contact records, with many-many

SQL Server

SQL bucket filling example permalink

I’m frightened that one day this sample disappears, its from a SQL Server Central.com thread, Filling Bucketsoriginally posted by “[email protected]” -- Originally from: -- http://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx DECLARE @AmountToAllocate INT = 21 ;WITH Calculator AS ( SELECT BucketID, TotalSize, Amount, AmountLeftToAllocate = CASE WHEN @AmountToAllocate > (TotalSize

SQL Server

How to check if MS SQL Server, Linked Server object is in use

Linked server objects allow one SQL server to connect to another at the database engine level. A connection is defined when creating the linked server, with credentials that should be used for that connection. As time passes, the challenge is knowing if that old linked server object is still being

SQL Server

Update all XML element nodes in an XML field of a SQL table

I wrote an electronic invoicing solution for Dynamics GP a long time ago. The XML invoices of various formats are stored against the invoice number in the database. The column was upgraded to XML type when SQL sever started supporting it in SQL 2005. Although it is possible to cast

SQL Server

SQL Replication duplicate log reader in SQL replication monitor

It is upgrade time, Dynamics GP does not like schema objects bound by replication during upgrades. During version updates some tables are copied dropped and recreated, depending on what changes have been made to the schema. If replication is left running on a Dynamics GP company during the upgrade, then

SQL Server

SSMS locked / frozen after network disconnect or computer sleep

When both the following are true: * the machine is woken from sleep or the network connection has been interrupted to SQL server * SQL Server Managment Studio Activity Monitor was open on a tab somewhere in the editor deck in SSMS. -then It will be no longer be possible to interact

Microsoft Dynamics GP

SQL Server 2012 SP2 Replication Log Reader error v.11.00.5058

After moving Dynamics GP to a newly built server, using a back up and restore strategy, the Dynamics GP SQL replication kept causing problems. The following message drowned the error logs of the server. I ended up rebuilding replication configuration entirely to solve the issue, but there may be some

Microsoft Dynamics GP

Beware DEX_ROW_TS for data synchronisation

DEX_ROW_TS is a SQL server database field. It is found on some tables in Dynamics GP ERP system(since V10). The field contains a UTC timestamp that is “stamped” whenever that row in the database is changed, setting it to current time and date. A blog entry from