RSS

Category Archives: SQL SERVER

Cannot select a record in Students (CMCStudent). Name: 0. The SQL database has issued an error.

I have imported a model in Microsoft Dynamics AX 2012 R3 CU9 enviornment and model was imported successfully without any error but when i tried to open customized table or form developed in new Model then it throws me an error

Cannot select a record in Students (CMCStudent). Name: 0. The SQL database has issued an error.

when i executed query at SQL of database MicrosoftDynamicsAX then found table CMCStudent does not exists that why AX tries to access table CMCStudent that does not exist in DB yet.

I googled multiple places and everyone suggested to perform “Administration – Periodic – SQL administration – Table Actions – Synchronize database” but its didn’t work for me.

Solution:

1.Administration – Periodic – SQL administration – Table Actions –Select Table->Generate SQL scripts->…. for creating tables.

it will generate a script that you can use to add the selected tables to the database

SQL Error1.PNG

 

2.Execute SQL script generated by step 1 on Dynamics AX database

sql error 2.PNG

3.Now restart AX service and try to open form related to table or browse table.You will be able to open table without any error.

 

Happy Daxing. 🙂

 

 

 

Advertisements
 

Generate Next RecId for Dynamics AX Table from SSIS

If you want to insert records directly into the SQL database using SSIS, you cannot on-the-fly get RecId’s easily from SQL manipulation alone, so another option is reserving the Recid’s using x++ in an x++ job or service, for example:
To get the next Recid for SalesTable,use the below code and before printing the recid , suspendrecids() has to be call then after printing recid removeRecIdSuspension() method should be call….

static void getNextRecId(Args _args)
{
//Table that stores record ids details for tables
SystemSequences systemSequences;
Counter noOfResereveRecId;

///Suppose you wants to reserve 40 records

noOfResereveRecId = 40;

//Class that handles Record id generation
SystemSequence systemSequence = new SystemSequence();
;

select firstonly systemSequences
where systemSequences.tabId == Tablenum(SalesTable);

systemSequence.suspendRecIds(systemSequences.tabId);
info(strFmt(‘Next record id: %1’,systemSequence.reserveValues(noOfResereveRecId, systemSequences.tabId)));
systemSequence.removeRecIdSuspension(systemSequences.tabId);
}

This job will take a table, and reserve one recId to be used that the system will simply ignore/skip. A word of caution, if you accidently reserve a huge number it’s not easy to get those reservations back, and int64’s do have a limit (albeit a huge number).

You could probably create a service in x++ that you could hit from your SSIS job where you tell a table name and an amount to reserve, and get an int64 back, that way you could automate the SSIS job.

 

Tags: , ,

How to Install SQL Server 2014 and a SQL Server 2014 Service Pack1 (SP1) at same time using slipstream

With the release SQL Server 2014, I have compiled the list of steps to create a slipstream drop. Once you have created this slipstream drop, you can install the original release of SQL Server 2014  and SP1 at the same time.The slipstream functionality performs a single installs that is quicker when compared to installing the original release and then applying the service pack. Additionally this slipstream drop fixes any Setup issues that prevent a successful installation or upgrade that has been addressed in SP1.To patch existing SQL Server 2014 features, download SP1 from the download center or obtain from Microsoft Update when available.

When you are installing a new instance of SQL Server, you want to perform as few steps as possible. SQL Server 2014 setup is much better about slipstreaming both service packs and cumulative updates.

Here are the steps:

1. Mount your original SQL Server 2014 source media.

2. Download the SQL Server 2014  SP1 packages with the file name [...]_zip.exe and Extract each of the SQL Server 2014 SP1 packages to C:\AllUpdatesFolder as follows. For example, when you first download Service Pack 1.the download folder will look like this:

test1

3.Now when you run the below command line, when you get to the Product Updates screen in setup,

D:\setup.exe /Action=Install /UpdateSource=C:\AllUpdatesFolder

you should see that it has included both SP1 and CU1.

test2

Note that you can keep all of your Service Packs and Cumulative Updates over time in the same folder – if you use the /UpdateSource argument, SQL Server Setup will be smart enough to choose the latest SP and its latest CU, regardless of what else might exist in that folder.

 
1 Comment

Posted by on March 4, 2015 in slipstreaming, SQL SERVER

 

Tags: ,

SQL script for rebuild/reindex indexes for Dynamics AX 2012

This script will evaluate each index in the database, and determine if it should be reindexed or rebuilt based on how fragmented they are.

I have very good experience in doing this, and it really increases performance the Dynamics AX 2012 databases.

-- Ensure a USE <databasename> statement has been executed first.
Use MicrosoftDynamicsAX
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
 

Tags: , , , , ,

How to Find Large Tables in AX Database through SQL Server

If your database appears to be larger than you expect it to be, this article will show you how to display a list of your MS SQL Database tables in size order, and list the associate size of the table.

  1. Open and log in to Microsoft SQL Server Management Studio
  2. Copy the following script into the New Query page replacing [DatabaseName] with the name of your database and Click the Execute button
USE [DatabaseName]
GO
CREATE TABLE tempLargeTable(
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT tempLargeTable
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM tempLargeTable a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE tempLargeTable
4.tempLargeTable
🙂 Keep Daxing

 

 

Retrieving a list of System databases from SQL Server

I need to get a list of System databases from SQL Server.

A bit of googling provided enabled me to develop this SQL statement:

owner_sid is equal to 0x01 just for system databases. So you can use it to recognise if the database is a system DB or not.

select * from sys.databases
where owner_sid != 0x01

This works, it gives me a list of system databases.

🙂

 
Leave a comment

Posted by on August 7, 2014 in SQL SERVER

 

Retrieving a list of AX databases from SQL Server

i was trying to get list of AX databases on SQL Server.Microsoft Dynamics AX have capability to get list of database exists in SQL Server.it contains class SysDatabaseList.

SysDatabaseList sysDatabaseList;
container conDatabaseList;
Counter conlength;
Counter loopCounter;

sysDatabaseList = new SysDatabaseList();

conDatabaseList =sysDatabaseList.databaseNames();

conlength = conLen(conDatabaseList);

for(loopCounter = 1;loopCounter <= conlength;loopCounter++)
{
info(strFmt(“%1”,conPeek(conDatabaseList,loopCounter)));
}

🙂