Merant OBDC returning invalid data type to SQL 1

[PenelopeC]

Hello,
I've been lurking through FAQs and threads for two weeks and can't find an exact fix for my issue.

I have a stored procedure in SQL 2K that uses the Merant OBDC (3.51?) connection to suck data from PROGRESS (9.1C) on a Win2K Server. The procedure will work when run manually from within the SQL Enterprise Manger however when I try to run the sp from a DTS "Job" (to automate the process) the job history says a lot of stuff about OBDC driver not found.

<snip of error message>
DTSRun OnError: DTSStep_DTSDataPumpTask_12, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microso... Process Exit Code 9. The step failed.
</snip of error message>

I've put the error code in Microsoft's Knowledge Base and it ends up being pretty generic.

I've run the OBDC log file when running this process, if anyone thinks that will help I can forward it.

TIA, if I can solve this it will mean a huge load off my mind!

Penelope Sanders
City of Portland (Oregon)

PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

Is the sucking stored procedure using OPENQUERY on a linked Progress server? (Just want to make sure I'm getting the picture.)

I can't remember exactly why, but to make this work we had to upgrade to version 3.6 of the ODBC driver and 9.1D of Progress, although in our case Progress runs on a J50.

 

[PenelopeC]

I'm using DTS in SQL. I'm using the Merant (v 3.6 BTW - I just checked) OBDC connection to run SQL queries that select just the fields I need and puts them in SQL tables where I can actually work. Just writing this makes me think that if I can CAST or CONVERT every field I bring over, maybe that would solve the issue?

I'm not sure if this answers your question. I don't think the Progress server is linked(?) I am scheduled to have the 91.D upgrade very soon though. I hope that helps with the problem.

Thanks for your input.



PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

Ok, I see now: DTS package uses ODBC connection to Progress DB.

It sounds more like something is wrong with the ODBC connection than the things you're talking about. How about opening the DTS package and dropping a &quot;Data Driven Query Task&quot; into it. Set the connection to your ODBC/Progress source. Pick the &quot;Table/View&quot; radio button. Can you see your Progress tables in the drop-down combo box?

 

[PenelopeC]

Hiya Harebrain! Thanks for your help....here's what happened.

I didn't actually build the DTS packages myself. Some whiz this place had before me did. He didn't use &quot;Data Driven Query Task&quot; he used &quot;Execute SQL Task&quot; -- anyway, I dropped one in and it connected right up. I can see all the tables I need EXCEPT of course the one I really need. I get the message...

<snip>
[MERANT][ODBC PROGRESS driver][PROGRESS]Column ref_no in table PUB.Batch_Dtl has value exceeding its max length or precision.
[MERANT][ODBC PROGRESS driver]Error in row.
</snip>

The funny thing is, I don't even need this field of data!




PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

Aha!

SQL Server and the Merant ODBC driver take the schema's column-width declaration seriously; Progress doesn't. (To the former, it's a definition. To Progress, it's a formatting suggestion.)

So you have a couple of choices:
-- &quot;fix&quot; the bloated Progress data
-- change the Progress schema (widen the field)
-- create a view in Progress that only includes the fields you need and reference the view in your DTS package instead of the table.

Good luck!

 

[PenelopeC]

Aha is right!

I'm in kind of a funny position because the PROGRESS db is not considered ours, its a &quot;service bureau&quot; type set up. I am going to forward this to the programmer to see if the widen the schema part could be done by them. I don't want to mess up their database.

I'm going to forward the link to him to help explain.

Thanks so much for your help. As soon as I have a definite &quot;that worked&quot; I'll post here so everyone can know.

IRAI!!!!



PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

Hi Penelope,

I'm glad we're making progress! Because the database is under someone else's control, changing the field definition is the least likely thing they'll want to do as it is most likely to screw up other things. I think a custom view is your best bet.

Oh, I get it! Portland... swimming. <grin>

--harebrain of San Diego, where it seldom rains and we just don't get weather humor.

 

[PenelopeC]

Here I go with another issue then....how do I make a &quot;view&quot; table in PROGRESS? I know how to in SQL, however knowing the pickiness PROGRESS has with table owners, I have yet to make a connection work using any PROGRESS tools that I have. I've only been able to use SQL to access data to this point -- I just get tired of banging my head against the permissions wall constantly.

BTW - San Diego is one of my FAVORITE places! If I could afford it I would move there in a second

It doesn't really rain here that much, we just tell people that so they'll stay away.

PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

That would be a job for the Progress DBA, whom I take it is an employee of the service bureau. I'm not a Progress DBA and I haven't strayed into the SQL view territory, but it sounds good!

 

[harebrain]

Hi Penelope,

It just occurred to me that at the beginning of this discussion, I thought you were taking a different approach. In fact, that is an approach that would probably be a better solution than any of the others I've mentioned.

It would require you to create a link to the Progress table, which is easy to do in Enterprise Manager. Then, in your stored procedure, you'd reference the Progress table with an OPENQUERY, which is akin to creating a view on-the-fly. See the SQL Books Online for a description of OPENQUERY.

Unless there are other steps or scheduling in the DTS package, you could do without DTS altogether.

This approach would take your service bureau's DBA out of the loop--woo hoo!

 

[PenelopeC]

Hiya Harebrain
The Service Bureau fixed the field for me and they're going to load it tomorrow. I'll let you know if that worked.

Next stop is modifying the stored procedure.

Thanks for all your help!

PenelopeC
~~~>-/O~~~~~swimming right along