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

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

Stored procedure giving strange error

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server

I have as stored procedure which executes a dynamic SQL  at the end. The dynamic SQL inserts into a temporary table declared at the begining of the procedure. When i execute the proc, it executes for a while and then gives me a truncation error.

When i test the code witht out the proc, it works fine and come back with the result set. I am not able to figure out what culd be the issue.

Kindly assist me in this..I have tried setting all colum lenghts to very high values then required but still it does not work when i execute the proc.

View Complete Post

More Related Resource Links

Error Executing CLR Stored Procedure "Item has already been added. Key in dictionary"

Hello, I'm consuming a web service through assemblies in a Sql Server 2005 database. The client was made with VB.net 2005. Everything was fine in the deploy phase but once I compile the assembly generated in my sql server database and try to execute the CLR stored procedure defined in the assembly it crashes throwing the following error: Error: There was an error generating the XML document. Inner Exception: Item has already been added. Key in dictionary: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Key being added: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Where SqlCifin is the name of the assembly, InfoComercial is the web reference namespace and ParametrosConsultaDTO is a complex type defined in the WSDL to encapsulate the request parameters. I tried almost everything but nothing seems to work: Already checked the enviroment variables and . I would appreciate any help you can provide me.  PD: I'm using WSE 3.0. Thanks, Andres Diab.

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

Validation.Error giving strange behavior

I'm seeing some weird behavior with the Validation.Error attached event. I have a textbox bound to an object that implements the IDataErrorInfo interface. The textbox subscribes to the Validation.Error event. What is weird is that when you put the text box into an invalid state, the Validation.Error event gets triggered twice. Under .NET 3.5, the first time ValidationErrorEventArgs.Action == Removed. The second time ValidationErrorEventArgs.Action == Added. Under .NET 4, it's reversed. The first time ValidationErrorEventArgs.Action == Added. The second time ValidationErrorEventArgs.Action == Removed. Why would it be called twice and why would it work differently under .NET 3.5 and .NET 4? I'm trying to implement a validation summary control similar to the one from ASP.NET. There is a solution here: http://codeblitz.wordpress.com/2009/05/12/wpf-validation-summary-control/ but it doesn't work under .NET 4 because in order for it work work, when you put a control into an invalid state, the last call to the Validation.Error event has to be with ValidationErrorEventArgs.Action == Added. Here is my code:   <Window x:Class="WpfApplication1.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:local="clr-namespace:WpfApplicatio

Error when trying to create a stored procedure

Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet

error in passing xml to stored procedure


I have below function that return the error below.


System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: ARITHABORT. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.


public List<OrderLineEntity> wsGetShipmentInfoByBulk(string groupStatus, int? warehouseID,
                    int? searchType, DataSet SearchDetail)
            System.Xml.XmlDocument XmlDoc = new System.Xml.XmlDocument();
            System.Xml.XmlNode XmlNodeParent = XmlDoc.CreateNode(System.Xml.XmlNodeType.Element, "root", "");

            foreach (DataRow dr in SearchDetail.Tables[0].Rows)

Error while executing SSIS package from a Stored Procedure


I am getting an error while executing a SSIS Package from SQL Server Stored Procedure.  But when I run it in BIDS, it executes successfully. Any help on this ???

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured.  Error Code: 0x80040ED. An OLE DB record is available. Source "Microsoft OLE DB Provider for Oracle" Hresult 0x80040eD Description: "ORA-01017: invalid username/password; logon denied"

How can I catch an error from a SqlDataSource stored procedure


I have a stored procedure that creates a dynamic query.  Sometimes the query is not built because there are no elements in the dynamic query.  When the aspx page loads the SqlDataSource throws an error that the query is malformed.  How can I catch this error, and handle it with a default message, before it returns the error to the aspx page?

finding error while excuting stored procedure


Hi friends,

  I have created stored procedure in sql server 2005 and connected that database to asp.net application

when i submit a data from application form corresponding stored procedure

stored procedure(sp1) giving following error

Could not find stored procedure 'sp1'.

I have written following code on buttonclick event

please provide me a solution and point out the error in code if exists

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
    DataSet ds = new DataSet();
    SqlConnection con;
    //Here we declare the parameter which we have to use in our application
    SqlCommand cmd = new SqlCommand();
    SqlParameter sp12 = new SqlParameter();
    SqlParameter sp2 = new SqlParameter();

CLR Stored Procedure error:There was an error generating the XML document


I am using a CLR stored procedure to call a webservice. I have marked the project as  strongly named assembly and also created a serialization assembly using Generate Serialization assembly in VS 2008. It created both files which I copied on to the database server. I have created assemblies of both






CREATE PROCEDURE TestList(@date datetime) AS EXTERNAL NAME XXXCLRProcs.[XXXCLRProcs.CLRStoredProcedure].TestList



When I tried to execute stored procedure with @date = N'2000-1-1' I get following error:


A .NET Framework error occurred during execution of user-defined routine or aggregate "TestList": 

System.Exception: Failed to send outbound message There was an error generating the XML document.


   at XXXCLRProcs.CLRStoredProcedure.ConsumeTestList(DataTable dt)

   at XXXCLRProcs.CLRStoredProcedure.TestList(DateTime date)



Here is the code:


   public static void TestList(DateTime date)

custom stored procedure error


We have been presenting an issue randomly since we changed our replication to use custom stored procedures for replication.

We have several offices which replicate to our central datacenter, each of this offices have different data corresponding only to their site and we centralize the data from all databases in only one database.

We use custom stored procedures for some tables only for the insert method, but randomly the update method of that table drops and I have to create again. We have been set the replication by using the 'replication support only' method so it only creates the replications sp. All our articles have the create update, insert and delete stored procedures for replication.

We have done traces for this and the replications is generating the drop but it doesn't recreate the procedure. It only occurs for the articles with custom procs for insert.  

The snapshot jobs for each office are disabled.

error in pivot stored procedure




                    Please check my pivot stored procedure which i am getting error.I think there is a mistake in quotations.I am unable to figure it out.

I have a column name in the table but still getting errors

Invalid column name 'Totalhou.rsInvalid column name 'EmpNo'.


PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME

GridView delete using stored procedure problem (strange)


I'm getting an error and I can't understand why is this happening...
I have an stored procedure which receives a parameter to delete the database row corresponding to that key-parameter. In the GridView_RowCommand I'm setting up the datasource in the e.CommandName == "Delete" option     

this.SqlDataSourceGridView.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure;
this.SqlDataSourceGridView.DeleteCommand = "spDeleteGridView";

Then in the GridView_RowDeleting event, I'm preparing datasourceDeleteParameters with the Key that I want to delete in my database and call the         

this.SqlDataSourceGridView.Delete ( );

The row is deleted in the database but, and here is the problem, there is something that before it got error and throws me that:

El procedure or funcion 'spDeleteGridView' waits the parameter '@PARAM', that what not expected.

I must add the information that debugging GridView_RowDeleting it ends normally and calls the GridView_RowDeleted...

Any help??

Thank you!!!

Error passing parameters from C# to Stored Procedure



I have a Stored Procedure that receive a parameter.
- SP name: hr_all_markets
- SP parametter: @cs_rep (type varchar 50)
In my C# code I already called this SP without parameters and works fine, but when I try to run with parameters I have this error:
- Incorrect syntax near 'hr_all_markets'.

Stored Procedure Control Error


I am wirting a stored procedure, which basically just has some SQL Statements. In one line I am Casting to XML and inserting. Everything works good, but if an un formatted XML comes in the whole SP fails. I want to know a mechanism where by I can control this so what ever records are well formatted should pass through, and the ones that fail should be recorded in some other table something like Error table to just insert ID of the records that failed. Any suggestions. So end result is that SP should run successfully for well formatted XML documents and record ID in some other table for Failed ones.

Thanks Jess

Database Tuning Advisor, Stored Procedure with datetime parameters, syntax error


I receive these error(s) in the Tuning Log. I have several stored procedures and they all use a datetime parameter.

E000    exec <some sproc name> ''2009-01-01 00:00:00:000'',''2010-09-30 00:00:00:000'',N'',N'',NULL,NULL,NULL,NULL,NULL,NULL         1    [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009'.

I am using SQL 2005. Any suggestions?

Thanks, Reece

Capturing the row table that has generated an error in a stored procedure



I have implemented a stored procedure in SQL Server 2005. I need to capture the possible errors. I have written an Insert To ... Select statement, so I read a row block from a Table1 to insert it into a Table2. But I could have an error, f.e. a conversion error. I want to capture the row of the block that has generated the error for the insert. Is it possible? How?

Any helps for me, please? Many thanks

Unexpected Error when running large stored procedure report in SharePoint


I have a report which uses a stored procedure to retreive a resource intensive dataset from a Data Warehouse.  With a small scope it renders in SharePoint no problem, but when a larger scope is specified ShrePoint throws this non-descript error. 

I can execute the procedure in SQL Server Management Studio, and though it takes 2+ minutes with the larger scope (30 sec with smaller) it does return the data.  Similarly, in Business Intelligence Development Studio it returns the rendered report with data after 2-3 minutes.

Only in SharePoint and only with a larger dataset does this occur.  Timeout is set at 30 minutes and it occurs long before then.  SSRS server has over 12 gigs memory, running SQL Server 2008, SharePoint 2007 is in integrated mode. No errors in SharePoint server or SSRS server logs.  Ideas?

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