AS/400 Access Client destroys SQL Server 2000 DTS

Until this morning I had a fully functional SQL Server 2000 Enterprise manager with DTS capability. There is a requirement to have data pulled from an AS/400 machine into the SQL Server 2000 environment for reporting reasons. To do this we needed the AS/400 ODBC drivers.
I was given a CD which matches the OS on the 400 box that being; V4R5M0, i.e. Version 4 Rev 5, (4.5).
Well, when trying to use the DTC which up untill that moment worked perfectly I got the following message upon initiation of the Data Transformation Services Wizard;

The title is:
DTS Import/Export Wizard Error: CoCreateInstance
The very helpful message is;
Error in the DLL

Which DLL is a mystery, and I can't get DTS to recover.

If I go ahead I can define a connection but when I hit "next>" it just sits there. Basically it's FUBAR'd.

I've uninstalled the lousy IBM Client that trashed DTS, and I've uninstalled, and re-installed SQL Server 2000 about 3 times now but to no avail. I still cannot initiate DTC. The other wizards and Tools Database, Management, Replication all work fine.

Any ideas ?
Thanks
Jeremy GreavesOuch!

Since no one else has posted a reply yet, I suggest you cross post to http://communities.microsoft.com/newsgroups/default.asp?icp=SQL&slcid=us in the DTS forum.

Also not much help, but just in case it helps a little, you can find a list of DTS dll's at http://www.sqldts.com/main.asp?nav=1,6,225,0

John

AS 2000, Excel 2003 and calulated measures

Hi

I have created a calculated measure - which when i view in the Analysis Manager - Browse it displays what I need.

In my Excel Pivot Table I cannot see the field when I Show Field List? is it possible to have calc members in Excel 2003?

however, my initial problem is this (and I am hoping someone can come up with a better solution):

I have a fact table that is simply number of hours worked per month and linked to dimensions such as the customer. Each Customer has a budgeted time (which is on the customer dimension) - I need to include this budget.

I have a separate cube that is the detail of the budget and I have tried creating a virtual cube - the problem with this is the budget is only visible if I use dimensions from that cube and the hours are only visible when I use dimensions from that cube - I would liek to see them together. (the dimensions in both cubes are identical)

Anyone?

Steve

Sounds like you have a bunch of private dimensions in your cubes. Its been a while since I worked on 2K, but i think global dimensions (dimensions that can be shared in virtual cubes) are called "shared dimensions". If you used the wizard to create the dimensions and cubes I think it defaults to adding dimensions as private.

|||

That may solve the virtual cube issue - thanks.

Any idea why I cannot see a calculated member in Excel while I can see it in the MMC browser?

Thanks

|||

Shared dimensions and virtual cubes work a treat - thanks.

Apparently, a calculated measure that is based on a dimension table does not appear in Excel 2003!!

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jess R.
Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/default...b;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:

> Hello friends, in this moments I have a problem with my Ms Sql server 2000,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jess R.Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/defaul...kb;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:

> Hello friends, in this moments I have a problem with my Ms Sql server 2000
,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jesús R.Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/default.aspx?scid=kb;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:
> Hello friends, in this moments I have a problem with my Ms Sql server 2000,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Are text pages deleted when a column is converted to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?

If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
..morriszone@.hotmail.com (Steve Morris) wrote in message news:<757b6d8f.0404080820.7956cf62@.posting.google.com>...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
> .

just try:

select * into your_table_bak from your_table

and then try your alter table statment with no risk of dataloss!
alter table *your_table_bak* :-)

if you dont like to read 8000 charakter to find out wether some data
is loss or not try the function
"getsize"

hth