The following SQL error was unexpected

bhalpin · 2014-02-14

Hi.

I'm upgrading a 2013 DB to 2013 R2.
This is SQL 2012, and a system I've successfully performed other upgrades on.
After following all the upgrade steps, when attempting to run a page from Object Designed, I get this error:

The following SQL error was unexpected
Invalid column name 'Payroll Trans_Import Format'.
Statement(s) could not be prepared.

Does anyone have any pointers for fixing this?

Thanks!

geordie · 2014-02-14

There's a Kb for this: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;2780888

It this your case?

bhalpin · 2014-02-14

Hi.

No, that hotfix appears to be for 2013, but my problem is in 2013 R2.

Thanks anyway!

bhalpin · 2014-02-14

More information:

I exported all tables to txt and found this:

Table 98 General Ledger Setup
. . .
{ 160 ;   ;Payroll Trans. Import Format;Code20;TableRelation="Posting Exch. Def" WHERE (Type=CONST(Payroll Import));

I tried renaming "Payroll Trans. Import Format" to "Payroll Trans Import Format" (removed the '.') and compiled the table.

Now when I run a page I get:

The following SQL error was unexpected.
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

Not quite sure what that all means ...

bhalpin · 2014-02-14

(Yet) More Information:

That field (160) was added to base NAV in 2013 R2.

I tried replacing that table with the 2013 version (without that field). Compile now gives me the same error I get trying to run the page:

The following SQL error was unexpected.
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

I am on rollup 2. I am going to try rollup 3. (Expectations are not high ...)

JohnHunter · 2014-02-14

happens, for some reason the column didn't exist in SQL, Nav showed it... correcting it directly in SQL fixed the issue.

VectorK · 2014-02-18

It happens to me as well

Upgrading a 2009 R2 db to 2013R2 is not Working Properly for Spanish Localization,
After upgrading the Nav 2013 db, in Nav 2013 R2 it's giving error as below,

The following SQL error was unexpected.
Invalid column name 'Queue Folder Path'.
Invalid column name 'Queue Folder UID'.
Invalid column name 'Storage Folder Path'.
Invalid column name 'Storage Folder UID'.
Invalid column name 'Autodiscovery E-Mail Address'.
Invalid column name 'Email Batch Size'.
Statement(s) could not be prepared.

Using MS SQL 2012

Thanks in advance

Djou2424 · 2014-04-01

I am getting the exact same error when trying to launch the RTC Client during the upgrade process from 2013 to 2013R2

Any idea on how to fix this?

Johannes_Nielsen · 2014-04-30

From what I can gather. To avoid SQL errors when importing tables in 2013 R2.

Stop service tier (kick off all users as MS recommends. Well maybe not the kick part)
Import objects
Compile them for good measure
Start service tier

And, do run Rollup (no, Cumiulative Update) 5 (36281) or the new 6.

viewtopic.php?t=60930

kine · 2014-05-04

In most cases it is because the schema is not synchronized. Run the powershell command for it or open the Win client and connect to the instance (I prefer the first). Use the latest build or you risk data loosing. There are blog articles about the process, how the schema is synchronized and what you should do...

Doomhammer · 2014-12-08

similar error but with table, not field:

I upgraded small database from NAV 4.0 SP3 to 2013 R2. I used update 11 of NAV 2013.
problem is table 31084 - Acc. Sched. Expression Buffer
somewhere during upgrade was corrupted link between object in NAV, its metadata and SQL table.

I needed to delete SQL table because NAV told me that this object already exists on SQL. after deleting, upgrade process was completed and table was created on SQL.

When I try to copy company, I will get error message that this table already exists in copied company.
I tried to delete this table from NAV, from SQL, I tried to sync tenant, delete metadata but nothing helped me.

please, has anybody some clue?

Doomhammer · 2014-12-11

well, I found solution.

this table is part of CZ modification. in NAV 2009 was stored under ID 26578 and in 2013 is stored under 31084 but it has the same name - Acc. Sched. Expression Buffer
I deleted old table and copying of company works.
but I thought, two object cannot have same name. it is strange.

urpok · 2014-12-29

I had similar error, but this error was referring to whole table that is missing. Specifically missing tables were dbo.[company name].User Setup and dbo.[company name].Change Log Setup.

The error was caused in my case as following: Customer had created a new company with some capital letters and some lower case letters, for example "This COMpany". After creating the company, they noticed that the company name was mistyped, and changed it to "This Company".

Since NAV 2013R2 now creates databases with Case Sensitive, Accent Sensitive mode, this caused problems when I installed the customer a new license and restarted the NAV Server.

I fixed this with Management Studio by changing reference to the new company from dbo.Company table to correspond the company prefix in database.

Hope this helps someone else who googles this error message

Prospero año everyone!

Howdy, Stranger!

Quick Links

Categories

The following SQL error was unexpected

Comments