I Can't Find My Stored Proc In Query Analyser
Jun 4, 2006
1/
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 :)
View 9 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
Mar 17, 2004
Hi
Can anyone tell me the way to view stored produre code from query analyser. With sp_help I can only view input paramters
Cheers
View 4 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
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Jan 29, 2004
Hello all,
Being a relative newbie to large scale MSSQL development, I'd like to try and find out if there is some sort of utility, command or stored proc that I can use to globally change all text within my database's stored procedures.
For example... I would like to change a table name from dbo.xtable... to dbo.ytable... Is this possible?
Actually, I'd be happy with some way to search through all my stored procs to find a specific string (i.e. xtable in the example above).
Any help provided will be greatly appreciated!
Jordan Stradtman
View 7 Replies
View Related
Aug 24, 1999
I have 2 stored proc's that i want to run an append query every 4 or 8 hours.
how does a person do this in ms sql 7?
View 2 Replies
View Related
Nov 27, 2006
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies
View Related
Jul 1, 2004
Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi
View 2 Replies
View Related
Feb 7, 2006
I have two tables, Employee and EmployeeEval, and need to try and Select the LastName, FirstName, EmployeeID, and DeptID from the Employee Table where there is no record of that employee in the EmployeeEval Table. I tried this and it won't work, I should get back like 80 employees when the query is written correctly:
select e.deptid, (e.lastname + ', ' + e.firstname) as EmpName, e.employeeid from employee e INNER JOIN employeeeval ev ONe.employeeid <> ev.employeeid where periodid = 176 and e.companyid = '21' and e.FacilityID = '01'
View 3 Replies
View Related
May 11, 2004
Hi
I would like to write a query, where I would like to pass in a string value into proc, which is comma delimited, ie
create proc spName
@list as varchar(4000)
as
....
....
....
Then I wish to iterate through the string running a query for each item in the list.
Do I place values in @list into a cursor ?
View 3 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Jun 20, 2005
Hi,In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:Codes in ASPX.VB: Dim SQL as String="SELECT * From Table1" IF UserOption <> Null then SQL = SQL & " WHERE Column1=" UserOption" End ifNow, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).Hope you can understanding what my mean and give me advices.Regards,Raymond
View 8 Replies
View Related
Jul 18, 2014
In some of our business object reports we have to create variables to decode values to what we want. I am trying to replicate this in SQL Server and remember doing this in SQL server 2000 years ago back can't remember the exact way to do it. I remember running a query and calling stored proc within query which would return the value I wanted but not sure if I can still do this in SQL server 2008 and by that I mean doing it within query or have to do it another way.
Basically what I want is to have a procedure with all the variables replicated within that procedure so that when I run a query I can just call the appropriate bit of code by passing a specific name like
select job.dept, dbo.decodevariable('ShowJobDesc' job.jobtitle), job.salary
from job
so 'ShowJobDesc' and the job.jobtitle would be used to decode each job title to return job description.Just a bit unsure and can't remember if I am doing this the right way, is this possible?
View 2 Replies
View Related
Mar 17, 2006
Hello,
When I run the SQL statements piece by piece, the execution time under 10 seconds. The stored proc takes forever.
All I am doing is retrieving (10,000+) rows of data using temp tables and joins. I have indexed each of the temp table.
What could be the reason for the slowness?
Thanks in advance!!!
sqlnovice123
View 4 Replies
View Related
May 10, 2007
how can i view all the records in my stored procedure???
how is the query function done??
for example i had my datagrid view... and of course a view button that will trigger a view action in able to view the entire records that i input....
i am not familiar with such things..
pls help me to figure this out..
thanks..
im just a begginer when it comes to this..
pls help me..
thanks..
View 1 Replies
View Related
Jan 11, 2007
Is it possible to pass 5 variables to a proc and have IT do the thinking and query structuring?
An example of what I'm try to do is have one proc for getting vehicles by make, model, and years
example of what I'd like to accomplish:veh_list_vehicleInfo_byDetails
@TypeID int,
@MakeID int,
@ModelID int,
@begYear int,
@endYear int
AS
BEGIN
declare @SQL as nvarchar(500)
set @SQL = 'SELECT a.ID, b.Model, c.Make, d.Name, a.Year, a.Mileage, a.Price, a.Sale, a.Certified_Pre_Owned FROM veh_vehicles a INNER JOIN veh_model b ON a.ModelID = b.ID INNER JOIN veh_make c ON a.MakeID = c.ID INNER JOIN veh_location d ON a.LocationID = d.ID'
decalre @ATTRIBUTES as nvarchar(500)
if @TypeID is not null AND @TypeID > 0
begin
set @ATTRIBUTES = @ATTRIBUTES + ' a.TypeID = ' + @TypeID
end
if @MakeID is not null AND @MakeID > 0
begin
set @ATTRIBUTES = @ATTRIBUTES + ' a.MakeID = ' + @MakeID
end
....etc etc.......
if Len(@ATTRIBUTES) > 0
begin
EXEC(@SQL + ' WHERE ' + @ATTRIBUTES)
End
Else
BEGIN
EXEC(@SQL)
END
END
But I keep getting some errors regarding converting 'a.TypeID = ' to int
????? Please help!!
I figured this would be easier than writing stored procs for EACH situation
View 2 Replies
View Related
Aug 28, 2004
Yesterday i face a strange SQL Server 2000 behaviour :-(
I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.
So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.
Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.
My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?
View 5 Replies
View Related
Feb 27, 2008
This should be an easy enough answer to find if I just knew what to search on!
I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says:
select * from tblNames where FirstName = 'Hannah'
or
select * from tblNames where LastName = 'Montana'
or
select * from tblNames where FirstName = 'Hannah' and LastName='Montana'
My problem is putting the single quotes around the variable value.
SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it
Thanks
View 3 Replies
View Related
Aug 18, 2004
I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.
Thats the dream.
The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.
Here is what I have as of this moment for my sp.
ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/
PRINT @dtPP
RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/
The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '
Any help would be greatly appreciated as I am about to kick someone/something.
Thanks
View 2 Replies
View Related
Oct 10, 2006
Hi All,
I think what am trying to do is quite basic.
I have 3 paramaters@value1, @value2,@value3 being passed into a stored
proc and each of these parameters can be blank. If one of them is blank
and the rest of them have some valid values, then I should just exclude
the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would
do this
select * from tblName
where column1 like @value1 and column2 like @value2 and column3 like
@value3
else if I have one of the parameter being passed as empty, I should
ignore that parameter like
if@value1 is empty then my sql should be
select * from tblName
where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue.
I want it through a stored procedure only.
Also, all the three columns can have null values in the table.
Please let me know what is the best possible way to do this. Thanks in
advance !.
.noscripthide
{display:none;}
.noscriptinline
{display:inline;}
.noscriptblock
{display:block;}
.scripthide
{display:none;}
.scriptinline
{display:inline;}
.scriptblock
{display:block;}
.script12hide
{display:none;}
.script12inline
{display:inline;}
.script12block
{display:block;}
.lnav
{position:absolute;}
.lnavch
{margin-left:23.0ex;}
.script13hide
{display:none;}
.script13inline
{display:inline;}
.script13block
{display:block;}
.hide
{display:none;}
.hide_ie
{;}
.hide_ie
{display:none;}
img
{border:0;}
img
{border-color:#0000a0;}
input.ck
{margin-left:-2px;}
input.bt, button.bt
{padding:0 .4em 0 .4em;width:auto;overflow:visible;}
input.bt, button.bt
{width:1px;}
.fixed_width
{font-family:fixed-width, monospace;font-size:90%;}
a:visited
{color:#551a8b;}
a:active
{color:#f00;}
.inheritcolor a
{color:inherit;}
.minmaxwie
{width:100%;}
* html .minmaxwie
{;}
.fl:visited
{color:#551a8b;}
.fl:active
{color:#f00;}
.fl:link
{color:#7777CC;}
.z
{display:none;}
.on:active
{color:#f00000;}
.don:active
{color:#f00000;}
.mbody
{margin-top:4px;}
body,td,input,textarea,select
{font-family:arial,sans-serif;}
body,td
{font-size:83%;}
input,textarea,select
{font-size:100%;}
form
{margin:0;}
.tick
{font-family:webdings;text-decoration:none !important;}
.qr
{width:100%;padding:4px;font-family:arial,sans-serif;}
.nu
{text-decoration:none;}
.gt
{border-collapse:collapse;}
.gt td
{padding:.3em 4px;border-right:1px solid #ffcc33;}
.gm td
{padding:.3em 1em .3em 0px;}
.bnk
{border:1px solid #ffcc33;}
.bnk td
{border-right-width:0px !important;}
.sel td.seltd
{padding:4px 4px 4px 4px;border:1px solid #ffcc33;border-right:none;font-weight:bold;}
p.b
{margin-bottom:1.5em;margin-top:.3em;}
.adb, .adbrnav
{border-left:1px solid #fff4c2;}
.msgdate
{color:#676767;}
.md
{color:#555555;}
.st
{margin-left:-1px;}
.nb
{white-space:nowrap;}
.np
{padding:0px;}
.p
{font-weight:bold;}
.mo
{margin:.5em 0 0 0;}
.oa
{padding:2px .5em;}
.sbox
{margin-top:1em;margin-bottom:1em;}
button a:link
{text-decoration:none;color:black;}
button a:hover
{text-decoration:none;color:black;}
.b
{font-weight:bold;}
.fontsize0
{font-size:78%;}
.fontsize1
{font-size:87%;}
.fontsize2
{font-size:96%;}
.fontsize_25
{font-size:100%;}
.fontsize3
{font-size:108%;}
.fontsize4
{font-size:120%;}
.fontsize5
{font-size:133%;}
.fontsize6
{font-size:150%;}
.fontsize7
{font-size:150%;}
.cv
{width:100%;}
.lk
{color:#0000CC;text-decoration:underline;cursor:pointer;}
.nl
{padding:5px 0 2px 5px;}
.tsh
{border-top:1px solid #ffcc33;}
.tlsh
{border-top:1px solid #ffcc33;}
.blsh
{border-bottom:1px solid #ffcc33;}
.bsh
{border-bottom:1px solid #ffcc33;}
.lsh, .tlsh, .blsh
{border-left:1px solid #ffcc33;}
.lnav
{left:9px;width:23.0ex;overflow:hidden;}
.lnavch
{;}
.lnavi
{font-size:100%;}
.lnavim
{margin-left:-2px;}
* html .lnav
{left:11px;}
.alertboxout
{margin:5px 15px 0px 10px;clear:left;}
.alertboxin
{clear:left;color:black;background-color:#fad163;font-weight:bold;text-align:center;padding:0px 15px 0px 15px;position:relative;margin:-1px 0px;}
.ctl
{padding-left:2px;}
.mb
{padding:6px 8px 0 5px;}
.exh
{margin:0 0 0 5px;background-color:#e8e8e8;}
.exh div div
{padding-top:4px;}
.thread_star
{padding:0 0 4px 2px;}
* html .thread_star
{padding:0 0 0 2px;}
.blurb_star
{padding:0 0 0 2px;}
* html .blurb_star
{padding:2px 0 0 2px;}
View 7 Replies
View Related
Aug 8, 2007
Hi,
In SQL 2000, to debug a stored proc I would launch QA, right click and hit debug.
How do I accomplish this with SQL 2005. I can't see that it came with QA.
Thank you,
Steve
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
Oct 2, 2007
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
View 3 Replies
View Related
Aug 8, 2002
i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.
for example i want to use
select p1 = sp_diff d1,d2 from table1
sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value
View 1 Replies
View Related
Aug 15, 2007
Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos
View 4 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