Calling A Function From A Stored Procedure
Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.
The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:
'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query:
SELECT *
FROM UDFmanufacturer_SearchName('mol')
SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol'))
This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
I'm stuck. I haven't been able to find anything that shows me where I'm going wrong. Any thoughts or suggestions are appreciated.
Thanks,
Jay
View Complete Forum Thread with Replies
Related Forum Messages:
Stored Procedure And Calling User Defined Function
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-) Short list of process flow: 1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER 2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient 3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient 4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page 5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny. <FLOW WITH CODE AND FUNCTIONS :> This is not the correct syntax but is showing what I understand sort of how this is to be done so far. This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process. This is code in Form.asp 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check Postback Type 'We need to load up vendors associated with the current client. '--------- CHECK ACCESS HERE via function ab_HasAccessToClient -------- 'If the call returns 1, then the employee has access. 'Otherwise, just write out "Access to this client is denied." 'CALL SP - Not sure what parameters need to go with it or its syntax Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1) 'When it returns can check it here........ if ab_HasAccessToClient result is a 1 then 'boolean would be 1 so show panel Else 'boolean would be 0 so show access denied 'allow them to go back to the original page. end if 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > ON SQL SERVER: Stored Procedure ---------------------------------------------------------- -------------------------------- rx_sp_HasAccessToClient CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] @EmployeeID INT, @ClientSSN varchar(50), @ReturnBitValue = OUTPUT /* ' Parameters here passed via call from Form.asp - not sure what is passed yet. */ AS set nocount on /* Written by Mike Belcher 9/27/2007 for Form.asp 'Calls ab_HasAccessToClient function - not sure of the syntax as of yet, just making flow. 'Gets return bit and passes that back to the call from Form.asp */ GO ---------------------------------------------------------- -------------------------------- ON SQL SERVER: User-Defined Function ---------------------------------------------------------- -------------------------------- ab_HasAccessToClient CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN VARCHAR(50)) @ClientSSN varchar(50), @EmployeeID, @ReturnBitValue = OUTPUT AS SELECT 1 FROM tblEmployeesClients ec INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName WHERE e.EmployeeID= @EmployeeID AND c.InActiveClient=0 AND c.ClientSSN = @ClientSSN 'Some Code here to save result bit .. RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient ---------------------------------------------------------- -------------------------------- </FLOW WITH CODE AND FUNCTIONS :>
View Replies !
Stored Procedure And Calling User Defined Function
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-) Short list of process flow: 1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER 2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient 3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient 4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page 5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny. <FLOW WITH CODE AND FUNCTIONS :> This is not the correct syntax but is showing what I understand sort of how this is to be done so far. This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process. This is code in Form.asp 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check Postback Type 'We need to load up vendors associated with the current client. '--------- CHECK ACCESS HERE via function ab_HasAccessToClient -------- 'If the call returns 1, then the employee has access. 'Otherwise, just write out "Access to this client is denied." 'CALL SP - Not sure what parameters need to go with it or its syntax Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1) 'When it returns can check it here........ if ab_HasAccessToClient result is a 1 then 'boolean would be 1 so show panel Else 'boolean would be 0 so show access denied 'allow them to go back to the original page. end if 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > ON SQL SERVER: Stored Procedure ---------------------------------------------------------- -------------------------------- rx_sp_HasAccessToClient CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] @EmployeeID INT, @ClientSSN varchar(50), @ReturnBitValue = OUTPUT /* ' Parameters here passed via call from Form.asp - not sure what is passed yet. */ AS set nocount on /* Written by Mike Belcher 9/27/2007 for Form.asp 'Calls ab_HasAccessToClient function - not sure of the syntax as of yet, just making flow. 'Gets return bit and passes that back to the call from Form.asp */ GO ---------------------------------------------------------- -------------------------------- ON SQL SERVER: User-Defined Function ---------------------------------------------------------- -------------------------------- ab_HasAccessToClient CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN VARCHAR(50)) @ClientSSN varchar(50), @EmployeeID, @ReturnBitValue = OUTPUT AS SELECT 1 FROM tblEmployeesClients ec INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName WHERE e.EmployeeID= @EmployeeID AND c.InActiveClient=0 AND c.ClientSSN = @ClientSSN 'Some Code here to save result bit .. RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient ---------------------------------------------------------- -------------------------------- </FLOW WITH CODE AND FUNCTIONS :>
View Replies !
Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table. Code Snippet [SqlFunction ( FillRowMethodName = "FillRow", TableDefinition = "CustomerID nvarchar(MAX)", SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read, IsDeterministic=false)] public static IEnumerable GetWishlist () { using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) { List<string> myList = new List<string> (); conn.Open (); SqlCommand command = conn.CreateCommand (); command.CommandText = "GetObject"; command.Parameters.AddWithValue ( "@map", "Item" ); command.CommandType = System.Data.CommandType.StoredProcedure; using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) { if (reader.Read ()) { myList.Add ( reader[0] as string ); } } return (IEnumerable)myList; } } When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine. Code SnippetEXEC GetObject 'Item' Is there some sorf of trick I am missing? Thank you!
View Replies !
Calling A Stored Procedure Or Function From Another Stored Procedure
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 Replies !
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
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 Replies !
Calling Managed CLR Procedure From Inside User Defined Function -- How To ?
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value. I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement. I did read that you could use an external stored procedure and/or managed CLR procedures inside a function. I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine. However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement. Any advice on how to do this? Thanks, Bruce
View Replies !
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
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 Replies !
Using EXECUTE Statements Calling An Extended Stored Procedures From Function..
Hi, all I'm using Sql server 2000 I want to make select statement dynamically and return table using function. in sp, I've done this but, in function I don't know how to do so. (I have to create as function since our existing API..) Following is my tials... 1. alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS RETURN Exec spTest @from, @to GO Yes, it give syntax error.. 2. So, I found the following From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following.. "The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: " ..... * EXECUTE statements calling an extended stored procedures. So, I tried. alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS RETURN Exec master..xp_msver GO It doesn't work... syntax err... Here I have quick question.. How to execute statements calling an extended stored procedures. any examples? Now, I'm stuck.. how can I create dynamic select statement using function? I want to know if it's possible or not..
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Calling Stored Procedure Fromanother Stored Procedure
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 Replies !
Out Vs. Ref When Calling Stored Procedure
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 Replies !
Calling A Stored Procedure
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 Replies !
Calling Stored Procedure
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 Replies !
Calling A DTS Stored Procedure From ASP.NET
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 Replies !
Calling DTS From Stored Procedure
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 Replies !
Calling A VB Exe From A Stored Procedure
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 Replies !
Calling A Stored Procedure From A Dll
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 Replies !
Calling A Job From A Stored Procedure
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 Replies !
Calling Stored Procedure
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 Replies !
Calling Stored Procedure
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 Replies !
Calling Stored Procedure
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 Replies !
Calling A Stored Procedure From Vb.net
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 Replies !
Calling Stored Procedure From Another Sp
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 Replies !
Calling BCP From A Stored Procedure
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 Replies !
Calling A Stored Procedure From Within Another
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 Replies !
Calling A Stored Procedure
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 Replies !
Calling A Stored Procedure From Within A Stored Proc
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 Replies !
Stored Procedure Calling Another Stored Procedure_
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 Replies !
Calling Stored Procedures From Another Stored Procedure
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 Replies !
Error In Calling Stored Procedure
I want to call a stored procedure in ASP.Net 2.0. I've already made sure that the SP contains no error by the Query Analyzer.However, when I try to run this in the ASP.Net application, error occured.This error message is {"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."} Calling the SP via a Class1 Public Function ExecuteStoredProcedure(ByVal Name As String, ByVal Para() As String, ByVal Value() As String) 2 If Para.Length <> Value.Length Then Exit Function 3 SqlCmd = New SqlCommand 4 SqlCmd.Connection = SqlConn 5 SqlCmd.CommandText = Name 6 SqlCmd.CommandType = CommandType.StoredProcedure 7 For i As Integer = 0 To Para.Length - 1 8 'SqlCmd.Parameters.AddWithValue(Para(i), Value(i)) 9 Dim p As New SqlParameter(Para(i), SqlDbType.NVarChar) 10 p.Direction = ParameterDirection.Input 11 p.Value = Value(i) 12 SqlCmd.Parameters.Add(p) 13 Next 14 SqlCmd.ExecuteNonQuery() 15 End Function The Code behind file 1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 2 If txtFromDate.Text = "" OrElse txtToDate.Text = "" Then 3 trMsg.Visible = True 4 Exit Sub 5 End If 6 7 Dim ts As New Thread(AddressOf GenerateReport) 8 ts.Start() 9 10 MultiView1.ActiveViewIndex = 1 11 lblConfirmationMessage.Text = "Report generating in progress." & vbNewLine & "You can continue using other functions." 12 End Sub 13 14 Protected Sub GenerateReport() 15 Dim userName As String = CType(Session("User"), clsUser).UserName 16 Dim from As String = txtFromDate.Text 17 18 Dim conn As New clsConnector 19 conn.OpenConnection() 20 'conn.Insert("EXEC gen_report '" + txtReportName.Text + "','" + txtDescription.Text + "','" + userName + "','" + txtFromDate.Text + "','" + txtToDate.Text + "';") 21 conn.ExecuteStoredProcedure("gen_report", _ 22 New String() {"@name", "@remark", "@by", "@fromdate", "@todate"}, _ 23 New String() {txtReportName.Text, txtDescription.Text, userName, txtFromDate.Text, txtToDate.Text}) 24 conn.CloseConnection() 25 End Sub 26 Thanks!
View Replies !
Time Out Calling A Stored Procedure
I am trying to call a stored procedure from an asp.net application. If I execute the stored procedure in Query Analyzer it takes 1 second to execute, but when I am trying to call it from c# code it times out. Ideas? Thanks in advance.
View Replies !
Calling Other Databases From Stored Procedure
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 Replies !
Error When Calling Stored Procedure
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 Replies !
How Do I See The Result Of Calling A Stored Procedure In VB6 ?
HelloI'm calling a stored procedure in VB6 using the "execute" command ofrdoquery.Does anyone know how to check whether or not the execute command wassuccesfull or not. Not on the level of whether any rows were updated orselected but whether any Sql errors occured. VB picks up on any errorsbut I want to be able to identify them on the application level and dealwith them myself.I already tried the "rowsaffected" property but that isn't what I need.Any ideas anyone ?David Greenberg
View Replies !
Calling A Stored Procedure From Access
Given this connect string for cnn:Provider=SQLOLEDB.1;Data Source=MYDATABASE;ExtendedProperties="DEFAULTDATABASE=TRACS_Dev;InitialCatalog =TRACS_Dev;UID=sa;PWD=";with cnn.CursorLocation = adUseClientthe command cnn.Execute "usp_IPT" fails with "cannot find storedprocedure usp_IPT"the command cnn.Execute "dbo.usp_IPT" fails with "cannot find storedprocedure dbo.usp_IPT"but the command cnn.Execute "TRACS_Dev.dbo.usp_IPT" worksDoes anyone know why I have to specify TRACS_Dev when it is already inthe connect string?Thanks,Norman B. ScheininF-22 Applications DevelopmentM/S 4E-09(206) 655-7236Join Bytes!
View Replies !
Calling Stored Procedure From Powerbuilder 9
I'm trying to execute simple SQL Server stored procedure from Powerbuilder 9 development environment. The output parameter returns 0 to Powerbuilder, it is not what I was expecting Stored procedure is: create procedure manta_sequence_proc @sequence_name varchar(30), @sequence_id numeric(19,9) OUTPUT as begin SET NOCOUNT ON Begin Tran seq update manta_squrence set sequence_id=sequence_id+1 where sequence_name = @sequence_name Commit Tran seq select @sequence_id = sequence_id from manta_sequence where sequence_name = @sequence_name -- print 'name' + @sequence_name -- print 'id' + str(@sequence_id) SET NOCOUNT OFF /* Enable all rows affected messages */ return @sequence_id END go The call from Powerbuilder is: long ll_sequence_id string ls_sequence_name ls_sequence_name = 'TEST' declare manta_sequence procedure for manta_sequence_proc @sequence_name = :ls_sequence_name, @sequence_id = :ll_sequence_id output; exec manta_sequence; MessageBox('test', string (ll_sequence_id)) // this returns 0 and it should some other numer Can anyone help, I'm new to Sql Server. Thanks in advance
View Replies !
Calling Stored Procedure In Trigger
Hi I have a problem calling stored procedure in trigger.. When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value.. I have handled exception by returning values in case if any.. Here is the stored procedure CREATE PROCEDURE BidAllDestinations ( @ITSPID int, @DestinationID int, @BidAmount decimal (18,4), @BidTime datetime, @intErrorCode int out ) AS DECLARE @GatewayID int DECLARE @GatewayExist int SET @GatewayID = 0 SET @GatewayExist = 0 SET @intErrorCode = 0 UPDATE BID FOR CORRESPONDING GATEWAY DECLARE GatewayList CURSOR FOR SELECT Gateways.GatewayID FROM Gateways INNER JOIN GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN ITSPs ON Gateways.ITSPID = ITSPs.ITSPID Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID OPEN GatewayList FETCH NEXT FROM GatewayList INTO @GatewayID IF (@GatewayID = 0) SET @intErrorCode = 1 ELSE BEGIN -- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH WHILE @@FETCH_STATUS = 0 BEGIN SELECT@GatewayExist = Gatewayid FROMTerminationBids WHEREGatewayid = @Gatewayid AND DestinationID = @DestinationID IF @GatewayExist > 0 UPDATE TerminationBids SET BidAmount = @BidAmount, BidTime = getdate() WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID ELSE INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount) VALUES (@GatewayID,@DestinationID,@BidAmount) IF @@ERROR <> 0 BEGIN GOTO PROBLEM CLOSE GatewayList DEALLOCATE GatewayList END FETCH NEXT FROM GatewayList INTO @GatewayID END CLOSE GatewayList DEALLOCATE GatewayList END PROBLEM: BEGIN SET @intErrorCode = 100 END RETURN @intErrorCode GO TRIGGER CODE::: CREATE TRIGGER TR_TerminationBid ON dbo.TerminatorBidHistory FOR INSERT AS DECLARE @ITSPID int DECLARE @DestinationID int DECLARE @BidAmount decimal (18,4) DECLARE @BidTime datetime DECLARE @intErrorCode INT DECLARE @DistinationList varchar (8000) DECLARE @DestinationLevel varchar (100) SET @intErrorCode = 0 SET @ITSPID = 0 SET @DistinationList = '' -- CHECK ITPSID' S VALIDITY SELECT@ITSPID = i.ITSPID, @DestinationID= i.DestinationID, @BidAmount = i.BidAmount, @BidTime = i.BidTime FROM Inserted i INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output SELECT @intErrorCode Following should return value for @intErrorCode if any exception occures Any one can help what is wrong with it? Thanks
View Replies !
Calling Stored Procedure From Client
I have discovered that if a user other than the adminstrator logs onto a client, using an Access Project (adp), that the user must call stored procedures by including the owner, ie. dbo.usp_GetClient instead of just usp_GetClient I am wondering if any one knows a way to get around the reference to the object owner. I have a ton of forms and reports that call stored procedures without the dbo reference. I am using SQL 7.0
View Replies !
Calling A DTS Package From A Stored Procedure
I have a DTS package written to import data from a txt file into a SQL Server 7.0 table but before importing the data i need to delete the existing data from the table... for this i need to use the stored procedure to delete data first... how do i run the dts package from the stored procedure..does anyone know the command for this... regards, reshma
View Replies !
Error (214) When Calling Stored Procedure From Job
I have some very simple SQL statements in stored procedures. How simple? Try this: CREATE PROCEDURE [Insert_Inv_LevelA] AS truncate table BI_Inv_LevelA insert into BI_Inv_LevelA select * from Input_Inv_LevelA I can run the script fine in Query analyser both remotely and from the server console. When I schedule the script to execute as a step in a job in SQL Server Agent, it falls over and returns the following error (in the step details of the job history): Cannot convert parameter '@handle' to int data type expected by procedure. [SQLSTATE 42000] (Error 214). The step failed. Every called SQL script returns an identical error, yet they all parse and run fine in the query analyser. The syntax of the step is: sp_execute spInsert_Inv_LevelA This is run as T-SQL on the target database as user dbo (or "(Self)" - the same error occurs either way). Has anyone come across this before or have any clues? Technet and the MS site say nothing about it that I can find. Cheers, Mark.
View Replies !
Stored Procedure Calling DTS Error
I am uploading fixed record length text files into a SQL Server 7.0 table. I created a DTS to import the data which works fine (nothing fancy - a straight copy column) when I run it from within the Enterprise Manager. The text file name will change, so I want to call the DTS from a stored procedure and change the text file connection each time. Executing the stored procedure through Query Analyzer, I can create the DTS, load it, change the connection file, but the data pump task fails every time. I wrote the stored procedure using the sp_OA... methods based on sample scripts, I've checked the permission to the directory the text files reside in, and I have a password on the DTS so that it is accessible from other work stations. This has been driving me crazy for about 3 days. I appreciate any suggestions. Thank you.
View Replies !
Calling A Stored Procedure Within A Select
Is it possible to call a stored procedure inside a select statement: I tried this , but is giving error "Incorrect syntax near the keyword 'execute'." " SELECT PC.ProductId FROM ProductCategories PC WHERE PC.CategoryID in ( execute get_category_hierarchies 1 ) " where get_category_hierarchies is the stored procedure which expects an integer parameter.
View Replies !
Best Practice - Calling C#.net App From SQL Stored Procedure
Not sure what the best forum on this site for this. Looked around but couldn't find. When I google this... I just get a million threads about how to call stored procedures in c#. But what I want to do in this case is call a C#.net app from a stored procedure. It will need to be backwards compatible for 2000 unfortunately. I could compile the C# app as a win-32 app and kick it off from the Stored Procedure with command line parameters. But is there a better way? What is best practice?
View Replies !
|
TRACKER
