Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Roanoke, VA, US
    Posts
    390

    MSSQL error: rror converting data type varchar to numeric

    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-
    Code:
    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");

  2. #2
    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.
    edgedirector.com
    managed dns global failover and load balance (gslb)
    exactstate.com
    uptime report for webhostingtalk.com

  3. #3
    Join Date
    Aug 2007
    Posts
    5
    Code:
    $insert = "INSERT INTO Communities (StateID,TypeID) VALUES ($StateID,'$TypeID')";
    I think you should change to
    Code:
    $insert = "INSERT INTO Communities (StateID,TypeID) VALUES ($StateID,$TypeID)";

  4. #4
    Join Date
    Aug 2007
    Posts
    5
    That will be better if you bring here struture of the table, so we can see what problem!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •