So I'm writing a data migration script, thought I was done and now I get this error. The problem is some of my columns in the originating DB are "nvarchar" and the columns I'm migrating to are "int".

How do I overcome this error?

Here's what I've got code wise-

Actual error:
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric., SQL state 37000 in SQLExecDirect in D:\hosting\member\croakingtoad\site1\migrate.php on line 111
Cannot connect to database


# insert string for query
$insert = "INSERT INTO Communities (StateID,TypeID) VALUES ($StateID,'$TypeID')";


# perform the query
$result = odbc_exec($connect, $insert) or die ("Cannot connect to database");

I would encourage you to use a stored procedure for the task.

What you need is of the format:

insert newtable (a,b) select convert(varchar,a),b from oldtable

presuming that a is the nvarchar variable, and that it does in fact convert.

This will accomplish the whole job in one query without resorting to a cursor or cursor-like operations in odbc.

$insert = "INSERT INTO Communities (StateID,TypeID) VALUES ($StateID,'$TypeID')";

I think you should change to

$insert = "INSERT INTO Communities (StateID,TypeID) VALUES ($StateID,$TypeID)";

That will be better if you bring here struture of the table, so we can see what problem!