An Error while importing Analytical Accounting Budget

One of my clients is using Analytical Accounting to link cost centers with expense accounts, and therefore they are using the Analytical Accounting budget to specify a budget for both Projects (CAPEX) and to link their expense accounts with cost centers and specify a budget for each cost center on each expense account to manage their OPEX.

While loading the budget into the system, the user reported that he getting the below error:

Error converting data type varchar to numeric

Searching around the blogosphere and the partner source, I notice the same issue with version 8.0 and 9.0, but the customer is using version 2010 R2!

Performing deep investigations on the issue returned that one of periods contained a blank line instead of “ZERO”!! which been a tough task to be found through 6,000 lines and 12 periods!

Hope you get rid of this if found!

---

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

eConnect POP Integration: Invalid Object Name PA10601

I been trying to workout a receiving integration for one of my customers and faced the below error:

Invalid Object Name “PA10601”

I am sure that the project accounting is not installed and the same tool is installed at more than 20 customers so the code is definitely correct.

In addition, sometimes the same issue exist with integration manager and some orders worked properly using the same utility!

We have failed to find anything on the internet that could resolve this and all suggested answers on the community did not resolve the mystery! While doing deep investigations on the issue and before pressing “Submit” for the Microsoft incident request I noticed the solution!

Issue #1 Receiving Integration:

I noticed that the Purchase Order we been trying to import contains data in “Project Number” and “Cost Category Number” in POP10110 table which made the eConnect to query the Project Accounting tables and validates the information and in return it causes the receiving integration to fail!

Issue #2 Purchase Order Integration:

While researching the internet, I noticed that many people faced issues in Purchase Order Integration which cannot be the same issue we been through.

Resolution #1:

Removing data from the mentioned fields fixed the integration!

Resolution #2:

Folks at SalesPad been through this issue and posted a script that resolves the issue by replacing the eConnect stored procedure, below is the link to the updated procedure:

Troubleshooting

Hope that this saves some time!

---

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

Dynamics GP Workflow–Task Approval Page redirects to local server URL even if the used URL was published!!

 

Looks like the folk at Microsoft forgot to remove the local URL when redirecting from Task Approval page to the actual document behind “This workflow task applied to:”:

So if your workflow was published over the internet and you users tries to open a document, the system will redirect them to the local server and not to the website as shown below:

To workaround this, I had to use SharePoint Designer or any available scripting tool to change the redirection of the below hyperlink in (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\Dynamics.Workflow.TaskApprovalPage.aspx):

<a href="<%=this.DocumentUrl%>" id="A1" target="_blank"
     title="<%=this.WindowLinkTitle%>">
     <%=this.DocumentUrlText%></a>.

Into the below modified one:    

<a href="Dynamics.Workflow.GP.PurchaseOrderViewer.aspx?org=1&workflow=f01d80eb-d355-460c-8cb6-b2a2162a078b&PoNumber=<%=this.DocumentUrlText%>" id="A1" target="_blank"
     title="<%=this.WindowLinkTitle%>">
     <%=this.DocumentUrlText%></a>.

And it worked Hope that this helps.

---

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

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.

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

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

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

---

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