Weird Stored Procedure Behaviour (1 Viewer)

SkyCraw · Jan 9, 2015

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:

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

pbaldy · Jan 9, 2015

Since it gets executed on the server either way, you'd certainly expect the same results. Have you validated that the finished SQL in the pass through query is coming out exactly the same as that run from SSMS?

CJ_London · Jan 10, 2015

Not sure if this would have an effect (guess it depends on what the query is expecting as parameter values) but your date formats are different

in Access "mmm dd yyyy" will give a date of Dec 01 2014 whereas in SSMS it is December 1 2014

Also this line

Dim sd, ed, emp As String

only emp is declared as string the others will be variant

try

Dim sd as string
Dim ed as string
Dim emp As String

I don't think it makes a difference, but I've always been a bit concerned about declaring variables during code execution so suggest place them with the other dim statements.

SkyCraw · Jan 12, 2015

Thanks for the reply guys!

pbaldy, even the pass through query result-set was different than compared to the SQL one. Turns out it was just how I was declaring the sd and ed variables in VB.

CJ, thanks for pointing the variant declaration out! That was a bad habit I picked up somewhere on the forum, I mislead myself to think that...

Dim sd, ed, emp as String

...was the same as...

Dim sd as String
Dim ed as String
Dim emp as String

Unfortunately, even doing this still ends up with the same results...

pbaldy · Jan 12, 2015

What does the SQL of the pass through query end up getting set to?

SkyCraw · Jan 13, 2015

The pass through query is being set directly to a report.

I've even changed my VB to the following, still no success thus far:

Code:

    Dim db As Database
    Dim Q As QueryDef

    Set db = CurrentDb()
    Set Q = db.QueryDefs("Employee Evaluation")
    
    Dim sd As String
    Dim ed As String
    Dim emp As String
    sd = Format([Forms]![Admin Menu]![start date], "mmm d yyyy")
    ed = Format([Forms]![Admin Menu]![End Date], "mmm d yyyy")
    emp = InputBox("Enter Emp#")
    Q.SQL = "exec [sp_EmployeeEvaluation] '" & sd & "', '" & ed & "', '" & emp & "'"

CJ_London · Jan 13, 2015

Have you tried "mmmm d yyyy" so you have the same date structure as SSMS?

Minty · Jan 13, 2015

I would also make an educated guess that it's the dates (or more accurately the format) causing the issue.
I would alter the SP to take the dates in the format yyyy/mm/dd then adjust you code VBA to match. It will remove any doubt over compatibility.

pbaldy · Jan 13, 2015

SkyCraw said:
The pass through query is being set directly to a report.[/CODE]

What I'm saying is that you set the SQL of a pass through query named "Employee Evaluation". After you run this process, open that query in design view and compare the SQL with what you ran from SSMS.

SkyCraw · Jan 14, 2015

pbaldy, sorry about the misinterpretation. I tried copying the SQL from my pass through query, pasting it in SSMS and running it but it still showed the correction result-set data.

Minty, I tried using that date format setting in VB then running it, grabbing the SQL again from the pass through, pasting it in SSMS and running it again but still the same results.

This is the part of the stored procedure that causes different results between SSMS and Access:

Code:

	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @sd1 datetime, @ed1 datetime, 
	@sd2 datetime, @ed2 datetime, 
	@sd3 datetime, @ed3 datetime, 
	@sd4 datetime, @ed4 datetime, @weeks int,
	@sy datetime, @ey datetime, @sny datetime, 
	@eny datetime, @dt1 datetime, @dt2 datetime

	select @sy = convert(datetime, 'January 1, ' + convert(nvarchar, year(@sd)))
	select @ey = convert(datetime, 'December 31, ' + convert(nvarchar, year(@sd)))
	select @sny = convert(datetime, 'January 1, ' + convert(nvarchar, year(@sd) + 1))
	select @eny = convert(datetime, 'December 31 ' + convert(nvarchar, year(@ed) + 1))

	select @dt1 = (SELECT dt as dt1
	FROM (select dateadd(d, row_number() over (order by name), 'january 1 2001') as dt
		  from sys.columns a) as dates 
	WHERE (datepart(dw, dates.dt) = 1)
	and DATEPART(WW, dt) = DATEPART(WW, @sd)
	and DATEPART(year, dt) = DATEPART(year, @sd))

	select @dt2 = (SELECT dt as dt2
	FROM (select dateadd(d, row_number() over (order by name), 'january 1 2001') as dt
		  from sys.columns a) as dates 
	WHERE (datepart(dw, dates.dt) = 7)
	and (DATEPART(WW, dt) = DATEPART(WW, @ed) or DATEPART(WW, dt) = DATEPART(WW, @sny))
	and dt between @ed - 7 and @ed + 7)

	select @weeks = DATEDIFF(WW, @dt1, @dt2)

	select @sd1 = @dt1 + 7
	select @sd2 = @dt1 + 14
	select @sd3 = @dt1 + 21
	select @sd4 = @dt1 + 28
	select @ed1 = @dt2 - 7
	select @ed2 = @dt2 - 14
	select @ed3 = @dt2 - 21
	select @ed4 = @dt2 - 28
	
	if @weeks = 0
		exec [sp_Bonus Qual Month Summary] @dt1, @dt2, 1

	if @weeks = 1
		exec [sp_Bonus Qual Month Summary] @dt1, @ed1, 1
		exec [sp_Bonus Qual Month Summary] @sd1, @dt2, 2

	if @weeks = 2
		exec [sp_Bonus Qual Month Summary] @dt1, @ed2, 1
		exec [sp_Bonus Qual Month Summary] @sd1, @ed1, 2
		exec [sp_Bonus Qual Month Summary] @sd2, @dt2, 3

	if @weeks = 3
		exec [sp_Bonus Qual Month Summary] @dt1, @ed3, 1
		exec [sp_Bonus Qual Month Summary] @sd1, @ed2, 2
		exec [sp_Bonus Qual Month Summary] @sd2, @ed1, 3
		exec [sp_Bonus Qual Month Summary] @sd3, @dt2, 4

	if @weeks = 4
		exec [sp_Bonus Qual Month Summary] @dt1, @ed4, 1
		exec [sp_Bonus Qual Month Summary] @sd1, @ed3, 2
		exec [sp_Bonus Qual Month Summary] @sd2, @ed2, 3
		exec [sp_Bonus Qual Month Summary] @sd3, @ed1, 4
		exec [sp_Bonus Qual Month Summary] @sd4, @dt2, 5

	if @weeks = 5
		select @dt2 = @dt2 - 7
		select @ed1 = @dt2 - 7
		select @ed2 = @dt2 - 14
		select @ed3 = @dt2 - 21
		select @ed4 = @dt2 - 28

	if @weeks = 5
		exec [sp_Bonus Qual Month Summary] @dt1, @ed4, 1
		exec [sp_Bonus Qual Month Summary] @sd1, @ed3, 2
		exec [sp_Bonus Qual Month Summary] @sd2, @ed2, 3
		exec [sp_Bonus Qual Month Summary] @sd3, @ed1, 4
		exec [sp_Bonus Qual Month Summary] @sd4, @dt2, 5

[sp_Bonus Qual Month Summary] dumps data into another table and updates the rows based on various criteria. This dump table is then used within the select statement that follows the above SQL statements. The @dt1 and @dt2 statements select the previous Sunday to the Start Date and the Saturday to follow the End Date (Start and End Dates are the beginning and ending of a month).

Even when using the same formatting (i.e. yyyy/mm/dd) in both places, it causes different results... It's really weird, especially when I'm using the same [sp_Bonus Qual Month Summary] for another report and it's works fine in Access.

SkyCraw · May 1, 2015

Just visiting/cleaning up my forum posts right now...

Going to mark this thread as solved because I eventually restructured the stored procedure, it is now dumping data into a virtual temp table (in server RAM, not a physical temp table within SQL database) plus looking at another to simply calculations helps execution times.

pbaldy · May 1, 2015

Glad you got it sorted.

Weird Stored Procedure Behaviour (1 Viewer)

SkyCraw

Registered User.

pbaldy

Wino Moderator

CJ_London

Super Moderator

SkyCraw

Registered User.

pbaldy

Wino Moderator

SkyCraw

Registered User.

CJ_London

Super Moderator

Minty

AWF VIP

pbaldy

Wino Moderator

SkyCraw

Registered User.

SkyCraw

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread