SQL Server - Dynamic Code Blocks

Dynamic Code Blocks

Sign in Subscribe

SQL Server

A collection of 32 posts

SSMS Activity Monitor Pausing and Processor Time greyed out disabled

SSMS Activity Monitor Pausing and Processor Time greyed out disabled

Fix to SSMS Activity Monitor pausing.

Add QR code to Reporting Services(SSRS) report for free without using the Global Assembly Cache (GAC)

Add QR code to Reporting Services(SSRS) report for free without using the Global Assembly Cache (GAC)

Add QR code to Reporting Services(SSRS) report for free without using the Global Assembly Cache (GAC)

SSRS scheduling error creating a report schedule (timeout or low disk)

Fix to timeout or low disk error when scheduling reports

Invalid object name 'WO010032'

Microsoft Dynamics GP

Invalid object name 'WO010032'

Fixing [SQL Server]Invalid object name 'WO010032'. after removing the Dynamics GP Manufacturing Module

Microsoft Dynamics GP

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

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

See similar post: sql filtered index 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

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

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 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

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 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

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

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

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)

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

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

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

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 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 “ChrisM@work.” -- 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 >

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, including the credentials that should be used for that connection. As time passes, the challenge is knowing if that old linked server object is still

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