How to remove a company that is no longer being used in Microsoft Dynamics GP

This article explains how to remove a company that is no longer being used in Microsoft Dynamics GP. This is taken from Microsoft kb article http://support.microsoft.com/kb/849249

To remove a company that is no longer being used in Microsoft Dynamics GP, follow these steps:

  1. Follow the appropriate step:
  • If you are using Microsoft Dynamics GP 10.0 or later, click Tools on the Microsoft Dynamics GP menu, point to Utilities, point to System, and then click Delete Company.
  • If you are using Microsoft Dynamics GP 9.0, point to Utilities on the Tools menu, point to System, and then click Delete Company.
  • If you are using Microsoft Business Solutions – Great Plains 8.0, click Setup on the Tools menu, point to System, and then click Company.
  • If you are using a version that is earlier than Microsoft Business Solutions – Great Plains 8.0, click System on the Setup menu, and then click Company.
  1. Click the Company Name lookup.
  2. Click the company that you want to delete, click Select, and then click Delete.
  3. Verify that the company database was removed. To do this, follow the steps for the database that you are using with Microsoft Dynamics GP.

    Microsoft SQL Server 2005 / Microsoft SQL Server 2008 /Microsoft SQL Server 2008 R2 / Microsoft SQL Server 2012

    1. Start SQL Server Management Studio.
    2. Expand the instance of SQL Server for Microsoft Dynamics GP, and then expand Databases.
    3. Verify that the company database was removed. If the company database is listed, right-click the company database, and then click Delete.

Microsoft SQL Server 2000

  1. Start Enterprise Manager.
    1. Expand the instance of SQL Server for Microsoft Dynamics GP, and then expand Databases.
    2. Verify that the company database was removed. If the company database is listed, right-click the company database, and then click Delete.

Microsoft SQL Server Desktop Engine (MSDE)

  1. Start Support Administrator Console.
    1. Run the following command to verify that the company database still exists.

SELECT name FROM MASTER..SYSDATABASES

  1. If the company database is listed, run the following command to delete the database.

DROP DATABASE XXX

Note Replace XXX with the database name of the company that you want to delete.

  1. If the database has been manually removed or removed by the DROP script mentioned in step 4-c, then there may be some references from the deleted database still within the DYNAMICS/system database. These references need to be removed. To do this, find below the ClearCompanies.sql script attached along with this.

    Microsoft SQL Server 2005 / Microsoft SQL Server 2008 / Microsoft SQL Server 2008 R2 / Microsoft SQL Server 2012

Start SQL Server Management Studio.

  1. Click New Query from the menu bar to start a new query window.
  2. Paste the contents of the ClearCompanies.sql scripts into the empty query window.
  3. Click Execute or press F5 to run the script.

    Note ClearCompanies.sql is a script that is automatically setup to run against the appropriate databases.

Microsoft SQL Server 2000

  1. Start Query Analyzer.
    1. Click Query and then click New Query to open a blank query window.
    2. Paste the contents of ClearCompanies.sql into this empty query window.
    3. Click on the ‘Execute’ button or press F5 to run the script.

      Note ClearCompanies.sql is a script that is automatically setup to run against the appropriate databases.

  2. Log into Microsoft Dynamics GP to verify the company no longer exists.

CLEARCOMPANIES.SQL

/* ClearCompanys.sql – Script that will clear out all entrys in the DYNAMICS

database referencing databases that no longer exist on the SQL Server.

Requirements:

Company database you wish to have cleaned out of the tables in the DYNAMICS

database must be removed from the SQL server before running this script.

Ensure that all your databases have been restored or they will be erased

from the DYNAMICS database.

*/

set nocount on

/* Remove all references in the company master (SY01500) for databases that

Do not exist on the SQL Server */

delete DYNAMICS..SY01500 where INTERID not in

(select name from master..sysdatabases)

/* Clear out all tables in DYNAMICS database that have a CMPANYID field

that no longer matches any Company ID’s in the SY01500 */

USE DYNAMICS

