December 31, 2020 at 11:13 pm
SQL Server 2016
cursor and query:
select * from table1 where col1 = '1' and col2 = '00001' and col3='I' and col4 = 'JULY--2015'
this is a dynamically created query and for the most part the col1, col2 and col3 stay the same only the col4 changes in this test case. while all the other records for the col4 worked the JULY--2015 (double dash) would throw back 'Communication Link Failure' error. I went in and changed the double dash to a single dash and it worked. What is the deal with double dash and 2015. I ran my program against other col4 with double dash but they worked. like 'JUNE -- JULY'
Any info would be greatly appreciated.
December 31, 2020 at 11:17 pm
sorry probably more important info.
this comes from a create cursor and reading thru the rows
the fetch next on that row is the one that throws error
January 1, 2021 at 7:55 am
How about posting all the code along with the DDL for the table and the some sample data that demonstrates what works and what doesn't.
January 1, 2021 at 4:02 pm
Will see what i can do but client in question only allows access 8-5 M-F business hours. And as for all the details, this comes from acucobol (COBOL) running against SQL database. the COBOL creates cursors for every table that the COBOL program is accessing. I debugged the COBOL program with a trace log (COBOL Log) and can see where it starts the cursor and the fetch next are working until the JULY--2015. After i changed the row and ran the COBOL program again it worked.
I was hoping some one knew why -- was some kind of SQL issue. But after posting on this forum, I started to wonder if the Collation might have something to do with it. In the past I have seen Collation cause issues since COBOL uses the Collation of the PC/workstation running it.
January 1, 2021 at 7:05 pm
Curious... what is the datatype for Col4?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2021 at 5:51 pm
Char(18)
January 2, 2021 at 10:42 pm
The dash-dash marks a comment that ends at the newline. This convention comes from IBM and punchcards in the early days of DB2.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 4, 2021 at 7:19 pm
the collation is CP1_CI_AS. I plan on running a script to change the collation to CP437_BIN after hours. I think this should resolve this issue and any future issues related to it.
here is an overview of how this is going down. A query is run that selects the specific records with totals into a totals table. SO select c1,c2,c3,c4,totbal into the totals table. these (more came up from customer) rows i verified are all in the totals table (with double dashs, multiple ones) that we create. then our cobol programs creates a cursor and starts processing the rows. the fetch next that fails is when it comes across select rows with the DD in them. Some of the rows with DD work but on select ones line the JULY--2015 it fails. i queried the totals table and before the JULY--2015 row are 2 other rows that have a DD in them for col4. 'AUG -- 19' and 'JAN -- A1' they both worked BUT they have spaces before and after the DD. The program uses SQL ODBC driver to connect not a direct connection. i downloaded and used the latest version ODBC 17 driver. It changed the error from Communication Link Failure to something else. I don't have that error message yet since I am working thru our support staff to gather this info. Will probably get connected later today to get that error message and any other info. Hopefully i can run the change collation script and see if that resolves the issue.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply