Stored procedure not returning results when ran from application? 1

[1DMF]

Hi,

Why would a stored procedure return results when executed from the SQL Server Management Studio , but using the same arguments from my MS Access passthrough query I get no records?

I don't get an error I just get zero records returned to my Access App, yet there are two records if the same SP is ran direct in SQL?

How do I debug and what could be causing this?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

When you run the query in SSMS, is there any output in the messages tab?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

yes "Command(s) completed successfully."

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

OK, I think I worked it out.

JET is doing some date conversion I don't want, so even though I am passsing in a string date in UTC...

'1986-05-27' , it is messing with it and so when I put the same syntax direct in SQL it works?

I'm passing a string so why is JET messing with the format.

If I pass it in UK format '27-05-1986' it works?

What is going on?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

It's hard to say without seeing the code.

I suspect it has something to do with the default language for the login to SQL server and how dates are interpreted.

I blogged about this a long time ago.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

I encourage you to read it carefully. If there's something you don't understand, let me know.

As an aside... try removing the dashes from your first example: '19860527'



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

You could be right George!

I created a new user for the SP's as it was using 'SA' , so did some security housekeeping and rather than leaving the language on 'default' I selected 'British English'.

Would make sense why this suddenly was broken!

Funny how I'm learning to do things properly and tighten up with good practices such as users logins with datareader / datawriter instead of 'SA' and dbowner and now things are breaking!

I think I should change it to just English, there is too much legacy stuff formating to UTC to go fix now!

Thanks for the heads up!



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

You're welcome.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Just curious George,

Why even though the SQL user was set to British English was it messing with the date format.

As the date being passed in was UTC 'yyyy-mm-dd' , which is a Universal date format, it should be the same regardless of language setting.

UTC in USA is the same as UTC in UK, so why did it break?

Cheers,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

There are only 2 unambiguous date formats that will ALWAYS be interpreted the same regardless of language or dateformat settings. Unfortunately yyyy-mm-dd is not one of them.

For example, consider this date '2012-01-02'. What is the month for that date? I would say January, but that is based on my conditioning and my interpretation.

Here. Let me prove my point.

Set language 'us_english'
Select Month('2012-01-02')

Set language 'british'
Select Month('2012-01-02')

Without running the code shown above, what would you expect the output to be? Now go ahead and run it. It's ok. I'll wait....... Surprised? I bet you are, and so would most people.

The only 2 safe formats are: 'yyyy-mm-dd[!]T[/!]hh:nn' and 'yyyymmdd hh:nn'

If you use the first one, with the T, you must supply a time, and the T is pretty much hard coded in there. If you use the second one (my preferred), the time is optional. Regardless, there is never ambiguity regarding those 2 date formats.

Example:

Set language 'us_english'
Select Month('2012-01-02T00:00:00')

Set language 'british'
Select Month('2012-01-02T00:00:00')

Set language 'us_english'
Select Month('20120102')

Set language 'british'
Select Month('20120102')

As you can see, the code block above returns 1 for all queries. No ambiguity whatsoever.

There is, of course, a 3rd option. If your front end used a command object and you declared the parameter as a DateTime, the conversion from string to date would occur within the front end application and would use whatever the language/locale settings the user has set in windows.

Your "universal date format" may truly be universal for other things (other DB's, applications, etc....), but it is not universal for SQL Server.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

I guess as you say to be true UTC it needs a time, so in just a date format isn't truely ISO 8601 standards compliant.

So the only true common format in UTC style is without the separator! you learn something new every day!

Which actually comes in handy in JS for doing date comparisons when in this YYYYMMDD format as simple integer expessions can be used to see which is bigger/smaller.

Once again many thanks for your wisdom.







"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads