Ok, here's a tough one:

I have two nearly identical queries with VERY different execution plans and execution times. This is on SQL 2000.

--QUERY 1:

select A

from tb_1

where B = 'SomeUnicodeString'

and C = {guid'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'}

--QUERY 2:

declare @.B nvarchar(50), @.C UniqueIdentifier

select @.B = 'SomeUnicodeString', @.C = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

Select A

from tb_1

where B = @.B

and C = @.C

Indexes on tb_1

are ndx1 non-unique non-clustered (A,B)

and ndx2 non-unique non-clustered (B,C)

First query takes about 6 seconds. Second query runs in under 1/2 second.

Thoughts anyone?

In the first you might need to make SomeUnicodeString into an Unicode string by changing it to:

B = N'SomeUnicodeString'

This could be the difference, but you really need to look at the plan to know. In query analyzer, look in the query menu and run the queries with the include actual plans option. It should be showing in the plan.

It could be a cached plan issue. Consider flushing the procedure cache with DBCC FREEPROCCACHE and see if that helps too.

|||

Those two queries will be treated different by the query optimizer. SQL Server will try to parameterize the first one, if is not possible, then it will use the constants as they appear in the statement. The query optimizer probably will use the statistics from the histogram in the second index, to estimate the rows matching the filter. For the second one, it will probably use the density of the columns (B, C) from the second index, to estimate the rows matching the filter, and not the histogram, because the processor will not use the variables to analize the statistics.

Can you post both execution plans and the "All density" block from the result of "dbcc show_statistics (tb_1, ndx2)"?

If you want to parameterize the statement, use sp_executesql.

declare @.sql nvarchar(4000)

declare @.B nvarchar(50), @.C UniqueIdentifier

set @.B = N'SomeUnicodeString'

set @.C = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

set @.sql = N'Select A from tb_1 where B = @.B and C = @.C'

exec sp_executesql @.sql, N'@.B nvarchar(50), @.C UniqueIdentifier', @.B, @.C

go

AMB

oh so slow query...

The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?

Thanks in advance. Clive

SELECT
T1.APPT_REPT_FLG,
T18.X_ALIS_ID,
CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
T1.ASGN_USR_EXCLD_FLG,
T2.NAME,
T19.STAT_CD,
T1.APPT_REPT_TYPE,
T15.NAME,
CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
T1.TODO_CD,
T1.X_DOC_CAT_ID,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
T1.TARGET_OU_ID,
T7.ZIPCODE,
T3.ZIPCODE,
T9.EXP_RPT_NUM,
T1.LAST_UPD_BY,
T1.OWNER_PER_ID,
T1.PART_RPR_ID,
T1.RATE_LST_ID,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.ACTIVITY_UID,
T4.NAME,
T1.PR_TMSHT_LINE_ID,
T18.LAST_NAME,
T7.ADDR,
T18.SEX_MF,
T1.BILLABLE_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.SRA_SR_ID,
T1.TARGET_PER_ADDR_ID,
T18.X_FST_NAME,
T1.EVT_STAT_CD,
CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
T1.ROW_STATUS,
T1.ACD_CALL_DURATION,
T5.NAME,
T8.FAX_PH_NUM,
T8.X_FST_NAME,
T8.LAST_NAME,
T1.MODIFICATION_NUM,
T1.X_CAMP_ID,
CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
T1.ASSOCIATED_COST,
T13.NAME,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
T17.TMSHT_NUM,
T1.PR_SYMPTOM_CD,
T1.OPTY_ID,
CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
T1.PR_EXP_RPT_ID,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
T8.FST_NAME,
T16.SR_NUM,
T1.SRA_DEFECT_ID,
T1.CREATED_BY,
T8.WORK_PH_NUM,
CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
T1.CALL_ID,
T1.X_CLIENT_ID,
T1.PROJ_ID,
T12.DEFECT_NUM,
T1.CREATOR_LOGIN,
T1.CONFLICT_ID,
T19.OUTCOME_CD,
T1.TEMPLATE_FLG,
T2.PR_ADDR_ID,
T1.PREV_ACT_ID,
T1.X_DOC_NAME,
T1.EXP_RLTD_FLG,
T1.X_BATCH_REF,
T1.PRI_LST_ID,
T1.SRC_ID,
T1.X_POLICY_REF,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.EMAIL_FORWARD_FLG,
T11.DMT_NUM,
T1.TMSHT_RLTD_FLG,
T1.ROW_ID,
T10.NAME,
T18.CONSUMER_FLG,
T1.TARGET_PER_ID,
T18.FST_NAME,
T1.PRIV_FLG,
T3.PROVINCE,
T8.X_ALIS_ID,
T8.JOB_TITLE,
T14.NAME,
T1.NAME,
T1.PCT_COMPLETE,
T1.SRA_TYPE_CD,
T1.ALARM_FLAG,
T1.CAL_DISP_FLG,
T1.EVT_PRIORITY_CD,
T1.COST_CURCY_CD,
T2.LOC,
CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
T20.FILE_NAME,
T1.SRA_RESOLUTION_CD,
T6.PRDINT_ID,
T1.OWNER_LOGIN
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
WHERE
((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_SR_ID = '1-EQLOO')ummmm...nothing?

Seriously though...I think I'd break it up...

Looks like the driver is the aliased table T1...

Make a dervide table out of that and get it as small as possible, then do your joins...

You need to help us with DDL though...esp. the indexes...

Ya gotta love surrogate keys...|||I think I've realised what's going on. The query in question is generated by the application (app sever probably). However, it's not a stored procedure. The query is exactly the same every time it is executed with one exception - a single id value changes. That will mean compilation every time the query is made I guess. I was noticing this as I was trying out a derived table approach and I was using Profiler to see if any improvements were made. Running any version of the query for the first time in QA took around 2 minutes but subequent exectutions were a few seconds. I am assuming that if the application vendor rewrote that part of their app to call a stored procedure with the id value as a parameter, it would run a lot more quickly without all those compilations.

Thanks,

Clive|||Either sp or prepared statement would do. In either case the plan should be reused, though I'd prefer sp.

Offtopic: "query analyzer" for odbc datasources

I usually do sql-server programming but sometimes I also need to test and query ODBC-datasources with SQL such as Access or Progress databases. I usually do this using active server pages which I find rather obscure, and I would prefer to have a nice little program sort of like query analyzer, where I can specify the connection-string and write my SQL in there somewhere. Does anyone know of such a program?To test and query ODBC-datasources in the manner described one may use Microsoft Query MSQRY32.exe, Sql Plus, Sql *, or a variety of similar tools from packages like ER Studio, ERWin, etc.|||I posted this message in a usenet group aswell and someone came up with a neet little _free_ program that was exactly what I was looking for with alot of nice features...

-> http://www.indus-soft.com/winsql/sql

Offline command hangs when I try to take database offline

Hi, I am relative newbie to SQLServer. When I try to take a user database offline, the query "hangs," with the query processing circle spinning. The Sharepoint 7 application is running on top of the Enterprise SQL Server 2005 db with several logins sleeping and awaiting commands. No errors are generated until I kill the offline command. Anyone have any ideas? Do I need to kill all the connections?Are there any users / connections to the database that you want to take offline? If so, you should ensure that these are disconnected first, as you cannot take a database offline until there are no outstanding connections.

Thanks,|||

Try "alter database <db> set restricted_user with rollback immediate" before setting it offline. This will terminiate user connections and rollback their transactions and only allow priviledged users to connect.

Office Document Properties

If I use the Indexing Services built into the OS, I can query the office
doc properties (title, author etc).
Is it possible to do this if I have an office doc stored in an Image field
that is full-text indexed ?
Steve
It is possible in SQL 2005 - however you can't query for a specific
properties' value. IE I could not query to see if the DocAuthor property was
SteveW, but rather I could query to see if SteveW occurred in the content or
any of the document properties.
AS you are familiar with indexing Services the analog is the ALL property.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve W" <BunnyBoy123@.community.nospam> wrote in message
news:enEwUtxBGHA.3792@.TK2MSFTNGP10.phx.gbl...
> If I use the Indexing Services built into the OS, I can query the office
> doc properties (title, author etc).
> Is it possible to do this if I have an office doc stored in an Image field
> that is full-text indexed ?
> Steve
>

Oddball SQL calculation query - any Gurus up to this?

OK, so I have a situation where the Client gets money from activites based on mileage. The further from 'home', the greater the mileage rate is. The distance per stopover is measured from wither home, or the last stopover, making it a continuous interval.

Dependinig on the load size, the rates will increase or decrease as well.

Assume the following are skeleton table structures.

Table structures assume that appropriate keys are in place as 'Keyfields':

Table1Fields: <Table one holds the actual user-entered data per Size>

Key Keyfield(s), <keys to Table2's Keyfields>

Size Varchar,

DistanceFrom Number, (all distances are in miles)

DistanceTo Number,

Table2 Fields: <Table2 is the table holding the dollar rates per distance interval>

Key Keyfields(s),

Size Varchar,

DistanceFrom Number,

DistanceTo Number

Rate Number (dollar rate per mile )

Business rules:

1. The Table1 data may contain entries such as:

Size = 'A', DistanceFrom = 0, DistanceTo = 110

Size = 'A', DistanceFrom = 110, DistanceTo = 150

Size = 'B', DistanceFrom = 150, DistanceTo = 225, etc.

Note that the distance numbers are continuous, no breaks or gaps. Also note the Size B spans the interval in Table2

2. Table2 always contains unchanging intervals (ie: a lookup table) such as:

Size = 'A', DistanceFrom = 0, DistanceTo = 100, Rate = $10

Size = 'A', DistanceFrom = 100, DistanceTo = 200, Rate = $20, etc...

Size = 'B', DistanceFrom = 0, DistanceTo = 100, Rate = $15

Size = 'B', DistanceFrom = 100, DistanceTo = 200, Rate = $25

Size = 'B', DistanceFrom = 200, DistanceTo = 300, Rate = $30, etc...

Note that the distance numbers are continuous, no breaks or gaps. There may be from one to many rates per size (ie: could only have one interval defined, such as from 0 to 2000 miles, or several intervals, depending on the contract).

3. The max Table1.DistanceTo will never exceed the max Table2.DistanceTo.

4. The object is to calculate the total cost per Table1 intervals, per size given in Table1, using the lookup values in Table2. For example, the total cost for the data in Table1 would be:

Size A, 0 to 100 times $10 per mile = $1000 - plus -

Size A, 100 to 110 times $20 per mile = $200 - plus - (notice that the interval spanned that in Table2, so have to calc using next rate)

Size A, 110 to 155 times $20 per mile = $800 - plus -

Size B, 150 to 200 times $25 per mile = $1250 -plus -

Size B, 200 to 225 miles times $30 per mile = $750 (ditto, the interval spanned Table2 interval)

Total: Size A = $2000

Size B = $2000

===============================

Issue: How do I program this using SQL and not resorting to cursors (curses!)?

I've tried CASE statements, but keep getting bogged down in classic programming mush (ie: if-then thinking). My 'set' hat is crooked today!

Any ideas, given the business rules?

Thanks !

G.

Ok, that's a lot of information. Can you build a table, populate it with data and give us your desired results?|||

Thanks for reply.

My post has data for the simplest table, Table1 being the 'real-world' style of data I get. The calculations shown are for the data in Table1.

Table2 is the kind of lookup rates actually used.

G.

|||

Ok, but could you build something like:

CREATE TABLE table1
(
columns...
)

INSERT INTO table1
...

CREATE TABLE table2
(
columns...
)

INSERT INTO table2
...

I want to see:

table1.column table2.column
- --
x y
x z
z y

This would be really helpful to make us understand your requirements (and to make writing the query easier.)

|||

I believe that I have understood the problem you are trying to solve.

I worked my own example which is shown below. My approach was to first set up the join using the rule for overlapping -- t1.from < t2.to and t2.from < t1.to. Next, the calculation uses the min( t1.to, t2.to ) minus max( t1.from, t2.from ). This is the distance subject to the rate. You can see that I first did the calculation with extra fields then applied the sum.

drop table #table1
drop table #table2

create table #table1(
loadSize char(1) not null,
fromDistance int not null,
toDistance int not null
)

create table #table2(
loadSize char(1) not null,
fromDistance int not null,
toDistance int not null,
rate numeric(10,2) not null
)

insert #table1 values( 'A', 0, 110 )
insert #table1 values( 'A', 110, 200 )
insert #table1 values( 'A', 200, 2000 )
insert #table1 values( 'B', 0, 1100 )
insert #table1 values( 'B', 1100, 2000 )

insert #table2 values( 'A', 0, 100, 10.0 )
insert #table2 values( 'A', 100, 500, 20 )
insert #table2 values( 'A', 500, 2000, 15 )
insert #table2 values( 'B', 0, 2000, 30 )

select * from #table1
select * from #table2

Select t1.loadSize,
t1.fromDistance,
t1.toDistance,
t2.fromDistance,
t2.toDistance,
-- min( t1.toDistance, t2.toDistance )
(Case when t1.toDistance < t2.toDistance then t1.toDistance else t2.toDistance end
-
-- max( t1.fromDistance, t2.fromDistance )
Case when t1.fromDistance < t2.fromDistance then t2.fromDistance else t1.fromDistance end
)
*
rate
From #table1 t1
Join #table2 t2
On t1.loadSize = t2.loadSize
And t1.fromDistance < t2.toDistance
And t2.fromDistance < t1.toDistance

Select t1.loadSize,
t1.fromDistance,
t1.toDistance,
Sum(
-- min( t1.toDistance, t2.toDistance )
(Case when t1.toDistance < t2.toDistance then t1.toDistance else t2.toDistance end
-
-- max( t1.fromDistance, t2.fromDistance )
Case when t1.fromDistance < t2.fromDistance then t2.fromDistance else t1.fromDistance end
)
*
rate)
From #table1 t1
Join #table2 t2
On t1.loadSize = t2.loadSize
And t1.fromDistance < t2.toDistance
And t2.fromDistance < t1.toDistance
Group
By t1.loadSize,
t1.fromDistance,
t1.toDistance

|||

Thanks!

I'll get to try this out tomorrow and let U know what happens - looks like what I need essentially.

G.

sql

Odd results from query

