thendrickson
Programmer
I have an interesting error occuring.
I have simplified things down a bit to isolate the bug so bear with me.
I have a complex stored procedure (sql server 2000) which receives several parameters and returns records.
When I execute the procedure in Query Analyzer, things work properly and I receive the records I am looking for.
When I attempt to execute the procedure from VB6 (IDE), no records are returned.
I have simplified the code down to creating a string containing the proper SQL statement I wish to execute against the Database.
example:
strS= "ap_Proc 'ABC, 'value', 'Employee', '''XXX'', ''YYY'', ''VVV'', '' '5/1/2006', '8/3/2006'"
I then copy the exact string created into QA and execute it The sp executes as expected and returns the excessively large number of records 'required'
I then execute the next line of code in VB6 (IDE)
Set rs = strConn(strS,,adcmdtext)
I check rs.state which is closed at that point.
I have used this abbreviated syntax in the past and the current project contains the same abbreviated syntax in other locations where the syntax works as expected.
And I am logged in the same way to the database
The only difference I can see is that the proc which fails compares dates while the procs that do not fail do not compare dates.
Note: MDAC 2.7
SQL Server 2000 sp2
Win XP pro
Windows 2000 on the dev server (database is located there)
SQL Server has a Linked server to Informix 7.31 on an HP Unix box
Code that works also uses the Link server, but does not have any date comparisons.
At one point (3 years or so ago) I had a date problem with ADO/SQL Server and Null values until I upgraded to MDAC 2.8
Updating to MDAC 2.8 may not be a good idea since I cannot be certain QA will also update (not to mention users!)
They tend to just copy the exe out for updates.
Any other ideas before I update to 2.8?
I have simplified things down a bit to isolate the bug so bear with me.
I have a complex stored procedure (sql server 2000) which receives several parameters and returns records.
When I execute the procedure in Query Analyzer, things work properly and I receive the records I am looking for.
When I attempt to execute the procedure from VB6 (IDE), no records are returned.
I have simplified the code down to creating a string containing the proper SQL statement I wish to execute against the Database.
example:
strS= "ap_Proc 'ABC, 'value', 'Employee', '''XXX'', ''YYY'', ''VVV'', '' '5/1/2006', '8/3/2006'"
I then copy the exact string created into QA and execute it The sp executes as expected and returns the excessively large number of records 'required'
I then execute the next line of code in VB6 (IDE)
Set rs = strConn(strS,,adcmdtext)
I check rs.state which is closed at that point.
I have used this abbreviated syntax in the past and the current project contains the same abbreviated syntax in other locations where the syntax works as expected.
And I am logged in the same way to the database
The only difference I can see is that the proc which fails compares dates while the procs that do not fail do not compare dates.
Note: MDAC 2.7
SQL Server 2000 sp2
Win XP pro
Windows 2000 on the dev server (database is located there)
SQL Server has a Linked server to Informix 7.31 on an HP Unix box
Code that works also uses the Link server, but does not have any date comparisons.
At one point (3 years or so ago) I had a date problem with ADO/SQL Server and Null values until I upgraded to MDAC 2.8
Updating to MDAC 2.8 may not be a good idea since I cannot be certain QA will also update (not to mention users!)
They tend to just copy the exe out for updates.
Any other ideas before I update to 2.8?
