CODEWITHSUNDEEP
sqlsql-serverfunctiont-sql
Monsieur Mark
Monsieur Mark

Reputation: 301

SQL Server function returns different results in two different query panes

Got me flummoxed!: I have a function that I call...

SELECT UNIT 
FROM POWER_ASSETS.[dbo].[returnbaseload] ('03-12-2015','EUR') 
WHERE C_TIC = 'LSE:SOE'

The function "returnbaseload" queries values from a view and does some calcs with the values. Simple. It returns 29 rows.

If I open it up in a new SQL query tab, copy n paste... it returns 533 rows.

If I copy and paste from new tab back to old tab.... 29 rows.

Any ideas? Got me beat.

P.S have also tried putting 

Use POWER_ASSETS
GO

just in case there was a duplicate table accidentally created somewhere in the master...

I am worried because I am calling the function eventually from a vb program and am getting the wrong amount of rows from the sql query in vb. That's what got me investigating... the right amount of rows was from the new tab, 533 rows.

Upvotes: 0

Views: 2172

Answers (1)

jean
jean

Reputation: 4350

There's no way a deterministic select fetches different result sets when using the same parameters. Period.

As comments indicates you must being overloking or missing something.

1 - Be sure both panes are using the same.

[SERVER/INSTANCE].[DATABASE].[SCHEMMA].[TABLE]

it's by far the most common scenario. It also is valid for function/SP calls. Be sure you are calling the same object and not a different version of it.

2 - Be sure both are using the same user/privileges.

Maybe you are using different connection parameters .

3 - Be sure there's no implicit convertion messing with your query.

You are using some sort of varchar to date convertion here. Be sure you got the same settings (collation, copy from a unicode to a UTF-8 archive, etc.) in both tabs. Also you can try to query the table using some sort of GETDATE() function instead of dealing with that varchar literal.

4 - Be sure your data is not changing while you query it.

Stop the server and put it in single user. Maybe your data is just being updated.

5 - Be sure there are not any random function in the query.

Sometimes we got funny BL and someone unintented put some rand logic in it.

6 - Be sure you are not just drunk or tired.

Once I and a friend where working for like +20hrs no stop. He got angry with a buggy "dot" in the screen. Turned ou it was a actual bug (a fly) and also tried to get rid of it with the mouse pointer. Calm down and call a friend to get a look on it.

Upvotes: 2

Related Questions