please take a look at this strored proxedure and tell what is wrong with it?
CREATE PROCEDURE sp_Pictures_CreateTextPost
@UserID Int,
@Country varchar (2),
@Description varchar (1000),
@PostID int output
AS
BEGIN TRANSACTION
INSERT INTO Pictures_TextPosts
(UserID, Country, Description)
VALUES
(@UserID, @Country, @Description)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
BEGIN
COMMIT TRANSACTION
UPDATE Accounts_Users SET Count_InsertText = Count_InsertText + 1 Where UserID = @UserID
SET @PostID = @@IDENTITY
RETURN 1
END
i run this sp on MS SQL server on my local machine and it works fine. now i move the same sp to the host server and it no longer works. it is the problem with this sp as i run sql query to test it. i use the ASP.NET Enterprise Manager to insert this sp. can someone please help?
one hint: on the host server, if i add 'GO' to the end of the sp, error occured.
I have written an ASP.net app that iterates through the controls on my page if the are checkboxlist or radiobuttonlist controls, the id and value are serialized into an xml file which is sent to a SQL 2000 stored procedure where the xml data is inserted into a couple of tables.
BEGIN TRANSACTION insert into ihsurveyresponse (userid, Surveyid, Responsedate) select userid, surveyid, getdate() from openxml(@idoc, '/schema7ihsurveyresponse', 1) with (userid int, surveyid int)
if @@error<>0 begin rollback transaction select 2 return end
set @responseid = scope_identity()
INSERT INTO IHSResponsedetail (Question,Result, Response) SELECT Question, Result, @responseid FROM OPENXML (@idoc, '/schema7ihSurveyResponse/ihsresponsedetail') WITH ( [Question] char(10) '@Question', Result int '@Result') EXECUTE sp_xml_removedocument @idoc
if @@error<>0 begin rollback transaction select 3 return end
COMMIT TRANSACTION select @responseid GO
If anyone has any insight I would really appreciate the assistance.
Hiya, all! I've got this stored procedure: CREATE Procedure CheckReOccurences @PatID INT, @CPT CHAR(7),@DOS DATETIME,@DRecur SMALLINT AS DECLARE @DaysElapsed INT SET @DaysElapsed = (SELECT DATEDIFF(day,ISNULL((SELECT MAX(ServiceDate) FROM VouchersDetail WHERE PatID = @PatID AND ProcCode = @CPT),01/01/1900),@DOS)) IF @DaysElapsed < @DRecur --Patient has had procedure done too recently BEGIN PRINT 'Too Recent-Only ' + str(@dayselapsed) + ' days elapsed' RETURN 0 END ELSE --More time has elapsed since procedure last performed than is necessary BEGIN PRINT 'Not Too Recent- ' + str(@dayselapsed) + ' days elapsed' RETURN 1 END And when I run it with the exec command in Query Analyzer, filling in all parameters with proper values, I get this error message: Server: Msg 8114, Level 16, State 4, Procedure CheckReOccurences, Line 0 Error converting data type nvarchar to datetime.
Please note Line says 0, when no such line exists. I've tried running every single part separately in QA, and all runs well (the functions work, returns expected code, etc.). What's the problem?
I am having trouble getting this procedure to properly enter the data into a table. It works when I do not add the @ToName info and only ask to insert the AccountID and FromName data. When i add the @ToNamevariable, no data is inserted. I think it might have to do with the select statement. I have two TerritoryID, RegionID, DivisionID, and EmpID because I want this information to be entered for both the FromName and the ToName selected. Does anyone know what my problem is? Id appreciate any help.
CREATE PROCEDURE InsertAllThree @AccountID char(10), @FromName nvarchar(50), @ToName nvarchar(50) AS
insert into accounttransferstestmike (AccountID, FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID, ToTerritoryID, ToRegionID, ToDivisionID, ToEmpID) select Accounts.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID From Accounts, EndoscopySqlUser.Territories INNER JOIN EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID Where (Accounts.AccountID = @AccountID) and (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromN ame) AND (EndoscopySqlUser.Territories.TerritoryName=@ToNam e) GO
When I try to create the stored procedure below, I receive the following error. Can someone please let me know what is wrong with the code? The problem seems to be with the default parameter of getdate(). This is because when I comment the default parameter, it works fine. Please note that I need the @BeginDate parameter to be varchar(10). I cannot have it as datetime.
Msg 156, Level 15, State 1, Procedure test, Line 4 Incorrect syntax near the keyword 'convert'. Msg 137, Level 15, State 2, Procedure test, Line 8 Must declare the scalar variable "@BeginDate".
/* Stored Procedure*/ Create PROCEDURE test --test @BeginDate varchar(10) = convert(varchar(10),getdate(),101) as select convert(datetime,@BeginDate)
Appreciate any help that you can offer, I have three tables that need data entered when the user completes a web form, in short the tables are
dbo.JBAClient Which has JBCID as PK and other data
dbo.JBAEmployee Which has JBEID as PK and JBEClientID as a Foreign Key and other data
dbo.JBAdvert Which has JBAID as PK, JBAEmployeeID as Foreign Key , JBAClientID as Foreign Key and other data,
The stored procedure that I've tried to write is supposed to create a new client record and enter all of the relevant data into the client table (store the JBCID) then Create a new Employee Record, insert all relevant data including (JBCID into JBEClientID) and (Store both JBCID & JBEID). Finally create a new advert record and store all of the relevant data including (JBCID as JBAClientID and JBEID as JBAEmployeeID)
The stored procedure that I have written works fine until the last section, where instead of inserting JBCID into JBAClientID and JBEID into JBAEmployeeID, it inserts JBEID into JBAClientID and JBEID into JBAEmployeeID
So inserting JBEID twice and ignoring JBEClientID........
I have the following stored proceduredrop procedure ce_selectCity;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================create PROCEDURE ce_selectCity @recordCount int output -- Add the parameters for the stored procedure here --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>AS declare @errNo int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select ciId,name from ce_city order by name select @recordCount = @@ROWCOUNT select @errNo = @@ERROR if @errNo <> 0 GOTO HANDLE_ERROR return @errNoHANDLE_ERROR: Rollback transaction return @errNoGoand i was just testing it likeProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load db.connect() Dim reader As SqlDataReader Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection) Dim recordCountParam As New SqlParameter("@recordCount", SqlDbType.Int) Dim errNoParam As New SqlParameter("@errNo", SqlDbType.Int) recordCountParam.Direction = ParameterDirection.Output errNoParam.Direction = ParameterDirection.ReturnValue sqlCommand.Parameters.Add(recordCountParam) sqlCommand.Parameters.Add(errNoParam) reader = db.runStoredProcedureGetReader(sqlCommand) If (db.isError = False And reader.HasRows) Then Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />") While (reader.Read()) Response.Write(reader("ciId") & "::" & reader("name") & "<br />") End While End If db.close() End SubIt returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).BUT, its returning 0.What am i doing wrong??EDIT:Oh, and this is the function i use to execute stored procedure and get the readerPublic Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader sqlCommand.CommandType = CommandType.StoredProcedure Return sqlCommand.ExecuteReader End Function
1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error. Can somebody tell me please what wrong ?
ALTER PROCEDURE dbo.CreateCategory ( @category_name varchar(100) )
AS declare @place int
/* setting place to zero */ SET @place = 0
/* trying to get last place and set value into @place */ SET @place = (Select max(category_place) FROM fo_categories)
/* if got nothing from table then setting value of first category */ IF (@place = 0) set @place = 1
Running dbo."CreateCategory" ( @category_name = Public forums ). Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails.
2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?
So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it. Here is the code:#############################################ALTER PROCEDURE dbo.GetUserIdForUser @username NVARCHARASBEGINDECLARE @postedbyid UNIQUEIDENTIFIERSET @postedbyid = (SELECT UserIdFROM aspnet_UsersWHERE (UserName = @username))END###Which returns this### Running [dbo].[GetUserIdForUser] ( @username = jason ).No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser].############################################# This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build. If anyone can tell me what I am doing wrong I would appreciate it. I have tried a few things that have resulted in different failures. If I remove any references to variables (delete SET @postedbyid = and replace @username with 'jason') I can get it to return a result. If I put @username in though it doesn't work. Here are the examples of those:ALTER PROCEDURE dbo.GetUserIdForUser @username NVARCHARASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = @username)END###Which returns this###Running [dbo].[GetUserIdForUser] ( @username = jason ).UserId -------------------------------------- No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Here is a sanity check to show that the data is in fact in the database:ALTER PROCEDURE dbo.GetUserIdForUserASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = 'jason')END Running [dbo].[GetUserIdForUser].UserId -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Anyone have any ideas on what I am doing wrong?
oConn = New SqlClient.SqlConnection oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;" oCmd = New SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "TestStdInfo" 'parameters block oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString()) oParam1.Direction = ParameterDirection.Input oParam1.SqlDbType = SqlDbType.Int oCmd.Parameters.Add(oParam1) oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text)) oParam2.Direction = ParameterDirection.Input oParam2.SqlDbType = SqlDbType.DateTime oCmd.Parameters.Add(oParam2) oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text) oParam3.Direction = ParameterDirection.Input oParam3.SqlDbType = SqlDbType.VarChar oCmd.Parameters.Add(oParam3) oConn.Open() daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn) dsStudent = New DataSet daStudent.Fill(dsStudent) 'This line is highlighted when error happens oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
I get the following error for this procedure:Server: Msg 156, Level 15, State 1, Procedure Trig_Ins_TrackingNumbers,Line 6Incorrect syntax near the keyword 'IF'.CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbersINSTEAD OF INSERTASBEGINIF (SELECT ISNUMERIC(InvoiceNumber) FROM inserted)IF(EXISTS (SELECT I.InvoiceNumberFROM Invoices I, inserted InsWHERE I.InvoiceNumber=CAST(Ins.InvoiceNumber AS INT)))INSERT INTO TrackingNumbers(InvoiceNum,InvoiceNumber,TrackingNumber)SELECT CAST(InvoiceNumber AS INT), InvoiceNumber, TrackingNumberFROM insertedEND
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?
Bellow is the stored procedure. I wanted to do an If / Else statement. I'm getting a syntax error that something is wrong around my Begin / Else statements. If anyone knows what is wrong I would greatly appriciate it.
Thanks in advance as always. RB <code> Create Proc UpdateFundsAndTotals ASIF (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPledges.fundID AND FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)Begin Update FundsAndTotals Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledgesENDELSE (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPayments.fundID AND FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)Begin Update FundsAndTotals Set FundsAndTotals.TotalPayments = TotalPayments.TotalPaymentsENDElseEND </code>
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.
Hi, I'm getting an error ERROR 137: Must declare the variable '@' when I click the check sysntax button on my store proc window. Any ideas on how to resolve this??
CREATE PROCEDURE CabsSchedule_GetPrevYearMonth AS DECLARE @prvYearMonth int DECLARE @prvYear int DECLARE @prvMonth int
BEGIN SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule) IF ((SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @ prvYearMonth) > 15) ELSE BEGIN IF (SUBSTRING(CAST(@prvYearMonth AS VarChar(6)), 5, 2) = '01') BEGIN SET @prvMonth = 12 SET @prvYear = CAST(SUBSTRING(CAST(@prvYearMonth AS VARCHAR(6)),1,4) AS INT) - 1 SET @prvYearMonth = CAST(CAST(@prvYear AS VARCHAR(4)) + CAST(@prvMonth AS VARCHAR(2)) AS INT) END END END GO
In SQL Server for a field of datatype float(8), if i insert a value 2.62 , it saves it as 2.6200000000000001 like so for other values also. But in frontend i can see the right values.
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
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
I have an extremely annoying problem when debugging stored procedures in SQL Server 2014 with SSDT or SSMS. When calling a SP thru EXECUTE in Debug mode, 9 out of 10 SPs are traced with a wrong yellow arrow-pointer to the line currently reached.
The offset is between 6 to 15 lines downward. Tracing itself and update of the "Locals"-view works as expected. All SPs contain comments also before the Create Procedure statement. The SP shown when tracing show exactly the same content as the stored SQL in the SSDT project under work incl. Create procedure and all comments.
The picture here show the first line selected after the debugger has traced into the SP. The first line really executed with "Next" will be SET NOCOUNT ON.
If this does not turns out as my fault and some of you would support that, I would like to post this to SQL Connect.
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
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.
