Home > Dynamics SL (Solomon) Technical Info > Dynamics SL – Error 20201 when printing Sales Journal

Dynamics SL – Error 20201 when printing Sales Journal

A client sent me a message today saying they were getting an error message when trying to print the Sales Journal today.  The message number is 20201 and the error message begins with something like  “…kernel is not able to use sql server cursor functions…”

I didn’t find anything on the knowledgebase, so I ran a SQL Profiler trace to see if I could see what was going on.  Luckily, the error happened very quickly so I didn’t have to plow through a ton of results to find what I was looking for.

Pretty quickly, I found a ROLLBACK TRANSACTION and that sounded like what I was looking for.  Just before that, the Sales Journal pre-process (4069002.exe) had run 2 stored procedures.  ADG_GLWildcard_AcctXref and ADG_GLWildcard_SubXref.  I copied the parameters passed in from the Profiler trace and tried to run them in a Management Studio query window. The first one (the Acct related one) ran fine.  The second one (the Sub related one) returned an error related to a permission problem trying to select from vs_subxref.  This seemed promising.

I looked at the SQL inside the two stored procedures and both contained this clause – WITH EXECUTE AS ‘07718158D19D4f5f9D23B55DBF5DF1’.  This tells SQL not to run the SQL code with the calling user’s permissions, but this specified user instead.  (The long numeric string beginning with 0771 is a special login that Dynamics SL creates on your SQL Server when you install SL.)

Inside ADG_GLWildcard_SubXref, there was a SELECT statement querying vs_subxref.  I check that view and the 0771… user didn’t have SELECT rights to the view.  I added them and tried the report again and it ran fine.

Even though I had solved the problem, I was curious now.  I looked inside the ADG_GLWildcard_AcctXref.  In parallel fashion, it had a SELECT statement querying vs_acctxref.  I went to go see if I needed to fix permissions on it as well, and was surprised to find that it didn’t exist in the database.

I went back to the stored procedures and realized that the SELECT statements were actually inside a string, like this:

select @Query = “select …”

execute (@Query)

There was also a comment that said they were doing this so that the procedure would compile even though the view many not exist.

I understand that from a SQL point of view, but that was the first I had heard of SL doing it this way.  I looked at the properties of the vs_subxref and – sure enough, it was just created a couple of days ago.  We haven’t done any upgrades there recently, but I had just done a Rebuild security to fix up some new stuff I had added to the database.

I have no idea why the vs_subxref wasn’t there originally, why vs_acctxref isn’t there now, or why the permission wasn’t created correctly. If anyone can shine any light on that, please comment.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: