.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

CLR Stored Procedures with WSE - Error "Item has already been added. Key in dictionary"

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
Hi, I'm developing a CLR Stored Procedure that consumes a Third party Web Service with WSE 3.0 (Due to WS Owners). Everything was fine in the Visual Studio Framework, but once I deploy the assemblies in my database and try to call the service from a query windows, it shows an error message:  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' Stack Trace: at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id) at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle) at System.Web.Services.Protocols.SoapHttpClientProtocol.Serialize(SoapClientMessage message) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at SqlCifin.InfoComercial.InformacionComercialWSServiceWse.consultaXml(ParametrosConsultaDTO parametrosConsulta) at SqlCifin.StoredProcedures.sp_consultaXml(SqlString i_cod_info, SqlString i_motivo, SqlString i_ced_ruc, SqlString i_tipo_ced, SqlXml& o_xml)   I think it has to be something with the WSE but not completely sure. I really need to go t

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.

CLR Trigger with stored procedures - transaction error

Hi, I've developed sample CLR trigger which call stored procedure but if execution of stored procedure fail I got following exception The context transaction which was active before entering user defined routine, trigger or aggregate "CskTest" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.  My trigger [SqlTrigger(Event = "FOR INSERT", Name = "CskTest", Target = "Requirement")] public static void HandleRequirement() { using ( SqlConnection conn = new SqlConnection("Context Connection=true") ) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Test"; conn.Open(); try { cmd.ExecuteNonQuery(); } catch ( Exception ex ) { SqlContext.Pipe.Send(ex.Message); } } } I wanna handle this exception in my catch block. How can I do this?

Error Checking Nested Stored Procedures

Does anyone have an idea of the best way to handle error checking with nested store procedures?  Here is the background.  I have a file being uploaded by the user via the web.  The file is converted to a DataTable then imported using SqlBulkCopy.  Once the data is in the database I have 3 separate stored procedure calls.  The first prepares the import data by making some minor correction and running logic to insure integrity.  The second call inserts the final data into a production table which keeps a history of all data.  Once in the final table a series of calculations and logic need to be run on the data.  The first two stored procedures are called individually from the front-end and end properly when a RAISERROR is triggered from the stored procedure.  However the third stored procedure calls 3 other stored procedures.  From what I gather online it appears that RAISERROR messages will be sent back as exceptions to the front-end if called but will not be sent back to calling stored procedures.  If this is true I would have to do even more error checking in the parent stored procedure.  Does anyone know a way to avoid this extra checking for a return type from each of the stored procedure calls in the parent?  Thanks

error message on mismatch in transaction count when nesting stored procedures


Is this a purely informational message that I can ignore?  (i.e. are there no other effects from this message? is this really an "error"?):
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I'd like to do my rollback in a called generic error logging sproc if I haven't already handled the rollback, in order to be sure not to leave any open transactions; so in this case, the exit of the calling procedure will have a different count (zero) than whatever it began with. 

Transact-SQL Built-In Stored Procedures

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email

SqlDataSource how to change the stored procedures at run time



Is it possible to change the select, insert and update stored procedure of an SqlDataSource at run time. I don't mean to change the definition of the stored procedure, but I mean to change the assignment, i.e. To change the select stored procedure from "selectSP1" to "selectSP2" at run time. Is it possible?



Test Run: Testing SQL Stored Procedures Using LINQ


Language Integrated Query makes lots of things easier. Here we put LINQ, or more specifically the LINQ to SQL provider, to use testing SQL stored procedures.

Dr. James McCaffrey

MSDN Magazine April 2008

Advanced T-SQL: Automate the Generation of Stored Procedures for Your Database


Design-time automation makes coding faster and ensures that all the procedures generated use the same naming conventions and structure. In an effort to improve their coding efficiency in a large SQL project, the authors wrote a set of design-time stored procedures that generate run-time stored procedures, and have used them in project after project ever since. Recently, the authors updated their procedures to make use of SQL Server 2000 features, including user-defined functions. This article covers the creation and execution of these dynamic T-SQL scripts to automate the coding of common database stored procedures.

Peter W. DeBetta and J. Byer Hill

MSDN Magazine April 2003

.NET Reflection: Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET M


One major inconvenience of using SQL stored procedures from code is having to match type information. You have to write long wrapper functions around the procedures just to expose the data types. In the .NET Framework, however, the reflection services of the System.Reflection namespace allow you to query rich metadata that describe data types. This article explains how to use reflection in .NET to end your stored procedure nightmares. The author creates four custom classes, including one that generates a SQLCommand object based on the metadata of a method. Using this library, you'll be on your way to automating the generation of stored procedures.

Atif Aziz

MSDN Magazine August 2002

SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web


Front-end developers frequently want to add functionality to the presentation tier of an n-tier architecture, but such requests can require changes on all tiers just to get the data and present it. This process can be made easier and more flexible by using SQL Server stored procedures to automate the delivery of data in XML format from the database to the front-end components. In the component presented here, stored procedures are invoked by XML strings, XML is returned, converted using XSL, and presented to the client in HTML. The technique supports rapid changes yet doesn't sacrifice the n-tier approach. This approach can be used with either SQL Server 7.0 or SQL Server 2000.

Dave Grundgeiger, Anson Goldade, and Varon Fugman

MSDN Magazine August 2001

EF 4: Function Import problem with Stored Procedures


I have the following SP which includes a temp table:

 DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (val varchar

Stored Procedures & SqlDataAdapter not updating a calendar control


Hi all,

I have a database linked to a calendar. I had a peice of code that retrieved dates from a database and populated the calendar. I wanted to tidy up my code using stored procedures (they seem neater & i assume there's a benefit somewhere). so i came up with this, however it's not working. The calendar displays, but it's not being populated with the database entries. The SqlCommand & SqlParameter I have working elsewhere in my code; as with the SqlDataAdapter (which also worked before i tried to conver to a stored procedure!) but combining these three (SqlCommand, SqlParameter & SqlDataAdapter) seem to be causing me problems.

It's probably something stupid but if someone could take a look and point me in the right direction, I'd be very grateful.


Public Sub dbConnection()
        dbConnString = ConfigurationManager.ConnectionStrings("connString").ConnectionString
        sqlConn = New SqlConnection(dbConnString)
    End Sub

Public Sub EquipmentDDLSelection()

        Dim dbCMD As New SqlCommand("PopulateCalendar", sqlConn)
        dbCMD.CommandType = CommandType.StoredProcedure

        Dim parameterBookedEvents As New SqlParameter("@bookedEvents", SqlDbType.VarChar, 50)

Can I use TCP sockets within managed funcs, triggers and stored procedures?

I have seen nothing prohibiting this, but have not yet had time to write some test code. Does anyone know for sure if SQL Server 2008 prohibits or allows TCP sockets for communications, inside a C# SQL/CLR function or method? I guess it must because this is implict when creating data connections and so on, which are obvioulsy allowed. Thanks Cap'n  

Do stored procedures run in separate processes ?

If a stored procedure is run multiple times calling the same method created inside an SQL server project, does each call run inside the same process. For example if the method you are calling accesses a static variable, would this variable be shared amongst all callers or will each caller always be allocated its own memory.

Replication stored procedures not being passed parameters- default appears instead

Hi I am setting up transactional replication from Oracle to SQL Server, it is working fine for 9 tables, but for one it fails on updating and deleting with a message like this: Procedure or function 'spu_VGSM_SUBMISSION_CONTACT' expects parameter '@pkc1', which was not supplied. This is correct, in that the sp should be being passed this parameter, but when I do a trace it will show something like: exec "spd_VGSM_SUBMISSION_CONTACT" '12-3456-10','BLOGGS','S', '2010-07-01 12:43:43', 'M123456' default In other words, the old primary key values which should be being passed in are being replaced with "default". I am using the CALL syntax for the update sp. If anyone can suggest why this is I would be very grateful. This is the publication: exec sp_addpublication @publication = N'_Submission_Contact', @description = N'Transactional publication from Oracle Publisher.', @sync_method = N'concurrent_c', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'
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