Running NNSTAT against Microsoft SQL Server nickname failed with SQL1822N error

Technote (troubleshooting)


Problem(Abstract)

Why running NNSTAT against some of Microsoft SQL Server nicknames failed with SQL1822N error, but others did not?

Symptom

CALL SYSPROC.NNSTAT('Servername','Schema','Nickname',NULL,NULL,2,NULL,?);

SQL1822N Unexpected error code "42000" received from data source
"Servername". Associated text and tokens are "func="Mssql_Statement::parse" msg="Operand data type bi".. Output
parameters data: @Parameter1 = {null}


Cause

The remote column data type is BIT. The SQL SERVER max function doesn't support it. Please refer to this document:

http://msdn.microsoft.com/en-us/library/ms187751%28v=sql.105%29.aspx

Environment

Product: Federation Server

Data Source: SQL Server


Diagnosing the problem

In db2diag.log, we can see the whole error message returned by SQL SERVER:

2012-10-19-09.34.03.354593-300 I3512E1427 LEVEL: Error
PID : 9640 TID : 1118927168 PROC : db2fmp (
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, mssql wrapper, Mssql_Server::get_error_info, probe:7
MESSAGE : ODBC error txt:
DATA #1 : Hexdump, 216 bytes
0x00002AAAB38BE120 : 5B49 424D 2844 6174 6144 6972 6563 7420 [IBM(DataDirect
0x00002AAAB38BE130 : 4F45 4D29 5D5B 4F44 4243 2053 514C 2053 OEM)][ODBC SQL S
0x00002AAAB38BE140 : 6572 7665 7220 4472 6976 6572 5D5B 5351 erver Driver][SQ
0x00002AAAB38BE150 : 4C20 5365 7276 6572 5D4F 7065 7261 6E64 L Server]Operand
0x00002AAAB38BE160 : 2064 6174 6120 7479 7065 2062 6974 2069 data type bit i
0x00002AAAB38BE170 : 7320 696E 7661 6C69 6420 666F 7220 6D61 s invalid for ma
0x00002AAAB38BE180 : 7820 6F70 6572 6174 6F72 2E00 0000 0000 x operator......
0x00002AAAB38BE190 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00002AAAB38BE1A0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00002AAAB38BE1B0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00002AAAB38BE1C0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00002AAAB38BE1D0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00002AAAB38BE1E0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00002AAAB38BE1F0 : 0000 0000 0000 0000 ........

Resolving the problem

Using the following syntax to exclude the column whose remote data type is BIT, e.g. COL2:

CALL SYSPROC.NNSTAT('Servername','Schema','Nickname','COL1,COL3,COL4',NULL,2,NULL,?);

Related information

A simplified Chinese translation is available

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Federation Server
Data Sources and Wrappers - Microsoft SQL Server

Software version:

9.5, 9.7

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1615583

Modified date:

2013-04-01

Translate my page

Machine Translation

Content navigation