March 11, 2009 at 10:28 am
what error are you getting?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 10:32 am
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the nvarchar value '77 or 70 or 81' to data type int.
March 11, 2009 at 11:08 am
I have to leave my office until Friday, so pardon my lack of response until then!!
😀
March 11, 2009 at 11:31 am
Are you using that in your access system you have one column that has
'77 or 70 or 81' in it.
And you result is:
'77 or 70 or 81',"'77 or 70 or 81' (SOMEHEX VALUE)"
???
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 13, 2009 at 7:15 am
Sorry if I wasn't clear before, but in my access system a column value is
"70 or 60 or 81"
and when the query is run that specific value in Access is just returned as ERROR during the conversion to HEX.
But this error doesn't prevent the query from running, everything else is normal except for that one value in the results table.
However, in SQL, if I try to execute the query, SQL throws an error and won't run the query at all. I would go back and just get rid of that column value however it is necessary to be there.
March 13, 2009 at 7:31 am
is this a one off import?
If so then just add that one row seperatly
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 13, 2009 at 7:33 am
I'm sorry but could you please clarify?
March 13, 2009 at 10:30 am
A better option is to use the Microsoft Access Migration assistant or use SSIS package because converting Access to SQL Server can be very complex, it took more than one year at a previous place of employment.
However you could convert Varchar to Vabinary and convert Varbinary to Hex with the code below from Microsoft, your other option is to use SQL Server 2008 Express free and move it back to 2005 manually.
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx
Kind regards,
Gift Peddie
March 13, 2009 at 11:01 am
I used the Access migration assistant to upsize the database from access 2007 to SQL Server 2005. However, it only transferred the tables, data, and relational information. I have to put those queries in by hand, which is why I'm running into this problem.
I wanted to upsize to SQL 2008 initially, but the upsize wizard did not transfer any of the relational data, or the queries and I felt that would lead to a lot more manual work than was entirely necessary.
March 13, 2009 at 12:08 pm
I wanted to upsize to SQL 2008 initially, but the upsize wizard did not transfer any of the relational data, or the queries and I felt that would lead to a lot more manual work than was entirely necessary.
You either use the code I posted which is the only existing code or convert only this query to 2008 and then when you have moved all to 2005 just move it to 2008 and execute the 2008 version of the code. People load a lot crap into Access so conversion is never an easy task.
Kind regards,
Gift Peddie
March 13, 2009 at 12:28 pm
interestingly enough, I tried going from the successful version i have in SQL 2005 and bring it in to 2008 but all my relational data dissapeared :unsure:
March 13, 2009 at 12:35 pm
That is may not be complicated do some backup and restore and detach and attach with new names before moving it to 2008. Play some tricks to remove the Access definition from your new database before moving it to 2008.
Kind regards,
Gift Peddie
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply