syExcelReports Table & SQL Server Native Client 10.0 – Thanks Aaron Berquist


While upgrading our GP from GP2010 SP3 to GP2013 SP1, DYNAMICS database upgrade stopped unexpectedly with GP crashing without any information for me to debug.

But it stopped exactly at table syExcelReports and crashed. Without spending anymore time, I just went and checked my ODBC DSN setup, as explained by Aaron Berquist on his blog High Dynamics Range. Expectedly, the ODBC DSN driver was not pointing to SQL Server Native Client 10.0.

For some reason, GP fails to process upgrade further if your DSN driver is NOT native client 10.0 or above.

Those who upgrade your GP to latest version, please check DSN before you start database upgrade using GP Utilities.

VAIDY

DBCC SHRINKFILE


There is an amazingly simpler yet highly informative post up on TECHNET portal, which proved very handy this morning.

My systems admin reported to me that our SharePoint server ran out of storage for some reason unknown to him, but he could identify that it was SQL Server program files folder which consumed 80% of storage.

I immediately knew what could be the reason. It’s those crazy log files problem. To truncate and clear these overeating log files, I had to use this command: DBCC SHRINKFILE.

SP-Log-FileGrowth

Once I cleared out all unnecessary file space from these log files, I had to setup the File Growth and Maximum File Size (shown in the screenshot beside):

Never for a DB log file, we should setup the file growth as “In Percent”. This would prove disastrous over a period of time. Instead, always set it up to “In Megabytes” and enter a least sensible value; in my case I had set it up to 5MB.

For a DB Log file, 2GB should be reasonable amount of file space to hold the logs. To learn more about Log files, read this post on TECHNET: The Transaction Log (SQL Server).

We must understand one thing, that shrinking the DB log has got it’s own impact.

VAIDY

Implicit Conversions Causing Deadlocks in SQL Server


Quite an interesting but crucial post up there on Sans SQL blog.

The post explains that Implicit conversions in SQL Server could actually trigger a deadlock. And there are definitions for each important term (such as deadlock, implicit conversion, etc.) with an example.

I believe this post would be useful for those who work extensively on SQL Server.

VAIDY

T-SQL Function for Proper Case Format


We do not have a built in T-SQL function to convert any string (or a statement) to Proper Case format, also known as Title Case. This I feel is a very simple feature that SQL Server could have provided us, but missing even in it’s latest version, SQL Server 2012.

I thought I would highlight some links which would be useful for T-SQL programmers in our community:

  1. David Wiseman’s Solution
  2. Jeff Smith’s Solution
  3. Pinal Dave’s Solution

There are many more solutions, but above are noteworthy.

VAIDY

Quick SQL Tip: Keyboard Shortcut for Database Selection in SQL Server 2012 SSMS


Till SQL Server 2008 R2, if you want to change the database on which you would like to execute a SQL query in SQL Server Management Studio (SSMS), the keyboard shortcut to do so was CTRL + U from the query window.

Now from SQL Server 2012, it is CTRL + ALT + J.

So those who tried this using the old shortcut, keep in mind that the new shortcut.

VAIDY

SQL Server 2012 Performance Dashboards – Some Real-Time Snapshots


Following are some real time SQL Server 2012 Performance Dashboards, taken from a real time SQL Server 2012 environment:

HOME PAGE

 

EXPENSIVE QUERIES – CPU

 

EXPENSIVE QUERIES – DURATION

 

and much more…

It’s a great set of reports for every SQL Server DBA to understand his/her SQL Server environment.

VAIDY

Microsoft SQL Server 2012 – Performance Dashboard Reports


In March 2012 (precisely 6th March 2012), Microsoft made available SQL Server 2012 Performance Dashboard Reports which can be used to identify whether there is a current bottleneck on the system, and if so, capture additional diagnostic data that may be required to resolve it.

More information on this can be read from the download page: Microsoft SQL Server 2012 Performance Dashboard Reports.

One point worth to be highlighted is that this doesn’t require Reporting Services to be installed.

It’s a very crucial and important tool for any database administrator.

VAIDY

Formatting SQL Procedures


Have you ever opened a standard GP stored procedure?

I do it at least on a weekly basis and have always found it impossible to read as it is. So I end up aligning the procedure first and then read it to understand the logic.

Not anymore. David has shared information on some portals which does this in seconds and give us an aligned code.

Standing out from his list is Poor SQL, from what I learned from my usage.

And there is a plugin for SSMS which does this from within SSMS. This tool is called SSMS Tools Pack.

Happy aligning.

VAIDY

Introducing Microsoft SQL Server 2012 – Free eBook


That’s a great news for those who would love to know more about Microsoft SQL Server 2012.

Microsoft Press has released a FREE EBOOK detailing SQL Server 2012. And it has got 288 pages full of information and it’s free and we have several options of the file type; EPUB, MOBI & PDF.

Get your copy now and know what it is in Microsoft SQL Server 2012.

VAIDY

Dynamics GP 2010 is compatible with SQL Server 2012


Mariano, in his new post, confirms that Microsoft Dynamics GP 2010 is compatible with SQL Server 2012. The post also has got links to both CustomerSource and PartnerSource.

This is a good news, since SQL Server 2012 will precede Dynamics GP 2013, which means customers would be first upgrading their SQL Server first.

SQL Server 2012 has got tremendous amount of features that are going to be crucial in future.

So let’s gear up for some serious upgrades.

VAIDY