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)
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'.
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.
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.
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).
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)
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
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'
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
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
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?
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
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?
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
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.
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 !.
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
-- 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
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'
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?
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 .
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.
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.
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...
