Query Analyser And Procedure Debug
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
ADVERTISEMENT
Jun 19, 2008
Experts;
I am use the Query analyser to buidl a Master Stored Procedure that would go through the Stored Procedures in the DB to recreate them again
I am tesing this in the Query Analyser
This is the section of my code to create on of the Stored Procedures in the DataBase
select @SQL = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[IMGSAttributes'+@TableName+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
select @SQL = @SQL + char(13) + char(10) + 'drop procedure [dbo].[IMGSAttributes'+@TableName+']'
select @SQL = @SQL + char(13) + char(10) + 'GO'+ char(13) + char(10) +'SET QUOTED_IDENTIFIER ON'+ char(13) + char(10) +'GO'+ char(13) + char(10) +'SET ANSI_NULLS ON'+ char(13) + char(10) +'GO'+ char(13) + char(10) +'CREATE PROCEDURE dbo.IMGSAttributes'+@TableName
select @SQL = @SQL + char(13) + char(10) + '@pIndex varchar(50)'+ char(13) + char(10) + 'AS'+ char(13) + char(10) +'SET CONCAT_NULL_YIELDS_NULL OFF'+ char(13) + char(10) +'Select '+@PrimaryColumName + ' as GMKey,'+''''+@TableName +''' as TABLENAME, '+@ColText
select @SQL = @SQL + ' from '+@TableName+' where ' +@PrimaryColumName +' = @pIndex'
select @SQL = @SQL + char(13) + char(10) + 'RETURN '+ char(13) + char(10) +'GO '+ char(13) + char(10) +' SET QUOTED_IDENTIFIER OFF '+ char(13) + char(10) +'GO'+ char(13) + char(10) +'SET ANSI_NULLS ON '+ char(13) + char(10) +'GO'
exec(@SQL)
When I run this I got some errors:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'GO'.
Server: Msg 111, Level 15, State 1, Line 8
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@pIndex'.
Server: Msg 178, Level 15, State 1, Line 15
A RETURN statement with a return value cannot be used in this context.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'GO'.
View 3 Replies
View Related
Jan 5, 2004
Can anybody explain how to do debug a stored procedure from SQL Query Analyser.
When i tried opening Query Analyser and pressing F8 i am able to see Object Browser on left side, i selected the d/b and expanded it then i selected a stored procdure by right click of mouse. I selected "Debug".
It shows me alert msg "SQL Debugging may not work properly if you log on as 'Local System Account' while SQl server is configured to run as a service. You can open Event Viewer to see details." DO U WISH TO CONTINUE- I selected "YES"
I am able to see 3 split windows on right side and GO, Toggle, Untoggle are enabled BUT Step Into, Step Over, Step Out...Stop debugging are disabled at menu bar.
The 1st right split window shows the proc code, 2nd split window shows Local-Global-Callstack none of them shows any values(blank), 3rd split window shows records(result) and
@RETURN_VALUE = 0 message
I had Toggled at each and every line of the procedure in 1st split window still it doesnt respond anything.
What might be the problem, how to solve it do i need to give any permissions.
i tried logging from wind Authentication and also from Sql Authentication (sa/sa), still same problem occurs. By the way i am using SQL Server 2000.
Pls help me out
Thanks in advance
Murali Kumar
View 1 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 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
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
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
