Batch Updates With SQL CLR

Jul 31, 2007

Unfortunately batch updates (i.e. setting SqlDataAdapter.UpdateBatchSize to >1) are not possible with ADO.NET using a context connection. Does anyone know why it's not possible? Will it be implemented or allowed in a future version of .NET?

Here's what I'm currently doing:
I have a C# stored procedure, using a context connection, that updates all the records in a table (typically a million records) by repeatedly:
- sequentially reading a group of records (1000 at a time) into a SqlDataAdapter (using a 'select top row_number' -type statement that prevents re-querying processed records);
- performing complex processing on each record and writing the results back to the adapter (several fields of each record are updated);
- updating the table when each group has been processed.

This works, but it's not quite as fast as I'd have hoped for and is only slightly slower if using a non-context connection from an external application. I'd like to enable batch updating to increase performance (I actually get much faster performance from an external application that batch updates using a non-context connection, say 20% faster!)... but of course I can't with SQL CLR.

Any ideas on how I can improve performance of my updating?

Thanks in advance,

Graham

View 1 Replies


ADVERTISEMENT

Batch Updates And Triggers

Nov 14, 2007

Hello All

I am running a statment that updates 248 records in a table. That table has a trigger on update.

The First thing that the trigger does is to declare a number of variables and then set them using the values from the inserted and deleted temp tables.

This works fine when you are updating one row at a time but when you run a batch update it errors.

I believe this is because i am assuming that in a batch update a single row will be updated and then the trigger fired, then the next row and so on.

I now know that this is not the case. Is there a way of linking the 3 variables (see Below) so they all return information form a single row.

Declare@ApplicantId Int,
@NewStatus Int,
@OldStatus Int

Set @ApplicantId = (Select ApplicantID from Inserted)
Set @NewStatus = (Select StatusID from Inserted)
Set @OldStatus = (Select StatusID from Deleted)

Hope that makes sense

Thanks for any help.

Steve

View 2 Replies View Related

Logging Batch Updates Without Using A Trigger Or A Cursor

May 5, 2008

Hi,

Does anyone know if there's a way to log batch updates done using SQL queries without using a trigger or a cursor?

Thanks in advance,
Vinod

View 6 Replies View Related

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data

Dec 19, 2007

I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:




Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

View 4 Replies View Related

Passing Parameters To Batch File And Executing Batch File Loop

Aug 7, 2007

HELP,

I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.

Please help

Phil

View 4 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

SQLCMD Batch File With Script In Batch File

Dec 5, 2006

I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:

sqlcmd -S (local)SQLExpress -i C:CreateDB.sql

This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.

Thanks,

Dave

View 1 Replies View Related

1 To Many Updates

Feb 5, 2004

I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).

The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.

I have a number of questions:

How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?

Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.

What is being passed? Can you pass an array or something to a stored procedure?

View 4 Replies View Related

Using In For Updates

Jun 19, 2007

As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...

View 3 Replies View Related

Run 3 Updates In One?

Sep 20, 2014

I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.

UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer]
WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));

View 3 Replies View Related

37 Updates In One

May 10, 2007

okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?

here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)


update t1 set books_music='' from mailorder t1 where books_music is null
update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null
update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null
update t1 set Computer='' from mailorder t1 where Computer is null
update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null




in FoxSlow (foxpro) i could just do something like this:


do while !eof()
replace books_music with '' for books_music=null
replace car_buff with '' for car_buff=null
skip
enddo

View 9 Replies View Related

MS Updates?

Feb 17, 2008

I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?

View 3 Replies View Related

2 Updates In The Same Querry!

Oct 29, 2007

Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes':
1)  "Update tblDept SET IsTop ='No'
2) "Update tblDept  SET IsTop = 'Yes'  WHERE id = 200 "
Cheers!

View 4 Replies View Related

Need Advice On Updates

Mar 15, 2008

Hi, I just started learning ASP.NET this week and have watched a mountain of videos from this website which has helped me alot However I have been stuck on a problem for 2 days now. I have created an SQL database with the Following 2 tables: USERS                        COMPUTERSUserid                           Computerid   firstname                       Useridsecondname                  Manufacturer                                    Model I have made a relationship between the 2 tables. I then created a dataset with the following query:SELECT COMPUTERS.Computer_ID, COMPUTERS.Manufacturer, COMPUTERS.Model, USERS.First_Name, USERS.Last_Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID I however do not get the option in my grid view when i output this data, to UPDATE. The best i have found from google is that i need to use subqueries and not innerjoins but i just cant seem to get my head around them, please help as i feel my head may just explode if i think about this or try any more ways to get this to work :D 

View 5 Replies View Related

Msde Updates

Dec 16, 2003

I am using MSDE Release A was wondering what is the best way to find out when there are critical updates for MSDE. Is there a notification service for MSDE like there is for Windows?

View 1 Replies View Related

Looping With Updates

Jul 11, 2000

I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:

SELECT item_no
FROM Items

BEGIN

SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty
WHERE item_no=@item_no

UPDATE Items
SET Sales=@Total_Sales,
Qty=@Total_Qty
WHERE item_no=@item_no

END

I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?

Thanks for the help,
Chris

View 2 Replies View Related

Updates, Inserts

Jan 22, 2001

I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent

View 2 Replies View Related

Failed Updates - Help

Sep 29, 2000

Hi
I am using SQL Server 6.5 as my backend for a VB6 Application.
This is a OLTP kind of package that I have developed with about 600 Users.
I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application.
My database has grown considerably and all of a sudden, all
the CommitTran failed. None of the records were saved.

When I stopped and restarted my SQL Server, the operation became
normal all Update/Insert queries for the same Application succeeded.
(Note that I have not stopped my Server for the last 45 days).

WHY DOES THIS HAPPEN?
HOW DO I AVOID THIS IN FUTURE...
PLEASE HELP
Thanks
Jivee

View 1 Replies View Related

Easy Updates

Apr 5, 1999

Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.

View 3 Replies View Related

SQL Queries And Updates

Nov 6, 2006

How would I check a db to see if a record exists and if it does then do an update but if it doesn't do an insert. so: Set oRS = objDB.Execute("select loginid from computers where loginid='" & WshNetwork.Computername & WshNetwork.UserName & "'")

If that returns something then do an update but if not then do the insert.

View 2 Replies View Related

User Updates

Jun 18, 2004

Hi all,

I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.

Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.

Thanks,

Monk

View 1 Replies View Related

Automatic Updates

Jun 23, 2004

I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestions

View 14 Replies View Related

Automatic Updates

Jun 25, 2004

well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(

View 4 Replies View Related

Multiple Row Updates

May 25, 2004

Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?

Thanks you
Jeff

View 2 Replies View Related

Help Complicated Updates

Jun 16, 2007

Hello guys,
I am askng for any help ...am trying to get this SQL language.

I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100.
--------------------------------------------------------------------
There are three tables in the database: Customer, Product and Orders.

The Customer table has three column: Customer_id (PK), priority, Address.

The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)

The Product table has three columns as well: Product_id(PK), Product_name, Importance.

So the order table is connected to both the product and the customers table by respective foreign key.

View 2 Replies View Related

(newbie) Please May I Get Some Help With Updates

Jun 16, 2007

hello guys,

i want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. Thank you in advance.
Shcema----------------------------------------------------------------
There are three tables in the database: Customer, Product and Orders.

The Customer table has three column: Customer_id (PK), priority, Address.

The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)

The Product table has three columns as well: Product_id(PK), Product_name, Importance.

So the order table is connected to both the product and the customers table by respective foreign key.

View 2 Replies View Related

Handling 2 Way Updates

Oct 14, 2007

Hi all, working as an intern at MS for a year (SDET) and I've been asked to find a way of transfering items from one sharepoint lists to another (intranet to extranet) without access to the underlying SQL servers'. So what I've done is use an access databaes as a bridge creating active views of the two sharepoint lists. I wrote a C# console application that executed some SQL on the bridge to copy data between the two sharepoint lists, this was great could handle updates, deletions and additions of new items. Now I've been asked to make this update process 2 way on a certain field (status) I've been trying for a couple of days now and I'm having no joy. The method I was using (polling) allowed for the first couple of status changes (used 2 statements) but then wrote back the wrong way see below for example SQL:

Extranet owned statuses: Declined, Approved, Not Started, In-Progress, Completed.
Intranet owned statuses: Needs Review, MS Declined

UPDATE intranet JOIN extranet ON (blahblah=blahblah)SEt intranet.status=extranet.status WHERE extranet.status IN (all extranet owned statuses);

UPDATE extranet JOIN intranet ON (blahblah=blahblah)SEt extranet.status=intranet.status WHERE intranet.status IN (all intranet owned statuses);

Can anybody tell me where I'm going wrong and offer any suggestions?

Thanks,
Alex

View 1 Replies View Related

Sql Updates Using 2 Different Databases

Mar 4, 2008

I have 2 databases with the same tables, etc. I'm trying to copy one column from the same table from db1 to db2. Here is my sql:

update db1.dbo.table1 set db1.dbo.table1.memtype = db2.dbo.table1.memtype where db1.dbo.table1.memid = db2.dbo.table1.memid

When I parse it, it completes successfully but when I execute it, I get this error message:
The multi-part identifier "db2.dbo.table1.memid" could not be bound.

View 2 Replies View Related

Updates All 0's To NULL

Mar 20, 2008

Can someone help me with the following? I need to update all columns in a table where the value is zero with NULL where the row id's (primary keys) are between two values. I was hoping that I could do this without "hard coding" the column names as there are many columns. i.e:

UPDATE TABLENAME
FOR ALL COLUMNS
SET VALUE = NULL WHERE VALUE = 0
FOR ROWS BETWEEN n and m

Thanks!

View 1 Replies View Related

Updates At The Subscriber...

Jan 31, 2007

Hi,

             Can we use transactional replication with updatable subscriptions when we have more updates and transactional changes at the Subscriber side??

            We are using SQL Server 2005 SP1. We will be using 6 publications and 4 subscriptions for each publications, so totally 24 subscriptions. Both the Publisher and the Distributor is the same server. It is set to run in continuous mode. Type : Pull Subscriptions.

 

Regards,

Swapna.B.

View 6 Replies View Related

SQL CE Updates Not Persisting

Jun 7, 2007

I have a pocket pc app that is using a sql ce (.sdf) database. The inserts, updates and deletes are not persisting when I exit debug. It seems like it's getting put in the database because I bind drop down lists after the inserts, updates, etc and the new/changed data shows correctly after rebinding the data (when I repopulate the drop downs I make a call to the database, I don't just manually add the new item to the drop down so it seems like the changes are getting to the database). However, once I stop debugging and restart debugging all my changes are gone. I'm not using transactions but is there something that I need to do to commit the db changes?



Thanks

View 3 Replies View Related

Sp_configure 'allow Updates'

Aug 22, 2006

I updated my default (only) instance of sql2k to 2005. I'm now working on fixing backward compatibility issues, among these sp_configure 'allow updates'.

According to the BOL, reconfigure will throw an error if 'allow updates' is set to 1. And updates to system tables is disallowed entirely. However, when I started my work on a script that does these things, it ran without error on the newly updated server.

This code works, and if I understand the BOL correctly, it should not:
sp_configure "Allow Updates", 1
GO
reconfigure with override <-- works without complaint
GO
update sysusers set name='283c' where uid=31 <-- works without complaint
update sysusers set name='283c' where uid=31 <-- works without complaint
go
sp_configure "Allow Updates", 0
GO
reconfigure
GO

Did I miss something in my upgrade? I used the wizard. The management tool reports my version as 8.0.760. This database was present during the upgrade. I have not restored it from backup.

View 1 Replies View Related

Batch Job

Dec 26, 2006

Hi,
I want to schedule a daily job using sql server to update the info. in a sql server table. This is very new to me. Could you please forward me some helpful resources.
Thanks,

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved