DTS Export fails with "Invalid Object Name"

I have a database that used to have a table called MALL_Customers. The
table was renamed to tbl_Users, and a view was created called MALL_Customers
which simply selects all rows from tbl_Users, for backwards compatibility
with the web app accessing the database.
The database works fine; the web app works fine. All permissions are set
properly on all objects. The view is accessible via EM, as is the table.
However, when I attempt to run a DTS Export of this database to an empty
database on the same server, I get "invalid object name" on MALL_Customers.
Apparently somewhere, in some trigger, stored proc, or some other object,
there is a reference to MALL_Customers that doesn't recognize the VIEW as a
valid object, but is instead looking for a TABLE with that name. I cannot
find it anywhere, and I don't know where to start.
The overall question then is, how do I find all references to the text
"MALL_Customers" in the database schema so that I can track down why my DTS
package is failing. I have another database with a similar problem, and I
think the solution to this problem will also solve the other -- hidden
references somewhere in the database that cause "invalid object name" errors
when using DTS.
Where do I start?
Thanks in Advance,
Marcyou can search through the system table syscomments which, contains entries
for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint,
and stored procedure
Ex:
select * from syscomments where text like '%MALL_Customers%'
Also you can use stored procedure sp_depends to know dependencies of the
existing object.
Ex:
sp_depends 'MALL_Customers'
--
-Vishal
Marc Funaro <marc@.advantex.net> wrote in message
news:xyK7b.3731$nF5.632@.news02.roc.ny...
> I have a database that used to have a table called MALL_Customers. The
> table was renamed to tbl_Users, and a view was created called
MALL_Customers
> which simply selects all rows from tbl_Users, for backwards compatibility
> with the web app accessing the database.
> The database works fine; the web app works fine. All permissions are set
> properly on all objects. The view is accessible via EM, as is the table.
> However, when I attempt to run a DTS Export of this database to an empty
> database on the same server, I get "invalid object name" on
MALL_Customers.
> Apparently somewhere, in some trigger, stored proc, or some other object,
> there is a reference to MALL_Customers that doesn't recognize the VIEW as
a
> valid object, but is instead looking for a TABLE with that name. I cannot
> find it anywhere, and I don't know where to start.
> The overall question then is, how do I find all references to the text
> "MALL_Customers" in the database schema so that I can track down why my
DTS
> package is failing. I have another database with a similar problem, and I
> think the solution to this problem will also solve the other -- hidden
> references somewhere in the database that cause "invalid object name"
errors
> when using DTS.
> Where do I start?
> Thanks in Advance,
> Marc
>

DTS- Destination Column Name has spaces causing error

I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?

I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.

When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Use square brackets ( [ ] ) , instead of double quotes.|||

I tried this in a sql query against the Advantage ODBC and it failed. I believe this is a limitation of the ODBC drivers for Advantage.

The issus that the column names are causing this to fail inside the DTS Transformations themselves. Anyone know where I can edit the Destination column names so that I can wrap them with quotes or brackets.

Thanks

Donnie

|||

Have you tried enclosing the column names in single quotes instead of Microsoft brackets?,

i.e. SELECT 'My Column' FROM MyTable

|||

Yes tried single quotes.

If I try Insert into TableName ("Last Name") values ('Test') with the Advantage ODBC, it works. Issue is, I need to pull from SQL and push into Advantage. When I try with with transformations between the source and desitination, I get the missing closing ")" error.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Thanks for the help

Donnie

|||

This forum is for SSIS, not DTS.

You'll probably get better assistance on the DTS newsgroup.

|||Thanks DD. Got it over there as well as Google forums.|||Bumpsql

DTC question

Hi friends,
If I have a ms dtc log file on my hands, and it is, I believe, in some
binary format, is there any way to view it?
Thanks a lot in advanceHi,
> If I have a ms dtc log file on my hands, and it is, I believe, in some
> binary format, is there any way to view it?
I'm afraid there is no way to do it. The file is encrypted as an
internal part of DTC service.
Best regards,
Marcin Guzowski
http://guzowski.info|||It is sad.
It's actually dtc trace file.
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:esu87d$p2a$1@.news.onet.pl...
> Hi,
>> If I have a ms dtc log file on my hands, and it is, I believe, in some
>> binary format, is there any way to view it?
> I'm afraid there is no way to do it. The file is encrypted as an internal
> part of DTC service.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info

DTC question

Hi friends,
If I have a ms dtc log file on my hands, and it is, I believe, in some
binary format, is there any way to view it?
Thanks a lot in advance
It is sad.
It's actually dtc trace file.
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:esu87d$p2a$1@.news.onet.pl...
> Hi,
>
> I'm afraid there is no way to do it. The file is encrypted as an internal
> part of DTC service.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info

DTC question

Hi friends,
If I have a ms dtc log file on my hands, and it is, I believe, in some
binary format, is there any way to view it?
Thanks a lot in advanceHi,

> If I have a ms dtc log file on my hands, and it is, I believe, in some
> binary format, is there any way to view it?
I'm afraid there is no way to do it. The file is encrypted as an
internal part of DTC service.
Best regards,
Marcin Guzowski
http://guzowski.info|||It is sad.
It's actually dtc trace file.
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:esu87d$p2a$1@.news.onet.pl...
> Hi,
>
> I'm afraid there is no way to do it. The file is encrypted as an internal
> part of DTC service.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info

DSV Refresh

Hi,

I have an odd problem with the creation of objects in the data source view using AMO.

I added some tables and relations with AMO and everything seems to work well.

Then, using Analysis Services I selected the data source view and refreshed it.

The form that notifies the changes in the data source view showed a lot of changes that I didn’t understand, costraints created and removed, fields changed and other operations that I didn’t made...

I try to do the refresh with AMO using all the update and refresh options I found but with no results.

Every insight on the question will be of great help.

Thanks

This is an extract of the data source view refresh report:

Object

Change

_DIM_DimColl1

Changed

Constraint1

Deleted

_DIM_Dimensione

attrfinto

Changed

_DIM_eta2

aaa

Changed

_DIM_Mesi

Changed

_DIM_MesiColl

Changed

Constraint1

Deleted

_FK_eta2_aaa

Changed

Constraint1

Deleted

_FK_MesiColl_Livello03

Changed

Constraint1

I don't have a clue what's causing that, but let me suggest that you keep a copy of the .dsv (XML) file from before you refresh the DSV. Then you refresh it, save it, and do a diff versus the previous file. Then you can see exactly what properties changed. There's a pretty close mapping between AMO properties and the XML elements in the file.|||The refresh in AMO is different from the refresh in DSV diagram. DSV Diagram refresh will connect to the relational database and refresh the metadata. AMO refresh will not connect to relational database and just refresh the objects within AMO.|||

Do you know how can I refresh the DSV through AMO?

DSV Refresh

Hi,

I have an odd problem with the creation of objects in the data source view using AMO.

I added some tables and relations with AMO and everything seems to work well.

Then, using Analysis Services I selected the data source view and refreshed it.

The form that notifies the changes in the data source view showed a lot of changes that I didn’t understand, costraints created and removed, fields changed and other operations that I didn’t made...

I try to do the refresh with AMO using all the update and refresh options I found but with no results.

Every insight on the question will be of great help.

Thanks

This is an extract of the data source view refresh report:

Object

Change

_DIM_DimColl1

Changed

Constraint1

Deleted

_DIM_Dimensione

attrfinto

Changed

_DIM_eta2

aaa

Changed

_DIM_Mesi

Changed

_DIM_MesiColl

Changed

Constraint1

Deleted

_FK_eta2_aaa

Changed

Constraint1

Deleted

_FK_MesiColl_Livello03

Changed

Constraint1

I don't have a clue what's causing that, but let me suggest that you keep a copy of the .dsv (XML) file from before you refresh the DSV. Then you refresh it, save it, and do a diff versus the previous file. Then you can see exactly what properties changed. There's a pretty close mapping between AMO properties and the XML elements in the file.|||The refresh in AMO is different from the refresh in DSV diagram. DSV Diagram refresh will connect to the relational database and refresh the metadata. AMO refresh will not connect to relational database and just refresh the objects within AMO.|||

Do you know how can I refresh the DSV through AMO?