.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

IDENT_CURRENT not working in CLR Stored Proc

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
I'm doing an insert in a CLR stored procedure and I want to return the newly inserted row.  Using IDENT_CURRENT doesn't work, but using @@IDENTITY does.  I don't feel comfortable using @@IDENTITY as the semantics dictate that it may not return the intended row.  Is IDENT_CURRENT not supported in a CLR SP?
W. Brian

View Complete Post

More Related Resource Links

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

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

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

2005 proc not working in 2008

following proc works in 2005, and fails in 2008 returning "Msg 207, Level 16, State 1, Invalid column name 'xxx' " message.  Anyone have any ideas? The proc alters an existing proc in another db to return a different value: existing proc: /******************************************************* USE [tstDB] GO CREATE PROCEDURE [dbo].[sp_GetName] AS SELECT "aaa" AS strDescription GO /******************************************************* proc to alter return value: /******************************************************* USE [srcDB] GO Drop Procedure up_chg_name go Create Procedure up_chg_name (@chgName varchar(20),@db varchar(100)) AS Declare @ls_sql nvarchar(255) set @ls_sql='USE '+@db+' EXECUTE ('' ALTER PROCEDURE dbo.sp_GetName AS SELECT "'+@chgName+'" AS strName '')' exec(@ls_sql) GO /******************************************************* execute the altering proc: /******************************************************* USE [srcDB] GO exec up_chg_name @chgName=xxx',@db='tstDB' go /******************************************************* this fails with: "Msg 207, Level 16, State 1, Invalid column name 'xxx' message" - any ideas? Thanks  

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    

Error in executing a stored proc

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

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

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

SqlDataSource : in what order do parameters get fed to stored proc?

Hello, I'm wondering if someone can explain how a SqlDataSource determines the order in which parameters are fed to a stored procedure. I'm not able to find clear documentation.So suppose a proc has: CREATE PROCEDURE  updateOneCar@CarActiveFlagCheck int, @CommentsForCar varchar(1500) = NULL, @manufacturerID int...and then suppose the ASP.NET page has the manufacturerID supplied by a querystring, and has a FormView as follows: <asp:FormView ID="FormView1" runat="server" DataSourceID="SQL01"          DataKeyNames="manufacturerID" EditRowStyle-Wrap="False" RenderOuterTable="False">         <EditItemTemplate>         <div>             manufacturerID: <%# Eval("manufacturerID")%>             <br />             Is my Car on?: <%# Eval("carFlag")%>             <br />             Is my car Active Flag:             <asp:CheckBox ID="CarActiveFlagCheckBox" runat="server"              

Oracle stored proc optionally returning a ref cursor.

Hi, I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents. Now, we have done this with the following code:            OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");             command.Parameters.Add(ParameterFactory.CreateParameter("p_doc_id", RTSId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_rev_id", RevisionId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_c_item_id", ContentItemId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_error_code", OracleType.Number, ParameterDirection.Output));          &nbs

Can i write stored proc for the two queries, truncating and inserting two different tables?

Hi All, I have two queries below. trying to write stored proc so i can combine these queries so i can use only one execute sql task. both the queries are truncating and inserting records in two different tables. QUERY 1 à   truncate table [PlanFinder].[InvalidAwps] go   INSERT INTO [PlanFinder].[InvalidAwps]     (Ndc, AwpUnitCost)     SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost     FROM         PlanFinder.PlanFinder.HpmsFormulary P         LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices                     WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A     ON P.Ndc = A.Ndc      WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL)     AND p.Ndc IS NOT NULL --------------------------------------------------------- Query 2 à   truncate table [PlanFinder].[MissingNdcs] go   INSERT INTO [PlanFinder].[MissingNdcs]     (Ndc)     SELECT DISTINCT Ndc     FROM         PlanFinder.InvalidAwps       WHERE AwpUnitCost IS NULL  Thanks a

Converting a Stored Proc to a query

I have a stored procedure that I want to convert to a query that I can email to the DBAs to run.  The stored proc has 2 parameters (start and end date) as I need them to run the query in date chunks (i.e. 1/1/2010 - 1/31/2010) due to the size of the table. *** Please allow me to mark threads as answered and I will, Thank you ***

CLR stored procedures asssemblies working in sql server 2005 but giving issues over sql server 2008

Hi I developed CLR stored procedures assemblies using VS2005 and deployed in sql server 2005 are working fine. Currently we are migrating to sql server 2008. The same CLR assemblies are not working in 2008 version and raising following error. Here i created CLR assemblies in sql server 2008 using 'sa' user account which is having db_owner permission over 'mydatabase'. I am getting an error message like "A .NET Framework error occurred during execution of user-defined routine or aggregate". In sql logs showing messages like as -- Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework\v2.0.50727\. -- AppDomain 2 (mydatabase.dbo[runtime].1) created.   Please let me know what may be the cause for this type of issues. Is there any specific backward compatibility settings has to be done over sql server 2008 or sql server 2008 R2 while using 2005 CLR routines?   Thanks in advance.  

can i use stored proc to send email.

Hi how can i use stored proc for sending emails?

Had working Stored Procedure for years and broke all of sudden

I had a stored procedure that used a simple cursor to do a query against a linked SQL 2005 database.  It worked for years and failed out of the blue a month ago and the only way to fix it was to modify the cursor declaration to make it INSENSITIVE.  Any ideas why this would be?  No data is modified on the linked DB, just the local DB.  Seems weird this worked for years up until mid-august.  All SPs with cursors that were not local had to be modified to be INSENSITIVE.   Thanks Henry    MVP WanneBe
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