.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

Error in executing a stored proc

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
Hi Team, I have problem in executing a stroed proc SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec PBT_Broker_Report 'PBTBFDAGT ' Alter PROCEDURE PBT_Broker_Report  -- Add the parameters for the stored procedure here @BrokerID nchar(10)  AS BEGIN SET NOCOUNT ON; declare @sql nvarchar(3000) ;   IF EXISTS (    SELECT *    FROM information_schema.tables    WHERE Table_Name='Test_Broker'    )    BEGIN    DROP TABLE Test_Broker    END     set @sql = (case @BrokerID     when 'All' then    N'    SELECT PO.COMPANY_CODE,PO.POLICY_NUMBER, PO.primary_insured    INTO Test_Broker   from dbo.POLICY po   where PO.AG_AGENT_NUMBER <> 'PBT001'   '     else        N'   SELECT PO.COMPANY_CODE,PO.POLICY_NUMBER, PO.primary_insured,   INTO Test_Broker   from dbo.POLICY po   where    PO.AG_AGENT_NUMBER = @BrokerID   AND AG_AGENT_NUMBER <> 'PBT001'  '   end ) --print @sql execute sp_executesql @sql     Error: Must declare the variable '@BrokerID' Please suggest me some solution

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.

Testing Foregin Key without error out of stored proc

I have a Customer table. Several dirrect mapping tables link to this record. In my stored procedure I would like to try and delete this Customer record and if any keys are bound to it, then catch that error without breaking the stored proc ( try - catch ) Is this possible in a stored proc? I would like to just use a print statement that the record couldnt be deleted. Thanks, Nick

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"

Extremely weird error (stored proc)


Hi guys


We're getting a very weird error:

The stored procedure '***********' doesn't exist.

The solution to this may seem obvious but it's not. The website runs fine but with use and some traffic it starts popping up with this error on all pages accessing the database. While it's doing this, the stored proc(s) are there and they run fine via managment studio. Restart the website in IIS and it's fine again.

I have run a trace while this is happening and funny enough the specific proc doesn't show up in the trace at all.

This is happening on our dev and qa environments. Both running SQL2008 and IIS7.


Anyone else experienced this and have a solution for me please?



Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

how to write a stored proc to prevent multiple users in uploading the reports at a time? plsss help



I wanted to know how to prevent more than one user from uploading the report at a time .I am new to .net ,plss help with the code that would be great.Details are below:

This is a Windos based app written in c# . When User A  clicks Upload option on  one server  from the menu  to upload the files  and at the same time when User B clicks Upload option on different server , User B should be alerted a message saying "User A's uploading is in progress,pls wait" . How to achieve this, plsss help with the code.. I am thinking this logic should be kept in a stored proc, How do I write that proc? plss help


Thanks in advance!

Populating a list box in InfoPath from a sql stored proc


I have an InfoPath web enabled form that gets published to a SharePoint site.  I need to add a list box to the form that gets populated with data returned from an existing call to a SQL Stored Proc.  

As the form works now, the user types in a patient id #, then clicks a button that calls the Stored Proc and returns any patients that match the given patient ID.  This part works.  

My task is to add a list box so that if more than one patient is returned , display the list box with all of these patients populated in it.

I am hoping to do this within the confines of the existing code.  The existing code looks like this:

