SP's are still not my forte, but getting there!I am trying to hit two tables in this one and return the values but keep getting the error code = 1 built into the sp and can't see why.CREATE PROCEDURE dbo.retrieveScores @studentId VARCHAR(20), @courseId VARCHAR(20), @lessonLocation VARCHAR(20) OUTPUT, @lessonStatus VARCHAR(20) OUTPUT, @lessonScoreRaw VARCHAR(10) OUTPUT, @lessonScoreMin VARCHAR(10) OUTPUT, @lessonScoreMax VARCHAR(10) OUTPUT, @objId VARCHAR(20) OUTPUT, @objStatus VARCHAR(20) OUTPUT, @objScoreRaw VARCHAR(10) OUTPUT, @objScoreMin VARCHAR(10) OUTPUT, @objScoreMax VARCHAR(10) OUTPUT ASBEGIN DECLARE @errCode INT
DECLARE c_courseScores CURSOR FOR SELECT lessonLocation, lessonStatus, lessonScoreRaw, lessonScoreMin, lessonScoreMax FROM courseScores WHERE studentId = @studentId AND courseId = @courseId FOR READ ONLY
OPEN c_courseScores FETCH c_courseScores INTO @lessonLocation, @lessonStatus, @lessonScoreRaw, @lessonScoreMin, @lessonScoreMax CLOSE c_courseScores DEALLOCATE c_courseScores
DECLARE c_objScores CURSOR FOR SELECT objId, objStatus, objScoreRaw, objScoreMin, objScoreMax FROM objScores WHERE studentId = @studentId AND objId = @objId AND courseId = @courseId FOR READ ONLY
OPEN c_objScores FETCH c_objScores INTO @objId, @objStatus, @objScoreRaw, @objScoreMin, @objScoreMax CLOSE c_objScores DEALLOCATE c_objScores
IF( @@FETCH_STATUS <> 0 ) BEGIN SET @errCode = 1 GOTO HANDLE_APPERR END
SET @errCode = 0 RETURN @errCode
HANDLE_APPERR: IF( CURSOR_STATUS( 'local', 'c_courseScores' ) >= 0 ) BEGIN CLOSE c_courseScores DEALLOCATE c_courseScores END
SET @errCode = 1 RETURN @errCodeENDGOSuggestions?Thanks all,Zath
userid logintime logouttime subject question answer correct status 1000 9/2/2007 12:10:10 chemistry 0 1000 chemistry 1 T 1 1000 chemistry 2 F 0 1000 chemistry 3 a 1 1000 chemistry 4 a 0 1000 chemistry 5 b 1 1000 9/2/2007 12:20:30 chemistry 1 3000 9/2/2007 12:40:00 Math 0 3000 Math 1 c 1 3000 Math 2 a 1 3000 Math 3 T 0 3000 Math 4 F 1 3000 Math 5 b 0 3000 9/2/2007 12:50:25 Math 1 2000 9/2/2007 12:10:10 Biology 0 2000 Biology 1 T 1 2000 Biology 2 F 0 2000 Biology 3 a 1
Logintime means time person started the test. Logouttime means time person stopped the test. Correct 1 means correct answer Correct 0 means wrong answer Status 0 means test is started. Status 1 means test is stopped. (Some time people don't stop test due to network failure. So that record got no status 1 or logouttime).
i need to find total score of correct answer.
Based on below table, i need to find who passed and who failed. Subject Total_Question Main_Question Pass_Score Math 5 3,5 3 Biology 5 4 4 Chemistry 5 1,2 5
i need to write a stored procedure to do calculation.....so i want to find the below result......
userid logintime logouttime Duration Subject Score Question_Missed Result 1000 9/2/2007 12:10:10 9/2/2007 12:20:30 10:20 Chemistry 2 2 Fail 3000 9/2/2007 12:40:00 9/2/2007 12:50:25 10:25 Math 3 3,5 Pass 2000 9/2/2007 12:10:10 Biology 2 4 Fail How can i combine both table and get this result?...Please help me. I need help.
Hey all, Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation] @Id int, @Name varchar AS DELETE FROM Classifieds_Terminals WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this: USE [CLASSIFIEDSDB2.MDF] GO DECLARE @return_value int EXEC @return_value = [dbo].[RemoveLocation] @Id = 18, @Name = N'Terminal A' SELECT 'Return Value' = @return_value GO
It returns 0 and nothing happens....???
Here is the table: id | Name 18 Terminal A18 Terminal B18 Terminal C
Hi, I have a stored procedure which returns a dataset after some intense calculations. It actually calculates the daily work hours (wages) for each employee in a month. I am using a temporary table where for each employee, a day's calculations are stored as a row. So if e.g. there are 2 employees, a total of 2 x 31 = 62 rows are saved in the temporary table. In the end i flip the temporary table horizontally (each employee row than displays daily calculations for upto 31 days possibly) to produce a monthly report. The problem is that as the data has grown alot, it takes a very very long time (more than a minute) to calculate these values. The actual time is consumed in flipping the table horizontally. User can select any montly from Jan-Dec. Can any expert given me some advice on redefining this stored procedure (to optimize for speed) where i can calculate monthly wedges (each day included in the report) for each employee (over 400). In short, we can look at all daily calculations (31 days possibly) in one row for each employee. Regards....
How do most people handle database searches for things like a product database? What I mean by that is your typical product table may look like: ProductIDProductTitleProductDescriptionProductCategoryIDEtc... Assuming you have a Full Text Catalog set up on the ProductDescription field, would you use Dynamic Sql or a Stored Procedure? I'm wrestling with this because I haven't found a good way to either parse a parameter in SQL to make a stored procedure work with the same flexibility as Dynamic TSQL, and I would prefer not to have any direct access to the table from the application. My other option (which to me isn't a great one) is to have a fairly large number of optional "keyword" parameters in the stored procedure and then parse them on the application side...this is less than favorable in that in theory someone could pass more keywords than I've allotted in my stored procedure. How would you approach this challenge? Thanks in advance. Ryan
If I have a stored pocedure that just does one simple update command, do I need a commit after the update even if it is not part of a transaction. Or does it commit automatically? Thanks!
Hello everyone. I've attached a copy of my recently created stored procedure but now I want to expound on it by creating synatx within it that will allow me to create a 'weighting' type of scenario based on pre-determined weight/ranking classifications (example: a selection of skill '1' would grant the user 2 points toward their ranking, a selection of skill '2' might grant the user 4 pts., etc.) In the end, the users would be tallied and sorted based on the highest ranking (in pts) to the lowest. The business I'm in is that we develop a web site interface for recruiters and potential job seekers to post resumes, develop a career plan and rank their current work status against the open market. In short, does anyone out there know how I can implement a "ranking" type system into the syntax provided below? I've considered the CASE statement but was not clear on how it would work. Any suggestions would be great.
set nocount on select @jobcity = city, @jobposition = position_id, @jobrelocate = relocate_assist, @jobtravel = travel_id, @jobstate = state, @jobyears = position_yrs from t_job_order where job_id = @job_id
select @totcount = count(*) from t_job_vstat where job_id = @job_id
select @totcount = @totcount + 3
DECLARE Cand_Cursor CURSOR FOR
select candidate_id, key_skill_desc, strongest from t_cand_vstat, t_key_skill where t_cand_vstat.key_skill_id in (select key_skill_id from t_job_vstat where job_id = @job_id) and t_cand_vstat.key_skill_id = t_key_skill.key_skill_id order by candidate_id
CREATE TABLE #ReturnTemp ( candidateid uniqueidentifier NOT NULL, displayid int, city varchar(50), state varchar(2), Industry varchar(50), travel varchar(50), position varchar(50), hitcount smallint, tpercent numeric, ks1 varchar(50), ks2 varchar(50), ks3 varchar(50), ks4 varchar(50), ks5 varchar(50) )
OPEN Cand_Cursor
declare @candidate_id uniqueidentifier
FETCH NEXT FROM Cand_Cursor into @candidate_id, @key_skill_desc, @strongest select @holdid = @candidate_id
WHILE @@FETCH_STATUS = 0 BEGIN
if @candidate_id <> @holdid begin select @icount = @icount + 1 if @match = 1 update #ReturnTemp set hitcount = @icount, tpercent = (@icount/@totcount * 100), ks1 = @ks1, ks2 = @ks2, ks3 = @ks3, ks4 = @ks4, ks5 = @ks5 where candidateid = @holdid select @match = 1 select @ks1 = "" select @ks2 = "" select @ks3 = "" select @ks4 = "" select @ks5 = "" select @holdid = @candidate_id select @icount = 1 select @candrelocate = relocate, @candtravel = travel_id from t_cand_pref where candidate_id = @candidate_id select @candcity = city, @candstate = state, @displayid = display_id from t_candidate1 where candidate_id = @candidate_id select @candposition = position_id, @candyears = position_yrs, @candindustry = cat_sub_cat_id from t_cand_seek where candidate_id = @candidate_id if @candposition = @jobposition select @icount = @icount + 10 if @candyears = @jobyears select @icount = @icount + 8 if @candtravel = @jobtravel begin select @icount = @icount + 2 end
else if @jobtravel <> '91C858C8-4A46-4FD8-9B73-87FEE00F799E' begin if @candtravel = '91C858C8-4A46-4FD8-9B73-87FEE00F799E' begin select @match = 0 end else begin
select @icount = @icount + 1 end end
DECLARE City_Cursor CURSOR FOR
select distinct city, state from t_cand_pref_city_state C, t_city_state S where c.city_state = s.city_state and C.candidate_id = @candidate_id
OPEN City_Cursor
FETCH NEXT FROM City_Cursor into @prefcity, @prefstate
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM City_cursor into @prefcity, @prefstate select @citymatch = 0 if ((@prefcity = @jobcity) and (@prefstate = @jobstate)) begin --do nothing select @citymatch = 1 select @icount = @icount + 1 end
END
CLOSE City_Cursor
DEALLOCATE City_Cursor
if @citymatch = 0 select @match = 0
if @candindustry <> @jobindustry select @match = 0 if @strongest = 1 begin if @ks1 = "" select @ks1 = @key_skill_desc else if @ks2 = "" select @ks2 = @key_skill_desc else if @ks3 = "" select @ks3 = @key_skill_desc else if @ks4 = "" select @ks4 = @key_skill_desc else if @ks5 = "" select @ks5 = @key_skill_desc end if @match = 1 begin
select @candIndustrydesc = cat_sub_desc from t_cat_sub_cat where cat_sub_cat_id = @candIndustry select @candPositiondesc = position_desc from t_position where position_id = @candPosition select @candTraveldesc = travel_desc from t_travel where travel_id = @candtravel
INSERT INTO #ReturnTemp(Candidateid, displayid, city, state, Industry, travel, position, hitcount)
Hello, I'm new to SQL. I wrote this Stored Procedure but it does not work. When I'm executing it I get these errors:
Server: Msg 170, Level 15, State 1, Procedure Test, Line 12 Line 12: Incorrect syntax near '='. Server: Msg 170, Level 15, State 1, Procedure Test, Line 15 Line 15: Incorrect syntax near ')'. Server: Msg 170, Level 15, State 1, Procedure Test, Line 19 Line 19: Incorrect syntax near '='. Server: Msg 170, Level 15, State 1, Procedure Test, Line 24 Line 24: Incorrect syntax near '='
Can anyone please help me with that? Thank you in advance.
CREATE PROCEDURE Test As BEGIN TRANSACTION Select PVDM_DOCS_1_5.DOCINDEX1, TableTest.DOCINDEX2, TableTest.DOCINDEX3, TableTest.DOCINDEX4 From PVDM_DOCS_1_5, TableTest
WHERE TableTest.DOCINDEX1 = PVDM_DOCS_1_5.DOCINDEX1
AND (TableTest.DOCINDEX2 Is NULL or TableTest.DOCINDEX2 ='' OR TableTEst.DOCINDEX3 Is NULL or TableTest.DOCINDEX3 ='' or TableTest.DOCINDEX4 is NULL or TableTEst.DOCINDEX4 = '');
IF TableTest.DOCINDEX2 is NULL or TableTest.DOCINDEX2 = '' UPDATE TableTest.DOCINDEX2 = DOCINDEX2 WHERE (DOCINDEX1 IN (SELECT DOCINDEX1 FROM PVDM_DOCS_1_5)) END IF
If TableTest.DOCINDEX3 is NULL or TableTest.DOCINDEX3 = '' UPDATE TableTest.DOCINDEX3 = PVDM_DOCS_1_5.DOCINDEX3 WHERE (DOCINDEX1 IN (SELECT DOCINDEX1 FROM PVDM_DOCS_1_5)) END IF
If TableTest.DOCINDEX4 is NULL or TableTest.DOCINDEX4 = '' UPDATE TableTest.DOCINDEX4 = PVDM_DOCS_1_5.DOCINDEX4 WHERE (DOCINDEX1 IN (SELECT DOCINDEX1 FROM PVDM_DOCS_1_5)) END IF;
DELETE PVDM_DOCS_1_5 WHERE DOCINDEX1 = DOCINDEX1
IF (@@ERROR <> 0) GOTO on_error
COMMIT TRANSACTION -- return 0 to signal success RETURN (0)
on_error: ROLLBACK TRANSACTION -- return 1 to signal failure RETURN (1) GO
Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind? Stored ProcedureCREATE PROCEDURE InsertNearMiss @Branch Int, @Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@Branch) SET @Identity = SCOPE_IDENTITY() GO
Details : Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message. "The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM. If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab. Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Dear Friends, I have two tables........emp and salgrade
I need a procedure to update emp table with the given % hikes.The grades we should take from salgrade table
if the grade =1, then the hike will be 40% if the grade =2, then the hike will be 30% if the grade =3, then the hike will be 20% if the grade =4, then the hike will be 10%
so whenever i execute this procedure, the table should be automatically updated.
I am new to sqlserver.I got the doubt when i want to perform a task.Need yours advice
In a table i am having a column email with mailids. most of the rows are with the value email@email.com. My requirement is i want to change the value email@email.com as email1@email.com,email2@email.com,.....like that with autoincrement.Here primarykey attribute name is id
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a created a stored procedure which gets the values from the 'Orders' table. The procedure goes like this: CREATE PROC DataSource_GetDetails@OrderId int ASDECLARE @ColNumber int SELECT @ColNumber = (Select Coalesce(Col_length('Orders','BranchId'),0)) IF(@ColNumber >0) BEGIN SELECT Orders.OrderID, Orders.Buyer,Orders.Seller Orders.BranchIDFROM Orders WHERE Orders.OrderID = @OrderId END ELSE BEGIN SELECT Orders.OrderID, Orders.Buyer,Orders.Seller FROM Orders WHERE Orders.OrderID = @OrderId END Here i check whether a column named 'BranchId' exists in the 'Orders' table in a particular database and based on the result 2 different select statements are returned. The problem is, if i run this stored proc in the database which does not have a column 'BranchId' it throws me an errorMsg 207, Level 16, State 3, Line 1 Invalid column name 'BranchID'. How do i tackle this in the stored proc? Thanks
I am fairly new to using triggers and stored procs in SQL Server and could use some help.
Scenario:
I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.
Questions: 1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?
2) Can a Stored Proc be used in a trigger?
3) Can a Stored Proc call an outside function, such as an API call for third party software?
Hi all!I am in need of writing a few stored procedures.The first one is to create a stored procedure to recover a databasefrom backup and the second one is to create a stored procedure toexecute a transaction log backup (even though I know this can be donethrough a maintainence plan). Any help would be greatly appreciated.I know the commands to recover and backup -- but I dont know how toformulate them into a stored procedureThanks in advance!
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
Can someone please help me....I have created a DNN module that works on the test site but when I upload the module zip to a new site I get an error on creating my stored proceedure as follows:
Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'. Line 51: Incorrect syntax near '@Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC
EndJob End Sql execution: 01.00.00.SqlDataProvider file
The SP looks like this:
/* ------------------------------------------------------------------------------------- / ListTAS_Journal / ------------------------------------------------------------------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = ''
SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
This SP works on the test site. Any help would be creatly apreciated
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system. I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible? Thanks, Kevin
IF (@i_WildCardFlag=0)BEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END PRINT @SQLString SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)' EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS GO SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric. REQUIREMENT: The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks
