SQLDatareader via CLR not returning SQL Message right way

I execute the following code via CLR, is there a reason why the message is not printed to the SQL Server, does it need to wait until the Stored Procedure returns all the rows (there is about 7 Billion rows to return)

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spCommand_Select_Rows_For_Delete"; cmd.CommandTimeout = 41600; SqlDataReader reader = cmd.ExecuteReader(); try { string strSQL = ""; SqlContext.Pipe.Send(DateTime.Now.ToString() + " - Started working with ProductTable"); while (reader.Read()) { strSQL = "DELETE FROM ProductTable WHERE ProductId = " + reader["ProductId"].ToString(); SqlCommand cmdDelete = new SqlCommand(strSQL, conn); cmdDelete.Connection = conn; cmdDelete.CommandTimeout = 20800; cmdDelete.ExecuteNonQuery(); } SqlContext.Pipe.Send(DateTime.Now.ToString() + " - Completed working with ProductTable"); } finally { // Always call Close when done reading. reader.Close(); }

My Stored Procedure:

SELECT ProductId FROM ProductTable WHERE ProductInfoId IN ( SELECT ProductInfoId from DeletedProducts )

-------------Problems Reply------------

Here's how you delete 7 billion rows using a nice set based operation. You don't abuse iterate through a datareader in CLR.

SELECT 'Starting'
WHILE ROWCOUNT <> 0
DELETE TOP (1000000) P
FROM ProductTable P
WHERE EXISTS (
SELECT * from DeletedProducts DP WHERE P.ProductInfoId = DP.ProductInfoId
)

For more, see this question Bulk DELETE on SQL Server 2008

But to answer your question, yes, SQL Server will not PRINT (which is what you're doing) immediately

You could probably use SqlContext.Pipe.ExecuteAndSend with RAISERROR WITH NOWAIT to do what you want about the message.

But I'm with gbn's answer about not needing the CLR for batched deletes.

Category:.net Views:0 Time:2010-07-28

Related post

  • How to create CLR with return result 2010-06-29

    I have CLR which references Web service, into CLR i have one method with calling one of Web services method. What i need is, to make this CLR method return me information and show it in Result tab (in SQL server) . I printed result in SQL tab Message

  • Problems with CLR Code on Sql Server 2005 2009-03-02

    I am considering accessing some CLR code from a UDF in Sql Server 2005. I've heard stories that unhandled exceptions thrown in CLR code can bring down the server. Obviously my function would contain a try-catch block. However, certain exceptions (sta

  • other problem with sqldatareader when the condtion in sql command is arabic 2010-10-06

    There is a problem with sqldatareader when the condtion in sql command is arabic like this: select user_name from users where typ=N 'arabic text' This does not retrieve any data although there is a user name which has this type so can you help me ple

  • SQL Server 2008 CLR vs T-SQL: Is there an efficiency/speed difference? 2010-01-19

    I'm a C# developer who has done some basic database work in T-SQL. However, I need to write a very complicated stored procedure, well above my T-SQL knowledge. Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause

  • How to reference GAC assemblies when integrating a CLR extension into SQL Server 2010-02-21

    I've created an assembly for CLR integration in SQL Server 2008. It has one reference to System.Web.Extensions, which is an issue because when I try to add my assembly, I get the following error: Assembly 'system.web.extensions, version=3.5.0.0, cult

  • Can you call a CLR Injection into SQL Server from C++? 2010-06-11

    Does anybody know if you can you call a CLR Injection into SQL Server from C++? --------------Solutions------------- You need to compile your C++ project with /CLR option and use C++/CLI to invoke your CLR Injection code. Certainly that makes your C+

  • C# A validate method which can return multiple messages 2010-10-14

    I have a situation where I need to check a couple of things before I proceed with saving into the database. If I want to save an object to the database I have to check two things: Are all the required things filled? Is there already a thing with the

  • In a unix box, I am taking a list of files as input. If it is found, return the path otherwise return a message "filename file not found" 2010-11-19

    I have used the find command for this, but it doesnt return any message when a file is not found. And I want the search to be recursive and return a message "not found" when a file is not found. Here's the code I have done so far. Here "input.txt" co

  • Inno Setup GetExceptionMessage returns empty message 2011-05-13

    in Inno Setup script GetExceptionMessage returns empty message (it contains only colon ":" sign). The last version of Inno Setup (5.4.2) is used. try Log('Create IISNamespace'); // Create IIS namespace object if Length(virtualDirectoryName) > 0 th

  • Create process and run code after the process returns a message (C++) 2011-09-17

    I am trying to create a new process (which shouldn't block the current program) in C++, and to have the C++ listen for a message. When the message arrives, I want to run some more code. I have this method which executes the command and results the re

  • how to create soap server using php to return xml message of a structure as follows: 2011-12-05

    what's the best approach to create soap server using php either with zend or without to return xml message of a structure as follows: <soapenv:Body> <response> <element2> <element3> <element4> <element5> <elemen

  • asp.net mvc 3 return a message from controller action ajax 2011-12-20

    I have a question about Ajax form submit. Here is the code: Controller [HttpPost, ValidateAntiForgeryToken, ValidateInput(true)] public ActionResult Contact(ContactForm model) { if (!ModelState.IsValid) { return new EmptyResult(); } else { string mes

  • Using CLR UDTs as SQL Server stored procedure parameters 2012-01-17

    I've been doing some reading about CLR integration in SQL Server (I'm using 2008 R2, but I believe this is of little relevance to the question), and bumped into the subject of CLR UDTs. After some reading, I found that most people find them to be evi

  • Return error message when duplicate unique fields 2012-04-25

    I want to return error message when duplicate records occur, groupName is unique field in group table. I make like this, how can I make error handleing whithout using mysql-get-diagnostics because the server I am working on is version 5.0.77-log. DEL

  • SMTP server on my cell keeps returning the message "Account Verification Fail". 2012-07-26

    Hello, I updated my password and can connect to my hotmail account from all other devices, but the SMTP server on my cell keeps returning the message "Account Verification Fail". Any assistance would be appreciated. Thanks. --------------Solutions---

  • "Back to Messages/Return to Messages" 2012-09-13

    What is the best way to activate the old hotmail "back to messages" function? (Or was it, "return to messages"? I can't remember for sure. For example, after doing a search, or after opening and reading one email, I want to go back to the spot in my

  • Validation returned the message that it could not determine whether it was "genuine" or not. 2012-12-26

    Having issues with the license validation tool in Windows 7. I received a message to download and run a Validation tool on the assumption I was a thief. I followed the commands but Validation returned the message that it could not determine whether i

  • windows update agent returns a message "encountered an unrecoverable error" 2013-04-05

    Hi there,, Windows 7 starter 32 bit Acer D260 net book--windows update agent returns a message "encountered an unrecoverable error". I had Mcafee trial version before and later purchased a new Mcafee and used for one year. Just before the expiry I wa

  • return to messages 2013-08-13

    when viewing past history, hotmail had a "return to messages" button so you didn't have to return to the most recent page and start over, I haven't found this option on outlook is there one? --------------Solutions------------- Hi danreith, I'm happy

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.250 (s). 11 q(s)