With this statement I get an error where "Column QuoteHeaderID does not belong to table. " because Object reference isn't being set to an instance of an object, yet if I remove the max part it functions correctly.
SELECT MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (Select Name from kua where ID=A2.Valint) END) as Manage FROM ree A1 inner join data A2 on A1.DataID=A2.ID AND A1.SubType=31066 inner join ancestors AN ON AN.DataID = A1.DataID and AN.AncestorID=9735190
Every time I run it i get this error:Cannot perform an aggregate function on an expression containing an aggregate or a subquery...If I change the Field definition to:
MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (A2.Valint) END) as Manager
then it works. The query Select Name from kua where ID=A2.Valint has only one field as result. Why do I get an error?
dear folks, please check my function i'm getting error like this
Select statements included within a function cannot return data to a client.
create function getuomid(@uomcode varchar(50)) returns varchar as begin declare @uom_id varchar(50) select uom_id from vuom where uom_code=@uomcode return @uom_id end go
Vinod Even you learn 1%, Learn it with 100% confidence.
Dear experts, please tell me where is the error in my code?
create function getitemid(@uomid varchar(50)) returns table as begin declare @itemid varchar(50) select @itemid= column01 from table21 where column03=(select dbo.getuomid('no of leaves')) return (@itemid) end go
Vinod Even you learn 1%, Learn it with 100% confidence.
Hi, I am write one storedprocedure,inside that i use sum function like SUM((CASE WHEN d_end_name_qualifier LIKE 'Oc' THEN duration ELSE 0 END)) d_to_stream then i get following error,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation.
Dim drTemp As dsContacts.ContactsRow = dsContacts.Contacts.FindByContactID(iContactCode) . . ""If Not IsDBNull(drTemp.MobilePhone) Then Me.lblMobilePhone.Text = cstr(drTemp.MobilePhone)"" . .
When I Execute it , it returns this error message :
Cast from type 'DBNull' to type 'String' is not valid
Hi, When I try to create a CLR function in SQL 2005 (June CTP) I get the following error: Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'. The assembly registers successfully, with no errors. I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO The function's code is the following: using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient; namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) { //Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return ""; //Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . } return cFinal; } }} Thanks in advance, Anderson
but when I use following - 3) select name, datalength(Name), charindex('_2_', Name), substring(name, 5, charindex('_2_', Name) - 3) from msdb.sysjobs.name
I get the result I want (last column): Job_4927_2_7Sun 30 9 4927_2 Job_250144_2_6Sat 34 11 250144_2 Job_30197_2_1Mon 32 10 30197_2
but also with an error: Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function.
I have the below function which errors out telling me "Invalid use of 'getdate' within a function." I can run it as sql but not as a function, is there an issue with using getdate() in a function?
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER function fnGetQuantity( @orderid int ) returns int as /************************************************** **************************** ** File: fnGetQuantity.sql ** Name: fnGetQuantity ** Desc: Used to calculate the total order quantity for an order ** Quantity is sigfrequency.timesperday * sigdosageamt.descr * orders.duration ** ** Return values: Quantity ** ** Called by: ** ** Parameters: order Id ** Input Output ** ---------- ----------- ** @patientid ** Auth: DHoefgen ** Date: 04/30/05 ************************************************** ***************************** ** Change History ************************************************** ***************************** ** Date: Author: Description: ** -------- -------- ------------------------------------------ ** 05/02/05 KKowert Changed sql for effdt and added duration and ** times per day logic for zeros. ************************************************** *****************************/ begin declare @QuantityTotal int
SELECT @QuantityTotal = (o.Duration * f.TimesPerDay * d.Descr) FROM Orders o INNER JOIN SIGFrequency f ON o.FreqID = f.FreqID INNER JOIN SIGDosageAmt d ON o.DosageAmtID = d.DosageAmtID WHERE (o.OrderID = @orderid) AND (f.Effdt = (SELECT MAX(f2.Effdt) FROM SIGFrequency f2 WHERE f2.FreqID = f.FreqID AND f2.Effdt <= getdate())) AND (d.Effdt = (SELECT MAX(d2.Effdt) FROM SIGDosageAmt d2 WHERE d2.DosageAmtID = d.DosageAmtID AND d2.Effdt <= getdate()))
return @QuantityTotal
end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
while using following function I am getting this Error "Msg 195, Level 15, State 10, Line 1 'DATE' is not a recognized built-in function name."
select callid, DATE(segstart) AS calldate from October_CLI.dbo.raw Where callid >0 Segstart Column = Data with call date & time Callid Column = Unique Call Id October_CLI.dbo.raw = Table Calldate = Where i want the only date to be capture from segstart column
I have made an aggregate function sql (as far as I understand).I need the sum but also the features column. But I can only read one column in the select.Here is the select trying to read two columns:
select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems
here is the error:
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'features' as part of an aggregate function.
Hi all,I have the below user-defined function on mssql 2000 and I can't workout why i'm getting the following error:-----Server: Msg 156, Level 15, State 1, ProcedurefnCalculateOutworkerPaymentForBox, Line 15Incorrect syntax near the keyword 'IF'.Server: Msg 170, Level 15, State 1, ProcedurefnCalculateOutworkerPaymentForBox, Line 23Line 23: Incorrect syntax near ')'.----------CREATE FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)RETURNS moneyASBEGINRETURN (/* if the box is a paperback */IF (SELECT COUNT(BoxID) AS NoOfBoxes FROM OutworkerBoxes WHERE BoxID= @boxid AND BoxCode LIKE '%PAPER%') > 1/* If the books are paperback, charge 15p each and add on 30p for adescription book to make 45p */SELECT ((endref - StartRef) * 0.15) + (NoOfDescriptionBooks * 0.30)FROM OutworkerBoxes WHERE BoxID = @boxidELSE/* If the books are normal, charge 25p each and add 20p on fordescription books to make 45p */SELECT ((endref - StartRef) * 0.25) + (NoOfDescriptionBooks * 0.20)FROM OutworkerBoxes WHERE BoxID = @boxid)END-----Below is the sql for the table it works with:-----CREATE TABLE [OutworkerBoxes] ([BoxID] [int] IDENTITY (1, 1) NOT NULL ,[OutworkerID] [int] NOT NULL ,[ImportedBy] [int] NULL ,[StartRef] [int] NOT NULL ,[endref] [int] NOT NULL ,[DateIssued] [datetime] NOT NULL ,[BoxCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[DealerID] [int] NULL ,[StatusID] [int] NOT NULL ,[IssuedBy] [int] NOT NULL ,[BoxNotes] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,[DateImported] [datetime] NULL ,[NoOfDescriptionBooks] [int] NOT NULL CONSTRAINT[DF_OutworkerBoxes_NoOfDescriptionBooks] DEFAULT (0),CONSTRAINT [PK_OutworkerBoxes] PRIMARY KEY CLUSTERED([BoxID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO-----If anyone can advise me i'd be most grateful.Thanx in advanceJames
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:
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.
I am in the middle of creating an editable DatGrid:
Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs) AccessoryGrid.EditItemIndex = e.Item.ItemIndex End Sub
Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs) e.NewValues.Add("@AccessoryID", AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex) ("AccessoryID")) e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text) e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text) e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text) e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text) e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text) End Sub
For some reason, I get an error message like this: Server Error in '/' Application.
Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.
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: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?
I have been trying to get rid of this error by trying to change the field type within my database with no success, I keep on getting the same error either way.
I've found that I'm not the first one to get the error
"Procedure or function x has too many arguments specified"
while working with Stored Procedures (which is no surprise at all). But all suggested solutions didn't help, maybe this is because I misunderstood the whole concept. The situation is: On my page there is a FormView control including the EditItemTemplate. The database contains a Stored Procedure called UpdatePersonByID which is working fine as long as executed in Visual Web Developer. Here's the procedure code:
This is not exactly what it will finally have to do, of course the WHERE-clause later will contain an ID comparison. But since I tried to break down my code as much as possible I changed it to what you see right now. Here's the aspx-source (the red stuff is what I think is important):
And then once again a totally different question: Is there a way to post the highlighted aspx or vb code into this forum and keep the colors? i think I've seen that in some posts but didn't wanna do it manually.
I am creating a function which is going to return a table. The Code ofr the function is as follows... =============================== Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70)) AS begin declare @t1_sys char(10),@t1_all varchar(11) declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS) if (substring(@cg1,1,2)='Q$') set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$') DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY insert into @temp_qcard values(@cg1) OPEN C1 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all WHILE @@FETCH_STATUS = 0 BEGIN
insert into @temp_qcard values(@t1_all)
declare @t2_sys char(10),@t2_all varchar(10) DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
begin OPEN C2 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t2_all)
declare @t3_sys char(10),@t3_all varchar(10) DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
begin
OPEN C3 FETCH NEXT FROM c3 INTO @t3_sys,@t3_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t3_all) FETCH NEXT FROM c3 INTO @t3_sys,@t3_all end end close c3 deallocate c3 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all end end close c2 DEALLOCATE c2
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all END
CLOSE c1 DEALLOCATE c1 Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata
RETURN END ==========================
While compiling this I am getting the Below error .... ================== Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35 Mixing old and new syntax to specify cursor options is not allowed. =================
Can Anyone please help me how to resolve this issue...
I have setup CDC on 50 tables and then in one SP I’m calling all cdc function like below issue is I'm getting error “an insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes ... .” as error is not mentioning for which capture instance I'm getting this error so not able to find.
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old')
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double if pFields !FA00902_AMOUNT.Value > 0 and pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
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.
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code: SELECT CASE WHEN <CONDITION> THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE)) ELSE 0 END AS CurrentDateAdj, Year(CurrentDateAdj) + '_' + 'some text and processing') FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
i have a function Create Function ReturnAmountB(@CMID int) Returns Decimal as BEGIN declare @Return decimal select @Return =sum(PD_PaymentAmount) from Payment_Details where PD_IsRefund=1 and PD_PaymentType=0 and PD_CMID=@CMID return @Return END
when i run this on server(remote) this error shows... Incorrect syntex near 'Function' Must declare the variable '@CMID' A RETURN statement with a return value cannot be used in this context
i am not able to understand why this error shows... Any solution!!!
Is there any reason why I shouldn't cause an arithmetic error(say bydividing by zero) in a User Defined Function for a situation where in astored procedure you would use RAISERROR or in code you would throw anexception?In most situations, I can check the return value of the UDF to see thatit is valid. However, I want to sum the output of UDF and thereforecannot check the return value. Errors should be so rare that I wouldrather not use a cursor to do the sum--and take the peformance hit.Following is the code:SELECT SUM([dbo].[udf_MyFunction]([Column1],[Column2]))FROM tblMyTableThanks ~ Matt
I have a work database where I implemented a table-valued function. One colleague of mine reported to me that this function gave a Divide by Zero error when executed with some specific values given to its arguments (there are a 15 arguments). Then I started debugging, and I introduced some exit points to the function before its end in order to detect the point where the error appeared, since I don't have access to the database server and I cannot use the debugging tools from remote, due to the network configuration of my office. I can only do attempts on the code to try to find a solution.
Since I didn't manage to get rid of this error, I decided to make a silly and desperate attempt: I put a RETURN statement immediately after the BEGIN of the function body, with the idea that the function should not raie any error if it exit immediately after its beginning, despite the fact that this results in an empty table in return.
The result of my attempt is that the Divide by Zero error is still THERE (!), even if my function looks like
ALTER FUNCTION [dbo][<myFuncName>](...parameters...) RETURNS TABLE (...table definition...) AS BEGIN RETURN END GO
I created a clr proc that gets the most recent file within a directory based on the creation time property, see code below. I have attempted to replicate this within a clr scalar valued function in order to assign the resulting value to a variable within SQL server. I am getting the error message:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_fn_recentfile":
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.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at clr_fn_recentfile.clr_fn_recentfile.clr_fn_recentfile(SqlString Filepath)
After trying to troubleshoot this I am aware that this error is rather generic and have not been able to find any specific documenation regardint the use of file system objects with clr functions. I am at a loss. Any help would be appreciated! STORED PROC CODE WORKS
_ Public Shared Sub clr_recentfile(ByVal Filepath As SqlString) Dim strFile As String Dim sp As SqlPipe = SqlContext.Pipe() Dim maxDate As Date Dim fil As String Dim qry As New SqlCommand() Try If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString) Path.GetFileName(strFile) fil = Path.GetFileName(strFile).ToString Dim fi As New FileInfo(strFile) If maxDate = Nothing Then maxDate = fi.CreationTime fil = fi.FullName.ToString Else If maxDate < fi.CreationTime Then maxDate = fi.CreationTime End If End If
Next Else sp.Send("Directory does not exist") Return End If If fil <> Nothing Then qry.CommandText = " SELECT '" & fil & "'" 'Execute the query and pass the result set back to SQL sp.ExecuteAndSend(qry) sp.Send(qry.CommandText.ToString) End If Catch ex As Exception sp.Send(ex.Message.ToString) End Try End Sub End Class
Partial Public Class clr_fn_recentfile <Microsoft.SqlServer.Server.SqlFunction()> _ Public Shared Function clr_fn_recentfile(ByVal Filepath As SqlString) As SqlString Dim strFile As String Dim sp As SqlPipe = SqlContext.Pipe() Dim maxDate As Date Dim fil As String Dim qry As New SqlCommand() Try If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString) Path.GetFileName(strFile) fil = Path.GetFileName(strFile).ToString Dim fi As New FileInfo(strFile) If maxDate = Nothing Then maxDate = fi.CreationTime fil = fi.FullName.ToString Else If maxDate < fi.CreationTime Then maxDate = fi.CreationTime End If End If
Next Else sp.Send("Directory does not exist") Exit Function End If If fil <> Nothing Then Return fil End If Catch ex As Exception sp.Send(ex.Message.ToString) End Try
I have developed reports for Oracle database. I wanted to avoid use of OUT REF cursor hence am using function & calling it from reports.
The logic in SP/function is so complex that it takes 5 mins to execute n get the result. I see the results on the report server, But when I call that report from web application, I get System.Net.WebException - Time out error.
My questions:
1. What setting I make on report server to avoid timeout?
--Are these settings applied on report server restart?
2. Is there any setting needed for Datasource also?
