Fetching an id to use in another task

Hi there,

i have a task that has a ole-db destination. it inserts some data to a table. it's always 1 record that is gonna be inserted.after that the task is finished. however i want to use that same record in the followup-task. is there a way to retrieve the id of that specific record.

Please note that the id of the record is an int-identity from sql-server itself.

at the moment i'm fiddling with "remembering" the inserted data and hopefully it will be a 100%match in the next task, but i have a dark feeling it's not 100%.

is there a common way how to approach this?

The simplest way I can see is to use a Execute SQL task in the control flow to retrieve it after the Dataflow that inserted the row is done.

sql

Fetch Question

I have a stored procedure that inserts one row into a table. From a
SELECT statement I would like to call the SP on each row in the
results. Is setting up a cursor and using fetch statements the best
way (or even the only way) to do this?Hi

It is probably the safest way to do it, but without knowing exactly what you
are trying to do it is hard to suggest alternatives.

John

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0310010701.261cbff2@.posting.google.c om...
> I have a stored procedure that inserts one row into a table. From a
> SELECT statement I would like to call the SP on each row in the
> results. Is setting up a cursor and using fetch statements the best
> way (or even the only way) to do this?

Faulty insert into sql express database

Hello All,

I have an InfoPath form that submits data to a .NET webservice. The webservice parses the infopath form and inserts the data into a database. The database is SQL Server EXPRESS.

I have however run into some problems. The InfoPath form is within SharePoint and I noticed that when I submit the data, it only shows up in the database as a new record 9 out of 10 times. That is for every 10 submissions of the form, there are only about 9 new records in the database. The ID field (an autoincrement field) also skips a number indicating the the transaction may have failed.

However, when I test the form on my local PC, it inserts a new record for every submission.

The webservice and database are on a remote server not my local.

Does anyone have any ideas? Could it be a deficiency of SQL Server Express?

Any ideas, answers or suggestions would be much appreciated.

Hi Hugo,

I would suggest looking at the log files on the remote server where SQL Express is installed to see if there is any information about the failure there. I doubt any failure is the sole responsibility of SQL Express, it is probably a combination of issues from many sources.

Mike

fastest way to do large amounts of updates

I was wondering what is the fastest way to UPDATE lots of recods. I heard the fastest way to perform lots of inserts in to use SqlCeResultSet. Would this also be the fastest way to update already existing records? If so, is this the fastest way to do that:

1. Create a SqlCeCommand object.
2. Set the CommandText to select the datat I want to update
3. Call the command object's ExecuteResultSet method to create a SqlCeResultSet object
4. Call the result set object's Read method to advance to the next record
5. Use the result set object to update the values using the SqlCeResultSet.SetValue method and the Update method.
6. repeat steps 4 and 5

Also I was wondering do call the SqlCeResultSet.Update method once per row, or just once? Also would it be possible and faster to wrap all that in a transaction?

Would parameterized updates be faster?
Any help will be appreciated.
To answer some of my own questions, for an SqlCeResultSet object, you must call the Update function once per row. Also you can wrap it in a transaction, but that will probably slow the process down, although this may still be a good idea.

My main question still remains unanswerd: What is the fastest way to do large amounts of updates? I will be running some tests soon and will post my results here.
|||

Some things you can do to improve update performance:

1. make your update statement a parameterized query, prepare it, and reuse it for each update, changing only the parameter values

2. keep indexes on the table to a minimum (or even remove them in extreme cases - then readd them after the updates complete)

3. SqlCeResultSet is the fastest mechanism if ou are using CF2 and SQL Mobile - yes, you call update on each row

-Darren

Fast "bulk" inserts...

I have an app that must insert batches of transaction records every 30 min.
Typically only a few hundred each time. We found that just appending a
hundred or so INSERT commands together in a single query sent to the server
was significantly faster than our first approach of preparing a paraemterize
d
query and executing once for each record. So now we send only a few commands
to SQL instead of hundreds.
Someone suggested to me that we put all of the records into an XML
"document" and use OPENXML. We will test this approach.
Any suggestions, guesses on how the new approach and other possible
approaches to try?
Also, would a different approach be warranted if the number of records in a
batch was around 10K, instead of just 200-300?
Thanks,
LMcPhee"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:E2D39232-D7B3-450C-B76F-5536DC10FEF5@.microsoft.com...
>I have an app that must insert batches of transaction records every 30 min.
> Typically only a few hundred each time. We found that just appending a
> hundred or so INSERT commands together in a single query sent to the
> server
> was significantly faster than our first approach of preparing a
> paraemterized
> query and executing once for each record. So now we send only a few
> commands
> to SQL instead of hundreds.
> Someone suggested to me that we put all of the records into an XML
> "document" and use OPENXML. We will test this approach.
If you can get by with your current approach, do. In SQL 2005/ADO.NET 2.0
you will have the SQLBulkInsert, which will be the perfered method.

> Any suggestions, guesses on how the new approach and other possible
> approaches to try?
> Also, would a different approach be warranted if the number of records in
> a
> batch was around 10K, instead of just 200-300?
>
The batching approach will still work, but you wouldn't want to batch 10k
rows. That's just too much SQL. Keep your batch size constant, and send
multiple batches if necessary.
Another critically important thing for running multiple inserts is to use an
explicit transaction. Without an explicit transaction SQL Server must flush
the log to disk (aka commit) after every insert. The commit takes much
longer than just doing the insert, so you want to do all the inserts, and
just force a single commit at the end.
David