- Remove From My Forums
-
Question
-
Hi,
I’m connecting to MS SQL server 2000 (version 8.00.2039) via odbc to copy one table to the other in my C++ program
The tables Table1 and Table2 have same definition i.e., name varchar(20), empid numeric
Through my program written in C++ I use odbc functions to connec to sql server and copy records form Table1 to Table2.
Into Table1 I inserted 20 records through SQL query analyzer.
To insert records that have been read from Table1 to Table2, I use the following odbc functions.
SQLBindaParam()
SLQExecute()
Once the table1 is copied to table2 I find that every 10th record has the data from previous record for the second field which is of numeric type.
At times the insert statement that’s executed through sqlExecute to table2 gives the error:
[ODBC SQL Server Driver]Invalid character value for cast specification
Funny thing is if I change the data type for the second field to integer, the insertion is successful.
Also, instead of SQLBindParam if I use SQLBindParameter() the insertion is successful.
Please let me know what could be the problem.
System info:
Windows XP
SQL Server 2000 with SP4
Odbc version 3.2
radhika.ganganna@oracle.com
Answers
-
I am not certain that this is causing your issue, but SQL_NUMERIC should be SQL_C_NUMERIC in this case. Just to make sure that we are all on the same page…
This BindParam call tells me that you want to bind the second parameter of your statement with destination SQL type of SQL_CHAR[18] using an application buffer (tempVec.data()) that should be interpreted as a numeric type.
From what you indicate above, the buffer you are binding is a char buffer, perhaps you want to specify the target as being numeric and the source as being SQL_C_CHAR —
SQLBindParameter(hStmt, 2, SQL_NUMERIC, SQL_C_CHAR, <SQL_NUMERIC Metadata> tempVec.data(), &pcbvalue[1][0]);
For info on SQL Numeric decimal digits and column size see:
http://msdn2.microsoft.com/en-us/library/ms712499.aspx
Hope that helps,
John
- Remove From My Forums
-
Question
-
we are using ssma 6.0 to migrate mysql to sqlserver 2012 by mysqlodbc 5.3.4
when doing migrate data — for some tables we get
[Gui: Info] [11956/1] [2014-10-02 10:35:29]: Starting data migration…
[Gui: Info] [11956/1] [2014-10-02 10:35:33]: The data migration engine is migrating table ‘otrs.article’: > OTRS.dbo.article, 53529 rows total
[Gui: Info] [11956/1] [2014-10-02 10:35:35]: ERROR [22018] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.51-community-log]
[Gui: Info] [11956/1] [2014-10-02 10:35:35]: Errors: ERROR [22018] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.51-community-log]
[Gui: Info] [11956/1] [2014-10-02 10:36:03]: Table otrs.article data migration: 5495 rows processed…not all rows are migrated. checked data and could not see any specific characters/data
is this mysql error / sqlstate / ssma error ????
the text above is all we get in the logs…
Thanks for all update/help
best regards, Guy Przytula
Answers
-
Hi Przytula,
When we use SQL Server Migration Assistant for MySQL to migrate MySQL to SQL Server, please note that there are some basic steps as follows. For more details, you can review this
blog.
1. Create a MySQL migration project
2. Connect to a MySQL database
3. Create report of potential conversion issues
4. Connect to SQL Server
5. Convert schema
6. Synchronize with database
7. Migrate dataAccording to your description, specific characters or data are not migrated successfully. I recommend you check the data type of these data, as some MySQL data types are not supported in SQL Server. SSMA cannot handle such conversion issues automatically,
we need to manually change the data definition syntax before migrating them to SQL Server.For more details, please review the MigrateMySQLtoSQLServer2008 white papers which can be downloaded from the following link:
http://www.microsoft.com/en-us/download/details.aspx?id=24662 .Thanks,
Lydia Zhang-
Edited by
Friday, October 3, 2014 2:57 AM
-
Marked as answer by
Lydia ZhangMicrosoft contingent staff
Tuesday, October 21, 2014 1:23 AM
-
Edited by
-
August 11, 2016 at 12:15 pm
#329183
I am trying to get snapshot replication between two MSSQL servers to work but am receiving errors. Any help will be much appreciated!
My setup: Publisher/Distributor: SQL 2008 R2
Subscriber: SQL 2016
I am just trying it with a simple test table named Cars containing:
Car_ID Car_Name
1 Hummer
2 Porsche
3 Tesla
4 Bently
5 Lotus Elise
6 Mini
In the Replication Monitor I get the following errors:
Error messages: The process could not bulk copy into table ‘»dbo».»Cars»‘. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037 metadata query failed Invalid character value for cast specification (Source: MSSQLServer, Error number: 22018) Get help: http://help/22018 Unspecified error
-
Agnii
Ten Centuries
Points: 1192
1. Remove table from replication
2. Reinitialize subscription
3. Add table to replication
4. Reinitialize subscription
-
Marcus Lipski
SSC Veteran
Points: 259
Hi nstewart 66440,
I had have the same issue. Check your COMPATIBILITY_LEVEL of the subscriber and publisher database. I’ve configured both compatibility levels to 100 and now the replication is functional.
Greetings
Marcus -
skunki07
Valued Member
Points: 69
Marcus Lipski — Tuesday, March 28, 2017 7:47 AM
Hi nstewart 66440,
I had have the same issue. Check your COMPATIBILITY_LEVEL of the subscriber and publisher database. I’ve configured both compatibility levels to 100 and now the replication is functional.
Greetings
MarcusEven 120 for the subscriber database works fine.
Also replicating from SQL Server 2012 or higher to SQL Server 2016 works fine.
I guess that there is a problem with native client 10 and the way it gathers the meta data of the destination table.Regards
Wolfgang -
Daniel Adeniji-180202
SSC Veteran
Points: 231
Works for me, as well.
Source sql server 2005, Destination 2017.
Set Compatibility Level to 2008 using
ALTER DATABASE [LAB] SET COMPATIBILITY_LEVEL = 100
GO
Viewing 5 posts — 1 through 4 (of 4 total)