How to force the field type in an OLE INSERT statement
We have an external database that I'd like to feed Contact data into our Dynamics NAV database running on MS SQL database. The procedure we use is: DECLARE @newid nvarchar(7); SELECT @newid = 'K' + CONVERT(nvarchar,(RIGHT(ISNULL(MAX([No_]),'106001'), 6) + 1)) FROM [Contact Import]; INSERT INTO [Contact Import] (Name, [Name 2], Address, [Address 2], City, [Phone No_], [Country_Region Code], [Fax No_], [Post Code], County, [E-mail], [Home Page],Type, Status,[Company No_],[Company Name],Error,[Search Name],[MFiles ID],No_) VALUES (?, isnull(?,''),?,isnull(?,''),?,isnull(?,''),?,isnull(?,''),isnull(?,''),isnull(?,''),isnull(?,''),isnull(?,''),0, 0,'','','','',@newid,@newid); The receiving database does not tolerate NULL values, thus all the isnull- functions. I run into problems as the INSERT statement gets the wrong field type (DT_I4) for the field [Post Code] even though this field is varchar(30) in our database! As long as the value for [Post Code] is numeric all is fine. But if you try to place UK Post Codes having letters, we fail miserably with the data type conversion "string -> DBTYPE_I4" error. We do not have the problem with any other field. I read an article here with a similar problem combining several sources into one union. But could not apply the proposed fix to this case. Grateful for any ideas!