Hi!

I have a very simple DTS Package but it's failing! First, there is an
Execute SQL task that selects a short list of intentory item numbers from a
small table. It puts the item numbers into an output parameter rowset global
variable called rs_ItemIds

Then there is a data transformation task which is supposed to move all
the records for these individual inventory items from a table on one server
to a table on another. In the Source table tab, I have the rowset set as a
input parameter.

The relevant part of the query says 'where Item_Number = ?' And, yes I do
have parameter 1 mapped to the global variable parameter. And yes, I have
both tasks running on the main thread.

DTS Transform fails with a generic Access Violation error and a message that
says "Need to run the object in order to perform this operation.

I know, I should be able to do this using a simple SQL statement. But I
can't set the source server up as a linked server, so I can't access it from
a query on the destination server.

All I'm trying to do is limit the rows I'm pulling across. Oh, when I
change the global variable to a row item rather than a rowset, it works,
adding just the one row.

Any suggestions?

Please help.

Karen

hi Karen,

Can you set a specific log inside the pump task and then see the contents?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)


"Karen Grube" wrote:

Hi!

I'm sorry, I knon't know how to do that. I'll gladly try if you can
provide instructions or tell me where to look for how to do that. Having
said that, I don't think it'll help much. My take on the situation is that
the task fails immediately and may not even get far enough to generate
anything more in a log beyond the error message that's already being
displayed.

Apparently this is a fairly common problem. The usual fixes are running
the tasks on the main thread and making sure the server name is showing,
though I don't quite see how it could ever NOT show. There was also a note
somewhere about MDAC, but I don't think it proved helpful to the person
trying to resolve the problem.

I care very much about resolving this error. But I care even more about
getting this job done. All I need to do is limit the rows of information
being selected to be copied. The problem is that the list of records I want
to copy is on one server and the records I need to copy are on another. I
can't write to the other server where the records are stored, nor can I set
up a 'linked server' to that database because remote logins aren't enabled on
that server - intentionally. So, if you have any other suggestions other
than my trying to use a rowset as a parameter, I'll gladly try them. And
yes, I know I can loop through individual records from that rowset one at a
time within the package and use a 'value' rather than a rowset. It's just
that it ticks me off that this isn't working and that I can't figure out what
the heck is going on.

Let me know!

Thanks!
Karen