Category Archives: Administration

SQL Server 2012 Performance Dashboards – Some Real-Time Snapshots

Posted on by

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

Advertisement

Replicate Multi-Currency & Exchange Table Access Information From Existing Company

Posted on by

I had to create a new company with Functional Currency being same like existing production. Quite obviously, all Exchange Table IDs are going to be exactly same like in existing production company.

There were around 25 currencies that we have setup. Under normal circumstances, I have to open Multi-Currency Access (Tools -> Setup -> System -> Multi-Currency Access) and select each currency & respective exchange table ID and activate it. 25 times 3 mouse-clicks.

I then decided to tread SQL way. Following is the script which did that 75 clicks in less than a second:

/* MC60100 – Multi-Currency Access Table */
INSERT INTO DYNAMICS..MC60100 (CMPANYID, CURNCYID, INACTIVE) 
SELECT [new company id], CURNCYID, 0
FROM DYNAMICS..MC60100 
WHERE CMPANYID = [existing company id]


/* MC60200 – Exchange Table ID Access Table */

INSERT INTO DYNAMICS..MC60200 (CMPANYID, EXGTBLID, INACTIVE) 
SELECT [new company id], EXGTBLID, 0
FROM DYNAMICS..MC60200 
WHERE CMPANYID = [existing company id]
To know the company ID for each of your companies, you can query the table SY01500. Below is that query:
SELECT CMPANYID, INTERID, CMPNYNAM
FROM DYNAMICS..SY01500 
ORDER BY CMPANYID
Happy querying.
VAIDY

SQL Server Services – Do Not Set To "Automatic (Delayed Start)"

Posted on by

I am not sure anyone else would have faced this issue. But I did couple of days back that almost killed my system.

I formatted my system and built it from scratch, that obviously also included SQL Server. I always set SQL Server services (SQL Server Database Service, SQL Server Agent, SQL Server Reporting Service, etc.) to Automatic. This means, when OS gets booted, all these services will also get started before we see Desktop.

I read a KB article on MSFT site, which explained each of Application Startup options; Automatic, Automatic (Delayed Start), Manual, Disabled.

Difference between Automatic and Automatic (Delayed Start) is that Automatic option lets the concerned service to get started immediately after OS boots. Automatic (Delayed Start) option lets all services of OS to get started first and then after a brief delay starts the concerned service.

After reading above, I decided to set SQL Server Services startup option to Automatic (Delayed Start). And that proved disastrous.

Restarting the system never progressed beyond STARTING WINDOWS. And when I switched off system forcibly and rebooted it, system asked me to either BOOT SYSTEM NORMALLY or RUN SYSTEM STARTUP FAILURE DIAGNOSTIC.


Selecting either of above yielded nothing. I had no other option than to reinstall OS.

Moral of this bitter story: Never set Automatic (Delayed Start) as Startup option to all SQL Server Services. If you don’t want these services to be started immediately when OS boots, then set it up to Manual.

VAIDY