MS/SQL OpenQuery to linked DB2 Server error

Swamp_Thing
Swamp_Thing used Ask the Experts™
on
I have a SPROC that takes in parameters and uses OpenQuery to create a record in an AS400 DB2 table.   It works fine from my development server ( MS/SQL 2008 R2 ) but not from my production server ( MS/SQL 2008 R2 ).  The Linked Server to the AS400 is identical on both servers.  My production server returns this error:

A syntax error was detected at token ..  Token . is not a valid token.

The SPROC is attached.

I'm calling it as follows:

declare @result smallint
exec [dbo].[sprocAS400_Insert] 'VALUE1', 'VALUE-2', 'VALUE-0-3', 'VALUE4', 24, @result OUTPUT
select @result

above works fine but only from my test server.  The full error message ( also attached ) doesn't give me anything to go on - any suggestions?
sprocAS400_Insert.sql.txt
sprocError.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
ste5anSenior Developer

Commented:
Why not using the DATETIME value directly, instead of you manual conversion?
VP Technology / Senior Consultant
Commented:
I don't think DB2 likes the "." delimiter in "prodlib.mstable".  By defaule, the AS/400 DB2 delimiter is a "/".  The type of delimeter (and other important factors in how the query is interpreted) is controlled by the NAMING FORMAT selected when the connection is created.

I suspect that in your dev environment, the linked server is configured for *SQL naming format ("." delimiter), and in your prod environment it is configured for *SYS (system) naming format ("/" delimiter).

Take a look at the linked server configurations on each and make sure they match.

- Gary
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Swamp_Thing, do you still need help with this question?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial