"requested conversion is not supported" error in DTS data copy.

I have a DTS that pulls data in from a table in MysQL and inserts it into a tablle in SQL Server. it's failing when I run the step with the "requestd conversion is not supported" error, but when testing each single column copy in the Data Transformation step, it passes.

I just want an exact copy of the data.

the source MySQL table is...

CREATE TABLE `entry` (
`web_id` int(11) NOT NULL auto_increment,
`web_name` varchar(40) NOT NULL default '',
`web_email` varchar(60) NOT NULL default '',
`web_cli` varchar(50) NOT NULL default '',
`web_pu_id` varchar(60) default NULL,
`web_answer` varchar(60) NOT NULL default '',
`web_ddi` varchar(30) default NULL,
`web_time_entered` datetime NOT NULL default '0000-00-00 00:00:00',
`web_ip_address` varchar(20) default NULL,
`web_entry_status` set('failed','success') NOT NULL default '',
`web_polled` enum('true','false') default 'false',
PRIMARY KEY (`web_id`)
) ENGINE=MyISAM;

and the SQl Server destination is...

CREATE TABLE [entry] (
[web_id] [int] NOT NULL ,
[web_name] [varchar] (23) NOT NULL ,
[web_email] [varchar] (37) NOT NULL ,
[web_cli] [varchar] (15) NOT NULL ,
[web_pu_id] [varchar] (15) NULL ,
[web_answer] [text] NOT NULL ,
[web_ddi] [varchar] (11) NULL ,
[web_time_entered] [datetime] NOT NULL ,
[web_ip_address] [varchar] (15) NULL ,
[web_entry_status] [varchar] (15) ,
[web_polled] [varchar] (15) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [temp_webentry] WITH NOCHECK ADD
CONSTRAINT [PK_tmpwebentry] PRIMARY KEY CLUSTERED
(
[web_id]
) ON [PRIMARY]
GO

sample line of data ...

352711, 'some name', 'email@.addre.ss', '0123456789', 'AA_XX-Y-012345-2', '', '098765432', '2005-10-19 23:17:42', '192.168.28.165', 'failed', 'false'scratch that.. in compairing the DDLs it was obvious..

varchar -> text doesn't work well :)

a

"Oracle Not Available" error from SQL

We have a DTS package utilizing an ODBC connection to and Oracle database to run a few updates. I can TNSPING the Oracle instance but get the error Ora-01034 ORACLE not available when trying to run the package.
Any ideas?DTS packages can run as scheduled tasks, on the server itself, or from a workstation. They may or may not have the same NT Credentials. My guess would be that you're running the task from two different machines (a workstation and a server), where one has Oracle connectivity and the other does not.

-PatP|||Pat, Thanks for the reply.

This is all running from our SQL server and is scheduled from there. When i am looking at the DTS package, specifically the connection i can not even close it without getting the error.|||Have you rebooted NT since you installed the Oracle drivers on your server?

-PatP|||We have been utilizing the Microsoft ODBC for Oracle drivers.|||Have you tried running xp_cmdshell stored procedure from sql server with something like 'tnsping oracle_server'?

exec master..xp_cmdshell 'tnsping oracle_server_name'

what is the result? it could help to determine the source of problem

btw we have been using microsoft ole db provider for oracle for two years in dts packages and it's absolutely problemless. but only for selects. mojza

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIA
Lindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegr oups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>
|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

"Bulk copy insert failed"

I'm copying data from tables in one server to tables in another via DTS.

This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.

When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.

Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.

But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?