SQL Database in Recovery Pending Mode

So you have done a restore of your virtual machine.
Sign in to SQL and boom! Your database is in recovery pending mode!
What the hell is that?

To fix:
- Go to the folder where the ldf and mdf is stored, grant access to 'everyone'
-Sign back in to SQL, restart the service
-Run the following script and you are good to go!
ALTER DATABASE dbnamehere SET ONLINE

And voila! Problem resolved!

Dynamics SL ROI

Ever wondered what ROI stands for in Dynamics SL?

Well have no fear, you are in the right place sir/ma'am!

ROI stands for - Report Operations Interpreter

What is it - it is the screen you see immediately before running any SL Crystal Report. The purpose of the screen is to filter or group the report the way you want it to show at run time.

The remote server returned an error: (407) Proxy Authentication Required.

So you are installing Management Reporter Application and Process Service in the environment. Clean install. No reports or anything else exists. Then.... you get the above error! It looks like everything was installed though. :s

What next?
- Remove the Application and Process Service.
-Delete the Management Reporter database in SQL that was just made by the process.
-Go into Internet Explorer and turn off the Proxy Service.
-Re-run the install for the Application and Process Service.
-Voila!

Login failed for user smartview

This happens after restoring a backup.

The passwords are basically screwed.

To resolve, Go to tools>>Smartview>>Smartview setup.
Change the password.

Go to SQL> Security>Logins.
Change the password for smartview user here as well to match the new password in GP.

Voila!

Company Restored but data missing

You restored a live database to the test company and found that your data is missing in the existing live company.

Reason - the database name in the company maintenance window of SL is incorrect.
Have all users exit the system and manually update the Company table in the system database to reflect the correct application database name.

Dynamics SL Backup and Restore Company

1. Create additional empty database in Database Maintenance

2. Create company in SL. Go to System Manager>> Maintenance>> Company Maintenance.
Create new company ID, enter database name and other details.

3. Restore copy of live application database to test application database

4. In the domain table in the Microsoft Dynamics SL system database, update the application database name. Execute against the system database

UPDATE COMPANY SET DatabaseName = 'XXXXXX' where DatabaseName = 'YYYYYY'
UPDATE DOMAIN SET DatabaseName = 'XXXXXX' where DatabaseName = 'YYYYYY'

-Note Replace XXXXXX with the new name of the application database, and replace YYYYYY with the old database name.

5. Update database views in Database Maintenance.
-Database Maintenance.
-Connect to SQL Server instance
-In the System Database Name list, click the restored Microsoft Dynamics SL system database.
-Under Databases, click the restored Microsoft Dynamics SL application database.
-Click Update Views.
-At the bottom of the Database Maintenance (98.290.00) window, confirm that the progress bar has finished.
-Click Close.
-Log on to Microsoft Dynamics SL by using the appropriate SQL Server name and the appropriate Microsoft Dynamics SL system database name.

6. If errors are encountered. Run the triggers script in sql.




Triggers script:

-- Drop all windows authentication triggers.
-- If using windows authentication, it will also recreate the triggers
--  and cleans up any stray vs_acctsub or vs_acctxref records
--
-- 1 - Make a good database backup
-- 2 - Run as is against your SL System database
--
-- last updated: 5/13/2008

-- Step 1: Drop all ACCTSUB and ACCTXREF triggers

declare @triggername as char(100)
declare @execString as char(200)
DECLARE trigger_cursor CURSOR FOR
   select name from sysobjects where type='TR' and (
name like 'sDeleteAcctSub_%' or
name like 'sInsertAcctSub_%' or
name like 'sUpdateAcctSub_%' or
name like 'sDeleteAcctXref_%' or
name like 'sInsertAcctXref_%' or
name like 'sUpdateAcctXref_%')
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @triggername
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString = 'drop trigger ' + @triggername
print @execString
exec (@execString)
print 'Done'
FETCH NEXT FROM trigger_cursor INTO @triggername
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor


-- only do step 2 and 3 if windows auth
if (select top 1 text from syscomments where ID in (select ID from sysobjects where name='getauthenticationtype' and type='P'))
like '%Windows%'
begin

-- Step 2: Recreate a new set of 6 triggers for each app database listed in the company table

declare @dbname as char(100)
declare @execString2 as char(1000)
DECLARE db_cursor CURSOR FOR
   select distinct databasename from company  where databasename<>''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
         set @execString2 = 'CREATE TRIGGER sDeleteAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_AcctSub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
print @execString2
exec (@execString2)
print 'Done'

         set @execString2 = 'CREATE TRIGGER sInsertAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_AcctSub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'

print @execString2
exec (@execString2)
print 'Done'

         set @execString2 = 'CREATE TRIGGER sUpdateAcctSub_' + rtrim(@dbname)
+' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctsub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'
+' Insert into '+rtrim(@dbname)+'..vs_acctsub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'
+'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'

print @execString2
exec (@execString2)
print 'Done'

         set @execString2 = 'CREATE TRIGGER sDeleteAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER DELETE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
print @execString2
exec (@execString2)
print 'Done'

         set @execString2 = 'CREATE TRIGGER sInsertAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER INSERT '
+' AS Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'
print @execString2
exec (@execString2)
print 'Done'

         set @execString2 = 'CREATE TRIGGER sUpdateAcctXref_' + rtrim(@dbname)
+' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)
+' AFTER UPDATE '
+' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'
+' Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'

print @execString2
exec (@execString2)
print 'Done'

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

-- Step 3: Cleanup any stray vs_acctxref or vs_acctsub records

declare @dbName3 as char(85)
declare @execString3 as char(200)

DECLARE db_cursor3 CURSOR FOR
  select distinct databasename from company where databasename<>''
OPEN db_cursor3
FETCH NEXT FROM db_cursor3 INTO @dbName3
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctxref a left join acctxref b on a.acct=b.acct and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctsub a left join acctsub b on a.acct=b.acct and a.sub=b.sub and a.cpnyid=b.cpnyid where b.acct is null'
print @execString3
exec (@execString3)
FETCH NEXT FROM db_cursor3 INTO @dbName3
END
CLOSE db_cursor3
DEALLOCATE db_cursor3

END
-- END

Re-open Fixed Asset Year to un-do disposal (unofficial steps)

1.  Run a Select * from FA40200 and note the CURRFISCALYR of the books.
2.  From SQL studio, update the FA40200 table and set the CURRFISCALYR to the year that the asset was retired in.
3.  Go back into GP and undo the retirement.  Interface the transactions to the GL and delete or post them as necessary. 
4.  Go back to SQL studio and update the FA40200 to set the CURRFISCALYR to what is was before you updated it the first time.
Voila.

Unable to connect to GP

When testing ODBC connectivity the following error is obtained.

[Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Resolution:
Start the SQL Browser service
Turn off firewall.

Unable to add user to SQL when installing eConnect

Use solution 1 if you are installing econnect for the first time.

This issue happens if the user you are entering on the installation does not have DYNGRP role assigned to them for every GP database.

Go to SQL>>Security>>Logins
Add the user, give them sysadmin role
Give them access to the GP databases and also assign the DYNGRP role to each GP database.


Use Solution 2 if you are installing econnect on another machine or reinstalling.

During the eConnect Install
Mark/Tick the box 'Do not add service user to SQL' in the SQL Connection Information window.
This will prevent SQL from trying to re-add the user which gives the error that it cannot add the user.

Virtual machine could not start because the hypervisor is not running.

Ok so if you're not techy you can do the following:

1. Run cmd as admin and then paste this in
2. bcdedit /set hypervisorlaunchtype Auto

          then click enter.
     3. Then restart computer and your vm will work again.

This apparently has to be done every time your computer shuts down.


The long term fix is:
Cause

The Data Execution Prevention (DEP) setting is not enabled in BIOS. For HP server, it is called "No Execute Memory Protection"

Resolution

To fix the issue, enable the DEP setting in BIOS

SSRS add in for SL

Originally found on page - 

http://ajitkannan-erp.blogspot.com/2013/12/add-ssrs-report-in-dynamics-sl-2011menu.html

Add SSRS Report in Dynamics SL 2011Menu

This post is an effort to the various operating steps which helps to adding the SSRS Report under SL menu

   Step 1: Create New SSRS Report or use existing Report (.rdl) 

   Step 2: Include the report in SL root folder under Usr_Rpts

   Step 3: Open SL 11 -> Administration->Maintenance -> Report Control Maintenance  (98.300.00)

       Enter the Report information Report number, Report format name, Report format into the Report 

       Control  maintenance .

   Step 4:  Open SL 11 -> Administration->Maintenance -> Screen Maintenance (98.330.00)

       Enter the Report number, Report name, Module and Type 

   Step 5: Open SL 11 -> Administration->Maintenance -> Menu Maintenance (98.350.00)

1. Select the relevant Group in “Menu for Group”.
2. Select the Screens tab in the Menu Maintenance (98.350.00). 
3. Select ALL- Show All Screen from the Drop-down “Show Screen Access for Group”.  This will show up all the SL screens in the Screens tab.
4. Select the exact Report  from the Screens tab. Drag and drop the Report into the “Navigation pane work area” under  Inventory -> Report .
5. After add the report to menu exactly as a normal crystal report,but rename  the program ROISRS.EXE replacing from ROI. EXE.Report as shown in the below figure. 
6. Save and close the screen.

  Step 6:  Close the Solomon and launch it.

SSRS Trial Balance not sorting account numbers

So you try to print a GL Trial Balance but when you select the from and to accounts they are not sorted correctly.

Easy fix - change the sort order in stored procedure.

Edit the stored procedure that populates the drop down list (seeglSRSAccountListing in the company database) to end with this:
select * from #accountsegment order by segmentlisting

Originally it does not sort.

Also, you can technically change the drop down lists to text boxes and type in account segments instead of using that pre-populated drop down list if you want.