Deadlock From Calling Same Stored Procedure
Mar 20, 2007
I'm having a deadlock issue with a SQL Express database and a stored procedure call that ends up adding rows to three different tables, with the following basic heirarchy:
[Top]
|
+-----[Mid]
|
+------[End]
This was originally setup with auto-generated keys at each level, and the psuedo code for the stored procedure that's being called is basically:
begin transaction
insert into TopTable values from openXML
select @topID = scope_identity()
insert into MidTable values from openXML and @topID
insert into EndTable values from openXML and (select ID from MidTable that was just inserted)
commit transaction
Using the profiler there are a few places where the deadlocks occur between the 2nd and 3rd insert, it's always an index lock on the TopTable primary key or the MidTable primary key. The deadlock even occurs if the 3rd insert is taken out. We've tried changing from using autogenerated values to using natural keys, but have a very similar deadlock. Because time was short, we put an application lock in for the production code - it fixes it but testing shows it won't scale very well. The only two other things we've been able to get to work is putting a table lock on each table, or by using natural keys and relaxing the foreign key constraints. The table lock is a no-go because an SSIS package needs to read from the data periodically, and relaxing of the foreign key constraints has it's own problems - though that is where we're currently leaning if we can't come up with some other solution.
My collegues and I have been searching out the issue and working on the problem off and on for the last several days, but we're still a bit stuck. Is there something we've missed? Any pointers to a possible solution?
Following is the deadlock graph data from one of the deadlocks:
Deadlock graph 1379 1 sa 0X01 19 Z014719 2007-03-20 10:10:20.527 <deadlock-list>
<deadlock victim="process5b9c48">
<process-list>
<process id="process5b8c58" taskpriority="0" logused="1268" waitresource="KEY: 7:72057594039304192 (f60073ab7d7b)" waittime="4062" ownerId="48709" transactionname="user_transaction" lasttranstarted="2007-03-20T10:10:16.400" XDES="0x5b8d190" lockMode="S" schedulerid="1" kpid="3640" status="suspended" spid="56" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2007-03-20T10:10:16.370" lastbatchcompleted="2007-03-20T10:10:16.370" clientapp=".Net SqlClient Data Provider" hostname="Z014719" hostpid="3040" loginname="HTCHCON000307" isolationlevel="read committed (2)" xactid="48709" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="DeadlockTest.dbo.spWriteTable" line="59" stmtstart="2758" stmtend="4272" sqlhandle="0x030007003ec4c60b2a379700f79800000100000000000000">
insert MidTable (
TopID,
[Name],
[Value])
select
TopID = @TopTableId,
[Name] = r.[Name],
[Value] = r.[Value]
from OPENXML (@hdoc,'/data/top/mid', 1)
WITH ([Name] VARCHAR(50), [Value] int)r</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 197575742] </inputbuf>
</process>
<process id="process5b9c48" taskpriority="0" logused="624" waitresource="KEY: 7:72057594039304192 (f5009d04c869)" waittime="4062" ownerId="48726" transactionname="user_transaction" lasttranstarted="2007-03-20T10:10:16.400" XDES="0x5bd8860" lockMode="S" schedulerid="1" kpid="3600" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2007-03-20T10:10:16.370" lastbatchcompleted="2007-03-20T10:10:16.370" clientapp=".Net SqlClient Data Provider" hostname="Z014719" hostpid="3040" loginname="HTCHCON000307" isolationlevel="read committed (2)" xactid="48726" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="DeadlockTest.dbo.spWriteTable" line="59" stmtstart="2758" stmtend="4272" sqlhandle="0x030007003ec4c60b2a379700f79800000100000000000000">
insert MidTable (
TopID,
[Name],
[Value])
select
TopID = @TopTableId,
[Name] = r.[Name],
[Value] = r.[Value]
from OPENXML (@hdoc,'/data/top/mid', 1)
WITH ([Name] VARCHAR(50), [Value] int)r</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 197575742] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594039304192" dbid="7" objectname="DeadlockTest.dbo.TopTable" indexname="PK_TopTable" id="lock35dc680" mode="X" associatedObjectId="72057594039304192">
<owner-list>
<owner id="process5b8c58" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5b9c48" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594039304192" dbid="7" objectname="DeadlockTest.dbo.TopTable" indexname="PK_TopTable" id="lock35dcb00" mode="X" associatedObjectId="72057594039304192">
<owner-list>
<owner id="process5b9c48" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5b8c58" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
View 10 Replies
ADVERTISEMENT
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
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)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'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.
View 11 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
Oct 12, 2006
I have two store rpcedure as shown bellow,
When I run first dt_deadlock2 and then dt_deadlock1 deadlock happend and dt_deadlock1 is discarded by SQL server giving the deadlock message. What is the reason for it ?
CREATE PROCEDURE [agcdb].[dt_deadlock2] AS
BEGIN TRAN
UPDATE t1 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:10'
Select * from t1
COMMIT
GO
CREATE PROCEDURE [agcdb].[dt_deadlock1] AS
BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT
GO
Regards
Anil
View 7 Replies
View Related
Jun 12, 2007
We just went live today with a production SQL Server 2005 databaserunning with our custom Java application. We are utilizing the jTDSopen source driver. We migrated our existing application which wasusing InterBase over to SQL Server. To minimize the impact to ourcode, we created a stored procedure which would allow us to manage ourprimary key IDs (mimicing the InterBase Generator construct). Nowthat we have 150+ users in the system, we get the following errorperiodically:Caused by: java.sql.SQLException: Transaction (Process ID 115) wasdeadlocked on lock resources with another process and has been chosenas the deadlock victim. Rerun the transaction.atnet.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:365)at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)atnet.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:418)atnet.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:696)at database.Generator.next(Generator.java:39)Here is the script that creates our stored procedure:USE [APPLAUSE]GO/****** Object: StoredProcedure [dbo].[GetGeneratorValue] ScriptDate: 06/12/2007 10:27:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetGeneratorValue]@genTableName varchar(50),@Gen_Value int = 0 OUTASBEGINSET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT @Gen_Value = GENVALUE FROM GENERATOR WHEREGENTABLENAME=@genTableNameUPDATE GENERATOR SET GENVALUE = @Gen_Value+1 WHEREGENTABLENAME=@genTableNameCOMMIT;SET @Gen_Value = @Gen_Value+1SELECT @Gen_ValueENDThis stored procedure is the ONLY place that the GENERATOR table isbeing accessed. If anyone can provide any guidance on how to avoidthe deadlock errors, I would greatly appreciate it. The goal of thisstored procedure is to select the current value of the appropriaterecord from the table and then increment it, ALL automically so thatthere is no possibility of multiple processes getting the same IDs.
View 5 Replies
View Related
Jul 23, 2005
Here's a really weird one for any SQL Server gurus out there...We have observed (SQL Server 2000) scenarios where a stored procedurewhich(a) begins a transaction(b) inserts some rows into a table(c) re-queries another table using a subquery which references theinserted table (correlated or not)will deadlock itself at point (c), even when it is the only task onthe server.[I use the term 'deadlock' because I can't think of anything elseappropriate, even though, as stated, this is the ONLY task executing,so this isn't a classical deadlock situation]A typical deadlocking scenario would be (assume insert_table is thetable into which some rows are being inserted)begin transactioninsert insert_table(col....) select (col....) from some_other_table/* this following query will deadlock and never complete */select some_other_table.colfrom some_other_tablewhere not exists (select *from insert_tablewhere some_other_table.col = insert_table.col )Whereas if the offending second query in the sequence is rewritten asa joine.gselect some_other_table.colfrom some_other_tableleft join insert_tableon some_other_table.col = insert_table.colwhere insert_table.col is nullthe query will not deadlock.If the subquery is an aggregate function, a deadlock will also notoccur.If the transaction is committed prior to executing the blocking query,then it will not block (hardly surprising; if it did, there'd be majorproblems with SQL Server!).Note that this is a canonical case of a much more complex SP, and thatsimplified test cases often will not deadlock; you need a significantamount of data, typically 30,000 rows or more to see the problem. Theblocking query is, in real life, used to drive a subsequent tableinsert operation, but this is not relevant to the problem.We conclude that there is some problem, possibly involving cataloguecontention, if a temporary table must be created in a subquery withina transaction in a stored procedure, and if the subquery involvesreferences to a table for which locks have been acquired.Note that the lock timeout will never trigger and a deadlock victim isnever chosen, presumably because the deadlock occurs entirely withinthe same SPID.Locking hints and transaction isolation level setting does not affectthe result. Note also that the exact same queries, executed as a TSQLbatch, do not deadlock; you must place them in a stored procedure.Recovery mode for the database is SIMPLE, and the problem is portableacross databases; it can also be exhibited with MSDE/2000, and isindependent of whether or not the database server is the local machineor not.Has anyone else experienced this problem and/or know of a workaround,other than those mentioned here?. It does look awfully like a bug withSQL Server, since a single task should never be able to deadlockitself, surely.
View 7 Replies
View Related
Feb 4, 2008
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies
View Related
Aug 31, 2015
We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.
What could be done to avoid deadlock.
View 5 Replies
View Related
Oct 10, 2006
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
View 16 Replies
View Related
Mar 2, 2007
Hello people,
When I am trying to call a function I made from a stored procedure of my creation as well I am getting:
Running [dbo].[DeleteSetByTime].
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[DeleteSetByTime].
This is my function:
ALTER FUNCTION dbo.TTLValue
(
)
RETURNS TABLE
AS
RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'
This is my stored procedure:
ALTER PROCEDURE dbo.DeleteSetByTime
AS
BEGIN
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
CreatedTime is a datetime column and TTL is an integer column.
I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.
Your help is much appreciated.
View 6 Replies
View Related
Mar 23, 2007
Hi, i've had this query method:
34 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)35 {36 SqlConnection oConn = new SqlConnection(_connectionString);37 string strSql = "Insert into LogDetail (LogID, CategorieID, Inhoud)";38 strSql += "values(@logID, @categorieID, @inhoud)";39 SqlCommand oCmd = new SqlCommand(strSql, oConn);40 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;41 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;42 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;43 44 try45 {46 oConn.Open();47 int rowsAffected = oCmd.ExecuteNonQuery();48 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");49 oCmd.CommandText = "select @@IDENTITY";50 oCmd.Parameters.Clear();51 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();52 }53 catch (Exception ex)54 {55 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);56 }57 finally58 {59 if (oConn.State == ConnectionState.Open) oConn.Close();60 }61 }
which i've converted to a stored procedure: 1 ALTER PROCEDURE [dbo].[insert_DagVerslagDetail]
2 -- Add the parameters for the stored procedure here
3 @dagverslagdetailID int,
4 @logID int,
5 @categorieID int,
6 @inhoud varchar(100)
7 AS
8 BEGIN
9 -- SET NOCOUNT ON added to prevent extra result sets from
10 -- interfering with SELECT statements.
11 SET NOCOUNT ON;
12 SET @dagverslagdetailID = SCOPE_IDENTITY()
13
14 -- Insert statements for procedure here
15 BEGIN TRANSACTION
16 INSERT LogDetail (LogID, CategorieID, Inhoud)
17 VALUES(@logID, @categorieID, @inhoud)
18 COMMIT TRANSACTION
19 END
Now i would like to call that stored procedure in my previous method, so i've changed it to this:
1 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslagDetail";
5 strSql += "values(@logID, @categorieID, @inhoud)";
6 SqlCommand oCmd = new SqlCommand(strSql, oConn);
7 oCmd.CommandType = CommandType.StoredProcedure;
8 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;
9 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;
10 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;
11
12 try
13 {
14 oConn.Open();
15 int rowsAffected = oCmd.ExecuteNonQuery();
16 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");
17 oCmd.CommandText = "select @@IDENTITY";
18 oCmd.Parameters.Clear();
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
20 }
21 catch (Exception ex)
22 {
23 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);
24 }
25 finally
26 {
27 if (oConn.State == ConnectionState.Open) oConn.Close();
28 }
29 }
Do i still need the lines 17 oCmd.CommandText = "select @@IDENTITY";
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
Because i've declared the identity in my stored procedure
View 1 Replies
View Related
Apr 8, 2008
Hi, I have a stored procedure, and it is expecting an output. If I declared the passing varaible as ref, it compiles fine, but it is not returning any value. If I pass the varaible as out, and add the paramater
MyComm.Parameters.Add(new SqlParameter("@ReturnValue", returnValue)); it gives the following error.
Compiler Error Message: CS0269: Use of unassigned out parameter 'quoteID'.
And if I don't supply the previous statement, the following error occurs.
System.Data.SqlClient.SqlException: Procedure 'CreateData' expects parameter '@ReturnValue', which was not supplied.
How Can I fix this? thanks.
View 2 Replies
View Related
Dec 15, 2003
I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:
Stored Procedure:
CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUSsqlServer113 /NdtsPackage /UuserID /Ppassword'
GO
VB to run DTS:
Dim myCommand As SqlCommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "spSampleData"
myCommand.ExecuteNonQuery()
I'm not sure what I am doing wrong but any help would be great.
Thanks!
View 3 Replies
View Related
Feb 6, 2004
I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:
<%@ Page Language="VB" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.SqlClient" %>
.
.
.
Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn)
cmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = cmdInsert
cmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "Account_Number"))
cmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(New SqlParameter("@UserID", SqlDbType.varchar, 50, "UserID"))
cmdInsert.Parameters("@UserID").Value = Request("UserID")
cmdInsert.Parameters.Add(New SqlParameter("@Password", SqlDbType.varchar, 32, "Password"))
cmdInsert.Parameters("@Password").Value = Request("Password")
cmdInsert.Parameters.Add(New SqlParameter("@First_Name", SqlDbType.varchar, 32, "First_Name"))
cmdInsert.Parameters("@First_Name").Value = Request("FirstName")
cmdInsert.Parameters.Add(New SqlParameter("@Middle_Name", SqlDbType.varchar, 32, "Middle_Name"))
cmdInsert.Parameters("@Middle_Name").Value = Request("MiddleName")
cmdInsert.Parameters.Add(New SqlParameter("@Last_Name", SqlDbType.varchar, 32, "Last_Name"))
cmdInsert.Parameters("@Last_Name").Value = Request("LastName")
loConn.Open()
command.ExecuteNonQuery()
loConn.Close()
I get the following error:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'CommandType' is not declared.
This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure
Any help would be appreciated,
Greg
View 2 Replies
View Related
May 22, 2000
Hello everybody,
How can I call DTS from stored procedure, any help pls.
PS: I appreciate if you please give me an example.
Thank you....
View 2 Replies
View Related
Jan 19, 2001
I'm trying to call a job from a stored procedure.
To do so, I've found that sp_start_job is doing just that.
My problem is that the sp_start_job is not in my master
database stored procedures and I don't know how to add it.
I'm working with SQL Server 7 Enterprise without the SP.
Am I looking for the right thing ? (sp_start_job)
Where can I find it ?
View 1 Replies
View Related
Nov 3, 2005
Hey, I have a parent SP, and within that parent I want to call a a child what is the code to call that child procedure? or teh easiest way to make that happen?
View 1 Replies
View Related
Jan 30, 2004
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
Thanks
View 1 Replies
View Related
Apr 7, 2004
When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem?
Thanks...
View 1 Replies
View Related
Apr 29, 2008
Hi,
I am new to SQL and new to stored procedures!
What I am trying to do is call one stored procedure from another stored procedure. Very simple piece of code but can not get it to work correctly.....
This is the calling stored procedure: sp_TechRiskMandatory. It is calling a stored procedure called sp_Test.
What is happening is that it is executing the line before the "EXECUTE" command and never getting to this line.
If I put the "EXECUTE" command first it will execute this line and not get to the next.
My code is returning out of the stored procedure before finishing executing the remainder of the code....
CREATE PROCEDURE [sp_TechRisk_Mandatory]
@Conclusion varchar(100),
@TechRisk varchar(100)
AS
If (@Conclusion = 'Application/Changed') and ((@TechRisk = " ") or (@TechRisk = "N/A"))
Begin
Select "AsxErrorMessage" = "Technical Risk must be specified"
EXECUTE sp_Test
End
Else
Begin
Select "Looks Good" = " "
EXECUTE sp_Test
End
GO
This is the code for the sp_Test:
CREATE PROCEDURE sp_Test
AS
BEGIN
Select "AsxErrorMessage" = "Test"
END
GO
View 2 Replies
View Related
May 30, 2008
I know this thread is sql, and i'm asking a vb.net question but I cannot find a straight forward answer anywhere else. I am using visual studio 2008 designing a vb.net application where I created a stored procedure using sql management studio 2005. Here is the stored procedure:
CREATE PROCEDURE dbo.StoredProcedure2
@intPID char(10)
AS
SELECT SUM(Financial.Fee) from dbo.Financial
WHERE
CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, getdate())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Financial.Date)))
AND [SPatient Number]=@intPID
/* SET NOCOUNT ON */
RETURN
It takes the sum of my Fee column in my financial table where the current date is equal to the column named Date in my Financial table and SPatient Number equals my variable intPID, which the value is defined for in my application (yes, i know that is a horrible name for a column but I cannot change it for it is not my project).
Now, to my knowledge this procedure works fine and should output a single value. However, like i said, i am using visual studio 2008 and therefore am using vs's more automated way of connecting to sql servers(and by that I mean configuring the server through visual studio rather than manually defining datasets, dataadapters, and connection strings through code) All of the tutorials I find use data adapters and are done by manually declaring sql connections and so forth. I like visual studios more automated method of doing sql tasks, and would like to know if there is a simple way to call a stored procedure using visual studio in such a fashion where I would write something like "exec dbo.StoredProcedure2 'intPID' "
Any help is much appreciated, thank you
edit: If i did not provide enough information please let me know, i'm using a strongly typed dataset
View 1 Replies
View Related
Jun 10, 2008
I understand how to call a stored procedure using ordinal arguments, such as:
exec storedprocedure argument1 argument2
How do you do it while naming the arguments, and passing non-ordinal?
Thanks!
View 2 Replies
View Related
Nov 19, 2014
I have three stored procedure already created ABC. Now I need to create another one and call other three in each situation. Like If Apple then use Sp_A, if Orange then use Sp_B, and if Mango then use sp_C.
View 16 Replies
View Related
Mar 28, 2006
Hi all, I'm new to SQL Server and I'm trying to call BCP from a stored procedure with a parameter passed in as the path to which to export the datafile. This parameter is also the name of a network PC. However, I keep getting this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL
This is the stored procedure:
PROCEDURE DownloadLinkEvents
@localPath varchar(80)
AS
declare @bcpCommand varchar(200)
begin
set @bcpCommand = 'bcp <dbName> out ' + @localPath + '-c -t"|" -S<dbServer> -Usa -P<passowrd>'
exec master..xp_cmdshell @bcpCommand
end
Thanx.
View 3 Replies
View Related
Feb 29, 2008
Hi,
This is probably the most basic of solutions, but I have spent the last 3 hours trying to work it out, and searching google!
I am trying to call information from a stored procedure (B), from within another stored procedure (A). The select statement from Procedure A contains information to be passed to Procedure B, to get some information.
This is the Procedure I have come up with so far, and I have included dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) as part of the SELECT clause, in a vain attempt that this would work....but it dosn't!
Anyone got any ideas of how to do this? Or even what it would be called so I can start making inteligent searches on google?
USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/29/2008 18:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@STYPE Int, @UserID Int, @Username varchar(100), @Surname varchar(100), @DOB VarChar(40), @Location varchar(100), @Email Varchar(100), @Mobile varchar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age, dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID)
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
WHERE @STYPE = '1' AND ((dbo.Members.EntryID = @UserID) or
(dbo.Members.Username = @Username) or
(dbo.Members.Surname = @surname) or
(dbo.Members.DateofBirth = Convert(datetime, @DOB)) or
(dbo.ActiveMember_Location.Location2 = @Location) or
(dbo.ActiveMember_Location.Location3 = @Location) or
(dbo.ActiveMember_Location.Location4 = @Location) or
(dbo.ActiveMember_Email.value = @Email) or
(dbo.ActiveMember_Mobile.value = @Mobile))
ORDER BY dbo.Members.Username
View 1 Replies
View Related
Aug 11, 2005
wanted to use sp_OACreate, sp_OAMethod and sp_OADestroy to execute aDTS package from a stored procedure. I had the dba (using the saaccount) create a wrapper stored procedure as recommended inhttp://msdn.microsoft.com/library/d...rary/en-us/dnsq....However, when I executed the wrapper stored procedure, I stillreceived privilege errors from the underlying sp_oa extended storedprocedures.Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 6EXECUTE permission denied on object 'sp_OACreate', database 'master',owner 'dbo'.Any ideas on what we could be doing wrong, or any suggested resolutionswould be appreciated.Thank you.
View 1 Replies
View Related
Aug 25, 2005
I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.
View 5 Replies
View Related
Oct 2, 2007
I wrote a stored a stored procedure in SQL 2005 which finds a specific number. If I execute the procedure in SQL it finds the correct number. I want to call this stored procedure from within Visual Basic 2005 so I can use it in my program.
Any thoughts???
View 1 Replies
View Related
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have a stored procedure that return a resultsete.g. stored proc: get_employee_detailsselect emp_id, emp_name, emp_salary, emp_positionfrom empoloyeeI would like to write another stored procedure that executes the abovestored procedure - returning the same number of records but it willonly show 2 columnse.g. new stored proc: get_employee_pay -- executesget_employee_detailsI only need to know emp_id, emp_salary.How can this be done in sql stored procedure?Thanks,June Moore.
View 2 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Nov 12, 2006
I am trying to execute a store procedure from ASP/VB but it fails with the message:
Incorrect syntax near 'InitProject'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'InitProject'.Source Error:
Line 24: cmd.Parameters("@ProjectId").Value = 3
Line 25: cn.Open()
Line 26: cmd.ExecuteNonQuery()
Line 27: cn.Close()
Line 28: End Sub
Here is my code:
'Execute the InitProject stored procedure 'Create the connection from the string in the web.config file Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString) 'I want to execute InitProject stored procedure Dim cmd As SqlCommand = New SqlCommand("InitProject", cn) 'With the parameter @ProjectId = 3 cmd.Parameters.Add(New SqlParameter("@ProjectId", Data.SqlDbType.Int)) cmd.Parameters("@ProjectId").Direction = Data.ParameterDirection.Input cmd.Parameters("@ProjectId").Value = 3 cn.Open() 'But this fails cmd.ExecuteNonQuery() cn.Close()
And my stored procedure is defined as:
[dbo].[InitProject] @ProjectId int -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure hereinsert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )select @ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID from MATERIAL_TEMPLATEEND
The store procedure works fine when I do
exec InitProject 3
in sql query.
View 4 Replies
View Related
Jan 8, 2007
Hi,
I am working with multiple databases on the same server and in a stored procedure I need to be able to call on one of them.
Here is an example of what I am trying to do in this stored procedure:create procedure sp_procedure(@variable int)select anitem from atable where selection = @variable
declare @anothervariable Char(3)
select @anothervariable = item_that_determines_database from atable where selection = @variable
use dbo.@anothervariableselect count(id) from Some_table where selection = @variable
The database is not found using this method and I do need to use it in a stored procedure. All of the databases being used are (3) letter names in lower case (aaa, bbb, ccc, etc...), the info that @anothervariable pulls from the table is the name of that database but in all caps. Does this part make a difference?
Also, what method could I use to get the database variable to read from that selected database?
Thank-you for your help.
Eric
View 3 Replies
View Related