The environment is ASP.Net accessing the SQL server through IIS.
While, I've been told that .Net does not create a cache the behavior I'm
observing for intensive purposes looks as if there is a cache. I'm at a bit
of a loss to explain this behavior. Also, if I wait five or ten minutes, and
refresh I get the correct value.
I have a stored procdure that returns a series of rows, from which I sum a
particular number.
There is an action which causes another row to be added.
However, when I make this change to the database the change is not returned
in a query immediatly following the update. I have simultaneously run sql
query analyzer and verified that the table was updated correctly.
When the form is first displayed and then shortly after I perfomr the update
the value is recalculated via the following mechanism:
Using the global connection string a new SqlConnection object is built. Then
a new SqlCommand object is built using the new SqlConnection. The command
type is stored procedure. After the SqlCommand is setup (the command text and
parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
The SqlDataAdapter fills a new DataSet, via the Fill method.
When I say that the object is new I mean that literally, as in
SqlCommand cmd = new SqlCommand (ConnectionString);
I would very much like to get the correct result right away.
Thanks,
Dave
I think you should share more details about the nature of your stored proc.
Are any parameters being passed to the procedure? When you say 5 or 10
minutes have passed before it returns the correct sum, are you using the same
active browser window, or are you starting a new browser (thus starting a new
session, and perhaps refreshing the state with the server, thus passing new
parameters to the procedure).
Just a guess - I'm probably way off and you probably already thought of all
that
Brian
"Dave" wrote:

> The environment is ASP.Net accessing the SQL server through IIS.
> While, I've been told that .Net does not create a cache the behavior I'm
> observing for intensive purposes looks as if there is a cache. I'm at a bit
> of a loss to explain this behavior. Also, if I wait five or ten minutes, and
> refresh I get the correct value.
> I have a stored procdure that returns a series of rows, from which I sum a
> particular number.
> There is an action which causes another row to be added.
> However, when I make this change to the database the change is not returned
> in a query immediatly following the update. I have simultaneously run sql
> query analyzer and verified that the table was updated correctly.
> When the form is first displayed and then shortly after I perfomr the update
> the value is recalculated via the following mechanism:
> Using the global connection string a new SqlConnection object is built. Then
> a new SqlCommand object is built using the new SqlConnection. The command
> type is stored procedure. After the SqlCommand is setup (the command text and
> parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
> The SqlDataAdapter fills a new DataSet, via the Fill method.
> When I say that the object is new I mean that literally, as in
> SqlCommand cmd = new SqlCommand (ConnectionString);
> I would very much like to get the correct result right away.
> Thanks,
> Dave
sql

Odd query result in SQL server 2000

Hi,
I am running SQL Server 2000, SP4.
I have stored procedure with a query like this:
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (...list of IDs)
AND PE.EDU_ID=1
By running the stored procedure this query gives the result 221, -1
This result is wrong.
I run EXACTLY the same query using query analyzer and that gives the
result 199, -1
Which is correct
How is this possible? I am running the both the SP and the query under
the same account.
I have tried to drop the SP and re-create it. I have tried to re-
compile the SP. Nothing seems to help.
Any ideas?
Thanks.
Best regards,
EirikUse the Show Actual Query Plan feature to see what both queries are doing.
Also, is the list of IDs a parameter in the sproc?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Eiriken" <eirik@.oslo.online.no> wrote in message
news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> Hi,
> I am running SQL Server 2000, SP4.
> I have stored procedure with a query like this:
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (...list of IDs)
> AND PE.EDU_ID=1
> By running the stored procedure this query gives the result 221, -1
> This result is wrong.
> I run EXACTLY the same query using query analyzer and that gives the
> result 199, -1
> Which is correct
> How is this possible? I am running the both the SP and the query under
> the same account.
> I have tried to drop the SP and re-create it. I have tried to re-
> compile the SP. Nothing seems to help.
> Any ideas?
> Thanks.
> Best regards,
> Eirik|||Thank you for answering
The list of IDs is from a temporary table created in the same SP.
Looking at the execution plan shows that indeed there is a difference
between running the queries in the SP and in the Query Analyzer. The
"messages" tab also shows a difference. By running the queries in
query analyzer shows "23 rows affected and 1 rows affected". Running
the queries in a SP executed in query analyzer shows "23 rows
affected, 5 rows affected and 1 rows affected". Where the 5 rows come
from I don't know.
I have stripped down the queries and I am pasting the query here.
After stripping the result is the still wrong as earlier.
CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
NumberFullTime int, NumberPartTime int, NumberTotal int)
INSERT INTO #Tmp_Countries
SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
(5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
CountryId>=0)
AND PE.EDU_ID=1
DROP TABLE #Tmp_Countries
On 24 Nov, 17:39, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Use the Show Actual Query Plan feature to see what both queries are doing.
> Also, is the list of IDs a parameter in the sproc?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Eiriken" <ei...@.oslo.online.no> wrote in message
> news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> > Hi,
> > I am running SQL Server 2000, SP4.
> > I have stored procedure with a query like this:
> > SELECT COUNT(*), -1
> > FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> > PM.PER_ID=PU.PER_ID
> > LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> > WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> > AND PU.CNT_ID NOT IN (...list of IDs)
> > AND PE.EDU_ID=1
> > By running the stored procedure this query gives the result 221, -1
> > This result is wrong.
> > I run EXACTLY the same query using query analyzer and that gives the
> > result 199, -1
> > Which is correct
> > How is this possible? I am running the both the SP and the query under
> > the same account.
> > I have tried to drop the SP and re-create it. I have tried to re-
> > compile the SP. Nothing seems to help.
> > Any ideas?
> > Thanks.
> > Best regards,
> > Eirik|||Eiriken <eirik@.oslo.online.no> wrote in
news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
> Thank you for answering
> The list of IDs is from a temporary table created in the same SP.
> Looking at the execution plan shows that indeed there is a difference
> between running the queries in the SP and in the Query Analyzer. The
> "messages" tab also shows a difference. By running the queries in
> query analyzer shows "23 rows affected and 1 rows affected". Running
> the queries in a SP executed in query analyzer shows "23 rows
> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> from I don't know.
> I have stripped down the queries and I am pasting the query here.
> After stripping the result is the still wrong as earlier.
> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> NumberFullTime int, NumberPartTime int, NumberTotal int)
> INSERT INTO #Tmp_Countries
> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> CountryId>=0)
> AND PE.EDU_ID=1
> DROP TABLE #Tmp_Countries
Possibly the problem is with one or more PU/PE columns in the WHERE clause
being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the whole
of the WHERE clause will evaluate to NULL.
Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
HTH|||NULLs are my first guess for this too. They could cause problems because
settings are different between direct execution and the sproc settings'
execution context.
Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
it's owner/schema. Not only is this more efficient - there are scenarios
where it can lead to the wrong results too.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns99F2D9D92765DChrisCheneytesconet@.80.5.182.99...
> Eiriken <eirik@.oslo.online.no> wrote in
> news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
>> Thank you for answering
>> The list of IDs is from a temporary table created in the same SP.
>> Looking at the execution plan shows that indeed there is a difference
>> between running the queries in the SP and in the Query Analyzer. The
>> "messages" tab also shows a difference. By running the queries in
>> query analyzer shows "23 rows affected and 1 rows affected". Running
>> the queries in a SP executed in query analyzer shows "23 rows
>> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
>> from I don't know.
>> I have stripped down the queries and I am pasting the query here.
>> After stripping the result is the still wrong as earlier.
>> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
>> NumberFullTime int, NumberPartTime int, NumberTotal int)
>> INSERT INTO #Tmp_Countries
>> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
>> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
>> SELECT COUNT(*), -1
>> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
>> PM.PER_ID=PU.PER_ID
>> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
>> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
>> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
>> CountryId>=0)
>> AND PE.EDU_ID=1
>> DROP TABLE #Tmp_Countries
> Possibly the problem is with one or more PU/PE columns in the WHERE clause
> being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> whole
> of the WHERE clause will evaluate to NULL.
> Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> HTH|||I started investigating the case more carefully and compared the
result to see what was really the difference.
The answer was exactly as you proposed. The settings were indeed
different and the NULLs were the reason for the different result.
Thank you Kevin and Chris.
On 25 Nov, 00:05, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> NULLs are my first guess for this too. They could cause problems because
> settings are different between direct execution and the sproc settings'
> execution context.
> Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
> it's owner/schema. Not only is this more efficient - there are scenarios
> where it can lead to the wrong results too.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Chris.Cheney" <Chris.CheneyXXNOSPA...@.tesco.net> wrote in message
> news:Xns99F2D9D92765DChrisCheneytesconet@.80.5.182.99...
> > Eiriken <ei...@.oslo.online.no> wrote in
> >news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
> >> Thank you for answering
> >> The list of IDs is from a temporary table created in the same SP.
> >> Looking at the execution plan shows that indeed there is a difference
> >> between running the queries in the SP and in the Query Analyzer. The
> >> "messages" tab also shows a difference. By running the queries in
> >> query analyzer shows "23 rows affected and 1 rows affected". Running
> >> the queries in a SP executed in query analyzer shows "23 rows
> >> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> >> from I don't know.
> >> I have stripped down the queries and I am pasting the query here.
> >> After stripping the result is the still wrong as earlier.
> >> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> >> NumberFullTime int, NumberPartTime int, NumberTotal int)
> >> INSERT INTO #Tmp_Countries
> >> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> >> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
> >> SELECT COUNT(*), -1
> >> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> >> PM.PER_ID=PU.PER_ID
> >> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> >> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> >> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> >> CountryId>=0)
> >> AND PE.EDU_ID=1
> >> DROP TABLE #Tmp_Countries
> > Possibly the problem is with one or more PU/PE columns in the WHERE clause
> > being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> > whole
> > of the WHERE clause will evaluate to NULL.
> > Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> > HTH

Odd query result in SQL server 2000

Hi,
I am running SQL Server 2000, SP4.
I have stored procedure with a query like this:
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (...list of IDs)
AND PE.EDU_ID=1
By running the stored procedure this query gives the result 221, -1
This result is wrong.
I run EXACTLY the same query using query analyzer and that gives the
result 199, -1
Which is correct
How is this possible? I am running the both the SP and the query under
the same account.
I have tried to drop the SP and re-create it. I have tried to re-
compile the SP. Nothing seems to help.
Any ideas?
Thanks.
Best regards,
Eirik
Use the Show Actual Query Plan feature to see what both queries are doing.
Also, is the list of IDs a parameter in the sproc?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Eiriken" <eirik@.oslo.online.no> wrote in message
news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> Hi,
> I am running SQL Server 2000, SP4.
> I have stored procedure with a query like this:
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (...list of IDs)
> AND PE.EDU_ID=1
> By running the stored procedure this query gives the result 221, -1
> This result is wrong.
> I run EXACTLY the same query using query analyzer and that gives the
> result 199, -1
> Which is correct
> How is this possible? I am running the both the SP and the query under
> the same account.
> I have tried to drop the SP and re-create it. I have tried to re-
> compile the SP. Nothing seems to help.
> Any ideas?
> Thanks.
> Best regards,
> Eirik
|||Thank you for answering
The list of IDs is from a temporary table created in the same SP.
Looking at the execution plan shows that indeed there is a difference
between running the queries in the SP and in the Query Analyzer. The
"messages" tab also shows a difference. By running the queries in
query analyzer shows "23 rows affected and 1 rows affected". Running
the queries in a SP executed in query analyzer shows "23 rows
affected, 5 rows affected and 1 rows affected". Where the 5 rows come
from I don't know.
I have stripped down the queries and I am pasting the query here.
After stripping the result is the still wrong as earlier.
CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
NumberFullTime int, NumberPartTime int, NumberTotal int)
INSERT INTO #Tmp_Countries
SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
(5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
CountryId>=0)
AND PE.EDU_ID=1
DROP TABLE #Tmp_Countries
On 24 Nov, 17:39, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Use the Show Actual Query Plan feature to see what both queries are doing.
> Also, is the list of IDs a parameter in the sproc?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Eiriken" <ei...@.oslo.online.no> wrote in message
> news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
>
>
>
>
|||Eiriken <eirik@.oslo.online.no> wrote in
news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:

> Thank you for answering
> The list of IDs is from a temporary table created in the same SP.
> Looking at the execution plan shows that indeed there is a difference
> between running the queries in the SP and in the Query Analyzer. The
> "messages" tab also shows a difference. By running the queries in
> query analyzer shows "23 rows affected and 1 rows affected". Running
> the queries in a SP executed in query analyzer shows "23 rows
> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> from I don't know.
> I have stripped down the queries and I am pasting the query here.
> After stripping the result is the still wrong as earlier.
> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> NumberFullTime int, NumberPartTime int, NumberTotal int)
> INSERT INTO #Tmp_Countries
> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> CountryId>=0)
> AND PE.EDU_ID=1
> DROP TABLE #Tmp_Countries
Possibly the problem is with one or more PU/PE columns in the WHERE clause
being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the whole
of the WHERE clause will evaluate to NULL.
Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
HTH
|||NULLs are my first guess for this too. They could cause problems because
settings are different between direct execution and the sproc settings'
execution context.
Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
it's owner/schema. Not only is this more efficient - there are scenarios
where it can lead to the wrong results too.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns99F2D9D92765DChrisCheneytesconet@.80.5.182. 99...
> Eiriken <eirik@.oslo.online.no> wrote in
> news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
>
> Possibly the problem is with one or more PU/PE columns in the WHERE clause
> being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> whole
> of the WHERE clause will evaluate to NULL.
> Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> HTH
|||I started investigating the case more carefully and compared the
result to see what was really the difference.
The answer was exactly as you proposed. The settings were indeed
different and the NULLs were the reason for the different result.
Thank you Kevin and Chris.
On 25 Nov, 00:05, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> NULLs are my first guess for this too. They could cause problems because
> settings are different between direct execution and the sproc settings'
> execution context.
> Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
> it's owner/schema. Not only is this more efficient - there are scenarios
> where it can lead to the wrong results too.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Chris.Cheney" <Chris.CheneyXXNOSPA...@.tesco.net> wrote in message
> news:Xns99F2D9D92765DChrisCheneytesconet@.80.5.182. 99...
>
>
>
>
>

Odd query result in SQL server 2000

Hi,
I am running SQL Server 2000, SP4.
I have stored procedure with a query like this:
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (...list of IDs)
AND PE.EDU_ID=1
By running the stored procedure this query gives the result 221, -1
This result is wrong.
I run EXACTLY the same query using query analyzer and that gives the
result 199, -1
Which is correct
How is this possible? I am running the both the SP and the query under
the same account.
I have tried to drop the SP and re-create it. I have tried to re-
compile the SP. Nothing seems to help.
Any ideas?
Thanks.
Best regards,
EirikUse the Show Actual Query Plan feature to see what both queries are doing.
Also, is the list of IDs a parameter in the sproc?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Eiriken" <eirik@.oslo.online.no> wrote in message
news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> Hi,
> I am running SQL Server 2000, SP4.
> I have stored procedure with a query like this:
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (...list of IDs)
> AND PE.EDU_ID=1
> By running the stored procedure this query gives the result 221, -1
> This result is wrong.
> I run EXACTLY the same query using query analyzer and that gives the
> result 199, -1
> Which is correct
> How is this possible? I am running the both the SP and the query under
> the same account.
> I have tried to drop the SP and re-create it. I have tried to re-
> compile the SP. Nothing seems to help.
> Any ideas?
> Thanks.
> Best regards,
> Eirik|||Thank you for answering
The list of IDs is from a temporary table created in the same SP.
Looking at the execution plan shows that indeed there is a difference
between running the queries in the SP and in the Query Analyzer. The
"messages" tab also shows a difference. By running the queries in
query analyzer shows "23 rows affected and 1 rows affected". Running
the queries in a SP executed in query analyzer shows "23 rows
affected, 5 rows affected and 1 rows affected". Where the 5 rows come
from I don't know.
I have stripped down the queries and I am pasting the query here.
After stripping the result is the still wrong as earlier.
CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
NumberFullTime int, NumberPartTime int, NumberTotal int)
INSERT INTO #Tmp_Countries
SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
(5,69,149,8,6,88,2,7,79,89,80,34,83,82,8
5,65,71, 73,86,87,1,68,190)
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
CountryId>=0)
AND PE.EDU_ID=1
DROP TABLE #Tmp_Countries
On 24 Nov, 17:39, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Use the Show Actual Query Plan feature to see what both queries are doing.
> Also, is the list of IDs a parameter in the sproc?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Eiriken" <ei...@.oslo.online.no> wrote in message
> news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>|||Eiriken <eirik@.oslo.online.no> wrote in
news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:

> Thank you for answering
> The list of IDs is from a temporary table created in the same SP.
> Looking at the execution plan shows that indeed there is a difference
> between running the queries in the SP and in the Query Analyzer. The
> "messages" tab also shows a difference. By running the queries in
> query analyzer shows "23 rows affected and 1 rows affected". Running
> the queries in a SP executed in query analyzer shows "23 rows
> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> from I don't know.
> I have stripped down the queries and I am pasting the query here.
> After stripping the result is the still wrong as earlier.
> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> NumberFullTime int, NumberPartTime int, NumberTotal int)
> INSERT INTO #Tmp_Countries
> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,8
5,65,71, 73,86,87,1,68,190)
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> CountryId>=0)
> AND PE.EDU_ID=1
> DROP TABLE #Tmp_Countries
Possibly the problem is with one or more PU/PE columns in the WHERE clause
being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the whole
of the WHERE clause will evaluate to NULL.
Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
HTH|||NULLs are my first guess for this too. They could cause problems because
settings are different between direct execution and the sproc settings'
execution context.
Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
it's owner/schema. Not only is this more efficient - there are scenarios
where it can lead to the wrong results too.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns99F2D9D92765DChrisCheneytesconet
@.80.5.182.99...
> Eiriken <eirik@.oslo.online.no> wrote in
> news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
>
> Possibly the problem is with one or more PU/PE columns in the WHERE clause
> being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> whole
> of the WHERE clause will evaluate to NULL.
> Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> HTH|||I started investigating the case more carefully and compared the
result to see what was really the difference.
The answer was exactly as you proposed. The settings were indeed
different and the NULLs were the reason for the different result.
Thank you Kevin and Chris.
On 25 Nov, 00:05, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> NULLs are my first guess for this too. They could cause problems because
> settings are different between direct execution and the sproc settings'
> execution context.
> Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
> it's owner/schema. Not only is this more efficient - there are scenarios
> where it can lead to the wrong results too.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Chris.Cheney" <Chris.CheneyXXNOSPA...@.tesco.net> wrote in message
> news:Xns99F2D9D92765DChrisCheneytesconet
@.80.5.182.99...
>
>
>
>
>
>
>
>
>
>sql

Odd query problem

All,
Sql 2000, sp3, Server 2000 sp4
I have 2 servers, a production and a warm standby that I
use for some reporting. The problem I am having is that on
my production server, execution plan is showing a 55% use
for a sort. That same sort is not on the standby server.
The standby completes queries in 10% of the time on
production. They are not identical servers, my production
is a much faster machine. This problem has just started in
the last week. I have done update statistics for all the
tables in the db. I do indexdefrags about once a month, my
db is 350 gbs in size. There is one table that I have not
touched at all yet, there are 1.5 billion rows in this
table. Any other ideas, before I tackle this table.
Interesting note, if I do a select top 100 on production
the ep is similar to the one on my standby. Any reasons?
Thanks in advance all,
Gary AbbottProbably due to other load and use on your production server. You can try
clearing data and procedure cache on the production box (NOTE: This may
cause a severe performance bottleneck if your server is experiencing any
kind of load. Do this during way-off-peak hours only).
To clear data cache: DBCC DROPCLEANBUFFERS
To clear procedure cache: DBCC FREEPROCCACHE
If you are comparing query performance on two different systems, this is the
only way to make sure current cache state doesn't overly influience the
outcomes.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:ed1901c3f0c0$0fe1f700$a501280a@.phx.gbl...
> All,
> Sql 2000, sp3, Server 2000 sp4
> I have 2 servers, a production and a warm standby that I
> use for some reporting. The problem I am having is that on
> my production server, execution plan is showing a 55% use
> for a sort. That same sort is not on the standby server.
> The standby completes queries in 10% of the time on
> production. They are not identical servers, my production
> is a much faster machine. This problem has just started in
> the last week. I have done update statistics for all the
> tables in the db. I do indexdefrags about once a month, my
> db is 350 gbs in size. There is one table that I have not
> touched at all yet, there are 1.5 billion rows in this
> table. Any other ideas, before I tackle this table.
> Interesting note, if I do a select top 100 on production
> the ep is similar to the one on my standby. Any reasons?
> Thanks in advance all,
> Gary Abbott|||you need to provide more info on the query, relevent
indexes in each db, and the execution plan details,
if there is a difference in the execution plan, then
either one has difference indexes or the statistics are
different, what does the execution plan show for estimated
row count in each case?
>--Original Message--
>All,
>Sql 2000, sp3, Server 2000 sp4
>I have 2 servers, a production and a warm standby that I
>use for some reporting. The problem I am having is that
on
>my production server, execution plan is showing a 55% use
>for a sort. That same sort is not on the standby server.
>The standby completes queries in 10% of the time on
>production. They are not identical servers, my production
>is a much faster machine. This problem has just started
in
>the last week. I have done update statistics for all the
>tables in the db. I do indexdefrags about once a month,
my
>db is 350 gbs in size. There is one table that I have not
>touched at all yet, there are 1.5 billion rows in this
>table. Any other ideas, before I tackle this table.
>Interesting note, if I do a select top 100 on production
>the ep is similar to the one on my standby. Any reasons?
>Thanks in advance all,
>Gary Abbott
>.
>

Odd query problem

All,
Sql 2000, sp3, Server 2000 sp4
I have 2 servers, a production and a warm standby that I
use for some reporting. The problem I am having is that on
my production server, execution plan is showing a 55% use
for a sort. That same sort is not on the standby server.
The standby completes queries in 10% of the time on
production. They are not identical servers, my production
is a much faster machine. This problem has just started in
the last week. I have done update statistics for all the
tables in the db. I do indexdefrags about once a month, my
db is 350 gbs in size. There is one table that I have not
touched at all yet, there are 1.5 billion rows in this
table. Any other ideas, before I tackle this table.
Interesting note, if I do a select top 100 on production
the ep is similar to the one on my standby. Any reasons?
Thanks in advance all,
Gary AbbottProbably due to other load and use on your production server. You can try
clearing data and procedure cache on the production box (NOTE: This may
cause a severe performance bottleneck if your server is experiencing any
kind of load. Do this during way-off-peak hours only).
To clear data cache: DBCC DROPCLEANBUFFERS
To clear procedure cache: DBCC FREEPROCCACHE
If you are comparing query performance on two different systems, this is the
only way to make sure current cache state doesn't overly influience the
outcomes.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:ed1901c3f0c0$0fe1f700$a501280a@.phx.gbl...
> All,
> Sql 2000, sp3, Server 2000 sp4
> I have 2 servers, a production and a warm standby that I
> use for some reporting. The problem I am having is that on
> my production server, execution plan is showing a 55% use
> for a sort. That same sort is not on the standby server.
> The standby completes queries in 10% of the time on
> production. They are not identical servers, my production
> is a much faster machine. This problem has just started in
> the last week. I have done update statistics for all the
> tables in the db. I do indexdefrags about once a month, my
> db is 350 gbs in size. There is one table that I have not
> touched at all yet, there are 1.5 billion rows in this
> table. Any other ideas, before I tackle this table.
> Interesting note, if I do a select top 100 on production
> the ep is similar to the one on my standby. Any reasons?
> Thanks in advance all,
> Gary Abbott

Odd query plan for view

I have a SQL 2000 table containing 2 million rows of Trade data. Here
are some of the columns:

[TradeId] INT IDENTITY(1,1) -- PK, non-clustered
[LoadDate] DATETIME -- clustered index
[TradeDate] DATETIME -- non-clustered index
[Symbol] VARCHAR(10)
[Account] VARCHAR(10)
[Position] INT
etc..

I have a view which performs a join against a security master table (to
gather more security data). The purpose of the view is to return all
the rows where [TradeDate] is within the last trading days.

The query against the view takes over around 30 minutes. When I view
the query plan, it is not using the index on the [TradeDate] column but
is instead using the clustered index on the [LoadDate] column... The
odd thing is, the [LoadDate] column is not used anywhere in the view!

For testing purposes, I decided to do a straight SELECT against the
table (minus the joins) and that one ALSO uses the clustered index scan
against a column not referenced anywhere in the query.

There is a reason why I have not posted my WHERE clause until now. The
reason is that I am doing what I think is a very inefficient clause:

WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())

The function calculates the proper trade date based on the specified
date (in this case, the current date). It is my understanding that the
function will be called for all rows. (Which COULD explain the
performance issue...)

However, this view has been around for ages and never before caused any
sort of problems. The issue actually started the day after I had to
recreate the table. (I had to recreate the table because some columns
where added and others where renamed.)

On a side note, if I replace the WHERE clause with a hard-coded date
(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine but
STILL uses the clustered index on the [LoadDate] column.(JayCallas@.hotmail.com) writes:
> The query against the view takes over around 30 minutes. When I view
> the query plan, it is not using the index on the [TradeDate] column but
> is instead using the clustered index on the [LoadDate] column... The
> odd thing is, the [LoadDate] column is not used anywhere in the view!

But "Clustered index scan" is just the same as "Table Scan". So it is
not very strange. No non-clustered index was good, so it scans the
index.

> There is a reason why I have not posted my WHERE clause until now. The
> reason is that I am doing what I think is a very inefficient clause:
> WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())
> However, this view has been around for ages and never before caused any
> sort of problems. The issue actually started the day after I had to
> recreate the table. (I had to recreate the table because some columns
> where added and others where renamed.)

Statistics change, and old plan was not good any more. Yes, the above
is a problematic condition. Don't you read this newsgroup? :-) I
answered a very similar question last night.

You know something about the data that the optimizer does not. It
sees:

WHERE TradeDate > <UnknownValue
It estimates that it will hit 30% of the rows, a standard assumption.
And for 30% hit-rate a non-clustered index will be more expensive
than scanning the table.

This may be the place for an index hint See also the thread
"ranged datetime predicates & cardinality estimates" from yeaterday.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland for responding. As usual your posts are very helpful.

So, a Clustered Index Scan is as bad as a Table Scan? In this case,
what is it actually checking? The LoadDate is not even used in the
query so I am not sure what it is scanning for. Is it just using the
scan to look up the rest of the row?

As I already knew that the particular WHERE clause was bad, was I just
lucky all this time that the response time was good? Maybe because
there had not been any changes to the view or table in ages? Or maybe
because the plan was determined when there was a lot less data in the
table? Do query plans survive server reboots or restarting SQL?

Will take a look at the index hint any see how it goes.|||(JayCallas@.hotmail.com) writes:
> So, a Clustered Index Scan is as bad as a Table Scan? In this case,
> what is it actually checking? The LoadDate is not even used in the
> query so I am not sure what it is scanning for. Is it just using the
> scan to look up the rest of the row?

Let's say that you need to look up Michael Richardson in the telephone
book. Of course you open the book on R and quickly find him. You are seeking
the clustered index.

But say now that you are looking for someone whose first name is Jake,
and that he lives on Smallstreet, and you really need to find him. What
do you do? You read the phone book from start to end, that is you scan
the clustred index. The LastName, which is the key in the index is not
part of the search, but that is irrelevant.

> As I already knew that the particular WHERE clause was bad, was I just
> lucky all this time that the response time was good?

Bad is a little too strong a word. Problematic is more accurate.

The problem with a non-clustered index, is that if you get many hits,
and you for every hit you need to access the data page, you will do more
reads that you do, if you just scan the table from left to right.

>Do query plans survive server reboots or restarting SQL?

No. The plan is in cache only, and could also disappear during run-time,
if the plan is aged out.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The hint worked. Thanks.

Odd pivot table type query

I've been unable to find a way to write the following
query.
Assuming this table:
Year Quarter
-- --
1990 1
1990 2
1990 3
1990 4
1991 1
1991 2
1991 3
1991 4
Does anyone know how to write an SQL query to generate the
following results?
Year Quarter
-- --
1990 1,2,3,4
1991 1,2,3,4
For documented method, you will have to make use of procedural code to
achieve this. See following example. OR you will have to use some 3rd party
tool to do it(www.rac4sql.com).
Eg:
-- sample table
-- if object_id('tab') is not null
-- drop table tab
create table tab(ID int,
SEQ_NUM int ,
ROUTE varchar(50))
go
-- sample data
insert into tab values(1 ,1 ,'AA')
insert into tab values(1 ,2 ,'BB')
insert into tab values(1 ,3 ,'CC')
insert into tab values(2 ,1 ,'AA')
insert into tab values(3 ,1 ,'VV')
insert into tab values(3 ,2 ,'XX')
go
-- t-sql code for generating report
if object_id('tempdb..#tmp') is not null
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int
declare @.seq_num int
declare @.route varchar(50), @.f_route varchar(50)
select @.id=0, @.seq_num=0, @.route='', @.f_route=''
while @.id is not null
begin
select @.id=min(id) from tab where id > @.id
while @.seq_num is not null
begin
select @.seq_num=min(seq_num), @.route=min(route)from tab where id = @.id
and seq_num > @.seq_num
If @.seq_num is null and @.id is not null
insert into #tmp values(@.id, @.f_route)
select @.f_route = @.f_route + case @.f_route when '' then '' else ','
end + @.route
end
select @.seq_num=0, @.f_route=''
end
select * from #tmp
truncate table #tmp
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks for the response Vishal. Unfortunatly this
solution isn't going to work for me since I dealing with a
very large number of records the performace of populating
a new table is going to be too great.
I'm adding a "download" feature for the data so this would
result is a large number of records being retuned and
written to file.
What would be great is is I could use somethine like a
GROUP BY and SUM where the SUM would append strings
together.
SELECT year, APPEND(Quarter)
FROM mytable
GROUP BY year

>--Original Message--
>For documented method, you will have to make use of
procedural code to
>achieve this. See following example. OR you will have to
use some 3rd party
>tool to do it(www.rac4sql.com).
>Eg:
>-- sample table
>-- if object_id('tab') is not null
>-- drop table tab
>create table tab(ID int,
>SEQ_NUM int ,
>ROUTE varchar(50))
>go
>-- sample data
>insert into tab values
(1 ,1 ,'AA')
>insert into tab values
(1 ,2 ,'BB')
>insert into tab values
(1 ,3 ,'CC')
>insert into tab values
(2 ,1 ,'AA')
>insert into tab values
(3 ,1 ,'VV')
>insert into tab values
(3 ,2 ,'XX')
>go
>-- t-sql code for generating report
>if object_id('tempdb..#tmp') is not null
>drop table #tmp
>create table #tmp(id int, tmpval varchar(50))
>go
>declare @.id int
>declare @.seq_num int
>declare @.route varchar(50), @.f_route varchar(50)
>select @.id=0, @.seq_num=0, @.route='', @.f_route=''
>while @.id is not null
>begin
> select @.id=min(id) from tab where id > @.id
> while @.seq_num is not null
> begin
> select @.seq_num=min(seq_num), @.route=min(route)from
tab where id = @.id
> and seq_num > @.seq_num
> If @.seq_num is null and @.id is not null
> insert into #tmp values(@.id, @.f_route)
> select @.f_route = @.f_route + case @.f_route when ''
then '' else ','
>end + @.route
> end
> select @.seq_num=0, @.f_route=''
>end
>select * from #tmp
>truncate table #tmp
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Shawn,
There is no function as such which will do the things for you.
You may try following approach using User defined function. But remember,
this is not a documented method, so it can not be reliable under all
cirumstances.
CREATE FUNCTION EmpPhones (@.ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.str VARCHAR(1000)
SELECT @.str = ISNULL(@.str + ',', '') + cats
FROM tab WHERE id = @.ID
RETURN (@.str)
END
-- sample data / result set.
if object_id ('tab') is not null
drop table tab
go
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'1-001')
insert into tab values(1 ,'2-002')
insert into tab values(1 ,'3-003')
insert into tab values(2 ,'1-011')
insert into tab values(3 ,'1-012')
insert into tab values(3 ,'2-022')
go
--And then you would call this UDF from within a SELECT statement, as
follows:
select distinct id,dbo.empphones(id) 'comma seperated value' from tab
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

odd permissions problems

It has always been my understanding that if I run something from Query
Ananlyzer, it was run under the account that Im logged into it as? And that
if I run that same code in a job, it would be run under the account that SQL
Server Agent was run as? So heres what happened while logged into QA as a
Domain Admin:
1. I tried to Restore a db from a backup and it said "Access is denied" in
the Error Log.
2. I assigned Full Control to the SQL Server Agent account (not a Domain
Admin) on the folder that contains the backup.
3. Now, still logged into QA as the Domain Admin, the Restore works.
Why would this be? Do Restores actually run under the account that the Agent
runs under?
--
SQL2K SP3
TIA, ChrisR> Why would this be? Do Restores actually run under the account that the Agent
> runs under?
No, it uses the account that SQL Server uses. Which are probably the same, which in turn explains
what you experienced-
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ChrisR" <bla@.noemail.com> wrote in message news:OlC4FamJFHA.220@.TK2MSFTNGP10.phx.gbl...
> It has always been my understanding that if I run something from Query
> Ananlyzer, it was run under the account that Im logged into it as? And that
> if I run that same code in a job, it would be run under the account that SQL
> Server Agent was run as? So heres what happened while logged into QA as a
> Domain Admin:
> 1. I tried to Restore a db from a backup and it said "Access is denied" in
> the Error Log.
> 2. I assigned Full Control to the SQL Server Agent account (not a Domain
> Admin) on the folder that contains the backup.
> 3. Now, still logged into QA as the Domain Admin, the Restore works.
>
> Why would this be? Do Restores actually run under the account that the Agent
> runs under?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Tibor you are correct, SQL Server and the Agent are the same account. But
why would it use either of those accounts and not who I'm logged into QA as?
When else does this occur? Is this just a Restore thing?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oc$BTVnJFHA.1392@.TK2MSFTNGP10.phx.gbl...
> > Why would this be? Do Restores actually run under the account that the
Agent
> > runs under?
> No, it uses the account that SQL Server uses. Which are probably the same,
which in turn explains
> what you experienced-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "ChrisR" <bla@.noemail.com> wrote in message
news:OlC4FamJFHA.220@.TK2MSFTNGP10.phx.gbl...
> > It has always been my understanding that if I run something from Query
> > Ananlyzer, it was run under the account that Im logged into it as? And
that
> > if I run that same code in a job, it would be run under the account that
SQL
> > Server Agent was run as? So heres what happened while logged into QA as
a
> > Domain Admin:
> >
> > 1. I tried to Restore a db from a backup and it said "Access is denied"
in
> > the Error Log.
> > 2. I assigned Full Control to the SQL Server Agent account (not a Domain
> > Admin) on the folder that contains the backup.
> > 3. Now, still logged into QA as the Domain Admin, the Restore works.
> >
> >
> > Why would this be? Do Restores actually run under the account that the
Agent
> > runs under?
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >
>|||Think of it from a technical viewpoint. It is SQL Server who reads the file to restore. For SQL
Server to open the file in *your* account, something we call "delegation" is needed. Delegation is
supported in Windows, with some configuration first. The application (SQL Server in this case) need
to be programmed for it, though. SQL Server supports delegation for some operations (linked
servers).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ChrisR" <bla@.noemail.com> wrote in message news:%232PndSoJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Tibor you are correct, SQL Server and the Agent are the same account. But
> why would it use either of those accounts and not who I'm logged into QA as?
> When else does this occur? Is this just a Restore thing?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oc$BTVnJFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> > Why would this be? Do Restores actually run under the account that the
> Agent
>> > runs under?
>> No, it uses the account that SQL Server uses. Which are probably the same,
> which in turn explains
>> what you experienced-
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "ChrisR" <bla@.noemail.com> wrote in message
> news:OlC4FamJFHA.220@.TK2MSFTNGP10.phx.gbl...
>> > It has always been my understanding that if I run something from Query
>> > Ananlyzer, it was run under the account that Im logged into it as? And
> that
>> > if I run that same code in a job, it would be run under the account that
> SQL
>> > Server Agent was run as? So heres what happened while logged into QA as
> a
>> > Domain Admin:
>> >
>> > 1. I tried to Restore a db from a backup and it said "Access is denied"
> in
>> > the Error Log.
>> > 2. I assigned Full Control to the SQL Server Agent account (not a Domain
>> > Admin) on the folder that contains the backup.
>> > 3. Now, still logged into QA as the Domain Admin, the Restore works.
>> >
>> >
>> > Why would this be? Do Restores actually run under the account that the
> Agent
>> > runs under?
>> >
>> > --
>> > SQL2K SP3
>> >
>> > TIA, ChrisR
>> >
>> >
>>
>|||So then what other operations would use the SQL Server account, not my
account, from QA?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6rnXyoJFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Think of it from a technical viewpoint. It is SQL Server who reads the
file to restore. For SQL
> Server to open the file in *your* account, something we call "delegation"
is needed. Delegation is
> supported in Windows, with some configuration first. The application (SQL
Server in this case) need
> to be programmed for it, though. SQL Server supports delegation for some
operations (linked
> servers).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "ChrisR" <bla@.noemail.com> wrote in message
news:%232PndSoJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > Tibor you are correct, SQL Server and the Agent are the same account.
But
> > why would it use either of those accounts and not who I'm logged into QA
as?
> > When else does this occur? Is this just a Restore thing?
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:Oc$BTVnJFHA.1392@.TK2MSFTNGP10.phx.gbl...
> >> > Why would this be? Do Restores actually run under the account that
the
> > Agent
> >> > runs under?
> >>
> >> No, it uses the account that SQL Server uses. Which are probably the
same,
> > which in turn explains
> >> what you experienced-
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "ChrisR" <bla@.noemail.com> wrote in message
> > news:OlC4FamJFHA.220@.TK2MSFTNGP10.phx.gbl...
> >> > It has always been my understanding that if I run something from
Query
> >> > Ananlyzer, it was run under the account that Im logged into it as?
And
> > that
> >> > if I run that same code in a job, it would be run under the account
that
> > SQL
> >> > Server Agent was run as? So heres what happened while logged into QA
as
> > a
> >> > Domain Admin:
> >> >
> >> > 1. I tried to Restore a db from a backup and it said "Access is
denied"
> > in
> >> > the Error Log.
> >> > 2. I assigned Full Control to the SQL Server Agent account (not a
Domain
> >> > Admin) on the folder that contains the backup.
> >> > 3. Now, still logged into QA as the Domain Admin, the Restore works.
> >> >
> >> >
> >> > Why would this be? Do Restores actually run under the account that
the
> > Agent
> >> > runs under?
> >> >
> >> > --
> >> > SQL2K SP3
> >> >
> >> > TIA, ChrisR
> >> >
> >> >
> >>
> >>
> >
> >
>|||The only time, AFAIK, SQL Server uses delegation is for linked servers. For all other operations,
the service account is used (regardless of whether the client application is QA, SQL Server Agent, a
web app etc.)..
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ChrisR" <bla@.noemail.com> wrote in message news:uqfKO2oJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> So then what other operations would use the SQL Server account, not my
> account, from QA?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u6rnXyoJFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Think of it from a technical viewpoint. It is SQL Server who reads the
> file to restore. For SQL
>> Server to open the file in *your* account, something we call "delegation"
> is needed. Delegation is
>> supported in Windows, with some configuration first. The application (SQL
> Server in this case) need
>> to be programmed for it, though. SQL Server supports delegation for some
> operations (linked
>> servers).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "ChrisR" <bla@.noemail.com> wrote in message
> news:%232PndSoJFHA.1948@.TK2MSFTNGP14.phx.gbl...
>> > Tibor you are correct, SQL Server and the Agent are the same account.
> But
>> > why would it use either of those accounts and not who I'm logged into QA
> as?
>> > When else does this occur? Is this just a Restore thing?
>> >
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:Oc$BTVnJFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> >> > Why would this be? Do Restores actually run under the account that
> the
>> > Agent
>> >> > runs under?
>> >>
>> >> No, it uses the account that SQL Server uses. Which are probably the
> same,
>> > which in turn explains
>> >> what you experienced-
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "ChrisR" <bla@.noemail.com> wrote in message
>> > news:OlC4FamJFHA.220@.TK2MSFTNGP10.phx.gbl...
>> >> > It has always been my understanding that if I run something from
> Query
>> >> > Ananlyzer, it was run under the account that Im logged into it as?
> And
>> > that
>> >> > if I run that same code in a job, it would be run under the account
> that
>> > SQL
>> >> > Server Agent was run as? So heres what happened while logged into QA
> as
>> > a
>> >> > Domain Admin:
>> >> >
>> >> > 1. I tried to Restore a db from a backup and it said "Access is
> denied"
>> > in
>> >> > the Error Log.
>> >> > 2. I assigned Full Control to the SQL Server Agent account (not a
> Domain
>> >> > Admin) on the folder that contains the backup.
>> >> > 3. Now, still logged into QA as the Domain Admin, the Restore works.
>> >> >
>> >> >
>> >> > Why would this be? Do Restores actually run under the account that
> the
>> > Agent
>> >> > runs under?
>> >> >
>> >> > --
>> >> > SQL2K SP3
>> >> >
>> >> > TIA, ChrisR
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Odd error with Reporting Services and Oracle

I've got a fairly simple report that hits oracle with two parameters set as strings.

When I run the query in the designer, I get

Error Source: System.Data.OracleClient
Error Message: ORA-01858: a non-numeric character was found where a numeric was expected

However, when I run the report in preview everything just works. Same inputs on the parameters.

Any ideas?

What types are your parameters and what is the SQL you are using in your DataSet? I've seen this kind of error passing a parameter that is declared as a string in the report to an oracle parameter that is compared against a Date type in Oracle.

Mike

|||Sorery I meant to post a followup to this. The solution is that SQL RS 2k5 takes a string that happens to be a date and makes it a date without telling.

When it goes into oracle as this "Date" it will go in in the default SQL date format. So you need to match the format mask in the oracle TO_Date function call to the 3 letter month sql format.

Interesting, RS 2K did not do this.

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd characters added to T-SQL Automatically

I am using Reporting Services 2000 and therefore VS 2003 and am having a problem editing the T-SQL query in the data view of Visual Studio.

Basically if I manually edit the T-SQL query or touch anything in the designer view of the query I get odd characters added in the T-SQL and the query no longer works. I have tried re-installing VS2003 and RS 2000 but it's still happening.

FYI I also have SQLServer 2005 and VS 2005 on my machine as all applications we develop are in .Net 2.0 and 3.0. We only use VS2003 for RS.

Does anyone have a clue how to fix this? Thanks

the characters being added are highlighted below:

... tablename left outer join (select ...) aliasname

WITH ?刓 ? 凶掱凸?凴?凳?刓 ? 凶?凴 凴 凴?刓  凲 ? 凶莊凴?凲?刓?刓 ? 凶?凶?凸?凴?凲 ? 凶?凳?刓?Ф??  膋?

on tablename.column = aliasname.column ....

If I manually delete the highlighted section it puts it straight back in. I now can't edit any of our reports at all as its doing this to all of them. Help!

ODBC-Call Failed - Access

We have this fairly simple Access database with a link table to a view in an SQL Server. The Select Query in Access is a simple select from this view with a view criteria set. Nothing fancy. What is driving is moderately crazy is that this Select Query in Access seems to work fine for months and than all of a sudden (same query performed around the same time with about the same workload on the SQL-Server) it responses with the helpful "ODBC-Call Failed" message. Closing the Access database, taking a coffee, coming back againg and try the exact same Select Query normally works, no "ODBC-Call Failed" message.

It not happens always, mosty we're fine.

When it happens it happens for all subsequent queries tried. We have to close and reopen the Access database and pray to the good Lord that it works.

We would be very interested in someone with a solution to this problem.

What ODBC driver and version are you using and to what version of SQL Server are you connecting?

You might want to try out a different ODBC driver in your DSN to see if that resolves the problem.

Vaughn

|||We are having the same problem using Access 2003 and SQL Server 8.0. I'm using the standard SQL Server driver in the DSN. Any suggestions?|||

Reading a bit it sounds like the "ODBC-Call Failed" message is coming from Access. I found this KB article that describes a similar problem for DB2 and suggests updating Jet as the solution.

http://support.microsoft.com/kb/227413/

I see you're using Access 2003 so this isn't likely the cause. Have you tried ODBC tracing through the ODBC Data Source Administrator control panel? Correlating that log to when the failure occurs might provide more clues as to the cause.

Vaughn

|||The problem is that I don't know what is the cause of the error. Is it a timeout on the client (MSAccess) side? Is it a deadlock situation? Is it table lock that couldn not be caried out? etc. Neither SQL Server nor MSAccess seem to bother to explain what's wrong. If if happend all the time I would look at replacing drivers but now I feel this should be somewhere else. Btw ODBC tracing gives me a bunch data wich not really eplains what 's going on.|||

So, this might have been a timeout afterall. In design view I changed the properties of the view from Snapshot to "Dynaset (Inconsistent Updates)" (although MS fails to describe in the online help what that exactly means). I would go with Snapshot as there is no need to update the data, but due to the fairly large amount of records, MS recommends using Dynaset. Furthermore the Timeout of the view was changed to 900 seconds (15min), if our SQL Server is doing some real work this is probably not enough. It would be helpfull that instead of ODBC-Call failed, MS-Access would report something like "Timeout expired", but there probably is a deeper reason why this is not possible.

Leaving the question: Dynaset, Dynaset (Inconsistent Updates), Snapshot? As an experienced P/SQL and T/SQL I have no clue what MSAccess is talking about here. The documentation is pretty dodgy also. I fact I only need to read the data (it's for reporting).

|||

I found some information that may shed some light on the differences at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproRecordsetType_HV05187937.asp

Basically, a Dynaset allows you to make changes in controls bound to a single table or tables with a one-to-one relationship. You can also edit the fields from a table on the "many" side of a one-to-many relationship.

A Dynaset (Inconsistent) allows you to update all data fields.