query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
Antonin
The answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.
|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

Query execution takes different times from different clients

Hi everyone,
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
Andrew
Try turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>
|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>
|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>

Query Challange

Im having some problems with a query Im working on and Ive googled it up and down and Im missing something. I have a subquery and I want the results, which return multiple columns, joined to the current tables. I'll try and explain a little better. One of my fields is called 'Assignee_type' and another is 'Assignee_ID'. Now, depending on the 'Assignee_type' depends on the table it looks up the 'Assignee_ID' in. Which is cool, a simple case statement inside the select. If the Assignee_type = 1 then it looks up the values in the Agent table, if its 2 then it looks in the agency table. Here is the cleaned up, help edition so its easier and cleaner to read:

DECLARE @.IDINTSET @.ID = 8--TYPE-- AGENT = 1-- AGENCY = 2SELECT A.ID,CASEWHEN A.ASSIGNEE_TYPE = 1THEN (SELECT AG.AGENT_ID ID, AG.FIRST_NAME +' ' + AG.LAST_NAME [NAME]FROM AGENT AG (NOLOCK)WHERE AG.AGENT_ID = A.ASSIGNEE_ID)WHEN A.ASSIGNEE_TYPE = 2THEN (SELECT AGY.ID, AGY.[NAME]FROM AGENCY AGY(NOLOCK)WHERE AGY.ID = A.ASSIGNEE_ID)END ASSIGNEEFROM APPOINTMENT A (NOLOCK)WHERE A.ID = @.id

Now if I take a column out of each subquery in case statement, so that it returns only 1, it works fine, but I want possibly up to 10 more fields. I tried Temporary Tables, CROSS APPLY and I cant seem to get it.Any help or suggestions would be great. TIA.

I don't know if it was ok to do it as a stored procedure, or if it has to be regular sql... but anyway, here is a stored procedure that does. If you need it as plain sql let me know, and I'll see if I can help

CREATEPROCEDURE sp_appointment

@.IDINT

AS

BEGIN

SETNOCOUNTON;

declare @.TypeINT

declare @.AssigneeIdINT

select @.Type= Assignee_Type, @.AssigneeId= Assignee_Idfrom appointmentwhere id= @.id

if @.Type= 1

begin

SELECT @.ID, AG.AGENT_ID ID,AG.FIRST_NAME+' '+ AG.LAST_NAME [NAME]

FROM AGENT AG(NOLOCK)

WHERE AG.AGENT_ID= @.AssigneeId

end

if @.Type= 2

begin

SELECT @.ID, AGY.ID,AGY.[NAME]

FROM AGENCY AGY(NOLOCK)

WHERE AGY.ID= @.AssigneeIdend

END

GO

|||

SELECT A.ID,A.ASSIGNEE_ID,NAME

FROM Appointment a

LEFT JOIN (

SELECT 1 AS [Type],AGENT_ID AS ID,FIRST_NAME + ' ' + LAST_NAME AS [NAME]

FROM Agent

UNION ALL

SELECT 2 AS [Type],ID,NAME

FROM Agency

) t1

ON t1.ID=A.ASSIGNEE_ID AND t1.[Type]=A.ASSIGNEE_TYPE

WHEREA.ID=@.ID

|||

Try selecting the main table into a temporary table and then run a series of updates, one for each type. When all updates are done, select from the temporary table, to get your output.

(By temporary table, I am referring to an in-memory table variable.)

|||

DECLARE @.IDINTDECLARE @.A_TYPEINTDECLARE @.ttable( idintnot null,name varchar(50)null)SET @.ID = 8SELECT @.A_TYPE = A.IDFROM APPOINTMENT A (NOLOCK)WHERE A.ID = @.idif(@.A_TYPE = 1)begin insert into @.tselect AG.AGENT_ID ID,AG.FIRST_NAME +' ' + AG.LAST_NAME [NAME]from AGENT AG (NOLOCK)where (AG.AGENT_ID = A.ASSIGNEE_ID)end else if(@.A_TYPE = 2)begin insert into @.tselect AGY.ID,AGY.[NAME]from AGENCY AGY(NOLOCK)where AGY.ID = A.ASSIGNEE_ID)endselect *from @.t

|||

Thank you all for your responses. It helped me overcome this challenge and get the results I was looking for and needed.

Klaus - I originally did it this way, but I noticed I would have to repeat alot of code and I knew there had to be a simpler solution, non-the-less it works.

Motley - THANKS. I liked this solution and integrated it into my sproc. UNION ALL was perfect but I didnt think of it :)

Kyle - I didnt get a chance to try your solution, but thats one thing I tried and I couldnt get it to work (table variable), but your solution looks great.

Thanks all again.

But here is the final solution for others to stumble across who is looking:

ALTER PROCEDURE dbo.SEARCH_APPOINTMENT_DETAILS_BYID ( @.idint )ASSELECT'Code: ' +CASE A.CODEWHEN''THEN'N/A'ELSE A.CODEEND CODE,CONVERT(VARCHAR(10), A.EFFECTIVE_DT, 101) EFFECTIVE_DT,ISNULL(CONVERT(VARCHAR(10), A.END_DATE, 101),'PERPETUAL') END_DATE, A.NOTES,ISNULL(AFF.[NAME],' No Affiliate') AFFILIATE_NAME, CR.[NAME] CARRIER_NAME, C.CODE CONTRACT_CODE, t1.ID ASSIGNEE_ID, t1.[NAME] ASSIGNEE_NAME, t1.ADDRESS1, t1.ADDRESS3, t1., t1.PHONEFROM APPOINTMENT A (NOLOCK)LEFTOUTER JOIN [CONTRACT] C (NOLOCK)ON C.ID = A.CONTRACT_IDLEFTOUTER JOIN CARRIER CR (NOLOCK)ON CR.ID = C.CARRIER_IDLEFTOUTER JOIN CARRIER AFF (NOLOCK)ON AFF.ID = A.APP_AFFILIATE_IDLEFTOUTER JOIN (SELECT 1AS [TYPE], AGENT_ID ID, FIRST_NAME +' ' + LAST_NAME [NAME], ADDRESS1, CITY +' ' + STATE +' ' + POSTAL_CODE ADDRESS3,ISNULL(EMAIL_ADDRESS,'N/A') EMAIL, ISNULL(DBO.FUNC_FORMATPHONENUMBER(CONTACT1_VALUE),'') PHONEFROM AGENT (NOLOCK)WHERE DELETED <> 1UNIONALLSELECT 2AS [TYPE], A.ID, A.[NAME], A.ADDRESS ADDRESS1, A.CITY +' ' + S.ABBR +' ' + A.ZIP ADDRESS3,'N/A' EMAIL,ISNULL(DBO.FUNC_FORMATPHONENUMBER(A.PHONE),'') PHONEFROM AGENCY ALEFTOUTER JOIN STATE SON S.ID = A.STATE_ID )t1ON t1.ID=A.ASSIGNEE_IDAND t1.[TYPE]=A.ASSIGNEE_TYPEWHERE A.DELETED <> 1AND A.ID = @.id