Strange results when calling a SQL stored proc from within an Access 2000 project
-
Hi,
I have written a stored procedure which includes a DATEPART command, i.e.
DATEPART(weekday, (datefield))
The result when ran from SQL Query Analyser is as expected . i.e. Sunday returns 1, Monday 2, etc
When the same proc is called from within the Access 2000 project Sunday is returned as 7, Saturday as 6 instead of 1 and 7 respectively. Basically the same stored proc returns different data depending on from where it has been called.
This is causing some issues obviously as the resulting tables and reports are showing incorrect data when presented in Access 2000.
Has anyone else experienced this before or have any idea what may cause it.
Regards,
Iain
-
SET DATEFIRST 7
Select Datepart(dw, '2006/01/01') --this is a Sunday
--returns 1 on my PC
SET DATEFIRST 1
Select Datepart(dw, '2006/01/01') --this is a Sunday
--returns 7 now.
Just run the correct datefirst statement in autoexec at the startup of access and you'll be fine. You'll obviously have to do the same if you create new connection objects NOT based on currentproeject.connection!
-
I have a strange feeling this depends on what OS you run the sp on, 2000, XP etc....
FInd out whicg OS does what and then code this into the sp.
-
How would the OS affect this?!?!.
A date is a date is a date. No matter which YEAR [
] windows was made in.The regional settings might have an effect and the server language and datefirst setting, but nothing else!
-
I suggest you jump into Books Online and have a look at the DateFirst function. Your problem certainly does seem a little odd, but the solution may be in setting the first day of the week in your sp rather making an assumption. As a simple test, if you execute the statement "select @@DateFirst" from QA then again from your project, do you get a different response?
A simple solution may be to mod the sp. Query the @@DateFirst setting and write it into a variable. Then set DateFirst so there is no assumption and nothing is left to chance. When the sp is done, set it back again to the value held in the variable.
The cause could lie in the language settings of the project vs SQL Server. I know that the language setting of SQL Server determines the default date format. I think the standard install of SQL sets the language format to 1 which is US and therefore the date format is US. Perhaps the date is being interpreted in a different format in each context, thus it becomes a different date and day.
I hope this may help provide some insights into what is going on.
All the best.
Rowan
-
thanks for all the suggestions.
Rowan seems to have hit the nail on the head so thanks again.
I decided to redevelop the front end in VB as a few other date related issues were cropping up in the linked Access project. It does still strike me as odd how it can return a totally different recordset from the same stored procedure dependant on from where it was called. After all, the only information that should be passed between the packages is the recordset generated by SQL Server. The same behaviour does not occur when pulling the recordset via ADO in VB so I have assumed that the issue was related to the way Access interacts with SQL server.
We learn something new everyday.
Thanks again all,
Iain
-
Did you even try my solution. I had the exact same problem as you had and I resolved it simply by executing a single line of code when creating a connection... hardly worth a recode!
-
Yes I tried it, and it didn't work on this occasion hence my reply. On top of that there were also other date related issues and interaction issues with some of the stored procedures. These issues are now non existant. It also no longer looks like it was developed in 1995 😉
-
1975 now
?Good week-end guys.
-
1960 would have been an improvement 😉
have a good weekend
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply