Query Would Run For Ever - No Clue

Hello fellow DBAs

I have a strange situation here. I am executing a SQL query which runs for ever till it fills up all the available temp space.

The same query runs within 1 minute in another database on another server. That database is a development database but with same records (and data).

I tried the following:

UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG

Nothing helps... what should I do next?post the ddl + the query so we can see. Its common to have a rogue/running away query that would take the server down to its knee.

e.g.
select *
from master..syscolumns,master..syscolumns,master..sysc olumns,master..syscolumns

Query works in QA, but NOT in SQL Server stored proc?!

Ok...below is a simple query that inserts some records into a temp
table then updates another table using the temp table. It works great
in Query Analyzer, but refuses to save in SQL Servers' stored procedure
area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
I'm banging my head against a wall here, please help! I've tried
placing single and double quotes around fvd_count to no avail...
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exists
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count <<<<<--ERROR
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND
a.poe = b.poe
GOAre you creating the sp in EM?. Try creating the sp from SQL Query Analyzer.
AMB
"roy.anderson@.gmail.com" wrote:

> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||Did you cut/paste the error message? If so, then you have a typo somewhere
in your code because the error message references field 'fvd_cnt', while the
sp that you show us names it 'fvd_count'.
If that is just a typo in your post...then...another thought is to use
[square brackets] around the field name. It shouldn't be necessary in this
case, but worth a try.
<roy.anderson@.gmail.com> wrote in message
news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||I dont think this is the full sproc, i have just tested this in QA & EM
without any error
USE Northwind
GO
CREATE TABLE firstvd (doc varchar(10), poe varchar(10), equipment varchar(10
))
CREATE TABLE lla (doc varchar(10), poe varchar(10), fvd_count int)
GO
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
EXEC Update_Counts
GO
DROP TABLE firstvd
DROP TABLE lla
DROP PROCEDURE [Update_Counts]
GO
Instead of using a temp table why not do it in the query
UPDATE a
SET a.fvd_count = (SELECT COUNT(equipment)
FROM firstvd b
WHERE b.doc = a.doc AND b.poe = a.doc)
FROM lla a, firstvd
Andy
"CPK" wrote:

> Did you cut/paste the error message? If so, then you have a typo somewher
e
> in your code because the error message references field 'fvd_cnt', while t
he
> sp that you show us names it 'fvd_count'.
> If that is just a typo in your post...then...another thought is to use
> [square brackets] around the field name. It shouldn't be necessary in thi
s
> case, but worth a try.
> <roy.anderson@.gmail.com> wrote in message
> news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
>
>|||Hey all, thanks for the input. I tried using QA to load it in and it
works like that... the first time... but when I call it after that it
ends up producing nothing (called from my asp.net page) or erroring out
in QA or EM.
Tried the square brackets, no go (and yes, it was a typo on my part).
I'll try your query idea next Andy, I don't know what to say regarding
your experiment except to say it just doesn't work in my EM. I did
clarify where the error occurs though. It happens at b.fvd_count below:
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exi=ADsts
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count =3D >>>>>>> b.fvd_count <<<<<<--ERROR HERE
FROM #FVD_Temp b, lla a
WHERE a.doc =3D b.doc AND
a=2Epoe =3D b.poe
GO=20
****************************************
***********************|||Roy
Just try this in QA first, forget about EM its a GUI and should be just
treated that way, i spend 90% of my time using QA
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
Oh just one more thing does this field "fvd_count" exist in table lla as we
know it exists in the temp table #FVD_Temp
Andy
"roy.anderson@.gmail.com" wrote:

> Hey all, thanks for the input. I tried using QA to load it in and it
> works like that... the first time... but when I call it after that it
> ends up producing nothing (called from my asp.net page) or erroring out
> in QA or EM.
> Tried the square brackets, no go (and yes, it was a typo on my part).
> I'll try your query idea next Andy, I don't know what to say regarding
> your experiment except to say it just doesn't work in my EM. I did
> clarify where the error occurs though. It happens at b.fvd_count below:
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exi_sts
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = >>>>>>> b.fvd_count <<<<<<--ERROR HERE
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>
> ****************************************
***********************
>|||Yes, the create proc above works in QA and yes, fvd_count exists in
lla...|||Wild guess: Try adding SET NOCOUNT ON in the beginning of your proc code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<roy.anderson@.gmail.com> wrote in message
news:1107286986.218999.296680@.f14g2000cwb.googlegroups.com...
> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Roy
have you sorted this out.
If the CREATE PROC statement returned "The command completed successfully"
then the procedure has been created. But does it execute & do what you
expect'
Andy
"roy.anderson@.gmail.com" wrote:

> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Yes, the query proc works! Thanks much. Additonally, the suggestion to
create it in QA (the original stored proc) was accurate. The SP works
when called from my asp.net page. Even though both ways work (outside
of EM), I'm sticking with the query method because it seems less cpu
intensive.
The weird thing is still that the original SP won't save and the syntax
check fails when I open it (the original SP) in EM. But no matter, at
least I have a working now process now. Thanks everyone for all the
great suggestions!

query where Case sensitive

hi, how to write query is Case sensitive?
select * from tb where fd like '%Temp'
will only return not "temp" ones? Thanks.Youcan explicitly define the collation for the query comparison:
select * from tb where fd like '%Temp' COLLATE
SQL_Latin1_General_CP1_CS_AS
CS which means "Case Sensitive"
HTH, jens Suessmeyer.|||Hi
Here are some examples
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
SELECT description
FROM ABCD
WHERE description ='dhZ3'COLLATE Latin1_General_BIN
SELECT description
FROM ABCD
WHERE charindex('h',description COLLATE Latin1_General_BIN)>0
--for sql2000
SELECT *
FROM Authors
WHERE au_lname COLLATE Latin1_General_CS_AS = 'green' COLLATE
Latin1_General_CS_AS
AND au_lname = 'green'
"js" <js@.someone.com> wrote in message
news:uxEv$ciNGHA.2920@.TK2MSFTNGP10.phx.gbl...
> hi, how to write query is Case sensitive?
> select * from tb where fd like '%Temp'
> will only return not "temp" ones? Thanks.
>