Monday, May 27, 2013

Dynamics GP - Backup / Migrate all RDL files from SSRS or Sharepoint Integrated Mode

For standard SSRS Reports

https://code.google.com/p/reportsync/

Use Powershell_ise script debugger to run the scripts
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227


  • Ensure your windows account has db_datareader access to the ReportServer Database (Or run as administrator)
  • Download the SSRS_ExportReports.ps1
  • Right click on it, edit
  • It will open in the Powershell ISE editor
  • Enter all of the parameter info
    • Enter Server name
    • Enter output folder path
  • Save it
  • Run it (F5)

For Sharepoint Integrated Mode Reports
Click on Library>Open with Explorer (Under open with excel)
You will probably get this error "Your client does not support opening this list with Windows Explorer"
Try to resolve it here.
I was unable to resolve this error.
http://support.microsoft.com/kb/2629108



Original Code from
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227
SSRS_ExportReports.ps1
-----------------------------------------------

<# .SYNOPSIS 
      Export of all SSRS reports datasources and images 
   .DESCRIPTION 
      This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database 
      to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -. 
      Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension. 
      Please change the "Configuration data" below to your enviroment. 
      Works with SQL Server 2005 and higher versions in all editions. 
      Requires SELECT permission on the ReportServer database. 
   .NOTES 
      Author  : Olaf Helper 
      Requires: PowerShell Version 1.0, Ado.Net assembly 
   .LINK 
      GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx 
#> 
 
 
# Configuration data 
[string] $server   = ".\SQLEXPRESS";        # SQL Server Instance. 
[string] $database = "ReportServer";        # ReportServer Database. 
[string] $folder   = "D:\Export\";          # Path to export the reports to. 
 
# Select-Statement for file name & blob data with filter. 
$sql = "SELECT CT.[Path] 
              ,CT.[Type] 
              ,CONVERT(varbinary(max), CT.[Content]) AS BinaryContent 
        FROM dbo.[Catalog] AS CT 
        WHERE CT.[TypeIN (2, 3, 5)"; 
 
# Open ADO.NET Connection with Windows authentification. 
$con = New-Object Data.SqlClient.SqlConnection; 
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;"$con.Open(); 
 
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ..."); 
 
# New command and reader. 
$cmd = New-Object Data.SqlClient.SqlCommand $sql$con$rd = $cmd.ExecuteReader(); 
 
$invalids = [System.IO.Path]::GetInvalidFileNameChars(); 
# Looping through all selected datasets. 
While ($rd.Read()) 
{ 
    Try 
    { 
        # Get the name and make it valid. 
        $name = $rd.GetString(0); 
        foreach ($invalid in $invalids) 
           {    $name = $name.Replace($invalid"-");    } 
 
        If ($rd.GetInt32(1) -eq 2) 
            {    $name = $name + ".rdl";    } 
        ElseIf ($rd.GetInt32(1) -eq 5) 
            {    $name = $name + ".rds";    } 
 
        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -$name); 
 
        $name = [System.IO.Path]::Combine($folder$name); 
 
        # New BinaryWriter; existing file will be overwritten. 
        $fs = New-Object System.IO.FileStream ($name), Create, Write; 
        $bw = New-Object System.IO.BinaryWriter($fs); 
 
        # Read of complete Blob with GetSqlBinary 
        $bt = $rd.GetSqlBinary(2).Value; 
        $bw.Write($bt, 0, $bt.Length); 
        $bw.Flush(); 
        $bw.Close(); 
        $fs.Close(); 
    } 
    Catch 
    { 
        Write-Output ($_.Exception.Message) 
    } 
    Finally 
    { 
        $fs.Dispose(); 
    } 
} 
 
# Closing & Disposing all objects 
$rd.Close(); 
$cmd.Dispose(); 
$con.Close(); 
$con.Dispose(); 
 
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Finished");

Sunday, May 26, 2013

Dynamics GP - Custom Activity Tracking Reports - Auto-Enable Activity Tracking

Activity Tracking Log Table is

select * from dynamics.dbo.sy05000

Scheduled SQL Job to enable activity tracking for all users


update Dynamics.dbo.SY60100
set TRKUSER = 1 
where TRKUSER = 0
print 'Changed : ' + str ( @@ROWCOUNT )


Original Post here

Friday, May 24, 2013

Dynamics GP - SQL - Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim

To identify deadlocks, run this script.
However, you may have to run this before you get the error message, as the deadlock will have already been removed by the time you see the error message.
Attempt to replicate the lock, and run the script to see what's being locked.


--------------------------------------------------------------------------------------------------
select
 db_name(sp.dbid) as database_name, d.text as sql_text, spid, blocked, cmd, sp.waittime, hostname, program_name,loginame, *
 from master.sys.sysprocesses sp OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
 where sp.blocked <> 0
 union all
 select db_name(sp.dbid) as database_name, d.text as sql_text, spid, blocked, cmd, sp.waittime, hostname, program_name,sp.loginame, *
 from master.sys.sysprocesses sp OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
 where blocked = 0 and spid in (select blocked from master.sys.sysprocesses where blocked <> 0)
 order by sp.waittime desc
-------------------------------------------------------------------------------------------------------------------
Original post

Monday, May 13, 2013

Dynamics GP - Incorrect Functional Currency Index - Incorrect Currency Indexes

Whenever you combine companies that have had separate installs and separate dynamics databases, chances are, the currency indexes across the companies are different. Even if the currency name is the same, GP uses the indexes for everything.
You will realize this problem if you are trying to install Management Reporter and you get the error

  • You must set up a functional currency for the following companies before the integration can continue

Check indexes in the following tables to ensure the currency indexes in the company match the currency indexes in the dynamics db.

  • MC40000 in company
  • MC40200 in Dynamics

Update the MC40000 index to match the MC40200 as long as the currency id's are the same

Dynamics GP eOne Extender Standard - Microsoft Dynamics GP Extender will be disabled until it is initialized by the system administrator


  1. Install the latest version of extender standard, this issue has been resolved 
  2. You may need to rebuild your views manually within extender