ExecuteReader: CommandText property has not been initialized in Dynamics AX 365/ AX 7.0

I got the below error while processing the XML files by using the data entities.

Reason: Tag is missing in the XML file. In my case <Document> tag is missing.  I was defined the data entity with this tag and it was missing in the XML.

<headerstart>
<Name></Name>
</headerend>

Solution: Add the missing <Document> tag in the XML and reprocess the file.

<Document>
     <headerstart>
         <Name></Name>
      </headerend>
</Document>

your client ip does not have access to the server. sign in to an azure error

I got the below error while access the Dynamics 365 PROD DB from my DEV box.

your client ip does not have access to the server. sign in to an azure error

Reason: Dev box IP was not whitelisted in the PROD instance.

Solution:

Below is the query to add the IP addresses to the firewall table(sys.database_firewall_rules) to access the database from outside.

Run the below query to see existing IP's
select * from sys.database_firewall_rules;


I executed the below queries to add the IP addresses to the table.

-- Create database-level firewall setting for only for one IP 0.0.0.4 EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.4'; -- Update database-level firewall setting to create a range of allowed IP addresses EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.6';

Use what is my ip in google for knowing your public IP.
Note: My AX is on cloud and we created a custom screen in AX to run the queries.

Below are the links helped me.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database?view=azuresqldb-current
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

SQL query to extract primary address from CUSTTABLE in AX2012/Dynamics 365 operations

Hi,

Below is the the query we can for the customers primary postal address.


--For all addresses
 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS,Address.COUNTRYREGIONID 
 from CUSTTABLE 
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY 
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.RECID 
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID 
 Order by CUSTTABLE.ACCOUNTNUM 


--For primary address

 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS,Address.COUNTRYREGIONID  

 from CUSTTABLE  

 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY  

 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION  

 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID  

 Order by CUSTTABLE.ACCOUNTNUM 

Azure SQL Database: Use SQL Server Management Studio to connect and query data

• 
  

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows. This quickstart demonstrates how to use SSMS to connect to an Azure SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Prerequisites

This quickstart uses as its starting point the resources created in one of these quickstarts:

• An Azure SQL database. You can use one of these techniques to create a database:
  • Create DB - Portal
  • Create DB - CLI
  • Create DB - PowerShell

Install the latest SSMS

Before you start, make sure you have installed the newest version of SSMS.

SQL server connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

1. Log in to the Azure portal.
2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.
   
4. If you forget your server login information, navigate to the SQL Database server page to view the server admin name. If necessary, reset the password.

Connect to your database

Use SQL Server Management Studio to establish a connection to your Azure SQL Database server.

Important

An Azure SQL Database logical server listens on port 1433. If you are attempting to connect to an Azure SQL Database logical server from within a corporate firewall, this port must be open in the corporate firewall for you to successfully connect.

1. Open SQL Server Management Studio.
2. In the Connect to Server dialog box, enter the following information:

   Setting	Suggested value	Description
   Server type	Database engine	This value is required.
   Server name	The fully qualified server name	The name should be something like this: mynewserver20170313.database.windows.net.
   Authentication	SQL Server Authentication	SQL Authentication is the only authentication type that we have configured in this tutorial.
   Login	The server admin account	This is the account that you specified when you created the server.
   Password	The password for your server admin account	This is the password that you specified when you created the server.

   
3. Click Options in the Connect to server dialog box. In the Connect to database section, enter mySampleDatabase to connect to this database.
   
4. Click Connect. The Object Explorer window opens in SSMS.
   
5. In Object Explorer, expand Databases and then expand mySampleDatabase to view the objects in the sample database.

Query data

Use the following code to query for the top 20 products by category using the SELECT Transact-SQL statement.

1. In Object Explorer, right-click mySampleDatabase and click New Query. A blank query window opens that is connected to your database.
2. In the query window, enter the following query:
   SQL

   SELECT pc.Name as CategoryName, p.name as ProductName
   FROM [SalesLT].[ProductCategory] pc
   JOIN [SalesLT].[Product] p
   ON pc.productcategoryid = p.productcategoryid;
   

3. On the toolbar, click Execute to retrieve data from the Product and ProductCategory tables.
   

Insert data

Use the following code to insert a new product into the SalesLT.Product table using the INSERT Transact-SQL statement.

1. In the query window, replace the previous query with the following query:
   SQL

   INSERT INTO [SalesLT].[Product]
           ( [Name]
           , [ProductNumber]
           , [Color]
           , [ProductCategoryID]
           , [StandardCost]
           , [ListPrice]
           , [SellStartDate]
           )
     VALUES
           ('myNewProduct'
           ,123456789
           ,'NewColor'
           ,1
           ,100
           ,100
           ,GETDATE() );
   

2. On the toolbar, click Execute to insert a new row in the Product table.
   

Update data

Use the following code to update the new product that you previously added using the UPDATE Transact-SQL statement.

1. In the query window, replace the previous query with the following query:
   SQL

   UPDATE [SalesLT].[Product]
   SET [ListPrice] = 125
   WHERE Name = 'myNewProduct';
   

2. On the toolbar, click Execute to update the specified row in the Product table.
   

Delete data

Use the following code to delete the new product that you previously added using the DELETE Transact-SQL statement.

1. In the query window, replace the previous query with the following query:
   SQL

   DELETE FROM [SalesLT].[Product]
   WHERE Name = 'myNewProduct';
   

2. On the toolbar, click Execute to delete the specified row in the Product table.
   

Actual Post: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-ssms

Connect to SQL Data base with Visual Studio and SSDT

Use Visual Studio to query Azure SQL Data Warehouse in just a few minutes. This method uses the SQL Server Data Tools (SSDT) extension in Visual Studio.

Prerequisites

To use this tutorial, you need:

• An existing SQL data warehouse. To create one, see Create a SQL Data Warehouse.
• SSDT for Visual Studio. If you have Visual Studio, you probably already have this. For installation instructions and options, see Installing Visual Studio and SSDT.
• The fully qualified SQL server name. To find this, see Connect to SQL Data Warehouse.

1. Connect to your SQL Data Warehouse

1. Open Visual Studio 2013 or 2015.
2. Open SQL Server Object Explorer. To do this, select View > SQL Server Object Explorer.
   
3. Click the Add SQL Server icon.
   
4. Fill in the fields in the Connect to Server window.
   
   • Server name. Enter the server name previously identified.
   • Authentication. Select SQL Server Authentication or Active Directory Integrated Authentication.
   • User Name and Password. Enter user name and password if SQL Server Authentication was selected above.
   • Click Connect.
5. To explore, expand your Azure SQL server. You can view the databases associated with the server. Expand AdventureWorksDW to see the tables in your sample database.
   

2. Run a sample query

Now that a connection has been established to your database, let's write a query.

1. Right-click your database in SQL Server Object Explorer.
2. Select New Query. A new query window opens.
   
3. Copy this TSQL query into the query window:
   SQL

   SELECT COUNT(*) FROM dbo.FactInternetSales;
   

4. Run the query. To do this, click the green arrow or use the following shortcut: CTRL+SHIFT+E.
   
5. Look at the query results. In this example, the FactInternetSales table has 60398 rows.
   

Actual Post: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-query-visual-studio

Azure database level firewall queries

sp_set_database_firewall_rule (Azure SQL Database)

• 
  

Creates or updates the database-level firewall rules for your Azure SQL Database. Database firewall rules can be configured for the master database, and for user databases on SQL Database. Database firewall rules are particularly useful when using contained database users. For more information, see Contained Database Users - Making Your Database Portable.

Syntax

sp_set_database_firewall_rule [@name = ] [N]'name'  
, [@start_ip_address =] 'start_ip_address'  
, [@end_ip_address =] 'end_ip_address'
[ ; ]  

Arguments

[@name = ] [N]'name'
The name used to describe and distinguish the database-level firewall setting. name is nvarchar(128) with no default value. The Unicode identifier N is optional for SQL Database.

[@start_ip_address =] 'start_ip_address'
The lowest IP address in the range of the database-level firewall setting. IP addresses equal to or greater than this can attempt to connect to the SQL Database instance. The lowest possible IP address is 0.0.0.0. start_ip_address is varchar(50) with no default value.

[@end_ip_address =] 'end_ip_address'
The highest IP address in the range of the database-level firewall setting. IP addresses equal to or less than this can attempt to connect to the SQL Database instance. The highest possible IP address is 255.255.255.255. end_ip_address is varchar(50) with no default value.

The following table demonstrates the supported arguments and options in SQL Database.

Note

Azure connection attempts are allowed when both this field and the start_ip_address field equals 0.0.0.0.

Remarks

The names of database-level firewall settings for a database must be unique. If the name of the database-level firewall setting provided for the stored procedure already exists in the database-level firewall settings table, the starting and ending IP addresses will be updated. Otherwise, a new database-level firewall setting will be created.

When you add a database-level firewall setting where the beginning and ending IP addresses are equal to 0.0.0.0, you enable access to your database in the SQL Database server from any Azure resource. Provide a value to the name parameter that will help you remember what the firewall setting is for.

Permissions

Requires CONTROL permission on the database.

Examples

The following code creates a database-level firewall setting called Allow Azure that enables access to your database from Azure.

-- Enable Azure connections.  
EXECUTE sp_set_database_firewall_rule N'Allow Azure', '0.0.0.0', '0.0.0.0';  

The following code creates a database-level firewall setting called Example DB Setting 1 for only the IP address 0.0.0.4. Then, the sp_set_database firewall_rule stored procedure is called again to update the end IP address to 0.0.0.6, in that firewall setting. This creates a range which allows IP addresses 0.0.0.4, 0.0.0.5, and 0.0.0.6 to access the database.

-- Create database-level firewall setting for only IP 0.0.0.4  
EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.4';  

-- Update database-level firewall setting to create a range of allowed IP addresses
EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.6';