Viewing Stored Produre Code From Query Analyser
Mar 17, 2004Hi
Can anyone tell me the way to view stored produre code from query analyser. With sp_help I can only view input paramters
Cheers
ADVERTISEMENT
No Response To Code And Query Analyser Cannot Read Statement
Feb 20, 2004hi
have a problem ...
I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer)
select * from tstudents where (login1='@param1')
if not what could me wrong in my code ,because I get no response to the sql server
Public Function login(ByVal login1 as string) as dataset
Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes")
dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection)
mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1))
dim DS as new dataset()
try
myconnection.open
dim adpt as new sqldataadapter
adpt.selectcommand = mycommand
adpt.fill(DS,"tStudents")
catch ex as exception
throw new exception(ex.message)
return nothing
finally
myconnection.close
end try
try
mydatagrid.datasource=DS.Tables("tStudents")
mydatagrid.databind()
catch ex as exception
errorlabel.text=ex.message
end try
return DS
End Function
thankx in advance
pamela mkosana
Using Query Analyser To Create A Stored Procedure
Jun 19, 2008Experts;
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'.
I Can't Find My Stored Proc In Query Analyser
Jun 4, 20061/
I create stored procedure in Query Analyser using:
ALTER PROCEDURE dbo.unshippedtotal
@name char,
@ytd int output
AS
select * from vwaccount
select @ytd=sum(AccAccnnmb) from vwaccount
return
GO
But When I try to expand the list of stored procedures under the DB and the server name, I can t see my Stored Proc called "unshippedtotal"
Any ideas
2/ Another question pls:
Can we use Query Analyser to do the same tasks we do with Entreprise Manager like creating and modifying tables, Creating Stored Procedures, Modifying Views... (I m more familiar with Entreprise Manager but somebody told me it s better to use Query Analyser)
Thanks for coaching :)
Help! Debugging Stored Procedure From Query Analyser - Step Through Disabled
Jan 5, 2004Can 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
About Store Produre
Jul 20, 2005Hi Every Body.Its Biswajit I want to know about use of Store Procedure inSQL.What is benifit of store procedure and we r using it.
View 1 Replies View RelatedQuery Runs Fine In Query Analyser But Not The Query Debugger
Dec 19, 2003I'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
Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?
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
How To Query An Oracle Data From SQL Server Query Analyser
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
Is A Bug In Query Analyser
Sep 29, 2000Hi
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 ?
Help On Query Analyser
Aug 2, 2003Hi 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.
SQL Query Analyser
Jun 6, 2002We 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 RelatedUsing Query Analyser
Jan 5, 2006I 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
Query Analyser
Mar 20, 2006Jst curios,
How does the Query Analyser connect to the database..does it use any of the ODBC or OLE DB API's
THx
Venu
Query Analyser
May 24, 2007I 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
Query Analyser
Feb 1, 2008Is there Query Analyser in SQLServer 2005 like there was in 2000 ? If so, how do I access it.
Thanks
Query Analyser
Jun 18, 2006Hi,
Can't seem to find which download includes Query Analyser, I have downloaded Management Studio Express
Help Please
Query Analyser Is Not Working
Jun 11, 2001Hi 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
Query Analyser Is Not Working
Jun 11, 2001Hi 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
Query Analyser Without SQL Server Name
Jun 3, 2004Hi,
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
Sessionid In Query Analyser
Sep 30, 2005I want to know my session id number in Query Analyser
Do you know a procédure or a command ?
Query Analyser In 2005 ?
Feb 14, 2006I cannot find the query analyser in SQL 2005
do you know where it is ?
thank you
Query Analyser QUestion
Mar 28, 2004Hi 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
Cant See From Query Analyser The Text...
Jan 24, 2006In 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?
Query Analyser Not Running
Mar 18, 2006Hi
I am install sql 2000, Server is running properly
but query analyer is not working...
Thanks
ASM
Prompt In Query Analyser
Feb 5, 2007Is 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.
Query Analyser In 2005 ?
Apr 18, 2007Hi:
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.
Rollback Via Query Analyser
Jul 23, 2005HiI'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 RelatedQuery Analyser 2000 Vs 7.0
Jul 20, 2005ISTR 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 RelatedSQL Timeout Through ADO But Not From Query Analyser
Jan 12, 2007I 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...
Query Analyser Execute Sequence
Jan 4, 2001Howdy
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.
Truncate Transaction Log Through Sql Query Analyser
Oct 20, 2005Hi 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
Clearing Of Cache In Query Analyser
Apr 3, 2002HI
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