Microsoft Dynamics Most Influential People Top 100 – 2012

 

DYNAMICS WORLD launched the 2012 process for nominations! Votes were started, and below is my poll:

http://www.dynamicsworld.co.uk/top-100-voting-page-6/

Awaiting for your votes!!


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Advertisements

Dynamics GP Extended Pricing Error

 

Few weeks back I have posted about this error which was repeated again yesterday with one of my customers:

Microsoft SQL Native Client SQL Server Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

image_thumb

In my previous article I have explained everything about the issue and how it occur, this time I have decided to write a script that re-index the IV10400 table as below:


DECLARE @COUNTER INT
SET @COUNTER = 30
DECLARE CURR CURSOR FOR SELECT SEQNUMBR FROM IV10400
DECLARE @SEQNUMBR BIGINT
OPEN CURR
FETCH NEXT FROM CURR INTO @SEQNUMBR
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE IV10400 SET SEQNUMBR = @COUNTER WHERE SEQNUMBR = @SEQNUMBR
SET @COUNTER = @COUNTER + 30
FETCH NEXT FROM CURR INTO @SEQNUMBR
END
CLOSE CURR
DEALLOCATE CURR

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Enjoy!


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Kill All Connections to an SQL Server Database

 

Have you ever tried to restore a database using SQL 2008 and the restore failed since the database is in use? In 2005 we used to open “Detach” database and click on the hyperlink of the existing connection which will open the activity monitor and show existing connections.

Currently in SQL 2008 clicking the hyperlink will only display a message informing you that the database is currently in use without redirecting you to the connections page, and you will have to go to the activity monitor, find connections related to your database and kill them one by one.

However, I found an interesting Stored Procedure that kills all database connections Smile

By Henry Huey’s:

http://www.imiscommunity.com/sql_stored_procedure_to_kill_all_connections_to_a_database

Run the script that follows against the master db, then execute the procedure like this:

sp_KillSpidsByDBName MyDBName
CREATE PROCEDURE [dbo].[sp_KillSpidsByDBName] 
@dbname sysname = ''
AS
BEGIN

-- check the input database name
IF DATALENGTH(@dbname) = 0 OR LOWER(@dbname) = 'master' OR LOWER(@dbname) = 'msdb'
RETURN

DECLARE @sql VARCHAR(30)
DECLARE @rowCtr INT
DECLARE @killStmts TABLE (stmt VARCHAR(30))

-- find all the SPIDs for the requested db, and create KILL statements
-- for each of them in the @killStmts table variable
INSERT INTO @killStmts SELECT 'KILL ' + CONVERT (VARCHAR(25), spid)
FROM master..sysprocesses pr
INNER JOIN master..sysdatabases db
ON pr.dbid = db.dbid
WHERE db.name = @dbname

-- iterate through all the rows in @killStmts, executing each statement
SELECT @rowCtr = COUNT(1) FROM @killStmts
WHILE (@rowCtr > 0)
BEGIN
SELECT TOP(1) @sql = stmt FROM @killStmts
EXEC (@sql)
DELETE @killStmts WHERE stmt = @sql
SELECT @rowCtr = COUNT(1) FROM @killStmts
END

END

GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Microsoft Open Door 2011

 

Kindly note that Microsoft Jordan Open Door 2011 this year is on the 1st and 2nd of November 2011.

 

Venue: Sheraton Hotel, Amman.

 

Time: 8:30 AM- 5:00 PM.

 

For registration and further information kindly follow the link below:

Open Door 2011

Event Overview

Learn about the latest IT administration and management, software development and see how to solve actual business tasks.

The event is your chance to increase your knowledge, advance your skills and network with your local IT community as well as regional Microsoft and industry experts.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Microsoft Open Door Event

Guys,

For those who would like to meet and exchange business cards, I will be available at Microsoft Open Door event in Sheraton Amman – Jordan at Tuesday and Wednesday in the MVPs section to answer any inquiries you might have about Dynamics GP related issues.

Meet you there!

Regards,

Mohammad R. Daoud MVP – MCT
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.mohdaoud.com

– Posted using BlogPress from my iPhone

Another Visit to Jeddah – KSA

 

Guys,

I am travelling to Jeddah after a couple of hours for long 5 days, if you would like to meet and exchange business cards, I will be normally in Break-time Café most of the time and my phone number below will be available all the time.

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

 

Today, one of my customers reported the below error message whenever they try to post any kind of inventory transactions:

Microsoft SQL Native Client SQL Server Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

image

Nothing modified at their database and the error start appearing suddenly, they are running the latest service pack for GP and running SQL Server 2005 with the latest service pack.

They followed all the known procedures trying to figure out what’s the reason behind this, but unfortunately non of the procedures helped. Worth to mention that the transaction posts properly and does not actually has any obvious issues.

I ran an SQL Server Profiler trace and tried to locate the actual reason behind the issue and noticed the problem in the Extended Pricing module.

The issue is that the customer has 50 concurrent users working on the application and they have big number of transactions daily, which lead to the fact that IV10400 table is no longer accepting new entries, the table contains a field called “SEQNUMBR” which holds the sequence number for transaction and increased by 16XXX each time a new record created.

The maximum number returned for SEQNUMBR returned was 22,000,000 which is too large to be handled by an integer data type, I had to modify the table structure for IV10400 to replace the integer data type with bigint and to update a stored procedure called “sopExtPriceBookSetup” to replace the declaration of integer variables into bigint’s, if you need help in replacing this, just let me know and I will send you the stored procedure I already modified.

Enjoy!

UPDATE: Before applying modifications on the database, I checked the linked table in dexterity and noticed that the Sequence Number is defined as long integer, that’s why I posted the article, but my customer informed me that this did not work as expected and they gets additional errors! I check the maximum number they achieved after 3 working years in the table and it was 2,700,000,000! It works as all sequence numbers in Dynamics GP by multiplying 16384 which allows only 131072 records in the table as the maximum allowed length for long integer is an integral number in the range [–2,147,483,648 to 2,147,483,647].

Therefore, I had to re-index the table to reduce the values by exporting table data to an excel sheet, renumber the SEQNUMBR field and return the data back to GP.

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com