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 Complete Forum Thread with Replies
Related Forum Messages:
How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Here is the scenario, I have 2 stored procedures, SP1 and SP2 SP1 has the following code: declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )' EXEC (@tmp) SP2 has the following code: SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this) Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown: FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable -- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1 RETURN END
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 !
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't... I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it. So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need. Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like: DECLARE @table table@table=EXEC sp_pkeys MyTable That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great. Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out... SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT) Syntax Error. Ugh. Eventually, I even try: SELECT *FROM Split(substring('abc,def',2,6),DEFAULT) Syntax Error. Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function? SELECT *FROM Split('bc,def',DEFAULT) works just fine. So my questions are: Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it? Is there any way to pass a scalar-valued function as a parameter into a table-valued function? Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround: DECLARE @tmp varchar(8000) SET @tmp=(SELECT dbo.fn_pkeys('MyTable')) SELECT * FROM Split(@tmp,DEFAULT)
View Replies !
Variable Type Errors When Calling Stored Procedure
I currently have a stored procedure that is defined as follows: CREATE PROCEDURE UpdateSyncLog @TableName char(100), @LastSyncDateTime datetime, @ErrorState int OUTPUT I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered: exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results). I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32 The parameter mappings are as follows: Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1 Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1 The error I am getting when running this execute sql task is as follows: Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well. What gives?
View Replies !
Grant All Function Giving Errors
I'm migrating from 2000 to 2005, what is the best way to handle the following error: The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity The code is below: DECLARE @sp_name AS sysname; DECLARE syscursor CURSOR FOR SELECT name FROM sysobjects WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0); OPEN syscursor; FETCH NEXT FROM syscursor INTO @sp_name; WHILE (@@FETCH_STATUS = 0) BEGIN EXECUTE ('GRANT all ON ' + @sp_name + ' TO Public'); FETCH NEXT FROM syscursor INTO @sp_name; END CLOSE syscursor; DEALLOCATE syscursor;
View Replies !
Calling Scalar Valued Function From SSIS OleDB Command Transformation
Hi There, I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say.. 'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column. I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this. Thanks a lot!!
View Replies !
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 Replies !
Problem With Stored Procedure And Table-valued Functions
Hello Gurus, I have a stored procedure that gathers data from three tables and joins them, two of the tables need to have different rowcounts set, ie. pull only a certain number of rows from one table and only a certain number of rows from another table... The number of rows it should pull are stored within a table for each. Let me explain.... these tables hold Exchange storage group and mailstore data for a number of servers. Each server has a table entry with the number of child storage groups and each storage group has a table entry with the number of child mailstores. The tables get updated every two minutes via a program. I need to be able to get the most Data with the correct child counts for each server and storage group. I believe that i've found a way to do this with a stored procedure that calls a table-valued function. The table-valued function simply filters down the storage group table to it's number of storage groups, ordered by timestamp. I may be way off here, but i can't tell because both the stored procedure and function check out fine but when i execute the stored procedure it gives me the following error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetExchSGInfo", or the name is ambiguous. My code is below: Stored Procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetExchangeData2] @top INT, @SID INT, @SGCount INT, @ServerName VARCHAR(50) AS Set @SID = (SELECT ServerID FROM dbo.Servers WHERE ServerName = @ServerName) Set @top = (SELECT sum(Children) FROM dbo.ExchangeSG WHERE ServerID = @SID) Set @SGCount = (SELECT SGCount FROM dbo.Servers WHERE ServerID = @SID) SET ROWCOUNT @top SELECT dbo.ExchangeMSData.*, dboExchangeMailStore.*, dbo.GetExchSGInfo(@SID,@SGCount) As ExchangeSG, dbo.Servers.* FROM dbo.Servers INNER JOIN ExchangeSG ON dbo.Servers.ServerID = ExchangeSG.ServerID INNER JOIN dbo.ExchangeMailStore ON ExchangeSG.StorageGroupID = dbo.ExchangeMailStore.StorageGroupID INNER JOIN dbo.ExchangeMSData ON dbo.ExchangeMailStore.MailstoreID = dbo.ExchangeMSData.MailstoreID WHERE (dbo.Servers.ServerName = @ServerName) ORDER BY dbo.ExchangeMSData.[TimeStamp] DESC, dbo.ExchangeSG.[TimeStamp] DESC SET ROWCOUNT 0 And the Function: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[GetExchSGInfo] ( @SID INT, @SGCount INT ) RETURNS TABLE AS RETURN ( SELECT TOP (@SGCount) * FROM dbo.ExchangeSG WHERE ServerID = @SID ORDER BY [TimeStamp] ) Can anyone help me? Thanks.
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"))="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 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 !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
Table-Valued Function
I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this? Thanks Mangala
View Replies !
Common Table Expression Calling Stored Procedure
Hi, I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table. So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this. This is what I want to change: Declare @Col Varchar(10) Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10)) Insert into @TempTable Exec Procedure @Paramter select @Col = col2 from @TempTable INTO With CTE(Col2, Col3, Col4) AS ( Exec Procedure @Paramter) select @Col = col2 from @TempTable Thanks Punu
View Replies !
Trigger On Table-valued Function?
Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.
View Replies !
Table Valued Function And Constraints
Is it possible to define a constraint for Primary Key on more than 1 column or an alternate index on a column in a return table from an inline table valed function? Example Header: alter FUNCTION [dbo].[fntMetaFrame] (@ii_CompanyID int) RETURNS @tbl_MetaFrame TABLE ( pk_Key int Identity(1,1) primary key, ObjectID int , Seq int null ) I want the primary key to be pk_Key, ObjectID OR I want to add another index on ObjectID.
View Replies !
How To Join Using A Table-valued Function?
Hi there. I've hit some gap in my SQL fundementals. I'm playing with table-valued functions but I can't figure out how to join those results with another table. I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this. What am I misunderstanding here? The Given Objects: function Split(stringToSplit, delimiter) returns table (column: token) table Words (column: Words.word) -- table of predefined words table Sentences (column: Sentences.sentence) -- table of sentences; tokens may not be in Words table, etc The Problems: 1) how do I query a set of Sentences and their Tokens? (using Split) 2) how do I join tables Sentences and Words using the Split function? The Attempts: A) select word, sentence, token from Words, Sentences, dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences? where word = token resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option." B) select word, sentence from Words, Sentences where word in (select token from dbo.Split(sentence, ' ')) -- correlated subquery? resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."
View Replies !
Indexing Table-valued Function?
I am using a multi-statement table-valued function to assemble data from several tables and views for a report. To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row. Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns. The problem I'm having is that the UPDATEs are taking forever to execute. I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns. In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function. Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions? I think that would improve the UPDATE performance dramatically. Thanks, Lee Silverman JackRabbit Sports
View Replies !
Join With Table Valued Function
Hi, I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method .. eg.. SELECT A.Col1,A.Col2,B.Col1,B.Col2 FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B ON A.Col1 = B.Col1 any body help me ... thanx in advance..
View Replies !
Table-valued Function Return Error
I have this tsql Declare @IDtable table(id uniqueidentifier, [Value] varchar(50)) Declare @iDoc Int exec sp_xml_preparedocument @iDoc Output, @Data insert into @IDtable(id,[Value]) Select * From OpenXML(@iDoc, '/listing/Item', 2) With (Id VarChar(40), Value varchar(20)) exec sp_xml_removedocument @iDoc but when I tried to right table value function of it so I can use it many place with just change paremeter value like this ALTER FUNCTION [dbo].[splitXMLvalue](@editors xml) RETURNS @etable table(id uniqueidentifier, [name] varchar(50)) AS BEGIN Declare @iDoc Int exec sp_xml_preparedocument @iDoc Output, @Data insert into @IDtable(id,[Value]) Select * From OpenXML(@iDoc, '/listing/Item', 2) With (Id VarChar(40), Value varchar(20)) exec sp_xml_removedocument @iDoc --select id as parentId, [Value] as parentValeu from @IDtable RETURN END it gave me this error Msg 557, Level 16, State 2, Line 1 Only functions and extended stored procedures can be executed from within a function. can any one help me what I'm doing wrongthanks
View Replies !
Multi-statement Table-Valued Function
I'm creating a Multi-statement Table-Valued Function... Is it possible to insert variables into the table? In other words, is it possible to have something like declare @value1 varchar(10) @value2 varchar(10) BEGIN <do some work on value1 and value2> INSERT @returningTable @value1, @value2 instead of BEGIN <do some work on value1 and value2> INSERT @returningTable SELECT col1, col2 from T_SOURCE Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key. I'll go with an example to explain what i have to do Source_table col1 col2 Mike 10 Mike 20 Ben 50 John 15 John 25 John 35 The table that my function needs to create should look like this col1 col2 col3 Mike 10 1 Mike 20 2 Ben 50 1 John 15 1 John 25 2 John 35 3 I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table. Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated. Thank you.
View Replies !
Indexes On Table Variable Of Table Valued Function
Hi there, Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work. ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence ( @param1 int, @param2 int ) RETURNS @Result TABLE (resultcol1 int, resultcol2 int) AS BEGIN CREATE INDEX resultcol2_ind ON @Result -- do some other stuff RETURN END
View Replies !
Table-Valued Function Result Vs. Calculation Table
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures. Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example: DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1... Or using a table-valued function to return a temp table as the result. I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
View Replies !
Problem Passing A Variable Into A Table-valued Function
Hi, i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID. The syntax is as follows: select car.id, car.licenceNumber, car.brand, car.model, (select driverName from getCurrentDriver(car.id)) as driverName from car When I try to compile I get following error on the line of the function: Incorrect syntax near '.' The database version is SQL Server 2000 SP3. What am I doing wrong? Is there a workaround for this error?
View Replies !
Performance Of Table-valued Function And Execution Plan
I am using SQL2005 EE with SP1. The server OS is windows 2K3 sp2 I have a table-valued function (E.g. findAllCustomer(Name varchar(100), gender varchar(1)) to join some tables and find out the result set base the the input parameters. I have created indexes for the related joinning tables. I would like to check the performance of a table-valued function and optimize the indexing columns by the execution plan. I found the graphic explanation only show 1 icon to represent the function performance. I cannot find any further detail of the function. (E.g. using which index in joinning) If I change the function to stored procedure, I can know whether the T-SQL is using index seek or table scan. I also found the stored procedure version subtree cost is much grether that the table-valued function I would like to know any configureation in management studio can give more inform for the function performance? Thanks
View Replies !
Inline Table-valued Function With Multi-value Parameter
Hello everybody, I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution? Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"? Thanks in advance for your answers.
View Replies !
Temporary Table Vs. Table Valued Function
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures. Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example: StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1... Or using a table-valued function to return a temp table as the result. I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
View Replies !
Table-valued Function Run Once For Each Row In A Table Variable.
I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function. Code Snippet declare @IdTable table ( EmployeeId nvarchar( 16 ) not null ) insert into @IdTable select EmployeeNumber from Employees /* I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set. */ select * from fn_GetEmployeeById( EmployeeId ) Any help is very much appreciated. Andrew
View Replies !
Table-valued Function Into A @table Variable
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this? ALTER PROCEDURE [dbo].[rpt] ( @CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421' ) AS --split CourtIDs into a table DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY) INSERT INTO @tbCourtIDs select * from dbo.Split(@CourtIDs, ',')
View Replies !
Table-valued Function Does Not Accept Chinese Characters As Parameter
I have a table-valued function in mssql 2005 as below: ALTER FUNCTION fn_test{ @test nvarchar(1000)}RETURNS@TEMP TABLE{ test nvarchar(1000)}ASBEGIN INSERT INTO @TEMP SELECT @test RETURNEND Everytime, I passed in chinese character (@test), such as 測驗, the function will return ????. What should I do for the table-valued function, so that the chinese character can be passed in? Please help. Note: I can search and get the chinese characters if I use stored procedures; and the columns in the tables can store chinese chararcters as well. Only table-valued function is not working with the chinese characters. Is it a bug from MSSQL 2005?
View Replies !
System.DirectoryServices Performance Issue In Table-valued Function
Hi, I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET). Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server? Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class
View Replies !
Handling Large Queries In A Table Valued User Defined Function
Hello, We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions . What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out. Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran: Sec / Actual Rows Returned 0 0 10 237 20 447 30 481 40 585 50 655 60 725 70 793 80 860 90 940 100 1013 110 1081 120 1115 130 1151 140 1217 150 1250 160 1325 170 1325 180 1430 190 1467 200 1502 210 1539 220 1574 230 1610 240 1645 250 1679 260 1715 270 1750 280 1787 290 1822 300 1857 310 1892 320 1923 330 1956 340 1988 350 1988 360 2022 370 2060 380 2094 390 2094 400 2130 410 2160 420 2209 430 2237 440 2237 450 2274 460 2274 470 2308 480 2342 490 2380 500 2380 510 2418 520 2418 530 2451 540 2480 550 2493 560 2531 570 2566 It took 570 seconds (just over 9 1/2 minutes to return 2566 rows). The minute breakdown during my trial is as follows: 1 = 655 (+ 655) 2 = 1081 (+ 426) 3 = 1325 (+244) 4 = 1610 (+285) 5 = 1822 (+212) 6 = 1988 (+166) 7 = 2160 (+172) 8 = 2308 (+148) 9 = 2451 (+143) As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution. Allow me to explain the code in further detail: [SqlFunction(FillRowMethodName = "FillListItem")] public static IEnumerable DiscoverListItems(...) { ArrayList listItems = new ArrayList(); SPToSQLService service = new SPToSQLService(); [...] DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service //Load the DS to the ArrayList return listItems; } public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...) { ArrayList item = (ArrayList) obj; col1 = item.Count > 0 ? (string) item[0] : ""; col2 = item.Count > 0 ? (string) item[1] : ""; col3 = item.Count > 0 ? (string) item[2] : ""; [...] } As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once. Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set. Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions. Thanks, Dragan Radovic
View Replies !
Error With Multi-Valued Report Parameter Using Stored Procedure
Hi All, I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset: CREATE PROCEDURE spprodsales @productid int AS select SalesOrderID,OrderQty,UnitPrice,ProductID FROM Sales.SalesOrderDetail Where ProductID IN (@productid) RETURN And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter : select SalesOrderID,OrderQty,UnitPrice,ProductID FROM Sales.SalesOrderDetail Where ProductID IN (@productid) So, can anyone please help me out possibly using the same stored procedure as above. Thanks, Kripa
View Replies !
Stored Procedure Giving Error When Searching On Date Range
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1] @ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
View Replies !
Differences Between SQL Stored Procedures And Table-valued Functions
I am a bit confused by the difference between a stored procedure and a table-valued function. Can somebody please either give me a simple explanation, or point me at something I can read. I thought I had it worked out, and had coded some action queries as stored procedures, and I wrote a table-valued function that was effectively an encapsulated SELECT so that SELECT * FROM Spouse(@ID) worked fine. Then I wanted to use a function SpousePair, that was similar to Spouse, to power a Gridview. I discovered that I couldn't. It seems that a SQLDataSource requires either a SELECT statement or a stored procedure. So I wrote a stored procedure SpousePair(@ID1, @ID2). I find that whereas I tested Spouse with SELECT * FROM SPOUSE(@ID) I tested SpousePair with EXEC SpousePair @ID1 @id2 Now I want to combine these: if I could I would write SELECT * FROM SPOUSE(@ID) WHERE SPOUSEID NOT IN (SELECT SPOUSEID FROM SpousePair(@ID1, @ID2)) However this is invalid because you can't put a stored procedure in a Select statement, and SELECT .... NOT IN (EXEC SpousePair @ID1 @ID2) is also invalid. Is there any alternative to creating a table-valued function, SpousePairA, that is identical to SpousePair but coded as a function. I'm reluctant to do this because then I'll have two bits of quite complicated SQL logic to maintain.
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 !
&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function: ALTER FUNCTION MakeArDetail ( -- Add the parameters for the function here @dateStart DATETIME, @dateEnd DATETIME ) RETURNS @arDetail TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) AS BEGIN DECLARE @arTemp TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) INSERT INTO @arTemp SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims; UPDATE @arTemp SET Claim = (SELECT SUM(Pims.AmtReq) FROM Pims WHERE Pims.Insurer = @arTemp.Insurer AND Pims.NABP = @arTemp.NABP AND Pims.NumRx = @arTemp.NumRx ); INSERT INTO @arDetail SELECT * FROM @arTemp RETURN END GO I get Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43 Must declare the scalar variable "@arTemp". I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared. If I don't include the UPDATE command the thing works.
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 !
Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all, I copied the following code from Microsoft SQL Server 2005 Online (September 2007): UDF_table.sql: USE AdventureWorks; GO IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL DROP FUNCTION dbo.ufnGetContactInformation; GO CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL ) AS -- Returns the first name, last name, job title, and contact type for the specified contact. BEGIN DECLARE @FirstName nvarchar(50), @LastName nvarchar(50), @JobTitle nvarchar(50), @ContactType nvarchar(50); -- Get common contact information SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM Person.Contact WHERE ContactID = @ContactID; SELECT @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = @ContactID) THEN (SELECT Title FROM HumanResources.Employee WHERE ContactID = @ContactID) -- Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) THEN (SELECT ct.Name FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) -- Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = @ContactID) THEN (SELECT ct.Name FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE ContactID = @ContactID) ELSE NULL END; SET @ContactType = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = @ContactID) THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) THEN 'Vendor Contact' -- Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = @ContactID) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM Sales.Individual i WHERE i.ContactID = @ContactID) THEN 'Consumer' END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END; GO ---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise. Thanks in advance, Scott Chang
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 !
SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With &&"sys&&"?
Hi all, I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got: Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9 System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled. I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K? Thanks, Jos Here's a test script: /* ---------------------------------------------------------------------------------------------------- T-SQL code to test creation of three types of function where the function name begins with "sys_". Jos Potts, 02-Nov-2006 ---------------------------------------------------------------------------------------------------- */ PRINT @@VERSION go PRINT 'Scalar function with name "sys_" creates ok...' go CREATE FUNCTION sys_test () RETURNS INT AS BEGIN RETURN 1 END go DROP FUNCTION sys_test go PRINT '' go PRINT 'In-line table-valued function with name "sys_" creates ok...' go CREATE FUNCTION sys_test () RETURNS TABLE AS RETURN SELECT 1 c go DROP FUNCTION sys_test go PRINT '' go PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...' go CREATE FUNCTION sys_tmp () RETURNS @t TABLE (c INT) AS BEGIN INSERT INTO @t VALUES (1) RETURN END go DROP FUNCTION sys_test go PRINT '' go /* ---------------------------------------------------------------------------------------------------- */ And here€™s the output from running the test script in Query Analyser on our server: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Scalar function with name "sys_" creates ok... In-line table-valued function with name "sys_" creates ok... Multi-statement table-valued function with name "sys_" generates error 1706... Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11 System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled. Server: Msg 3701, Level 11, State 5, Line 2 Cannot drop the function 'sys_test', because it does not exist in the system catalog.
View Replies !
Calling A .Net Assembly From Script Component Giving Error
Hi, I am trying to access a .Net assembly in script component, which internally uses Microsoft Enterpise library dll's. The problem I am facing is when I copy the config sections needed for the Enterprise library from web.config to dtsdebughost.exe.config file and run the package, It ends in failure with below message "Error: The script files failed to load." My dtsdebughost.exe.config looks like below: Code Snippet <configuration> <startup> <requiredRuntime version="v2.0.50727"/> </startup> <configSections> <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> <section name="exceptionHandling" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Configuration.ExceptionHandlingSettings, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> </configSections> <loggingConfiguration name="Logging Application Block" tracingEnabled="true" defaultCategory="" logWarningsWhenNoCategoriesMatch="true"> <listeners> <add fileName="LogMedtrack-Error.log" rollSizeKB="5000" timeStampPattern="dd-MMM-yyyy" rollFileExistsBehavior="Overwrite" rollInterval="Day" formatter="Default Formatter" header="----------------------------------------" footer="----------------------------------------" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.RollingFlatFileTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.RollingFlatFileTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" name="Rolling Flat File Trace Listener" /> </listeners> <formatters> <add template="Timestamp: {timestamp}
Message: {message}
Category: {category}
Priority: {priority}
EventId: {eventid}
Severity: {severity}
Title:{title}
Machine: {machine}
Application Domain: {appDomain}
Process Id: {processId}
Process Name: {processName}
Win32 Thread Id: {win32ThreadId}
Thread Name: {threadName}
Extended Properties: {dictionary({key} - {value}
)}" type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" name="Default Formatter" /> </formatters> <logFilters> <add categoryFilterMode="AllowAllExceptDenied" type="Microsoft.Practices.EnterpriseLibrary.Logging.Filters.CategoryFilter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" name="Category Filter" /> <add minimumPriority="0" maximumPriority="2147483647" type="Microsoft.Practices.EnterpriseLibrary.Logging.Filters.PriorityFilter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" name="Priority Filter" /> </logFilters> <categorySources> <add switchValue="All" name="Tracing"> <listeners> <add name="Rolling Flat File Trace Listener" /> </listeners> </add> </categorySources> <specialSources> <allEvents switchValue="All" name="All Events"> <listeners> <add name="Rolling Flat File Trace Listener" /> </listeners> </allEvents> <notProcessed switchValue="All" name="Unprocessed Category" /> <errors switchValue="All" name="Logging Errors & Warnings" /> </specialSources> </loggingConfiguration> <exceptionHandling> <exceptionPolicies> <add name="Business Policy"> <exceptionTypes> <add type="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" postHandlingAction="NotifyRethrow" name="Exception"> <exceptionHandlers> <add logCategory="Tracing" eventId="100" severity="Error" title="Agility Application Log." formatterType="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.TextExceptionFormatter, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" priority="0" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.LoggingExceptionHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" name="Logging Handler" /> </exceptionHandlers> </add> </exceptionTypes> </add> </exceptionPolicies> </exceptionHandling> </configuration> Please let me konw, If there is anything wrong I am doing or is there any other way to handle the situation Regards, Kalyan
View Replies !
Trouble Calling Function In View && Multi-Table Select...
How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID? Following Function populates a field (with concat list of related titles) with other required fields: Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000)) Returns Varchar(8000) as Begin Declare @Lists as varchar(8000); Select @Lists = ''; Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier Where @qcParent_ID = qcParent_ID; Return Substring(@Lists,1,len(@Lists)-len(@Delimeter)); End works perfect against single table select (returning 54 distinct rows by qcParent_ID): Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix From dbo.a2_qcEntryForm a JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i on a.qcParent_ID = i.qcParent_ID But data is needed from 3 tables... - Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID) - Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins) This VIEW returns ALL (82) rows in table: CREATE VIEW z_QClocate_openAll AS SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID, dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality FROM dbo.a1_qcParent INNER JOIN dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR (dbo.a1_qcParent.qcStatus = 'In Process') OR (dbo.a1_qcParent.qcStatus = 'Re-Opened') Calling like this returns ALL 82 rows (negating the functions distinct): Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix From z_QClocate_openAll a JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i on a.qcParent_ID = i.qcParent_ID AND THEN THERES... Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES: How to integrate the Function call: JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i on a.qcParent_ID = i.qcParent_ID into the multi-table Select relationships (while maintaining Where & Order By): SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID, dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality FROM dbo.a1_qcParent INNER JOIN dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR (dbo.a1_qcParent.qcStatus = 'In Process') OR (dbo.a1_qcParent.qcStatus = 'Re-Opened')
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 !
Select Statement In Asp.net Giving Errors
hi all i built and sql statemnet up in enterprise manager but when i paste it into my asp.net code it gives error, this is what i have objDA1 = new SqlDataAdapter("select DISTINCT categories.categorydescription, vehicles.vehicleID from vehicles "_&"INNER JOIN ON Vehicles.VehicleID=Parts.VehicleID INNER JOIN Categories ON Parts.CategoryID = Categories.CategoryID "_& "where CategoryID = " & LoadDataByCategory & ";", objConn) objDA1.fill(objDS1, "Categories") whats wrong with it ? is it concatinated wrong thanks ?
View Replies !
|
TRACKER