declare @CMPANYID char(150)

declare CMPANYID_Cleanup CURSOR for

select ‘delete ‘ + o.name + ‘ where CMPANYID not in (0,-32767)’

+ ‘ and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)’

from sysobjects o, syscolumns c

where o.id = c.id

and o.type = ‘U’

and c.name = ‘CMPANYID’

and o.name <> ‘SY01500’ order by o.name

OPEN CMPANYID_Cleanup

FETCH NEXT from CMPANYID_Cleanup into @CMPANYID

while (@@FETCH_STATUS <>-1)

begin

exec (@CMPANYID)

FETCH NEXT from CMPANYID_Cleanup into @CMPANYID

end

DEALLOCATE CMPANYID_Cleanup

go

/* Clear out all tables in DYNAMICS database that have a companyID field

that no longer matches any Company ID’s in the SY01500 */

USE DYNAMICS

declare @companyID char(150)

declare companyID_Cleanup CURSOR for

select ‘delete ‘ + o.name + ‘ where companyID not in (0,-32767)’

+ ‘ and companyID not in (select CMPANYID from DYNAMICS..SY01500)’

from sysobjects o, syscolumns c

where o.id = c.id

and o.type = ‘U’

and c.name = ‘companyID’

and o.name <> ‘SY01500’

set nocount on

OPEN companyID_Cleanup

FETCH NEXT from companyID_Cleanup into @companyID

while (@@FETCH_STATUS <>-1)

begin

exec (@companyID)

FETCH NEXT from companyID_Cleanup into @companyID

end

DEALLOCATE companyID_Cleanup

go

/* Clear out all tables in DYNAMICS database that have a db_name field

that no longer matches any company names (INTERID) in the SY01500 */

USE DYNAMICS

declare @db_name char(150)

declare db_name_Cleanup CURSOR for

select ‘delete ‘ + o.name + ‘ where db_name <> ”DYNAMICS” and db_name <> ””

and db_name not in (select INTERID from DYNAMICS..SY01500)’

from sysobjects o, syscolumns c

where o.id = c.id

and o.type = ‘U’

and c.name = ‘db_name’

set nocount on

OPEN db_name_Cleanup

FETCH NEXT from db_name_Cleanup into @db_name

while (@@FETCH_STATUS <>-1)

begin

exec (@db_name)

FETCH NEXT from db_name_Cleanup into @db_name

end

DEALLOCATE db_name_Cleanup

GO

set nocount on

/* Clear out all tables in DYNAMICS database that have a dbname field

that no longer matches any company names (INTERID) in the SY01500 */

USE DYNAMICS

declare @dbname char(150)

declare dbname_Cleanup CURSOR for

select ‘delete ‘ + o.name + ‘ where DBNAME <> ”DYNAMICS” and DBNAME <> ””

and DBNAME not in (select INTERID from DYNAMICS..SY01500)’

from sysobjects o, syscolumns c

where o.id = c.id

and o.type = ‘U’

and c.name = ‘DBNAME’

set nocount on

OPEN dbname_Cleanup

FETCH NEXT from dbname_Cleanup into @dbname

while (@@FETCH_STATUS <>-1)

begin

exec (@dbname)

FETCH NEXT from dbname_Cleanup into @dbname

end

DEALLOCATE dbname_Cleanup

GO

set nocount on

/* Remove all stranded references from the other Business Alerts table that

no longer exist in the SY40500 */

delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

GO

CRM 2013 New Feature-Using Entity Images

With the release of Microsoft Dynamics CRM 2013, a new feature was added, which allowed users to set record specific images. Certain system entities and all custom entities can be enabled to display images in the application.

The following system entities have image attributes.

Account* Incident Contact* Lead*
Publisher* Sales Order Product* Opportunity Product
Campaign Queue Competitor* Goal
Contract Organization Territory Invoice
Connection Mailbox KbArticle Transaction Currency
System User* Resource*  Territory EmailServerProfile

Those marked with asterix (*) are enabled by default to show them in application.

Steps to Enable Entity Image on Form

  1. Open Form Editor
  2. Go to Display Tab
  3. In the Image Section, enable the “Show image in Form” option.

crm2

Now create a record and add Entity Image.

crm1

After uploading image, record will look like this:

crm

Use Entity Image in Custom Entity

To Add Entity Image in Custom Entity, you first need to create a new image attribute.

crm3

The schema name will automatically change when you select Type as Image.

To enable image on form, follow the steps described above.

Programmatically Setting Entity Image

Updating Entity image using code is very much similar to adding any other attribute. Specify the ID of the record you want to update and then set the “entityimage” field to actual bytes of the file.

crm4

Creating SSRS report using ‘Query’ and ‘Auto Design’

In this example we will create a SSRS report that shows all the records in ‘VendTable’

1. Create a new query.

     a) Create a new query “SSRS_VendTable” in the AOT and add “VendTable” as the data source and rename it to “VendTable_DS”.

s1

     b) Now set the “Dynamic” property of ‘Fields’ to “Yes” (in order to include all the fields from the table).

s2

You can manually select the fields from the table “VendTable” and drag and drop them to the data source “VendTable_DS”.

     c) Set the range of the query.

The main purpose of setting a range is to filter the data that is to be displayed on the report. Let set field “AccountNum” as range

s3

2. Create a new report project: The next step is to create a SSRS report that will use this query to display the records from “VendTable”.

     a) Launch Visual Studio, click on “File” menu and select “New >> Project”. It will open the following dialog box:

s4

Here select the option “Report Model” under “Microsoft Dynamics AX” and specify the name of the project “SSRS_VendTable” and press “OK”. This will create a new SSRS project.

     b) Add “Report” to the newly created project by referring the screenshot below.

s5

     c) Add the dataset to the newly added report.

s6

Rename the dataset to “VendTable”.

     d) Right click on the dataset “VendTable” and select properties.  When you select the property “Query” a dialog box will pop up.

This dialog box will list all the queries in AOT. Select the query “SSRS_VendTable” and click on “Next”.

s7

The next dialog box will list all the fields in the query “SSRS_VendTable”. Click on “All the fields” and select “OK”. You can manually select the fields that you want to include in this report.

s8

     e) Add “Design” to the report.

In SSRS reports there are two kinds of designs “Auto Design” and “Precision Design”. We generally use “Precision Design” when the report is complex in nature.

So continuing with our example, simply select the dataset “VendTable” and drag and drop it on the “Design” node, it will create a new “Auto Design”.

 s9

Change the name of the design to “Design”.  Here we need to set an important property Layout Template set it to “ReportLayoutStyleTemplate”.

3. Our report is now complete. In order to view the report right click on report and select “Preview”

s10

 You will get the following screen:

s11

In the step “1.c” we specified “AccountNum” as the query range. So click on “Select” and specify the range of the query. Now click on “Report” you will be able to view the report:

s12

Written By: Shikha Satija (Dynamics AX Team, Cipher Dynamics) 

Send document in E-mail (GP)

By using this functionality, you can send documents in e-mail. You can send single document or send multiple documents using lists.

You can send documents in e-mail if the following conditions are met:

 Setup Customer Emails

 –          Open Customer Maintenance window (Cards >> Sales >> Customers)

–          Select one customer, click on Address button and select the Address ID.

–          Click Internet Addresses button.

–          Enter e-mail address for this customer/address combination and click Save. 

1

 Setup Customer E-mail Options

–          Open Customer Maintenance window (Cards >> Sales >> Customers)

–          Select one customer, click E-mail button.

You can either send documents as attachments or embed documents in the message body. If you send documents as attachments, you can select to send multiple attachments to the customer and set the maximum file size of the document. Mark the documents you want to send in e-mail. You can select a default message ID for each document and select the format that you want to send the document in.

Word templates for Microsoft Dynamics GP must be enabled in the Template Configuration Manager window (Reports >> Template Configuration) before you can send documents as DOCX, PDF, or XPS attachments.

2

You also can specify message IDs and the document format to use for the customer. A message ID is a predefined message that you can assign to a document that you want to send in e-mail.

Message Setup window

You can customize your messages by adding fields that are associated with a document type. A message can be personalized to address each customer or vendor by name. You could add the due date for a sales invoice in a message or the PO number for a purchase order.

3

 You can send the following documents: 

Purchase Order Receivables Invoice
Vendor Remittance Receivables Return
Sales Quote Receivables Debit Memo
Sales Order Receivables Credit Memo
Sales Fulfillment Order Receivables Finance Charges
Sales Invoice Receivables Service/Repairs
Sales Return Receivables Warranty

 Table Definitions

SY04904 (Email Card Setup) – This table is populated with values from the “Select Document Options” section from the Customer Email options (or) Vendor Email options window.

SY04905 (Email Card Documents) – This table is populated with values from the “Send Forms as E-mail” section from the Customer Email options (or) Vendor Email options window.

SY04910 (Email Details) – This table contains the details of the emails captured at the various transaction levels in the Email Detail Entry window.

Written By: Shyam Nath (Dynamics GP Team, Cipher Dynamics)

Install Data Migration (Import\Export) Framework for Microsoft Dynamics AX

The Microsoft Dynamics AX 2012 Data Import/Export Framework is an extension that helps you export data and import it into Microsoft Dynamics AX. Examples of the data that you can import include master data, open stock, and balances.

Architecture:

1

Install DMF step by step:

 1.  Download the installation package from Microsoft Information source\Partner source for the Data Import/Export Framework, DataImportExportFramework.zip, and extract it to a local folder & execute the Set up, right-click Setup.exe, and click Run as administrator.

2

 2. The installation process is split into 3 main components :

a)       A component must be installed on a server running MS SQL Integration Services.

b)       A component must be installed on a server running AOT.

c)       A component must be installed on a workstation having the Dynamics AX client

3

 3. The wizard, the installer will check if you have the minimal requirement.

4

  4. Specify a user account to run the Data Import/Export Framework service. Enter the user account of AOS service.

5

 5. Click Next & the following components will be installed

6

 6. After all of the components have been installed. Now Import the Model file from a command line using AXUTIL. The modal file is located inside the folder of the client component.

Note that there is a matching folder in “C:\Program Files\ Microsoft Dynamics AX 2012 Data Import Export Framework Client Component” for each supported version of Dynamics AX.

  • AX 2012 -> Folder 2012.
  • AX 2012 Feature Pack -> Folder 2012 FP.
  • AX 2012 R2 -> Folder 2012 R2.

7

 For Dynamics AX 2012 R2 execute in command prompt: axutil import /file:”C:\Program Files\Microsoft Dynamics AX 2012 Data Import Export   Framework Client Component\2012 R2\Model\DataImportExportFramework.axmodel”

8

9

 7. After the model files have been imported, Restart the AOS Service

10

 8. Start the Dynamics AX Client, a dialog box pops-up “Model store has been modified”, click Compile and synchronize and press OK.

11

 9. When the synchronization is completed, click Compile into .Net Framework CIL.

 10. If the dialog box does not open by itself, follow these steps:

    1. a) Compile the application from System administration > Periodic > Compile.

              b) Click System administration > Periodic > Database > SQL administration. On the Table actions menu, click Synchronize database.

 11. Compile into .NET CIL from System administration > Periodic > Compile.

 12. After the model has been compiled into .NET CIL, the Data Import/Export Framework button is added to the navigation pane.

12

13

Written By: Neha Gupta (Dynamics AX Team, Cipher Dynamics)

Creating Setup for Web Application using Install Shield LE

While working on the latest project there was a requirement to build setup for web application. This was quite new and interesting for me .So I am sharing my experience through this article. This article demonstrate steps for creating a setup of a web application using Install Shield LE

Step 1: Add new Project (Setup and Deployment) in existing solution.

                Assign Name and Location and click Ok

 setup_img1

Note: If installShield is not installed in VS then click ok, it will redirect you to the website from where you can install it.

Step 2:  Click on Application Information.

setup_img2

Specify Company and application details

 setup_img3

 

Step3: Click on Installation Requirement.

Select Operating system and Application if any required for your Application.

 setup_img4

Step4: Click on Application Files

In InstallShield LE the Installation Architecture feature is not available. 

 setup_img5

Add Project output i.e. Primary output and content files.

Step5: Click On Application registry

Click Yes

 setup_img7

Step6: Click on Installation Interview

Choose option as per your application requirement

 ii

Step 7: Create Website in IIS

Here we need to create a website with a configuration and while the setup runs it will create the website dynamically in IIS.

To create a new website:

  1. Click on the “Configure the Target System in Install Shield”.
  2. Right-click on “Websites” in Internet Information Services and assign a name, as in my case SampleSite.

 setup_img8

  1. In the configuration section there is the option “Home Directory” and in that there is an option Content Source Path “Select “[IISROOTFOLDER]”(Create new folder in IISROOTFOLDER it will create in inetpub\wwwroot folder i.e. Samplesite in my case).

 setup_img9

Step 8: Add Source of website to the setup
now we need to add the source file of the setup. Go to the “Project Assistant” tab then select Application Files”.

 setup_img10

Here we will see that the newly created folder is named SampleSite inside “[IISROOTFOLDER]”.

  1. Click on the “SampleSite” folder and then click on “Add Project Outputs” and select the files as per your requirements.

Important Configuration

  • To see the log file of the installer we set the option to create the MSI logs to “Yes” in:
    1. Organization Your Setup In Install Shield Project
      • General Information >> Create MSI Logs =Yes

 setup_img12

 

  • In IIS click on APPLICATION POOLS =>DEFAULTAPPPOOL=> => Right click select ADVANCE SETTING=>ENABLE 32-BIT APPLICATION == TRUE

To Create Setup File

To create setup click build from menu and click “Build Solution”.

And you will get the setup file for the application.

Now If you see the setup file folder it will have many more additional Files but if you want to make only Single Setup File then follow the Below steps

Step 1 : Select the Project and click on Build option from VS2012 menu and then select Option Configuration Manager now select Single Image.

 cfm

Now build the solution.

Written By: Namita Sethi (Dynamics CRM Team, Cipher Dynamics)

What’s New in Microsoft Dynamics CRM 2013

Microsoft Dynamics CRM 2013 is a sensation!! Previously condemned ‘ORION’ the upgrade is titled Microsoft Dynamics CRM 2013. There are many new enhancements that will improve user experience and clarify business processes. CRM 2013 also improves usability across multiple browsers. Here are some new features.

1.      Command Bar

The left navigation used in previous version is replaced by the user-friendly Command Bar. Each command option features a down arrow indicating there is a menu below it.

11

The Command Bar drops down from top when you run your mouse over it.

2.      New User Experience

With an intuitive, less cluttered interface, you will notice the absence of pop-up windows. To navigate away from a page, hence, you do not “Close” the record, but select from the top navigation where you want to go.

12

3. Process Agility

Dynamics CRM 2013 gives user visual guidance to navigate processes that makes workflow more prescriptive and easier to follow. In a significant departure from CRM 2011, a new process bar prompts user to follow next action steps in line with your mapped workflow to progress leads, opportunities, services cases and other tasks.

13

4. Store Images In CRM 2013

Images can now be associated with all custom entities and some of the OOB, but with a catch-you still have only one image associated per record. This still serves the purpose in most cases where you want to store the contact or product image along with the record.

14

5. Quick Create Form

The Quick Create form makes it easier to create an entity record just by filling in key information. By default you will get quick forms in some of the entities like account, contact, lead, opportunity etc.

15

6. Portable Business Logic

Completely new to CRM 2013, Portable Business Logic is a wizard that allows you to create logical functions on the form including hiding and showing fields, enabling or disabling fields, setting requirement level. This new feature basically replaces some of the JavaScript needed in previous versions.

16

7. Quick View Forms

With Quick View Forms, you can create a form on one entity and use it in another entity. Most of the fields will be read only. In the image below, a Quick View form has been added to Opportunity showing contact information for primary Contact. Therefore, user can quickly call or email the contact without leaving opportunity.

17

8. Simplified Navigation

Rather than using a ribbon displaying every option to user, the navigation is streamlined to a few commands. The user can select the eclipses to find relevant commands for the records in context.

18

9. Different Categories Of Processes

In CRM 2013, there are four types of processes that you can utilize.

19

10. Auto-Save

Microsoft Dynamics CRM 2013 has the ability to auto save a record as you edit them. CRM saves a record every 30 seconds, and when you leave the record, it is auto-saved. This feature can be disabled from Settings.

20

21

 Written By: Akanksha Wadhwa (Dynamics CRM Team, Cipher Dynamics)

Microsoft MVP Award 2013

HIMBAP

mvplogoI am pleased to announce that fourth year in a row I have been awarded  Microsoft MVP for Dynamics CRM.

Thanks to Microsoft, Dynamics CRM community members and my MVP program manager Biplab Paul and MVP program lead Tanmay Kapoor.

Special thanks to my family member and my community friends for their continuous support. A big thanks to my blog reader, I will keep contributing to the community and will share my knowledge as much as possible.

Thank you all once again !!!

 

View original post

Step By Step Integrating Microsoft Dynamics CRM 2011 with Yammer

With Microsoft Dynamics CRM December 2012 Service Update Microsoft introduced OOB integration between Microsoft Dynamics CRM 2011 and Yammer integration. Yammer is an enterprise social network which is designed for private communication for members within a given organization. It is a social network for businesses and companies Like Facebook and Twitter is for Public use. You can set up basic account for free it is easy to use and perhaps most importantly it provides a private, manageable and secure online space for business to communicate. If your board room created a social networking site, this is what it would look like. Company domain users can access the company Yammer site. Yammer is designed for private communication for members of an organization/company. In this post we are going to use Microsoft Dynamics CRM online deployment, before you set up a connection to Yammer in Microsoft Dynamics CRM Online, you must create a paid Yammer account for your organization, such as an Enterprise Network or SharePoint Online + Yammer account. You can’t use the free edition for the integration.

Basically it’s an alternative for the activity feeds and you will have to choose as well.

Note: You can’t use Activity Feeds together with Yammer, but you will have the configuration and UI of the Activity Feeds. But in the back it connects with Yammer and saves all the post there.

Connecting to Yammer is a one-way process. After the connection is established, it cannot be removed.

You can follow below steps to enable Yammer integration:-

Step 1: Navigate to Settings > Administration > Yammer Configuration

2013-06-19_151023

 Step 2: Agree to its terms & conditions

2013-06-19_151138

Step 3: Once the disclaimer is accepted you’ll be asked to authorize your Yammer account. Click on ‘Authorize Microsoft Dynamics CRM Online to connect to Yammer

2013-06-19_151429

Step 4: If you’re already logged into Yammer you’ll see your enterprise Yammer network along with the groups. Otherwise you’ll be asked to login to Yammer.

2013-06-24_161043

Step 5: Select your Yammer group and the security level. We’ve selected a specific group to ensure all the system posts & user posts go into a single group rather than cluttering up the ‘all company’ activity stream.

1

Now that the connection is setup you can configure rules. These rules will allow you to instruct the system to post to Yammer upon specific system events.

Step 6: Click on ‘Edit message rules’ or go into Settings > Administration > Post Rule Configurations. Locate the rule you’d like to enable and click on ‘Enable for Yammer

In the example below, we’ve enabled the system to post to Yammer when a new lead is created. To test, create a new lead, wait a few seconds, login to Yammer, you’ll see the activity in the Yammer activity feed ticker on the top right hand corner. 

2013-06-24_161227

 Written By: Namita Sethi (Dynamics CRM Team, Cipher Dynamics)