Hello everyone,
I haven't posted in a while, but this scenario is quite strange...
Does anyone know what would cause a SQL stored procedure to behave differently when called in Access 2007 via VB than within SSMS (SQL Server 2008 R2)?
This is what I have in Access:
This is what I'm executing in SSMS:
All the data is carried through the same and the Access result-set matches the SSMS result-set to a tee EXCEPT for data which is carried through from a SQL dump table. This dump table is populated via another stored procedure that is currently being run within sp_EmployeeEvaluation; some fields within the dump table are not being updated when sp_EmployeeEvaluation is run from Access (using the same dates and Emp#).
I had it running perfectly in Access, same result-set as SQL, at one point too... :banghead:
Here's some brief sample data of what the dump table would essentially look like after running sp_EmployeeEvaluation:
SSMS:
Access:
I haven't posted in a while, but this scenario is quite strange...
Does anyone know what would cause a SQL stored procedure to behave differently when called in Access 2007 via VB than within SSMS (SQL Server 2008 R2)?
This is what I have in Access:
Code:
Dim db As Database
Dim Q As QueryDef
Set db = CurrentDb()
Set Q = db.QueryDefs("Employee Evaluation")
Dim sd, ed, emp As String
sd = Format([Forms]![Admin Menu]![start date], "mmm dd yyyy")
ed = Format([Forms]![Admin Menu]![End Date], "mmm dd yyyy")
emp = InputBox("Enter Emp#")
Q.SQL = "exec [sp_EmployeeEvaluation] '" & sd & "', '" & ed & "', '" & emp & "'"
This is what I'm executing in SSMS:
Code:
exec sp_EmployeeEvaluation 'december 1 2014', 'december 31 2014', '77'
All the data is carried through the same and the Access result-set matches the SSMS result-set to a tee EXCEPT for data which is carried through from a SQL dump table. This dump table is populated via another stored procedure that is currently being run within sp_EmployeeEvaluation; some fields within the dump table are not being updated when sp_EmployeeEvaluation is run from Access (using the same dates and Emp#).
I had it running perfectly in Access, same result-set as SQL, at one point too... :banghead:
Here's some brief sample data of what the dump table would essentially look like after running sp_EmployeeEvaluation:
SSMS:
Code:
[U]Emp#[/U] [U]Week1[/U] [U]Week2[/U]
1 12 10
2 13 11
3 12 9
4 11 12
5 10 11
Access:
Code:
[U]Emp#[/U] [U]Week1[/U] [U]Week2[/U]
1 12 NULL
2 13 NULL
3 12 NULL
4 11 NULL
5 10 NULL