private bool LoadPatientInformationFromDB(string patientId, string patientFirstName,
      string patientLastName, string patientMiddleInitial, string birthDate, string gender,
        string admitDate, string location)
      bool completed = false;
      //Load from DB
      SqlConnection conn = new SqlConnection(GetOrderSetDBConnectionString()); 
      //Create Form Builder
      formBuilder = new FormBuilder();
      SqlDataAdapter adpt = null;
        if (!String.IsNullOrEmpty(patientId) || !String.IsNullOrEmpty(patientFirstName) ||
          !String.IsNullOrEmpty(patientLastName) || !String.IsNullOrEmpty(patientMiddleInitial)
          || !St

Using the BDC API how to catch the returned code when executing a stored procedure?

using BDC API, how can i catch the return code a stored procedure returns?

I tried Entity.Execute to execute a generic method. However, it returns an empty Entity table.

I know you can do it by setting up an output variable. I prefer returning an integer for tracking errors.

Any ideas?



Error on executing VB file : The assembly does not allow partially trusted callers

Dear All ! Though there are many posts stated with solutions, im still facing the error in my application. I have created a VB Class Library project in VS 2008 which on execution in another application (ERP) will open a VB form holding a report. Its working fine in Server machine. The report is drawn from a DB placed on another server. Everything works fine in Server. But in another machine its not.  I did opened the project from the local machine, Build it so tht the dll be caught in the ERP application and so it did, iam able to see the dll within the application. but on execution its throwing the error 'The Call to member failed. The assembly does not allow partially trusted callers ' What should i do in order to execute the project in all local machines application ??   Thanks in Advance... waiting for a solution.. Radhai.  

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

Sending Email by exeucting a stored proc on SQL Server that references CLR

Hi Guys, I want to modify the data type of the @body variable in the stored procedure that sends an email. The stored procedure is as follows:   CREATE PROCEDURE [dbo].[spSendMail4]<br/> @recipients [nvarchar](4000),<br/> @cc [nvarchar](4000),<br/> @subject [nvarchar](4000),<br/> @from [nvarchar](4000), @body [nvarchar](4000), @attachment [nvarchar](4000)<br/> WITH EXECUTE AS CALLER<br/> AS <br/> EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail]<br/> GO   I want to change the @body variable to data type: text. The reason for this, is because I am sending an HTML email whose content (characters) exceed 8000. Basically the new stored procedure should looks like below:   CREATE PROCEDURE [dbo].[spSendMail4] @recipients [nvarchar](4000), @cc [nvarchar](4000), @subject [nvarchar](4000), @from [nvarchar](4000), @body text, @attachment [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail] GO   If attempt to make this change, I get the below error:   CREATE PROCEDURE for "spSendMail" failed because T-SQL and CLR types for parameter "@body" do not match.   Understandably so, because from the article http://msdn.microsoft.com/en-us/library/ms131092%28SQL.100%29.aspx CLR data type (.NET Framework) is not compa

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?

Executing a job from a stored procedure stopped working

I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:   CREATE PROCEDURE xxxxx WITH EXECUTE AS 'domain\username'   AS   EXEC msdb.dbo.sp_start_job B'jobname' ;   RETURN   The domain\username is the in the database sysadmin role and the owner of the job.  To make this work originally, I had to change the msdb database to be trusted.   This worked for the past several months.   Now it doesn't work (perhaps after a reboot but not sure).  The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'   I looked to make sure that the account had grant execute rights and it does.  I tried setting it via GRANT statement and it was granted successfully yet the error still occurs.  I've tried changing accounts and anything else I can think of to no avail.   Any ideas how to troubleshoot this issue.  I've tried all the tricks I can think of.   Thanks - SM

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

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

"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.  

How to pass the file path in EXEC of Stored Proc for getting IIS Logfile data

Hello,                  I need to get IIS Logfile data from Stored Proc. How to do this. I am using Bulk Insert Method in my stored Proc When I execute the stored proc I am getting an error that  Incorrect syntax near 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log'.     Below is the code.                 Create  PROCEDURE  spWebLog @path varchar(300) AS begin exec('BULK INSERT Weblog from''' + @path + ''')  end go Declare   @SQL Varchar(100 ) Set    @SQL= 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log' exec   spWebLog @SQL    

Reading data to a table from a stored proc

This has me brain locked.  1. I've got a stored procedure.  2. It does a select with a couple joins.  I want to take the results of that stored proc and read them into some a table -- temp table would be ideal.  here's my (really tough) constraints... these really complicate things: 1. the stored proc uses dynamic SQL. As a consequence, I don't know the columns it's going to return (it's kind of the whole point of using it).  2. Since the stored proc uses dynamic SQL, it needs to use sp_executesql internally.    What won't work(i don't think?): 1. I can't create a function that returns a table and does a "return select * from....", since you can't (I don't think?) call a stored a stored proc in the return statement of a function 2. I can't just do an "Insert into #xyz exec .." because I can't pre-define the temp table (I think this is needed?). 3. I don't have a linked server. and i'd rather not. All of this runs local in the same instance of SqlServer, and will have to handle loads of volume (each hit will return a few million rows).  So (I think?) OPENQUERY  is out.  4. OPENROWSET is a possibility, I suppose, but again, everything is running locally. I think that goes through OLEDB (?) and I'm not sure that's even an option.    THOUGHTS???  HELP?? is it even possible?? Pl
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