the following query returns 2 different results on 2 instances of sql server 2008 r2:
create table a(id int) insert a(id) values(1) insert a(id) values(2) select id, (select count(dbo.a.id) dbo.a dbo.a.id = "a"."id") a.id = 1 first machine gives
id ----------- ----------- 1 2 second machine gives
id ----------- ----------- 1 1 we know how fix problem using explicit alias in subquery. because use constructions lot, immense work do. understand problem.
is there maybe option in sql server control behaviour?
2013/07/22:
dbcc useroptions;select @@version; gives
set option value ----------------------------- ---------------- textsize 2147483647 language deutsch dateformat dmy datefirst 1 lock_timeout -1 quoted_identifier set arithabort set ansi_null_dflt_on set ansi_warnings set ansi_padding set ansi_nulls set concat_null_yields_null set isolation level read committed ------------------------------------ microsoft sql server 2008 r2 (sp1) - 10.50.2500.0 (intel x86) jun 17 2011 00:57:23 copyright (c) microsoft corporation enterprise edition on windows nt 6.0 <x86> (build 6002: service pack 2) and
set option value ----------------------------- ---------------- textsize 2147483647 language deutsch dateformat dmy datefirst 1 lock_timeout -1 quoted_identifier set arithabort set ansi_null_dflt_on set ansi_warnings set ansi_padding set ansi_nulls set concat_null_yields_null set isolation level read committed ------------------------------------ microsoft sql server 2008 r2 (rtm) - 10.50.1600.1 (x64) apr 2 2010 15:48:46 copyright (c) microsoft corporation standard edition (64-bit) on windows nt 5.2 <x64> (build 3790: service pack 2) for first server query work how want it.
2013/07/24
it seems not depend on server on databases.
server:
set option value ---------------------------- ---------------------------------------------- textsize 2147483647 language deutsch dateformat dmy datefirst 1 lock_timeout -1 quoted_identifier set arithabort set ansi_null_dflt_on set ansi_warnings set ansi_padding set ansi_nulls set concat_null_yields_null set isolation level read committed (13 zeile(n) betroffen) die dbcc-ausführung wurde abgeschlossen. falls dbcc fehlermeldungen ausgegeben hat, wenden sie sich den systemadministrator. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- microsoft sql server 2008 r2 (sp1) - 10.50.2500.0 (intel x86) jun 17 2011 00:57:23 copyright (c) microsoft corporation enterprise edition on windows nt 6.0 <x86> (build 6002: service pack 2) (1 zeile(n) betroffen) following query:
create table a(id int) insert a(id) values(1) insert a(id) values(2) select * select id, (select count(dbo.a.id) dbo.a dbo.a.id = "a"."id") a.id = 1 drop table select user_name() currentuser; select schema_name() currentschema; first database gives:
id ----------- 1 2 (2 zeile(n) betroffen) id ----------- ----------- 1 2 (1 zeile(n) betroffen) currentuser -------------------------- dbo (1 zeile(n) betroffen) currentschema -------------------------- dbo (1 zeile(n) betroffen) second database gives:
id ----------- 1 2 (2 zeile(n) betroffen) id ----------- ----------- 1 1 (1 zeile(n) betroffen) currentuser ----------------------- dbo (1 zeile(n) betroffen) currentschema ----------------------- dbo (1 zeile(n) betroffen)
the simplest explanation a tables have diff. rows.
otherwise, can't reproduce problem. query references objects without explicit schema (ex. select * a) can give diff. results on same server if
1) query executed diff. users.
2) users mapped diff. schemas (ex. dbo , dbo2).
3) there two(or more) tables/objects same name in diff. schemas (ex: dbo.a , dbo2.a).
run script in ssms sqlcmd mode selected (menu: query > sqlcmd mode):
set nocount on; create database test; go use test; go create table dbo.a(id int); insert dbo.a(id) values (1); go create schema dbo2; go create table dbo2.a(id int); insert dbo2.a(id) values (1),(2); go -- test database has 2 tables [a] diff. schema (dbo, dbo2) use master; go create login login_test_1 password='pas$w0rd', default_database=test; go create login login_test_2 password='pas$w0rd', default_database=test; go use test; go -- user mapped schema [dbo] create user user_test_1 login login_test_1 default_schema=dbo; go grant select on dbo.a user_test_1; go grant select on dbo2.a user_test_1; go -- user mapped schema [dbo2] (!) create user user_test_2 login login_test_2 default_schema=dbo2; go grant select on dbo.a user_test_2; go grant select on dbo2.a user_test_2; go -- test login_test_1 :connect (local)\sql2008r2 -u login_test_1 -p pas$w0rd select user_name() currentuser; select schema_name() currentschema; select * a; go -- test login_test_2 :connect (local)\sql2008r2 -u login_test_2 -p pas$w0rd select user_name() currentuser; select schema_name() currentschema; select * a; go set nocount off; results:
connecting (local)\sql2008r2 login_test_1... currentuser -------------------------------------------------------------------------------------------------------------------------------- user_test_1 currentschema -------------------------------------------------------------------------------------------------------------------------------- dbo id ----------- 1 disconnecting connection (local)\sql2008r2 login_test_1... connecting (local)\sql2008r2 login_test_2... currentuser -------------------------------------------------------------------------------------------------------------------------------- user_test_2 currentschema -------------------------------------------------------------------------------------------------------------------------------- dbo2 id ----------- 1 2 disconnecting connection (local)\sql2008r2 login_test_2... in case, login_test_1 (dbo) gets 1 row login_test_2 (dbo2) gets 2 rows.
cleanup script:
/* drop database test; drop login login_test_1; drop login login_test_2; go */ 
Comments
Post a Comment