Migrate Sybase DB to MS SQL... datetime problem.

I have a Sybase server with my source data.

I have a MS SQL 2000 server for my destination.

Trying to migrate all the data from the Sybase instance over to the SQL instance... not really all the data, just the tables that have a rowcount above '0'. I don't care about PK/FK relationships, constraints, triggers or stored procs.

I came up with the following script, using the linked server features of SQL2000.

Code: declare tablecursor scroll cursor for select name from openquery(LINKEDSERV, 'select name from sysobjects where type= ''U'' and uid = 1 order by name') open tablecursor declare @tablename varchar(50) declare @sql nvarchar(800) fetch first from tablecursor into @tablename while @@fetch_status = 0 begin set @sql = N' IF (select * from openquery(LINKEDSERV, ''select count(*) from ' + @tablename + ''')) > 0 BEGIN select * into TargetDB.dbo.' + @tablename + N' from openquery(LINKEDSERV, ''select * from ' + @tablename + ''') END' -- select @sql exec sp_executesql @sql fetch next from tablecursor into @tablename end close tablecursor deallocate tablecursor But, about part way through I get an error.

Server: Msg 8114, Level 16, State 10, Line 3
Error converting data type DBTYPE_DBDATE to datetime.

Seems that implicit conversion is not working for the Sybase datetime datatype and the SQL2000 datetime datatype.

I don't want to write an import for each table... I have about 100 total tables to target.

Any ideas Can DTS help in this case, even though this source server is not an MS server I want the whole database, and I don't want to write a construct for each table.

Posted On: Saturday 10th of November 2012 04:57:41 AM Total Views:  576
View Complete with Replies

Related Messages:

EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.   (578 Views)
I want to send email using sql stored code is work fine in my local sqlserver account. when I use my online sql server it display this error. EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'. How canI solove this problem
create db (allow setting db path) using sqldmo or script   (303 Views)
i m confused in creating db in which i can set the db filename on my own.. i need to run this function from code , i can either achieve it in 2 ways 1) using query 2) using sqldmo how to accomplish that function in either one of these 2ways
Connecting to a SQL db   (160 Views)
Hi! New here. I now have a MS SQL database but I don't know how to set it up. I'm trying to put MaxWebPortal on my website but I can't follow the directions: -- Create a blank database on your SQL server (or have you host do it) -- Connect with at least dbo privelege -- Click on database and pick Tools>>SQL Query Analyzer -- Open MWP13_SQLSERVER.sql file - file open -- Press the green right arrow to run the script Then Load the Default data -- Open default_data.sql file - file open -- Press the green right arrow to run the script Can some tell me how to do this because my admin doesn;t know how
additional problem deleting dbs ( publication)   (320 Views)
hi now i'm able to delete any dbs even if other user are connected, but if there is a replication (publication) concerned with this dbs it won't delete so how can i find out with sql-dmo whether there is a replication installed on this dbs
Error querying against linked FoxPro db   (254 Views)
I have an issue querying against a FoxPro database. If I do: Code: select * from foxpro...train I get: Error converting data type DBTYPE_DBTIMESTAMP to datetime. This caused by one column, duedate. Code: select cast(duedate as varchar(30)) from foxpro...train it gives the same error. So it seems some implicit casting is being done even before I get any data. Unfortunately of all the data in that table, that is the one column I can't drop. This data is from a legacy application (we don't have foxpro as far as I know) and I am trying to import it into a SQL database. Works fine except for a handfull of tables with these date issues.
Is it possible to convert an Access 2003 db to an sql server 2005 db?   (275 Views)
I have a big database in Access 2003. However, now I have installed sql server 2005 instead and wonder if there is a way to convert my access db to and sql 2005 db instead. I would prefer not to transfer all the information manually... Hope for some useful hints or solutions.
Is it possible or maybe hard to migrate from Access2003 to SQL Server 2005 Express?   (238 Views)
I run a webserver using Windows Server 2003 with IIS 6.0. The database I have used for a while is Access 2003. I have now read about the freeware SQL Server 2005 Express Edition. I therefore have some questions that I hope you can answer. Is it possible to migrate from Access 2003 to this freeware instead What would my main benefits be if I decide to move to the Express Edition instead (stability performance something else) I have never used and SQL Server version and therefore also wonder if it is very different from Access... Hope you can help me out with my questions...
[ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Events'. on INSERT   (199 Views)
Code: dim eventInsertQuery eventInsertQuery = "INSERT INTO dbo.Events ('id','event_type','staff_id','department','subdepartment','event_date','timestamp','time_spent','comm_serv_id','advocacy_issue','notes')VALUES ('','" & request.Form("event_type") & "','" & request.Form("staff_id") & "','" & request.Form("dept") & "','" & request.Form("subdept") & "','" & request.Form("event_date") & "','" & NOW & "','" & request.Form("time_spent") & "','" & request.Form("comm_serv_id") & "','" & request.form("advocacy_issue") & "','" & request.form("event_notes") & "')" 'Response.Write(eventInsertQuery) 'testing echo dim insertCmd Set insertCmd = Server.CreateObject("ADODB.Command") insertCmd.ActiveConnection = MM_editConnection insertCmd.CommandText = eventInsertQuery insertCmd.Execute '''Offending line 454 insertCmd.ActiveConnection.Close Err0r: Microsoft OLE DB Provider for ODBC Drivers error '80040e37' [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Events'. /private/community_Services/addCommunitySvcContact.asp, line 454 -- According to Enterprise Manager owner is dbo.
ASP-SqlServer dynamic db creation   (381 Views)
I have a problem related to asp and sql server. I wana create sql server database ( a database, its tables and then insert data) dynamically in asp. is it possible if so, how looking for a solution.
create new db   (204 Views)
Greetings, Using a server base database called Respond (fe) and Sql server 2000 as a (be), I was trying to create a new db. Instead of designing from scratch, I was thinking to copy an existing Respond db, delete existing data, and modify it to fit my new requirements. I'm new to sql server db and would like to know 1. How to delete data from the table (be). 2. How to perform an automatic backup daily (or may be every other day) and how to check if the backup is doing what it was suppose to do in sql server 2000. Thank you in advance, OCM
sql server db connection   (270 Views)
If my asp files and sql server database are on different server, how can I code the connection statement can I do like this: strConn = "Provider=sqloledb;User ID='id'; pw='pwd'; Data Source=sql server name; Initial Catalog=db name"
Copying db to local machine   (202 Views)
I've been copying a db from production to my local machine, but I've always had to re-add the users to the local copy before it functions correctly. Is there a way to copy which will copy the users, as well
Odbc linked tables failed on insert   (279 Views)
I have a tough problem. I have an access 2002 front end linked up to a SQL server 2005 express back end. The problem I am having is that one of my clients has been using this database for the last 3 years and now and all of a sudden I am getting this error: odbc linked tables failed on insert About 4 customers in 5000 are affected by this random behavior. This happens when you use the form to create a new record. But this does not happen when you open the tables directly. There you can add, edit or delete new records. the tables in the SQL database have time stamp and primary key fields. The tables that require unique id's have triggers creating them. I have researched this problem and the only thing I came up with is that it might be the SQL driver version 3.7, but I can't find a link to an update.
Can I detach existing SQL2000 dbs then attach in SQL2005?   (233 Views)
Hi Guys, I have a SQL2000 database running in customer site for around 5 years. I have a lot of Stored Procedures, Views, and User Defined Functions in that database. I am new to SQL2005, but now I want to migrate to SQL2005 server. Can I just detach my database in SQL2000 then attach in SQL2005 If can, then after attached, will all the SP, Views, and User Defined Functions perform the same as in SQL2000 Will I have any disadvantage or effect in the long run if I detach and attach in this way
Copy Table Data from one sql server db to another server db.   (247 Views)
Hi Viewers' We've developed small application for a company. The Company Staff data in different db (imagine database name is A). Our application is installed on the company client system with our new database (our database name S). the data will change often in the company database. (i mean A database) i've write like schedule task to get that data every day! please let me know...
How do I modify login name for dbo?   (184 Views)
hello, I've been having problems accessing my sql 2005 database. I think I know why, but don't know how to change it. For the user name, dbo, I would like to put my xxxx\user name in the Login name field. It's blank. How do I change it Thank you
Role permission of tempdb due to server restart   (490 Views)
in our application that need to create some tables in tempdb. The user (call it APP) is not sa. Everytime that the server need to be rebooted, the db_owner role on tempdb for user APP disappear. So... 1. Is there any setting to ensure that the the db_owner role on tempdb for user APP will remain after server reboot 2. If not, Is there any solustion to run a script at server startup And does anybody have a script that will create the db_owner role on tempdb for user APP(or some pointer on how) Chirag Vyas Project Manager Software Outsourcing India www. www.
MS-SQL db connection problem   (248 Views)
I work from home and now have to connect to the internet via a satallite ISP. I have tried to connect to various MS-SQL db's using my old settings (which worked fine) but I get a "general network error (pre login handshake())" From reading on other forums and this one it would seem that my ISP appear to have firewalled port 1433 which has stopped me connecting to the MS-SQL db's that controle websites that I maintain. Appart from changing my ISP or the location and hosting of the db's, Does anyone know of a solution or work round that will let me connect via enterprise manager to the db's I have tried pinging the MS-SQL server but it just times out so I'm guessing the problem is to do with port 1433 being blocked
Backing up the db to MY harddrive   (206 Views)
s I have a... situation. My site is hosted at one of the local hosting companies, and all's good. They are giving me FTP services and an MSSQL server service. Now, ofcourse, I have access to that db, by username and pass, read write, whatever. But the bummer thing is... If I wanna backup my db, it's through them, and they are charging me for it (for sending me the backup file) When I access the db on my Management Studio, I can right-click and choose backup, but it only backs it up to their harddrive... Now, thing is I HAVE access to it, so I wanted to know if there is any way I can back the db to my computer... Maybe some script that reads the db and writes all it read (tables+content [thats almost all there is]) to a local db Plus, I wanted to know, if it's a reasonable thing for them to do, charge me for the backup
Cannot see the migrated packages   (168 Views)
I am unable to see my migrated packages in 2005, they were migrated successfully and when I try to migrate them it even tells me that these pacakges already exists. Does anyone know how do I see my migrated pacakages.