Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter -
Language Code = GetUserCulture()
Which translates to this in the database query (for us english) -
table.language_code = 'EN-us'
And of course I need it to look like this -
table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this
Language Code = dbo.ConvertFcnIWrote(GetUserCulture())
Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
Thanks,
Toni Fielder
View Complete Forum Thread with Replies
Related Forum Messages:
&&"Failed To Load Expression Host Assembly. Details: StrongName Cannot Have An Empty String For The Assembly Name. &&"
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process." First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report: "Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name." Please help.
View Replies !
How To Selectively Apply A Filter
Based on some report parameters value? My dataset to display is coming from a stored procedure. Now based on a report parameter which user selects, I want to filter the results being displayed but only if user selects certain value in the parameter drop down. I tried using iif statement and I can use it to filter dataset based on parameter but can't get how NOT to apply the filter for a particular value of the user selected parameter. Hope I am clear.
View Replies !
Call Function In Data Flow
how can you call a sql function in data flow? I have a function that calculate age base on the data in two columns . I would like to call this function in data flow to calculate the age..
View Replies !
Cross Apply Issue (Last Call) :)
I can't figure out what can be causing this. When I use this query Select top 1000 a.EmployeeID,b.* from #TmpActiveEmployeesWSeverance a cross apply dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b order by a.EmployeeID,a.BenefitTypeID It runs 4 seconds If I try to insert the results into anything It runs > 5 minutes (I have yet to let it finish) I have tried the two following pieces of code, both with the same results Select top 1000 a.EmployeeID,b.* into #Tmp from #TmpActiveEmployeesWSeverance a cross apply dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b order by a.EmployeeID,a.BenefitTypeID --and Insert Into TRP_ActiveEmployeesWSeverance (EmployeeID ,PK ,BeginningBalance ,BenefitInterestRowID ,BenefitInterestID ,BenefitTypeID ,DateReceived ,InvoiceDate ,Amount ,Hours ,Fraction1 ,Fraction2 ,Interest ,InterestAmount ,StartDate ,EndDate ,PeriodApplied ,Offset ,Reserve ,Account ,BenefitClosedID ,PaidOut ,ClosedAccount ,ai ,ClosedDate ,StartAgain ,PartialDividend ,PartialFraction ,SameDateCount) Select top 1000 a.EmployeeID,b.* from #TmpActiveEmployeesWSeverance a cross apply dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b order by a.EmployeeID,a.BenefitTypeID Any thoughts as to what can be disrupting this?
View Replies !
Global Expression Apply To All Fields In Report
Hi All, I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area. Any helps are appreciated. Cheers Nick
View Replies !
How To Call A Dot Net Assembly From Ssis
How to call a dot net assembly from ssis. I have used a script component and went in the design script editor by clicking the "Design Script" button.From their using a object browser trying to load a dll file in references to be used in the ssis application. But it gives error " Cannot browse the file" Can any body help me in the same.
View Replies !
Filter Expression
HiI have some data that need to be filtered based on a SET of Id's.If it's about a single ID, then i would pass it as a parameter in astored procedure and use it within the the WHERE Clause, but here thoseID's are determined in run time and I can't simply create a a storedprocedure for an unknown amount of ID's.I looked into the SQL Server 8.0 Manual but had no examples how to usethe Function Filter.Generaly, how can filter some records based on a set of ID's?Best regards
View Replies !
How To Call A DotNet Assembly In SSIS Script Component
I tried to access a dot net assembly (.dll) file in ssis script component using following steps. Create new Script Task in Data Flow Task Edit Design Script button-> Loads script Project in MS VSA Locate Object Browser Select Custom Component Set from the dropdown and hit browse button Browse and place the custom component dll (This Custom component dll has to be in GAC - Global assembly cache before browsing) Select the namespace from the Component list of the object browser and click on €œAdd to references to Selected project in the solution explorer€? button Write Imports <namespace> in the script code to invoke class methods from the .NET custom component The following steps worked properly with June CTP version of yukon.In september CTP version of Yukon in SSIS when i browse the dot net assembly with the same above steps i get a error stating " The file could not be browsed ".Can anybody help me in the same. Prashant Utekar
View Replies !
Is It Possible To Create A UDF From A .NET 2.0 Assembly That Uses Dllimport To Call Unmanaged Code?
I have been reading up on everything I can find on this subject and I am not clear if this is allowed within the SQL Server 2005 CLR. My function calls work within a Windows form project, but don't run when invoked from a SQL function. I don't get any errors or warnings when creating the assembly and functions within SQL Server, but when running via a select statement, the spid just hangs and I have to stop & restart the service to kill the process. I have been investigating the security settings for this assembly, but I think I have that covered via the RunTime Security Policy settings in the .NET Framework 2.0 Configuration tool. Any insights, knowledge, or thoughts would be greatly appreciated. Barry
View Replies !
How Do I Write An OUTER APPLY Without A Table Function?
I'd like to select the last unshipped order, or if none is open, the last shipped order, for each customer. I noticed that all the examples on line for OUTER APPLY involve table-valued functions, but I have read (in a book I don't have here) that a subquery can work. The following shows what I want, but it isn't valid syntax: SELECT c.CustomerName, oa.OrderNumber, oa.Status FROM Customers c OUTER APPLY ( SELECT TOP 1 CustomerID, OrderNumber, CASE WHEN ShipDate IS NULL THEN 'due ' + CAST(DueDate AS VARCHAR) ELSE 'shipped ' + CAST(ShipDate AS VARCHAR) END AS Status, CASE WHEN ShipDate IS NULL THEN DueDate + 1000 -- Give open orders priority. ELSE ShipDate END AS SortOrder ORDER BY SortOrder ) AS oa ON oa.CustomerID = c.CustomerID
View Replies !
Can You Call/execute A Stored Procedure From An Expression?
I want to use Navigation to click-through to another report. However, the source report is an MDX summary report, and the report I'm calling needs the entire Customer MDX tuple as a parameter. (the entire customer tuple is not available in my cube) The text box has enough information to identify the customer, but not the entire tuple. I want to pass the text box contents to a SP which returns the MDX string, and pass that to the new report. I do not see how using another data source can help me accomplish this. Basically, I am wondering is there a way I can have an expression similar to =EXECUTE(spX, parm=txtbox.Value) Thanks In Advance for any help...
View Replies !
Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View Replies !
Problem Creating A Function From An Assembly
I have compiled a dll with the following code: Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Imports System.Data.SqlTypes Public Class SICTrans Public Shared Function TransSIC(ByVal inpSIC As String) As String 'Dim conn As SqlConnection = New SqlConnection("context connection=true") Dim NewSIC, TempSIC, tempFSIC As String If Len(NZ(inpSIC)) > 0 Then TempSIC = NZ(inpSIC) If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC)) tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2) If Val(Right(TempSIC, 1)) > 0 Then tempFSIC = tempFSIC + "/" + Right(TempSIC, 1) End If End If NewSIC = tempFSIC TransSIC = NewSIC End Function Public Shared Function NZ(ByVal input As String) As String If Not (input Is Nothing) Then Return input Exit Function End If Return String.Empty End Function End Class Which compiles fine... i then use the following code to create the assembly in SQL which is fine: USE NARD GO CREATE ASSEMBLY SICCodeTrans FROM 'c:SICCodeTrans.dll' WITH PERMISSION_SET = SAFE GO but when i goto create the function with the following code it wont have it! CREATE FUNCTION TransSICCode(@inpSIC varchar) RETURNS varchar AS EXTERNAL NAME SICCodeTrans.SICTrans.TransSIC GO It gives me the following error message Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1 Could not find Type 'SICTrans' in assembly 'SICCodeTrans'. Any ideas?????????? Thanks Marek Kluczynski
View Replies !
2005: Creating Aggregate Function From .NET Assembly
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
View Replies !
Access Static Function From Custom Assembly
I think I posted this in the wrong forum as I got no response. Please see here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3264437&SiteID=1&mode=1 I am trying to access a static function from a custom assembly. The assembly loads, but I cannot call any functions contained within. Thanks for any help.
View Replies !
Count Function To Filter
Guys, I'm using the following code and I just want display all glcodes that have a count less than 2: select glcode, sum(abs(sysvalue)) 'Movement', count(glcode) 'Occurances' from jet group by glcode order by occurances The following sytax works i know i need to use the where function. Does anyone know the syntax? Cheers Michael
View Replies !
How To Create Assembly Function Using Dll Files In SQL Server 2005???
Hiiiiiiii all I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration I have tried like this CREATE ASSEMBLY abc FROM 'C:abc.dll' WITH PERMISSION_SET = SAFE but it gives me incorrect syntax error so plzzzzz anyone help me wht to do in my probbbbbbbbb??????? Pratik Kansara
View Replies !
ALTER ASSEMBLY Error Msg 6509 An Error Occurred While Gathering Metadata From Assembly ‘&&<Assembly Name&&>’ With HRESULT 0x1.
I work with February CTP of SqlServer 2008. I have an Assembly with several UDTs inside. Version of assembly is 1.0.* I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version). Then I try to update assembly in SqlServer. I use ALTER ASSEMBLY <name> FROM <path> WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA statement for this. Statement runs with error: Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1. I found the list of condition for ALTER ASSEMBLY in MSDN: ALTER ASSEMBLY statement cannot be used to change the following: · The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly. · The signatures of methods in the assembly that are called from other assemblies. · The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly. · The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly. · The FillRow method name attribute for CLR table-valued functions. · The Accumulate and Terminate method signature for user-defined aggregates. · System assemblies. · Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead. Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes: · Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed. · Adding new public methods. · Modifying private methods in any way. But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions. What could be the reason for such behavior? P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
View Replies !
Call A Function
Does anybody knows how to call a function from one VB source file to another VB source file?? I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??
View Replies !
Need Help Understanding A Call To A Sql Function
Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in. 1 ALTER PROCEDURE [dbo].[GetPortalSettings]2 (3 @PartyId uniqueidentifier,45 AS6 SET NOCOUNT ON7 CREATE TABLE #Groups8 (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)
View Replies !
Call Custom SQL Function In ASP.NET
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
View Replies !
Overload Function Call
Hi, I want to write one function like that dbo.function( number , 1 , 2 ) , but I would like to overload, and send char or number dbo.function( number, 'abx' , ' xpto' ). I would like to keep the same name, Can I do this? or Do I need to write to differents functions thanks,
View Replies !
Call Function For Inner Join
I have a procedure which has query like Query 1. Query 1 Select Clinetid from clinet inner join { select centerid from GetChildCenter(@Centerid) union select centerid from getParentCenter(@Centerid) } as Center c on c.Centerid = client.Centerid Query 2 declare @Center table ( centerid int) insert into @Center select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid) Select Clinetid from clinet inner join @Center c on c.Centerid = client.Centerid I just want to know which one is better performance wise.. because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()
View Replies !
Calling VB Based SQLCLR Function Failed With Error Can't Load System.Web Assembly
I created a CLR function based on following VB code: Imports Microsoft.SqlServer.Server Public Partial Class SqlClrVB <Microsoft.SqlServer.Server.SqlFunction()> _ Public Shared Function GetTotalPhysicalMemory() As Integer GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory End Function End Class The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function: create assembly totalmem from '!WORKINGDIR! otalmem.dll' WITH PERMISSION_SET=UNSAFE go create function fnGetTotalMem() returns int as external name totalmem.SqlClrVB.GetTotalPhysicalMemory go When I call this function, it returned following error: select dbo.fnGetTotalMem() Msg 6522, Level 16, State 2, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem': System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. System.IO.FileNotFoundException: at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value() at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance() at SqlClrVB.GetTotalPhysicalMemory() . Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005. Thanks much, Zhiqiang
View Replies !
How To Grant Rights For The Anonymous IIS Web User IUSR_.. To Execute Scalar Function In Assembly
Dear all, Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET. I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string: select dbo.CLRHTMLString('abc') The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.). To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied: EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'. Note: The 'webuser' database role has Execute permission on the Assembly. I have also tested this from my actual web page, with the following results: (1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly. (2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly. Further test results: (3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user. (4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list. Thankful for any advice on this matter.
View Replies !
CROSS APPLY Vs OUTER APPLY Example Messed Up?
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example. I'd like help getting it to work correctly, or understand why it is isn't working the way I planned. On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database: Code Block CREATE FUNCTION fnGetAvgCost(@ProdID int) RETURNS @RetTable TABLE (AvgCost money) AS BEGIN WITH Product(stdcost) AS ( SELECT avg(standardcost) as AvgCost FROM Production.ProductCostHistory WHERE ProductID = @ProdID ) INSERT INTO @RetTable SELECT * FROM Product RETURN END and then run a sample T-SQL statement Code Block SELECT p.Name, p.ProductNumber, Convert(varchar, cost.AvgCost,1) AS 'Average Cost' FROM Production.Product p CROSS APPLY fnGetAvgCost(p.ProductID) AS cost WHERE cost.AvgCost IS NOT NULL ORDER BY cost.AvgCost desc My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values? When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values. Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function. So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause? (Please refrain from introducing another example into this question.)
View Replies !
Trying To Call A Function On A Weekly Timer
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View Replies !
Call Function From Stored Procedure
Hi All, I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions: 1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject. 2) Can someone provide any links to online tutorials on t-sql? Thanks in advance. Curtis
View Replies !
How To Call A Function Using OLE DB Command Tranformation
Hello i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,? there are no errors while executing this transformation but this function returns a value Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation Thanks
View Replies !
How To Bind Return Value From Function Call
I have a function that I need to call from an execute sql task. I want to bind the return value from the function to an ssis variable. Can someone please show me an example of what the function syntax needs to look like in order for this to work? I know that with sp's, you need to explicitly state the column names. I have tried many things without success. Thanks
View Replies !
Trying To Call The Function A Web Service From Transact-SQL
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error. I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too? Thanks for any help. ALTER PROCEDURE [dbo].[pTAPServiceWeb] @sUrl varchar(200), @response varchar(8000) out AS DECLARE @obj int DECLARE @hr int DECLARE @status int DECLARE @msg varchar(255) EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT IF @hr < 0 BEGIN RAISERROR('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) RETURN END EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false IF @hr < 0 BEGIN SET @msg = 'sp_OAMethod Open failed' GOTO err END EXEC @hr = sp_OAMethod @obj, 'send' IF @hr < 0 BEGIN SET @msg = 'sp_OAMethod Send failed' GOTO err END EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT IF @hr < 0 BEGIN SET @msg = 'sp_OAMethod read status failed' GOTO err END -- IF @status <> 200 -- BEGIN -- SET @msg = 'sp_OAMethod http status ' + str(@status) -- GOTO err -- END EXEC @hr = sp_OAGetProperty @obj, 'responseText', @response OUT IF @hr < 0 BEGIN SET @msg = 'sp_OAMethod read response failed' GOTO err END EXEC @hr = sp_OADestroy @obj RETURN err: EXEC @hr = sp_OADestroy @obj RAISERROR(@msg, 16, 1) RETURN GO
View Replies !
Function Call In Insert Statment
Hi i m trying to call a function in insert statment Insert Into (value, value1) Value(@value, dbo.function(@value1) dbo.function returns a value, when i test the function in querry builder all goes fine. In my program i become a error "Parameterized Query '' ' expects parameter @value1 , which was not supplied." I m using visual studio , tableadapter.update function to insert datarecords in db thx for help
View Replies !
Function Call In Dataset Query
Hello Guys, I have a question that seems easy but I can not figure out... Premise: Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly. I have a Dataset that has a calculation for a column within a select statement Query Pseudocode: select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from (subquery"blah" ) Union (Subquery"blah") Custom Code: Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double if denominator = 0 then return 0 else return numerator/denominator end if End Function How To use: If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field. Problem: How do I add this code reference in the following dataset select statement select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from (subquery"blah" ) Union (Subquery"blah") table1 I tried to do this: from this: [FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ... to this [FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ... But it did not work...gave me this error: TITLE: Microsoft Report Designer ------------------------------ An error occurred while executing the query. Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. ------------------------------ ADDITIONAL INFORMATION: Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Help! P.S. this is a Matrix report and this select statement is within one of the datasets that fill a matrix.
View Replies !
How To Call A Userdefined Function Within A Stored
Hello All, How do i call a user defined function from within a stored procedure, I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string. That part works. declare @fullname varchar(400) @fullName=getFullName(@firstname,@lastname) As always thanks for all your input
View Replies !
Filter Expression For Searching &"with All The Words&"
Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is: Description LIKE '%{0}%' For example, if “John Smith” is typed into the search textbox, the results will include a row with: 1. “John Smith is my neighbor” but NOT a row with 2. “John is my neighbor. His last name is Smith”. How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox. Thanks for any help you can provide in helping me refine my search options.
View Replies !
Using Cint Function In Expression Editor
Hi, I am a new to Reporting Services... I am using RS2000 I am trying to apply cint function in the expression editor for the textbox eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value). This is returning error The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow. Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.
View Replies !
Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it. CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int, @as_of_date datetime) RETURNS TABLE AS RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value FROM dbo.Issue INNER JOIN dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date) GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date HAVING (dbo.Assets.deal_id = @deal_id) ) I need to do calculations on market value based on the default date. If default date isn't specified then it should be 100% of par amount. If default date is less than one year ago - 65% of the par_amount. If default date is one or more years ago - 0. I have no idea about how to do this and everything I try wont work. I created another function to do the calculations and this seems to work, but it only does one record instead of all of them. CREATE FUNCTION dbo.tf_Asset_Portfolio2 (@deal_id int, @as_of_date datetime) RETURNS @Market TABLE (issue_id int, par_amount money, market_value money) AS BEGIN DECLARE @ReturnDate datetime DECLARE @DD datetime DECLARE @PA money DECLARE @MV money DECLARE @ID int DECLARE @DateD int SELECT TOP 1 @ReturnDate = LAST_BATCH FROM master..sysprocesses WHERE SPId = @@SPID SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID SET @DateD = DateDiff("yyyy", @DD, @ReturnDate) If @DD = Null BEGIN SET @MV = @PA END Else If @DD > @ReturnDate BEGIN SET @MV = @PA END Else If @DateD < 1 BEGIN SET @MV = @PA * .65 END Else If @DateD >= 1 BEGIN SET @MV = 0 END insert into @Market (issue_id, par_amount, market_value) values (@ID,@PA,@MV) RETURN END I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
View Replies !
|
TRACKER
