SQL Timeout Through ADO But Not From Query Analyser
Jan 12, 2007
I have a strange problem running a stored proc in SQL 2000. The proc returns XML and I'm using ADO to call the proc and return the data as a stream. When I call the proc through ADO, it times out because it is taking 40 seconds to run and my command timeout is set to 30. I can increase this no problem but the strange thing is that when I run the same proc from within query analyser, it completes in under a second.
I put profiler on it tracing the command start and finish events and it's definitely taking 40 seconds between start and finish. The proc is used with various input parameters and the timeout is only occurring for some combinations of inputs, but it always seems to take much longer than it does when run from query analyser.
Can anyone suggest what might be causing the delay, or where I might look to find the problem?
Thanks...
View 13 Replies
ADVERTISEMENT
May 10, 2006
Hi,
Ive got a collection of view which piece together data from varying sources and assemble it ready for a stored procedure to squirt the data into a table.
This has worked fine up til yesterday when i had to amend one of the views to include a reference to another data set.
Essentially the data is for students and comes from our old db and our new one, each year group has different values so the views act to extract all data relevant for a child from both databases and accross all year data sets.
The problem im having is that i can get the final stored procedure to run. It ran fine the other day but since ive amended one of the views i get timeout errors wherever it's run. real problem when query analyser which has no timeout settings (apparently) times out as it means i cant run the query anywhere....enterprise manager will timeout after 30secs despite amending the query timeout setting to 600s.
so im a bit stuck, both views which the problem view is trying to combine run in under 30 seconds...when you try and combine them it times out- seems to run for a long long time.
note sure why this has started happening, from all ive read most peeps get this with EM and solve it using QA. but what if QA cant handle the query either?
heres the sql: (wont make much sense but hopefully shows that its not a complex query)
SELECT TOP 100 PERCENT dbo.StudentDetMaster.SetId, dbo.StudentDetMaster.StudentId,
dbo.StudentDetMaster.Name,
dbo.StudentDetMaster.CourseYear,
dbo.StudentDetMaster.House,
dbo.StudentDetMaster.Tutor,
dbo.PGJSProfile.[Key Stage 1 Reading],
dbo.PGJSProfile.[Key Stage 1 Writing],
dbo.PGJSProfile.[Key Stage 1 Speaking & Listening],
dbo.PGJSProfile.[Key Stage 1 Maths],
dbo.PGJSProfile.[Key Stage 2 English],
dbo.PGJSProfile.[Key Stage 2 Maths],
dbo.PGJSProfile.[Key Stage 2 Science],
dbo.PGJSProfile.[Verbal Reasoning Year 3],
dbo.PGJSProfile.[Year 3 English Autumn],
dbo.PGJSProfile.[Year 3 English Year Group Average],
dbo.PGJSProfile.[Year 3 English Summer],
dbo.PGJSProfile.[Year 3 English Year Group Average Score], dbo.PGJSProfile.[Year 3 Maths Autumn],
dbo.PGJSProfile.[Year 3 Maths Year Group Average],
dbo.PGJSProfile.[Year 3 Maths Summer],
dbo.PGJSProfile.[Year 3 Maths Year Group Average Score], dbo.StudentDetMaster.[Year 3 Maths Set],
dbo.PGJSProfile.[Verbal Reasoning Year 4],
dbo.PGJSProfile.[Year 4 English Autumn],
dbo.PGJSProfile.[Year 4 English Year Group Average],
dbo.PGJSProfile.[Year 4 English Summer],
dbo.PGJSProfile.[Year 4 English Year Group Average Score], dbo.PGJSProfile.[Year 4 Maths Autumn],
dbo.PGJSProfile.[Year 4 Maths Year Group Average],
dbo.PGJSProfile.[Year 4 Maths Summer],
dbo.PGJSProfile.[Year 4 Maths Year Group Average Score], dbo.StudentDetMaster.[Year 4 Maths Set],
dbo.PGJSProfile.[Verbal Reasoning Year 5],
dbo.PGJSProfile.[Year 5 English Autumn],
dbo.PGJSProfile.[Year 5 English Year Group Average],
dbo.PGJSProfile.[Year 5 English Summer],
dbo.PGJSProfile.[Year 5 English Year Group Average Score], dbo.PGJSProfile.[Year 5 Maths Autumn],
dbo.PGJSProfile.[Year 5 Maths Year Group Average],
dbo.PGJSProfile.[Year 5 Maths Summer],
dbo.PGJSProfile.[Year 5 Maths Year Group Average Score], dbo.StudentDetMaster.[Year 5 Maths Set],
dbo.PGJSProfile.[Verbal Reasoning Year 6],
dbo.PGJSProfile.[Year 6 English Autumn],
dbo.PGJSProfile.[Year 6 English Year Group Average],
dbo.PGJSProfile.[Year 6 English Year Group Average Score], dbo.PGJSProfile.[Year 6 Maths Autumn],
dbo.StudentDetMaster.[Year 6 Maths Set],
dbo.StudentDetMaster.[JS Baseline],
dbo.PGJSProfile.[Year 6 English Mock],
dbo.PGJSProfile.[Year 6 Maths Mock],
dbo.PGJSProfile.[Year 6 English Mock Year Group Average], dbo.PGJSProfile.[Year 6 Maths Mock Year Group Average]
FROM
dbo.PGJSProfile LEFT OUTER JOIN
dbo.StudentDetMaster ON dbo.PGJSProfile.SetId = dbo.StudentDetMaster.SetId AND
dbo.PGJSProfile.StudentId = dbo.StudentDetMaster.StudentId
ORDER BY dbo.StudentDetMaster.Name
cheers
greg
View 11 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
Apr 30, 2007
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
MaurÃcio
View 2 Replies
View Related
Sep 3, 2007
Hi ,
I am having 2 data store .
1. Oracle 10g
2 SQL server 2000
My requirement is that , i need to insert some data from sql server database table to oracle database using sql server query analyser or interface.
If there is any way ,plz let me know it
Thanks
Abraham
View 3 Replies
View Related
Sep 29, 2000
Hi
This what i did , since i need to maintain
five sql servers ,i thought i will build a repository
so on my desk top (running sql server ) i created a
table name master_dbscript with the following fields
server_name varchar(20),
dbname varchar(20)
db_create_scripts text
using enterprise manager-- all tasks --generate sql scripts , (cut & paste
to the insert statement in query analyser, the following is the insert statement
insert into master_dbscript values ('isd11t','test','ALTER TABLE [dbo].[child] DROP CONSTRAINT FK_child_parent GO /****** Object: Trigger dbo.test_patcase Script Date: 25/08/2000 12:10:09 ******/ if exists (select * from sysobjects where id = object_id(N'[dbo].[test_patcase]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)drop trigger [dbo].[test_patcase] GO ')
oops it created all the objects in the database where i tried to run the
insert statement. god saved me , i tried this with the test database.
when i tried the same with bcp it worked fine and i was able to see the
record in my table (one record) ,note you cannot use dts because it will support maximum 8000 chars only .
what is the problem with the query analyser ?
View 1 Replies
View Related
Aug 2, 2003
Hi Everybody,
I am executing the following query in the query analyser.
"select * from alien119700 order by alienid"
In the message pane it shows
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(43 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 454 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
What does all this mesaages mean?
Can anyone explain this to me?
Thanx in advance.
Regards,
Samir.
View 1 Replies
View Related
Jun 6, 2002
We use SQL 2K(service pack 1).Our query analyser will freeze often.So we loose all query production work.Does anyone know if Version 2 has a fix?Please help.
View 4 Replies
View Related
Jan 5, 2006
I noticed that query analyser is much more quicker than EManager when I access my database from my hosting provider... is there any way to see the properties of the table X for example as one can do with EM...
I would be grateful if you could provide me with any query sample conserning this issue...
Thanks
View 9 Replies
View Related
Mar 20, 2006
Jst curios,
How does the Query Analyser connect to the database..does it use any of the ODBC or OLE DB API's
THx
Venu
View 1 Replies
View Related
May 24, 2007
I am currently using the trial version of SQL 2000 to connect to a remote db so I can query the data.
When the trial version runs out what can i do? will MSDE work for me or will the express edition of SQL 2005 be better?
Thanks! Bruno
View 1 Replies
View Related
Feb 1, 2008
Is there Query Analyser in SQLServer 2005 like there was in 2000 ? If so, how do I access it.
Thanks
View 9 Replies
View Related
Jun 18, 2006
Hi,
Can't seem to find which download includes Query Analyser, I have downloaded Management Studio Express
Help Please
View 3 Replies
View Related
Jun 11, 2001
Hi All ,
In my M/c the query analyser is not working when ever I try to run it by Using Enterprise manager / Explorer / Start menu - Program / Isqlw.exe.
When I Tried the task manager it shows the process isqlw is working. But I can't see any window coming up ..
Please help me otherwise I will go mad....using other tools to query . 50 % time i use this tool in work. U guys know how important it is ..
Thank u
Jeo
View 2 Replies
View Related
Jun 11, 2001
Hi All ,
In my M/c the query analyser (SQL 2000) is not working when ever I try to run it by Using Enterprise manager / Explorer / Start menu - Program / Isqlw.exe.
When I Tried the task manager it shows the process isqlw is working. But I can't see any window coming up ..
Please help me otherwise I will go mad....using other tools to query . 50 % time i use this tool in work. U guys know how important it is ..
Thank u
Jeo
View 1 Replies
View Related
Jun 3, 2004
Hi,
I am studying SQL Server by myself (I am a Sybase DBA) and I have a little problem with the Query Analyser.
I actually have 2 server in my PC (XP) and I can see them via SQL Server Enterprise Manager, I also can open Query Analyser for these two server via Enterprise Manager.
When I try to launch SQL Query Analyser from the start menu, I can't see any of my two server and can't chose a connection.
Do I need to write some parameter somewhere?
It's not a big problem as I can work via Enterprise Manager but I just want to know.
Thank you for your response.
Eric
View 6 Replies
View Related
Sep 30, 2005
I want to know my session id number in Query Analyser
Do you know a procédure or a command ?
View 3 Replies
View Related
Feb 14, 2006
I cannot find the query analyser in SQL 2005
do you know where it is ?
thank you
View 2 Replies
View Related
Mar 28, 2004
Hi all
I am a novice to SQL Server 2000
Is there a quick reference somewhere for using Query Analyser??
I need to declare variables and pass them into a store procedure.
say if i have the following procedure showing only the input variables/
CREATE PROCEDURE CalcLocalTime
(
@postcode int,
@sDate datetime
)
also how do i display a return variable?
James
View 4 Replies
View Related
Jan 24, 2006
In a field there is a very long text. I am trying to retrieving using query analyser but again it gives me only part of the document...
any help please?
View 8 Replies
View Related
Mar 18, 2006
Hi
I am install sql 2000, Server is running properly
but query analyer is not working...
Thanks
ASM
View 9 Replies
View Related
Feb 5, 2007
Is there antway I can get the Query Analyser to prompt me with a message saying '234 records will be updated' and then give me the option to continue or not.
I want to run an update query but want to see how many records it will update first.
View 7 Replies
View Related
Apr 18, 2007
Hi:
I am a Sql server 8.0 user,and using query analyser extensively. Is the query analyser available in SQL sever 2005 version ?
thank you in advance.
View 5 Replies
View Related
Jul 23, 2005
HiI'm expecting the answer no here but its worth asking.If I wanted to test an arbitary program could I somehowsay in query analyser, start a global transaction on thisdatabase, run my tests, then at the end roll it back from withquery analyser?I know I can use backup and restore but this would beeasier, still, again, I don't think you can do this.TaF
View 1 Replies
View Related
Jul 20, 2005
ISTR QA running in 7.0 would return print statements and messages as thequery ran (well, after a "GO" statement anyway). In 2000 it doesn't returnany messages until after the entire query batch has completed. In wanting tokeep an eye on progress on long batches, how can I make 2000 work more like7.0 in this respect?
View 3 Replies
View Related
Jan 4, 2001
Howdy
If I have serveral queries as shown below:
================================================== ==========
USE XXX
SELECT XX_ZZZZZ, XX_YYY, XX_XXX
FROM ZZZ_ZZZZ_ZZZZ
WHERE QQQQQQQQQQQQQ ='PGL'
USE XXX
SELECT AAA_AAAAA_AAAA,AA_BBB, CC_DDD
FROM SSSS_SSSSS_SSSSS
WHERE FFF_FFFF ='A'
================================================== ===========
Can anyone tell me if the queries execute concurrently or
is it one after the other?
Many thanks,
W.
View 1 Replies
View Related
Oct 20, 2005
Hi guys
My website is in asp and sql2000. My problem is the ISP gave access to database through query analyser. some days the transaction log is growing too high. so i want to clear it. i call up them and clear it. My question is can truncate the log file through query analyser ?
I had limited access to database.
Jini
View 11 Replies
View Related
Apr 3, 2002
HI
May I know how to clear the cache area in the analyser as I need to
know the time taken for execution of an sql statement(query) before
and after creation of indexes.
tia
MK
View 1 Replies
View Related
Feb 18, 2008
I have to disable update, delete and insert commands in my sql query analyser window
View 14 Replies
View Related
Jan 13, 2004
Hi folks
I´ve a simple doubt (I hope) ... is it possible export the results from a simple query to a ms access file using just the query analyser??? I´m using SQL Server 2000 and MS Access XP
thanks for your help!!!
View 12 Replies
View Related
Jan 29, 2004
Hi,
I am trying to use debug option for a stored procedure from SQL Query Analyser and I get the error:
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on TRAX2STAGE (Error = 0x800706ba). Ensure that client-side components, such as SQLDBREG.EXE, are installed and registered on WKVKARAMCHED. Debugging disabled for connection 80.
What do I need to fix this?
Any help is appreciated.
Vinnie
View 4 Replies
View Related
Apr 21, 2006
I am trying to test sp in Query Analyser as below but get the error
Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0
Error converting data type varchar to datetime.
How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?
EXEC spRMU_GetChaseFiles
@strStartDate ='01/01/05',
@strEndDate = '21/04/06',
@strDepartment = 'Central Units',
@strDivision = 'Personnel',
@strSection = ''
View 20 Replies
View Related
Dec 19, 2006
Could somone advise on how to change Names of Columns in a table
via a script rather then Enterprise Manager. I have lots of columns (need to do it in bulk.) called like SUM(abc1...etc), which I can not change. This upsets the Business Objects because it thinks it needs a GROUP BY statement.
I know in MapBasic there is something like " Alter Table "tablename" ( rename Date Date_of_Day ) " Is there an equivalent for TRANSACT SQL?.
Please help, (and Happy Xmas!)
Gezza
View 5 Replies
View Related
