.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

More than one records return error

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi Below is my query used in procedure. This query can return no records or more than one records. If this is the case then I wanted to return error message. How to do this? Select Band, SimpleBand into <complexband>, <simpleband> from HHSRS_HazardBand where lowerbound <= <hazardscore> and upperbound > <hazardscore>  

View Complete Post

More Related Resource Links

linq and max, if return null return error


hi there
i have this query :

var result2 = (from TH in DB.Threshold
                          where TH.ProductSKUID == int.Parse(HID_ProductSKUID.Value)
                          select TH.ThresholdID).Max();

the problem is that if this query does not find any items, return null, so thow exception
how can i do to return 0??

Left Outer Join fails to return all records

I have a table tblDrop that contains 1067 records for 8/31/2010 and a table tblSoftcount that has 739 records, some with bad serial numbers. I need to match all of the records in tblDrop with those in tblSoftcount. I have a query using Left outer join that has worked for the duration of the application, but has recently stopped returing the full set of records from tblDrop. We have not changed the SQL versions on the server (compatibility level 2005). Here is the query: SELECT   v.SlotNum_ID, s.SlotNum_ID, v.PTNumber, s.PTNumber, v.DropBillAmount AS [Drop], v.ReportDate, v.DateValue     FROM tbl_Drop v LEFT OUTER JOIN tblSoftCount s ON v.SlotNum_ID = s.SlotNum_ID AND v.ReportDate = s.ReportDate WHERE   s.ReportDate = '8/31/2010' AND v.DropBillAmount <> 0 AND s.Slotnum_ID IS NULL;  

how to return records in squence of inner join table?

Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

ASP.net Web Site Administration Tool. An error was encountered. Please return to the previous page a

I have just installed Visual Studio 2010 trial. Previously I have been using Visual Studio Web Developer 2010 Express. Now every time I try to invoke the ASP.net Cofiguration tool either via the icon or through the Project File Menu item, I get the error message "An error was encountered. Please return to the previous page and try again." I am trying to develop a bog standard Silverlight Business Application. There are no other diagnostics. Is there anywhere I can look for more clues or anything else to do to alleviate the problem? or is this something peculiar to the trial version I am using?   I also think it might have something to do with the fact that I have created this Silverlight Business App on a directory on my D: drive and not the normal, default c: drive path and maybe the web sit admin tool is looking somewhere else. Could this have something to do with it? If so what is my remedy?   Help please.....

Error: "Select statements included within a function cannot return data to a client"

hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

Getting VP.NET code to respond to SQL Return values and Error Codes


I have SQL stored procedures that will return values depending on the outcome of its execution. I have the following code at the end of each of my Stored Procedures:

RETURN @siReturnValue

It would be great to be able to use this Return value after running the Stored Procedures from my VB.NET code. I have included an example of how I am running SQL Stored Procedures below:

    Public Function SearchKeyword(ByVal SearchString As String) As Data.DataTable
        Dim AerialConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("aerialConnectionString").ConnectionString)
        Dim MediaArchive As New Data.DataTable
        Dim reader As SqlDataReader
            Dim SelectMediaArchiveCommand = New SqlCommand("EXEC spSearch @keywords", AerialConnection)
            SelectMediaArchiveCommand.Parameters.AddWithValue("keywords", SearchString)
            reader = SelectMediaArchiveCommand.ExecuteReader()
        Catch e As Exception
        End Try
        Return MediaArchive
    End Function

How can I code this to respond to different Return values, or even error cod

Browse /ReportServer return 401.3 error after joined domain



Hi all,

After my server joining a domain, when I try to browse the ReportServer by using a local user account call 'ReportViewer' with "browse" role. I got the follow error:

Server Error in '/ReportServer' Application.

Access is denied.

Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control List). Ask the Web server's administrator to give you access to 'c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Rporting Services\ReportServer\Reserved.RportServer'.

My 'ReportViewer' user which belong Users group used to works fine before my machine join the domain. And I check both follow folders and the Users group do have read and execute permission.

  • C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager
  • C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer

Environment Information:

  • SQL Server 2008 (64 bit) along with SSRS and SSIS is installed on a 64 bit machine Running Windows Server 2003 R2 Standard,

return records based on current date?


I tried finding a QA which already addressed this idea, but with no luck. Sorry if it's been addressed before.


I have a table where sales pricing is managed; it contains history of pricing where every price record has an 'effective date'. Currently when a user needs the 'active' price for a customer/item combination, the table is queried by the record that has the most recent 'EffectiveDate' before GETDATE(). Here is the def for table 'tim_PriceSheet'

ItemKey int, not null

EffectiveDate datetime, notnull

CurrID varchar(3)

ListPrice decimal (15,5)

The primary key is set on ItemKey, EffectiveDate

Sample Data:


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-07-01 00:00:00.000	CAD	100.00000
45	2010-09-14 00:00:00.000	CAD	110.00000
45	2010-10-01 00:00:00.000	CAD	112.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000
63	2010-09-18 00:00:00.000	CAD	40.00000
63	2010-10-01 00:00:00.000	USD	40.00000
63	2010-10-15 00:00:00.000	CAD	41.00000
63	2010-10-21 00:00:00.000	USD	41.00000

My challenge is this:

I want to return a record set which shows the current price for each distinct item and currency code; in the example below the result would be based on the date being September 16, 2010

how can i return 401 error from a method return ActionResult?


how can i return   401 error from a method return ActionResult?

People Picker fails to search or resolve and return an error message.



I got an error while trying to access the people picker from some specific locations. I'm experiencing those problems ONLY in the cental administration and also there, just on a few places. For example if I try to change the authorization settings of any service application there is a different people picker pop up than, for example, if I want to change the admins of any service application. Any other people picker window outside the central administration works for me.

So it seems to me, that there are two different types of people picker windows. The one which is not working for me also appears in the metadata service application which is the reason I can't use this service as I'm not able to define an owner of a keyset.

In the error log I found the following message: "Ausnahme im Forderungsanbieter 'Alle Benutzer' beim Aufruf von 'SPClaimProvider.FillHierarchy()': Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt." It basically says: An exception occurred in 'all users' claim provider when calling 'SPClaimProvider.FillHierarchy()': The object reference was not set on a object instance. This is event-id 8307 which you can find here: http://technet.microsoft.com/en-us/library/ff535778.aspx

Strange issue with a huge SSRS 2008 report (supposed to return 3 million records)



NOTE: The strange part (as mentioned in title) will come in the end.

I am running a SSRS 2008 report which fetches 3 million records from a remote server. After around 1 hour the report processing stops and I see an error icon on the lft bottom of the browser window. When I click on that to see the error details it shows some PageRequestManagerSQLErrorException with an unknown error message with code 12029 (sometimes 12002).

When I see the reportserver logs there is an error message logged in it which says "Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached."

The <DatabaseQueryTimeOut> value in the report server configuration file is already having a value set to 7200 seconds(2 hours).<

Read only error trying to copy and paste rows of records from Excel spreadsheet


I'm using Sharepoint 2010 and trying to open a list in Edit in Datasheet View. The list opens but when I try to paste lines into the sheet I get a Read-only error and it doesn't accept my paste. I removed all required fields.

I'm using XP and IE8

This function was working in SharePOint 2007.


WCF async call return a 10054 error: "An existing connection was forcibly closed by the remote host"



I have a WCF consumed by a Windows Mobile 5.0 or above application with .NET Compact Framework 2.0 SP2 and C#.

When the application consume a asynchronous "method" it throws that message. This is also the stackTrace of inner exception:

at System.Net.Sockets.Socket.ReceiveNoCheck()

OCIEnvCreate failed with return code -1 but error message text was not available.



We have a requirement to connect to oracle from ASP.NET and we installed ODAC(ODAC1110621) components  on the server but still we are getting the following error while opening the connection. Please find the details below:-

OCIEnvCreate failed with return code -1 but error message text was not available.

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.Exception: OCIEnvCreate failed with return code -1 but error message text was not available.

Source Error:

Line 16:     {
Line 17:         OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["HRDBA"].ConnectionString);
Line 18:         con.Open();
Line 19:         Label1.Text = con.State.ToString();
Line 20:         con.Close();

Source File: \\hs003fs01-02.homebase.com\wfmp\Webpage1.aspx.cs    Line: 18

Stack Trace:

XML Value Method to return multiple records


I am new in XML SQl i have created a schema with namespace in SQL 2005; I can return single records with the following query using table and column name; however, i do not understand how to return multiple (nodes) records from xml segments where there are multiple (same) elements.

Here is the query which works with one record, when trying to declare the Namespace to use nodes get error

The schema is an HIPAA 837 schema with multiple elements ; Table name = PATIENTTRAC_SUBMISSION_HISTORY; xml field = PATIENTTRAC_EDI_SUBMISSION




Error 504 - ReadResponse() failed: The server did not return a response for this request.


When I call this method from my wcf serivce :

        public override Restaurant[] GetAll()
            return (from c in GetContext().Restaurants orderby c.Name select c).ToArray();

I get following inner exception :

 {System.Net.WebException: The remote server returned an error: NotFound. ---> System.Net.WebException: The remote server returned an error: NotFound.
   at System.Net.Browser.BrowserHttpWebRequest.InternalEndGetResponse(IAsyncResult asyncResult)
   at System.Net.Browser.BrowserHttpWebRequest.<>c__DisplayClass5.<EndGetResponse>b__4(Object sendState)
   at System.Net.Browser.AsyncHelper.<>c__DisplayClass2.<BeginOnUI>b__0(Object sendState)
   --- End of inner exception stack trace ---
   at System.Net.Browser.AsyncHelper.BeginOnUI(SendOrPostCallback beginMethod, Object state)
   at System.Net.Browser.BrowserHttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
   at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelAsyncRequest.CompleteGetResponse(IAsyncResult result)}



Error on getting return value from Oracle procedure in SSIS package

In my case, I want to execute a stored procedure in Oracle database using SSIS package and if there has error in Oracle, I want to get the error message from Oracle and pass it to SSIS to log the error.

I can execute the stored procedure through SSIS successfully, but the problem is I cannot get the error message using OUT parameter of procedure in Oracle, the following part is what I have done to test whether SSIS can fulfill my requirement:

1. Oracle client installed, use Microsoft OLE DB Provider for Oracle, Test Connection to Oracle succeeded.

2. The stored procedure created in Oracle database:
    create or replace p1(in1 varchar2, out1 OUT varchar2) as begin
        out1 := 'nice';

3. In the SSIS package, I add a "Execute SQL Task" component:

Variable defined = User::output (String datatype)
ResultSet = Single Row
SQLSourceType = Direct Input
SQLStatement =

        in1 varchar2(10);
        out1 varchar2(10);
        in1 := 'wow';
        out1 := '1a2';
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend