&&"RETURN Statements In Scalar Valued Functions Must Include An Argument&&" ERROR
Hi,
I am trying to write a function which takes a string as input and returns the computed value.
I need to use the output of this function as a coulmn in another select query.
Here is the code (Example: @Equation = '(100*4)+12/272')
create function dbo.calc(@Equation nvarchar(100))
returns float
as
begin
return exec('SELECT CAST('+@Equation+' AS float)')
end
I am getting this error when i compile it
"RETURN statements in scalar valued functions must include an argument"
Any suggestions would be appreciated.
Please respond
Thanks
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Return Statements In Scalar Valued Functions Must Include An Argument
I'm trying to create a SQL server 2000 function that returns a scalar value, but I keep getting the error "Return statements in scalar valued functions must include an argument". Online clarification of this error message is no help at all.I've tried all sorts of combinations of the following, without much luck. Can someone point out my dim-witted mistake, please?ALTER FUNCTION dbo.intCoursesPublic (@intCatID as int) RETURNS intASBEGIN RETURN SELECT COUNT(intCourseID) AS Expr1 FROM dbo.tbl_guru_course_list WHERE (intCatID = @intCatID)END
View Replies !
View Related
Table Valued Functions - Yield Return Error
So I was creating a new table-valued function today which queries some data from a preexisting table. Since this is my first table-valued function, I decided to check out some of the examples and see what I can figure out. One particular example helped me out a bit until I ran into some data access issues... http://msdn2.microsoft.com/en-us/library/ms165054.aspx So I create my function: [SqlFunction(DataAccess = DataAccessKind.Read,SystemDataAccess=SystemDataAccessKind.Read,FillRowMethodName = "FillMyRow",TableDefinition ="p1 int, p2 int"] public static IEnumerable getMyTable() { using (SqlConnection conn = ....) { using (SqlCommand command = conn.CreateCommand()) { ///.... populate command text, open connection using (SqlDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { customObject1 o = new customObject1(); ///... populate o's parameters from reader ... yield return o; } } } } public static void FillMyRow( object source, out int p1, out int p2) { customObject1 f = (customObject1)source; p1 = f.p1; p2 = f.p2; } Notice, this example yield returns the value o upon each iteration of the reader. Despite the fact that the DataAccess is set to Read I still get the error... An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method. I did however get past this error, by creating a collection of customObject1, populated it within the while(rdr.Read()) loop, then return the collection after closing the connection, command and reader. I assume this error has something to do with the fact that you can't yield return results from within an open reader. Is this error right though in this case? Whats causing it to throw a InvOp Exception? Or is this a bug? Thanks for the attention.
View Replies !
View Related
Can't Use The NTEXT Datatype In SQLCLR Scalar-valued Functions
From the SQL Server documentation : "The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, except rowversion, text, ntext, image, timestamp, table, or cursor"This is a problem for me. Here's what I'm trying to do :I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.
View Replies !
View Related
Return NULL From A CLR Scalar-Valued Function
Hi, I have an assembly that contains the following function: Public Class Lookup <SqlFunction()> _ Public Shared Function MyTest() As Integer Return System.Data.SqlTypes.SqlInt64.Null End Function End Class Then in SSMS: CREATE ASSEMBLY IRS_MyTest FROM '\machine empmyAssembly.dll' GO CREATE FUNCTION dbo.MyTest() RETURNS INT AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest GO when I run: SELECT dbo.MyTest() the following is returned: Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest': System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. System.Data.SqlTypes.SqlNullValueException: at System.Data.SqlTypes.SqlInt64.get_Value() at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x) at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest() Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal. Thanks, Jan.
View Replies !
View Related
CLR Table Valued Functions Error
Hi, I'm trying to create a CLR functions this is the Sql Function attribute and the FillRowMethod signature [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName = "FillRows",IsPrecise=true, TableDefinition = "SOCIETA nvarchar(55),CLIENTE nvarchar(150),NUMEROCONTRATTO nvarchar(255),FIRMA datetime,CHIUSURA datetime,AUTORIZZATO float" )] public static IEnumerable dbf_Create_RiepilogoAccordi(SqlInt32 commessa, SqlInt32 tipo_commessa, SqlInt32 progetto, SqlInt32 DAC, SqlInt32 figura, SqlDateTime dataFatturazioneDa, SqlDateTime dataFatturazioneA) public static void FillRows(Object obj, out SqlString SOCIETA, out SqlString CLIENTE, out SqlString NUMEROCONTRATTO, out SqlDateTime FIRMA, out SqlDateTime CHIUSURA, SqlDouble AUTORIZZATO) Whe I try to deploy my function, I get the following error: Error 1 Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function 'dbf_Create_RiepilogoAccordi' due to column 6. CM.Reports.SIA.RiepilogoAccordi I get this error whichever combination of name/value I use for column 6 Can someone help me? Thanks Marco
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Using Scalar Functions In Views
I have a view that contains a complex query. A few of the columnscall a function that returns a specific output. I also use a functionto do a join as well.For example:SELECT l.ID, dbo.getStatus(l.ID) AS statusIDFROM tableName ALEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =s.statusIDFor 800 records, this query takes about 9 seconds. I realize that foreach record, the function is executed on a per row basis, so I amlooking for alternatives.Does anyone know of other ways to accomplish something of the same?Basically I would like to include UDFs in a query and use those UDFsin the where and join clauses.Thanks
View Replies !
View Related
Why Are Scalar Functions Soooo Sloooow?
Hi all,I've been using scalar functions as a way to perform some complex datatransformation operations, and I've noticed that scalar functionsreaaaaalllllyyyy sloooowwwwww thiiiiiings dooooooown. I expect slow-down, ofcourse, and would even not be surprised at slow-downs up to a factor of, say50:1, but I'm seeing slow-downs more like 1000:1 or 100000:1. I'm sure itwould actually be faster to actually export a table, use VB to process it,then import it back in.
View Replies !
View Related
Views Or Table Valued Functions?
Hi I am writting Stored Procedures that have to be built on the base of other tables specially created for this purpose, is it better to create these intermediate tables as views or as functions returning tables? I guess views would be lighter on performance as they would not be created on the fly?
View Replies !
View Related
Help!!! Troubles With Table-valued Functions
Hi! Here's my function. The trouble - I can not make ORDER BY the "visits_count", "properties_count", "enquiries_count" fields. May be some one could help me with this? CREATE FUNCTION [dbo].[GetPagedStatistics] ( @start_index int, @count int, @condition nvarchar(255), @order_field nvarchar(255), @date_from datetime, @date_to datetime ) RETURNS @total_stat TABLE ( username nvarchar(255), first_name nvarchar(255), last_name nvarchar(255), properties_count int, enquiries_count int, visits_count int, id_user int) BEGIN INSERT @total_stat SELECT top (@count) dbo.users.username, dbo.users.first_name, dbo.users.last_name, ISNULL(COUNT(DISTINCT dbo.advertisement.id_advertisement), 0) AS properties_count, ISNULL(COUNT(DISTINCT dbo.enquiry_emails.id_enquiry_email), 0) AS enquiries_count, ISNULL(COUNT(DISTINCT dbo.property_statistics.id_statistics), 0) AS visits_count, dbo.users.id_user FROM dbo.property_statistics RIGHT OUTER JOIN dbo.advertisement RIGHT OUTER JOIN dbo.users ON dbo.advertisement.id_user = dbo.users.id_user LEFT JOIN dbo.enquiry_emails ON dbo.enquiry_emails.id_advertisement = dbo.advertisement.id_advertisement ON dbo.property_statistics.id_advertisement = dbo.advertisement.id_advertisement WHERE 1=@condition and (dbo.advertisement.creation_date <= @date_to and dbo.advertisement.creation_date >= @date_from ) and ( (dbo.enquiry_emails.creation_date <= @date_to and dbo.enquiry_emails.creation_date >= @date_from and dbo.property_statistics.view_date <= @date_to and dbo.property_statistics.view_date >= @date_from ) or (dbo.property_statistics.view_date is null) or (dbo.enquiry_emails.creation_date is null) ) and (ISNULL(dbo.advertisement.id_parent, 0) = 0) GROUP BY dbo.users.username, dbo.users.first_name, dbo.users.last_name, dbo.users.id_user order by case when @order_field='username' then dbo.users.username end, case when @order_field='first_name' then dbo.users.first_name end, case when @order_field='last_name' then dbo.users.last_name end, case when @order_field='properties_count' then 1 end, case when @order_field='enquiries_count' then 1 end, case when @order_field='visits_count' then 1 end RETURN END
View Replies !
View Related
Inline-table-valued Functions
Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.
View Replies !
View Related
Help!!! Troubles With Table-valued Functions
Hi! Here's my function. The trouble - I can not make ORDER BY the "visits_count", "properties_count", "enquiries_count" fields. May be some one could help me with this? CREATE FUNCTION [dbo].[GetPagedStatistics] ( @start_index int, @count int, @condition nvarchar(255), @order_field nvarchar(255), @date_from datetime, @date_to datetime ) RETURNS @total_stat TABLE ( username nvarchar(255), first_name nvarchar(255), last_name nvarchar(255), properties_count int, enquiries_count int, visits_count int, id_user int) BEGIN INSERT @total_stat SELECT top (@count) dbo.users.username, dbo.users.first_name, dbo.users.last_name, ISNULL(COUNT(DISTINCT dbo.advertisement.id_advertisement), 0) AS properties_count, ISNULL(COUNT(DISTINCT dbo.enquiry_emails.id_enquiry_email), 0) AS enquiries_count, ISNULL(COUNT(DISTINCT dbo.property_statistics.id_statistics), 0) AS visits_count, dbo.users.id_user FROM dbo.property_statistics RIGHT OUTER JOIN dbo.advertisement RIGHT OUTER JOIN dbo.users ON dbo.advertisement.id_user = dbo.users.id_user LEFT JOIN dbo.enquiry_emails ON dbo.enquiry_emails.id_advertisement = dbo.advertisement.id_advertisement ON dbo.property_statistics.id_advertisement = dbo.advertisement.id_advertisement WHERE 1=@condition and (dbo.advertisement.creation_date <= @date_to and dbo.advertisement.creation_date >= @date_from ) and ( (dbo.enquiry_emails.creation_date <= @date_to and dbo.enquiry_emails.creation_date >= @date_from and dbo.property_statistics.view_date <= @date_to and dbo.property_statistics.view_date >= @date_from ) or (dbo.property_statistics.view_date is null) or (dbo.enquiry_emails.creation_date is null) ) and (ISNULL(dbo.advertisement.id_parent, 0) = 0) GROUP BY dbo.users.username, dbo.users.first_name, dbo.users.last_name, dbo.users.id_user order by case when @order_field='username' then dbo.users.username end, case when @order_field='first_name' then dbo.users.first_name end, case when @order_field='last_name' then dbo.users.last_name end, case when @order_field='properties_count' then 1 end, case when @order_field='enquiries_count' then 1 end, case when @order_field='visits_count' then 1 end RETURN END
View Replies !
View Related
Multi-statement Table-valued Functions
Hello I am trying to do the following: 1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter. 2. Then, use it like this: "Select * from table T, mstvF1( T.id )" It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id. If I do Select * from table T, mstvF1( 5 ), then it works. Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T? Thanks for any help.
View Replies !
View Related
Table-valued User-defined Functions
Is there a way to execute a table-valued user-defined function within SSIS - one that no only returns a "table" of values but also passes in variables to the udf? Example of it in query form: DECLARE @Var1 INT SET @Var1=ID FROM dbo.dbtable WHERE Status = 1 SELECT * FROM udf_Foo(@Var1) Thanks.
View Replies !
View Related
&&"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 !
View Related
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 !
View Related
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 !
View Related
Streaming Table-Valued Functions (STVF) And Permission_set Safe
Is there a fundamental disconnect between the stvf architecture and permission_set safe which prohibits using threads not managed by sql server including ThreadPool threads?. Documentation (performance of clr integration) states that the rationale is to allow consumption of data when the first row is produced rather than waiting for the entire result set. But in the single-threaded scenario the enumerable object can't be returned until it's fully populated. Wouldn't the following give better results? [SqlFunction] MyFunction(...) { // get raw data from context connection // stuff it into data tables or whatever // create an empty enumerable object // queue a work item to the thread pool, passing it the enum object and the data tables // return the enumerable object } At this point the database engine will enumerate the object while simultaneously the background thread is populating it. Of course this also requires synchronization between the threads as well as signalling via events from the background thread that there's a new item to be enumerated or that there's no more data. But all this requires permission set unsafe where there's no specific guidelines other than 'try at your own risk'. Does this use of the thread pool (which presumably is created when sql server loads the CLR) plus a few synchronization objects for a brief period of time constitute 'unsafe' usage that is liable to make sql server unstable or crash? Any insights would be appreciated.
View Replies !
View Related
Table-valued UDF With Conditional RETURN
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF. I've got: CREATE FUNCTION myFunction(@param1,...,@param4) RETURNS TABLE AS BEGIN IF @param1='x' BEGIN RETURN(SELECT columns FROM TableX) END ELSE BEGIN RETURN(SELECT columns FROM TableY) END END I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help! Thanks, -Ed H.
View Replies !
View Related
Argument Data Type Text Is Invalid For Argument 1 Of Replace Function.
Hi There, Could someone please tell me why I am getting the above error on this code: select (replace (replace (replace (replace (serviceType, 'null', ' ') , '<values><value>', ' ') , '</value><value>', ',') , '</value></values>', ' ')) from credit serviceType (text,null) Thanks, Rhonda
View Replies !
View Related
Conditional Statements Within Aggregate Functions
Hi there, I'm probably just being thick but is there a way to use conditional statements within aggregate functions? I'm trying to do something along the lines of the following - Code: SELECT SUM( CASE WHEN Currency='GBP' THEN TotalAmountCharged ELSE TotalAmountCharged/1.45 ) as total from bookshop_orders where year(OrderDate) = 2004 and month(OrderDate) = 9 TIA Nick
View Replies !
View Related
Passing Table Names To Table Valued Functions
Hello, It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions? For example, a simple stored procedure is this: CREATE PROCEDURE SelectTop(@tableName sysname) AS BEGIN Execute('Select top 10 * from ' + @tableName + ';') END I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
View Replies !
View Related
Return And Assigning Values In Functions
Hi , I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ? For eg I have written the following function which I called from a stored procedure. BEGIN --Declare the return variable here DECLARE @Rows NUMERIC(10) DECLARE @RETURN_ENABLED VARCHAR(1) -- Add the T-SQL statements to compute the return value here SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name Group By Profile_INdex IF @Rows > 0 SELECT @RETURN_ENABLED = 'N' ELSE SELECT @RETURN_ENABLED = 'Y'; -- Return the result of the function RETURN @RETURN_ENABLED; END Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ? From the stored procedure I'm calling the function inside a CTE. ;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled) AS (--Anchor. select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled From sys_menu_item as tb1 where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help') And tb1.item_parent= @menu_name --Members UNION ALL select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled from sys_menu_item as tb2 , GetHierarchy where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help') And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name ) select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled From GetHierarchy Am I doing it correctly ? I would appreciated any help you could give me. Thank you
View Replies !
View Related
Two Select Statements In SP Return No Value In Web Form (only)
Hello. I a SP with 2 select statements. If the first returns no results it does the second select. This works fine if I test it on the sql server. But when I try it in a webform using a sqldatasource it only returns a result if the first Select returns rows. If not It is an empty set (where I know it should not be. It returned resultrs on the SQL server). This is the code:1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 SELECT p.UserName, 8 9 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 10 11 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount 12 13 FROM Profiles p WHERE UserName = @SearchFor 14 15 IF (@@ROWCOUNT = 0) 16 17 Begin 18 19 SELECT p.UserName, 20 21 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 22 23 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 24 25 DIFFERENCE(p.UserName, @SearchFor) as Similarity 26 27 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 28 29 ORDER BY Similarity 30 31 END 32 33 RETURN Now If i change the SP to test it to the code below, it does work in both the webform and on the sql server: 1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 DECLARE @RowCount int 8 SET @RowCount = 0 9 10 IF (@ROWCOUNT = 0) 11 12 Begin 13 14 SELECT p.UserName, 15 16 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 17 18 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 19 20 DIFFERENCE(p.UserName, @SearchFor) as Similarity 21 22 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 23 24 ORDER BY Similarity 25 26 END 27 28 RETURNOnce again, Everything works as it should (for both versions), if I am testing it directly on the SQL server. Can anyone Help me on this? I am stumped and cannot find any answers about this.
View Replies !
View Related
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 !
View Related
Argument Not Specified For Parameter Error
I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong. What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select....Where function? After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind(). here's some of the code: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) if Not Page.IsPostBack then dim Code as String Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code")) label1.Text = Code GetPropertyCodes(Code) ddlPropertyCode.DataBind() end if End Sub Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @Code)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_code.ParameterName = "@Code" dbParam_code.Value = Code dbParam_code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_code) Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function Thanks for your help. Chris
View Replies !
View Related
Argument Not Specified For Parameters Error
hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc... Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute. the code is this: Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click Dim report As Byte() = Nothing 'Create an instance of the Reporting Services 'Web Reference Dim rs As localhost.ReportingService = New localhost.ReportingService 'Create the credentials that will be used when accessing 'Reporting Services. This must be a logon that has rights 'to the Axelburg Invoice-Batch Number Report. '***Replace "LoginName", "Password", and "Domain" with 'the appropriate values. *** rs.Credentials = New _ System.Net.NetworkCredential("Administrator", _ "password", "localhost") rs.PreAuthenticate = True 'The Reporting Services virtual path to the report. Dim reportPath As String = _ "/Galactic Delivery Services/Axelburg/Invoice-Batch Number" ' The rendering format for the report. Dim format As String = "html4.0" 'The devInfo string tells the report viewer 'how to display with the report. Dim devInfo As String = _ "<DeviceInfo>" + _ "<Toolbar>False</Toolbar>" + _ "<Parameters>False</Parameters>" + _ "<DocMap>True</DocMap>" + _ "<Zoom>100</Zoom>" + _ "</DeviceInfo>" 'Create an array of the values for the report parameters Dim parameters(1) As localhost.ParameterValue Dim paramValue As localhost.ParameterValue _ = New localhost.ParameterValue paramValue.Name = "StartDate" paramValue.Value = calStartDate.SelectedDate parameters(0) = paramValue paramValue = New localhost.ParameterValue paramValue.Name = "EndDate" paramValue.Value = calEndDate.SelectedDate parameters(1) = paramValue 'Create variables for the remainder of the parameters Dim historyID As String = Nothing Dim Credentials() As localhost.DataSourceCredentials = Nothing Dim showHideToggle As String = Nothing Dim encoding As String Dim mimeType As String Dim warnings() As localhost.Warning = Nothing Dim reportHistoryParameters() As _ localhost.ParameterValue = Nothing Dim StreamIDs() As String = Nothing Dim sh As localhost.SessionHeader = _ New localhost.SessionHeader rs.SessionHeaderValue = sh Try 'Execute the report. report = rs.Render(reportPath, format, historyID, _ showHideToggle, encoding, mimeType, _ reportHistoryParameters, warnings, _ StreamIDs) sh.SessionId = rs.SessionHeaderValue.SessionId 'Flush any pending responce. Response.Clear() 'Set the Http headers for a PDF responce. HttpContext.Current.Response.ClearHeaders() HttpContext.Current.Response().ClearContent() HttpContext.Current.Response.ContentType = "text/html" ' filename is the default filename displayed 'if the user does a save as. HttpContext.Current.Response.AppendHeader( _ "Content-Disposition", _ "filename=""Invoice-BatchNumber.HTM""") 'Send he byte array containing the report 'as a binary response. HttpContext.Current.Response.BinaryWrite(report) HttpContext.Current.Response.End() Catch ex As Exception If ex.Message <> "Thread was being aborted." Then HttpContext.Current.Response.ClearHeaders() HttpContext.Current.Response.ClearContent() HttpContext.Current.Response.ContentType = "text/html" HttpContext.Current.Response.Write( _ "<HTML><BODY><H1>Error</H1><br><br>" & _ ex.Message & "</BODY></HTML>") HttpContext.Current.Response.End() End If End Try End Sub End Class This is the area underlined as failing: report = rs.Render(reportPath, format, historyID, _ showHideToggle, encoding, mimeType, _ reportHistoryParameters, warnings, _ StreamIDs) The errors all pretty much go like this: c:inetpubwwwrootAxelburgFrontEndReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'. I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.
View Replies !
View Related
Error In Function Argument
I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm. I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the wkdy20070416-a.rm filename so that when it's all said and done that entry would read: WeekdayA20070416.rm WeekdayB20070416.rm Conversation20070416.rm Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement. Code Snippet UPDATE T_Programs_TestCopy (SET RealAudioLink = REPLACE(RealAudioLink, '-a', '') AND (SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA') WHERE (RealAudioLink LIKE 'wkdy%')) I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer. Thanks
View Replies !
View Related
[|-)] Error: Stored Procedure Has Too Many Argument....
Hello frend...i have a problem with my application when i'm trying to delete a certain data from gridview...i have a store procedure that already create in my mssql server...and in datasource i'm using command that i've already created in my mssql server...the problem is i dont know how i can send my value to the parameter in datasource.....and one more thing what is exaclty the error with 'Store procedure has <my function> too many argument" occur? Please help me....so i paste my code below to easier and detect my problem....(sorry my english are no good).... 1) This is my code in asp.net <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:SqlDataSource ID="SqlDSRole" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>" DeleteCommand="Roles_delete" DeleteCommandType="StoredProcedure" SelectCommand="Roles_view" SelectCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="roleName" Type="String" /> </DeleteParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="Role_application_id" DataSourceID="SqlDSRole" ForeColor="#333333" GridLines="None" PageSize="4"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:CommandField ShowDeleteButton="True" /> <asp:TemplateField HeaderText="Role Application Id" Visible="False"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:Label ID="roleApplicationIdLabel" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Role Name" > <ItemTemplate> <asp:Label ID="roleName" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo" ></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="roleNameTxt" runat="server" Text='<%# Bind("Role_name") %>' Width="200px"></asp:TextBox> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo"></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Role Description"> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="roleDescriptionTxt" runat="server" Height="30px" Text='<%# Bind("Role_description") %>' TextMode="MultiLine" Width="300px"></asp:TextBox> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#E0E0E0" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </ContentTemplate> </asp:UpdatePanel> 2) And this is my Store procedure that i created in mssql server Store Procedure: Roles_view set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Roles_view] AS BEGIN SET NOCOUNT ON; SELECT * FROM Role END Store Procedure: Roles_delete set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Roles_delete] @roleName varchar(50) AS BEGIN SET NOCOUNT ON; DELETE FROM Role WHERE Role_name LIKE @roleName END 3) The problem is when i'm trying to delete my certain data, the message box appear and say "Procedure or function Roles_delete has too many argument specified".what should i do????anabosy please help me..
View Replies !
View Related
Return Results Of Mulitple Select Statements In Another Stored Procedure
Hi,I would like to have 1 stored procedure call another stored procedure(which contains multiple select statements) and then be able to accessthe 3 result sets.ExampleCreate Procedure [dbo].[GetValues]exec Multiple--manipulate result set A--manipulate result set B--manipulate result set CCREATE PROCEDURE [dbo].[Multiple] ASSelect * from ASelect * from BSelect * from CI currently have a SP like Multiple, that I call from a VB.Netapplication, in the application i can scroll through the 3 resultingtables. I want to do the same thing from a SQL Server StoredProcedure.FYI, tables A,B and C have nothing in common, this is not a place for aunion. I am simply trying to use an SP that was written with ADO inmind.Any comments??Jamiemcc
View Replies !
View Related
Error With NULL Using EXECUTE SCALAR
I keep getting an error message when I try to place the result of an execute scalar command into a text box. The msg is:"conversion from 'DBNull' to type 'String' is not valid" The code I am using is: Dim con as OLEDBConnection con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:caps.mdb") Dim cmd As OLEDBCommand cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue texBox1.Text = cmd.ExecuteScalar The code works fine as long as there is a value for the Product. However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this? Chas28
View Replies !
View Related
Must Declare The Scalar Variable Error
Hi All, I'm totaly new to administrating databases. All I want to do is run the sql server script located at http://www.data-miners.com/sql_companion.htm#downloads. This creates some tables and uploads a series of text files into them. When I run the script through SQL Server Express 2005 I get the error Must declare the scalar variable "@DATADIR". I suspect it's something with me putting in the wrong path. The text files that the script needs to load into the table are located on the K drive, and I have changed the path in declare @DATADIR varchar(128) set @DATADIR='C:gordonookdatafinal extfiles' to declare @DATADIR varchar(128) set @DATADIR='k: extfiles' I suspect this is the wrong syntax that's why it's not working but I might be totally wrong. The text file and the server are both saved on the k drive. Regards, Seaweed
View Replies !
View Related
How To Handle Error In Scalar Function?
Hi, I have the follow function: CREATE FUNCTION [dbo].[ToTime] ( @intHora int, --A valid hour @intMin int -- A valid minute ) RETURNS smalldatetime AS BEGIN declare @strTime smalldatetime declare @errorvar int select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar) return @strTime; END the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23 and the parameter for the minute is an negative number (-1) or a number > 59, the function produce an error. I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example if @intHora < 0 or @intHora >23 begin set @intHora = 0 end if @intMin <0 or @intMin>59 begin set @intMin = 0 end please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.
View Replies !
View Related
Must Declare Scalar Variable Error... Need Help URGENT!
doing insert using this method Dim insertSQL As String insertSQL = "Insert into " & myDB & " (student_name, student_passport, student_rcnumber, " & _ "test_level, test_venue1, test_venue2, test_row, test_column, " & _ "student_sex, student_age, student_dob,student_country, student_state, " & _ "guardian_name, guardian_passport, guardian_relation, " & _ "guardian_address1, guardian_address2, guardian_postcode, " & _ "guardian_homephone, guardian_mobilephone, guardian_otherphone, " & _ "payment, remarks, student_att) " & _ "" & _ "Values(@student_name, @student_passport, @student_rcnumber, " & _ "@test_level, @test_venue1, @test_venue2, @test_row, @test_column, " & _ "@student_sex, @student_age, @student_dob,@student_country, @student_state, " & _ "@guardian_name, @guardian_passport, @guardian_relation, " & _ "@guardian_address1, @guardian_address2, @guardian_postcode, " & _ "@guardian_homephone, @guardian_mobilephone, @guardian_otherphone, " & _ "@payment, @remarks, @student_att)" Dim conn As New OleDbConnection(myNorthWind) Dim cmd As New OleDbCommand(insertSQL, conn) cmd.Parameters.AddWithValue("@student_name", txtName.Text.Trim) cmd.Parameters.AddWithValue("@student_passport", txtPassport.Text.Trim) cmd.Parameters.AddWithValue("@student_rcnumber", txtReceipt.Text.Trim) cmd.Parameters.AddWithValue("@test_level", txtTestLevel.Text) cmd.Parameters.AddWithValue("@test_venue1", txtVenue1.Text.Trim) cmd.Parameters.AddWithValue("@test_venue2", txtVenue2.Text.Trim) cmd.Parameters.AddWithValue("@test_row", dropAlpha.SelectedItem) cmd.Parameters.AddWithValue("@test_column", dropNumeric.SelectedItem) cmd.Parameters.AddWithValue("@student_sex", dropSex.SelectedItem) cmd.Parameters.AddWithValue("@student_age", dropAge.SelectedItem) '------------Assembly Date Format Dim dob As New Date dob = dropDay.SelectedItem & "/" & dropMonth.SelectedItem & "/" & dropYear.SelectedItem dob = String.Format("{0:MM/dd/yyyy}", dob) cmd.Parameters.AddWithValue("@student_dob", dob) '------------End Assembly cmd.Parameters.AddWithValue("@student_country", txtCountry.Text) cmd.Parameters.AddWithValue("@student_state", txtState.Text) cmd.Parameters.AddWithValue("@guardian_name", txtGdName.Text.Trim) cmd.Parameters.AddWithValue("@guardian_passport", txtGdPassport.Text.Trim) cmd.Parameters.AddWithValue("@guardian_relation", txtGdRelation.Text.Trim) cmd.Parameters.AddWithValue("@guardian_address1", txtAddress1.Text.Trim) cmd.Parameters.AddWithValue("@guardian_address2", txtAddress2.Text.Trim) cmd.Parameters.AddWithValue("@guardian_postcode", txtPostal.Text) cmd.Parameters.AddWithValue("@guardian_homephone", txtHome.Text) cmd.Parameters.AddWithValue("@guardian_mobilephone", txtMobile.Text) cmd.Parameters.AddWithValue("@guardian_otherphone", txtOther.Text) cmd.Parameters.AddWithValue("@payment", txtPayment.Text.Trim) cmd.Parameters.AddWithValue("@remarks", txtRemarks.Text.Trim) If rdbAbsent.Checked = True Then cmd.Parameters.AddWithValue("@student_att", 0) ElseIf rdbPresent.Checked = True Then cmd.Parameters.AddWithValue("@student_att", 1) End If conn.Open() cmd.ExecuteNonQuery() conn.Close()Then i got this error must declar scalar variable @student_name need some enlighten plzz T_T
View Replies !
View Related
Return Error Code (return Value) From A Stored Procedure Using A Sql Task
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great. I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection. I have two parameters mapped: tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1 I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted. The first part of the sp is below and I set the value @i and return. CREATE procedure [dbo].[Marketing_extract_history_load_TEST] @table_name varchar(200), @i int output as Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
View Replies !
View Related
The Cursor Does Not Include The Table ... Error?
We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.). I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good. The next error is puzzling though: [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor. Any suggestions on how to fix this?
View Replies !
View Related
Capture Error Message And Include It In The Email
Hello, I have a SQL Task that executes some stored procedure. When a condition is met the stored procedure raises an error by calling RAISERROR (@ErrorMessage,16,1). Following the SQL Task I have Send Mail Task that sends an error email. I would like to know how to include the @ErrorMessage in the error email. Thank you, -Oleg
View Replies !
View Related
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 !
View Related
Must Declare The Scalar Variable &"@CategoryID&". Error In Stored Procdure.
Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview. Error message i'm getting is Must declare the scalar variable "@CategoryID". I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-) Here is the whole store procedure. ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update @ID int,@CategoryID varchar(10), @RegionID int,@CompanyName nvarchar(25), @Email nvarchar(25),@PDFFront nvarchar(50), @PDFBack nvarchar(50),@ThumbFront nvarchar(50), @ThumbBack nvarchar(50),@Notes nvarchar(max) AS DECLARE @SQL varchar(1000) SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '') SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '') SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '') SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '') SET @SQL = @SQL + ', ThumbBack = @ThumbBack' SET @SQL = @SQL + ' WHERE (ID = @ID)' Print '@SQL = ' + @SQLEXEC(@SQL) RETURN
View Replies !
View Related
Need Help With SQL UPDATE...and The Error: Must Declare The Scalar Variable &"@UserName&".
I am trying to update a field in a pre-existing record in my database. This update is supposed to happen when the protected sub entitled "PictureUpload" is called by button click. I listed the code immediately below and then I listed the error that I am getting further below that. Does anybody know what I am doing wrong and why I am getting this error? Thanks in advance and forgive my newbie ignorance! Here is a portion of the Protected Sub that I am using in an attempt to update a field in a pre-existing record... Protected Sub PictureUpload(ByVal sender As Object, ByVal e As System.EventArgs) Dim ImageUploaded As Integer = 1 srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded srcprofiles_BasicProperties.Update() End Sub Here is the SqlDataSource control I included on the page with (what I hope is) appropriate formatting... <asp:SqlDataSource ID="srcprofiles_BasicProperties" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|UserProfilesDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [profiles_BasicProperties] WHERE ([UserName] = @UserName)" UpdateCommand="UPDATE [profiles_BasicProperties] SET [ImageUploaded] = @ImageUploaded WHERE ([UserName] = @UserName)"> <SelectParameters> <asp:Parameter DefaultValue="imageuploaded01" Name="UserName" Type="String" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ImageUploaded" Type="Int32" /> </UpdateParameters></asp:SqlDataSource> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^...and now the error...^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Server Error in '/PC_Dev' Application. Must declare the scalar variable "@UserName". 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: Must declare the scalar variable "@UserName".Source Error: Line 164: Dim ImageUploaded As Integer = 1 Line 165: srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded Line 166: srcprofiles_BasicProperties.Update() Line 167: Line 168: Source File: C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx Line: 166 Stack Trace: [SqlException (0x80131904): Must declare the scalar variable "@UserName".] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +721 System.Web.UI.WebControls.SqlDataSource.Update() +17 ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx:166 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
View Replies !
View Related
Help With Inserting A New Record Into Database - Error Must Declare The Scalar Variable &"@BookMarkArrayA&".
Hi, Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error : Must declare the scalar variable "@BookMarkArrayA". when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error This is the code : Public Sub NewCustomer() Dim temp As String = " " Dim ID As Integer = 1 'Restore the array from the view state BookMarkArrayA = Me.ViewState("BookMarkArrayA") temp = BookMarkArrayA(6) Dim Customer As SqlDataSource = New SqlDataSource() Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString() Customer.InsertCommand = "INSERT INTO [Table1] ([ID],[Program]) VALUES (@ID, @BookMarkArrayA(6))" Customer.InsertParameters.Add ("ID", ID) Customer.InsertParameters.Add ("Program", @BookMarkArrayA(6)) Customer.Insert() End Sub Cheers Ken
View Replies !
View Related
Must Declare The Scalar Variable &"@UserName&" ERROR
I'm attempting to create my first login form using the CreateUserWizard. I've spent this week reading up on how to create and customizing it. I want it to 1) the required user name is an email address (which seems to be working fine) and 2) having extra information inserted into a separate customized table. I now have the form working to the point where it accepts an email address for the username and it then writes that information along with the password to the aspnetdb.mdf...but i can't get the rest of the information to write to my custom table.I am getting the error "Must declare the scalara variable "@UserName" here's my .cs code:public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e) { TextBox UserNameTextBox = (TextBox)CreateUserWizardStep1.ContentTemplateContainer.FindControl("UserName"); SqlDataSource DataSource = (SqlDataSource)CreateUserWizardStep1.ContentTemplateContainer.FindControl("InsertCustomer"); MembershipUser User = Membership.GetUser(UserNameTextBox.Text); object UserGUID = User.ProviderUserKey; DataSource.InsertParameters.Add("UserId", UserGUID.ToString()); DataSource.Insert(); } protected void CreateUserWizard1_CreatingUser(object sender, LoginCancelEventArgs e) { CreateUserWizard cuw = (CreateUserWizard)sender; cuw.Email = cuw.UserName; } } protected void CreateUserWizard1_CreatingUser(object sender, LoginCancelEventArgs e) { CreateUserWizard cuw = (CreateUserWizard)sender; cuw.Email = cuw.UserName; }} and the asp<asp:SqlDataSource ID="InsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:kalistaConnectionString %>" InsertCommand="INSERT INTO [Customer] ([CustID], [CustEmail], [CustFN], [CustLN], [CustAddress], [CustCity], [AreaTaxID], [CustPostal_Zip], [CustCountry], [CustPhone], [CustAltPhone]) VALUES (@UserId, @UserName, @FirstName, @LastName, @Address, @City, @ProvinceState, @PostalZip, @Country, @Phone, @AltPhone)" ProviderName="<%$ ConnectionStrings:kalistaConnectionString.ProviderName %>"> <InsertParameters> <asp:ControlParameter Name="CustEmail" Type="String" ControlID="UserName" PropertyName="Text" /> <asp:ControlParameter Name="CustFN" Type="String" ControlID="FirstName" PropertyName="Text" /> <asp:ControlParameter Name="CustLN" Type="String" ControlID="LastName" PropertyName="Text" /> <asp:ControlParameter Name="CustAddress" Type="String" ControlID="Address" PropertyName="Text" /> <asp:ControlParameter Name="CustCity" Type="String" ControlID="City" PropertyName="Text" /> <asp:ControlParameter Name="AreaID" Type="String" ControlID="AreaID" PropertyName="SelectedValue" /> <asp:ControlParameter Name="CustPostal_Zip" Type="String" ControlID="PostalZip" PropertyName="Text" /> <asp:ControlParameter Name="CustCountry" Type="String" ControlID="Country" PropertyName="SelectedValue" /> <asp:ControlParameter Name="CustPhone" Type="String" ControlID="Phone" PropertyName="Text" /> <asp:ControlParameter Name="CustAltPhone" Type="String" ControlID="AltPhone" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource> thanks for the help
View Replies !
View Related
|
TRACKER
