Import from Access - Datatype conversions

I have to do a lot of inporting from Access files. Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?

Thanks.

Kato

If you are using the import wizard then you can affect this with the mapping files at:

%PROGRAMFILES%\Microsoft SQL Server\90\DTS\MappingFiles

I'm guessing that the one you want is JetToSSIS.xml

If you are building packages manually then you can change this in the source adapter.

-Jamie

|||

Outstanding. Exactly what I was looking for.

Thank you.

sql

Import and Export foreign characters (non-DBCS only)

Hi
I have a table which stores the customer information...There is a Customer
Address column defined as nvarchar(50)...Data for the table is imported
using DTS Bulk Insert from a Customer.dat file...
My question: -
1. What are the requirements for me to view the foreign characters (Arabic,
Turkish, German, Italian etc...) using an editor. What setup needs to be
done on the server where the files reside.
2. How do I make the .dat file as a unicode file ?
3. Can I see the foreign characters by a simple select in query analyzer?
4. What are the requirements to export the file to contain all those foreign
characters?
Ps: - Focus is not on DBCS characters (Kanji characters etc...)
Any help would be much appreciated....
Thanks in advance...
ImtiazSee below.
Steve Kass
Drew University
Imtiaz wrote:

>Hi
>I have a table which stores the customer information...There is a Customer
>Address column defined as nvarchar(50)...Data for the table is imported
>using DTS Bulk Insert from a Customer.dat file...
>My question: -
>1. What are the requirements for me to view the foreign characters (Arabic,
>Turkish, German, Italian etc...) using an editor. What setup needs to be
>done on the server where the files reside.
>
As long as the data is stored as Unicode, and the client is Unicode
compatible, all you need is Unicode fonts in your editor.

>2. How do I make the .dat file as a unicode file ?
>
I don't know about DTS, but I think that bcp will leave out the
two-byte Unicode byte-order marker at the beginning of the file.
You may need to insert the two-byte sequence FFFE yourself
so that other applications will handle it correctly. For importing
back to SQL Server, this might be the wrong thing to do, though.

>3. Can I see the foreign characters by a simple select in query analyzer?
>
Yes. Just make sure the font in your results window is a Unicode font
(Arial MS Unicode, for example).

>4. What are the requirements to export the file to contain all those foreig
n
>characters?
>
Just export as Unicode and it should be fine.

>Ps: - Focus is not on DBCS characters (Kanji characters etc...)
>Any help would be much appreciated....
>Thanks in advance...
>Imtiaz
>

Implicit conversion of datatype text to nvarchar is not allowed.

I am facing a problem while using SQL Server with VB application.

Implicit conversion from datatype text to nvarchar is not allowed.
Use the convert function to run this query.

When i see the trace file, i see one stored procedure called but no
lines of code get executed, and immediately after that the ROLLBACK
TRANSACTION occurs and the applications fails.

But to my surprise i am able to do the same thing on a different
machine using the same application and the same database on the same
server with the same user id.

Can anyone explain the reason of occurance of this problem.

I require this very urgently, so i will be oblized if anyone can come
up with a quick response.

Kind Regards,
Amit KumarAmit (kumar_amit@.delhi.tcs.co.in) writes:
> I am facing a problem while using SQL Server with VB application.
> Implicit conversion from datatype text to nvarchar is not allowed.
> Use the convert function to run this query.
> When i see the trace file, i see one stored procedure called but no
> lines of code get executed, and immediately after that the ROLLBACK
> TRANSACTION occurs and the applications fails.
> But to my surprise i am able to do the same thing on a different
> machine using the same application and the same database on the same
> server with the same user id.
> Can anyone explain the reason of occurance of this problem.

It seems that the procedure has a parameter of the type nvarchar, but
the application tries to pass a text parameter.

Is that really the same executable you run on the two machines. Or could
it be that they are two different versions, and the bug has been fixed in
one of them?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9415F30B91F6DYazorman@.127.0.0.1>...
> Amit (kumar_amit@.delhi.tcs.co.in) writes:
> > I am facing a problem while using SQL Server with VB application.
> > Implicit conversion from datatype text to nvarchar is not allowed.
> > Use the convert function to run this query.
> > When i see the trace file, i see one stored procedure called but no
> > lines of code get executed, and immediately after that the ROLLBACK
> > TRANSACTION occurs and the applications fails.
> > But to my surprise i am able to do the same thing on a different
> > machine using the same application and the same database on the same
> > server with the same user id.
> > Can anyone explain the reason of occurance of this problem.
> It seems that the procedure has a parameter of the type nvarchar, but
> the application tries to pass a text parameter.
> Is that really the same executable you run on the two machines. Or could
> it be that they are two different versions, and the bug has been fixed in
> one of them?

No both the executables are exactly the same, and we are not passing
any text parameters via the application.

Also i found that there may be a problem due to some ODBC drivers not
able to adjust with the UNICODE datatypes. Because if the change the
datatype to non-Unicode then things work on fine. Now i want to know
what may the specific reason for the problems with non-unicode
datatypes...may be the windows NT or the ODBC drivers are the catch.
But i am not sure what and where to search for these.

Thanks,
Amit Kumar|||Amit (kumar_amit@.delhi.tcs.co.in) writes:
> Also i found that there may be a problem due to some ODBC drivers not
> able to adjust with the UNICODE datatypes. Because if the change the
> datatype to non-Unicode then things work on fine. Now i want to know
> what may the specific reason for the problems with non-unicode
> datatypes...may be the windows NT or the ODBC drivers are the catch.
> But i am not sure what and where to search for these.

Sounds like you should make sure that the machines have some good version
of the MDAC installed. Search around at www.microsoft.com. The MDAC
Component Checker can be a good thing to start with, although I've found
that it can get confused, if you have some newer version it does not
know about.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9417425FAA96Yazorman@.127.0.0.1>...
> Amit (kumar_amit@.delhi.tcs.co.in) writes:
> > Also i found that there may be a problem due to some ODBC drivers not
> > able to adjust with the UNICODE datatypes. Because if the change the
> > datatype to non-Unicode then things work on fine. Now i want to know
> > what may the specific reason for the problems with non-unicode
> > datatypes...may be the windows NT or the ODBC drivers are the catch.
> > But i am not sure what and where to search for these.
> Sounds like you should make sure that the machines have some good version
> of the MDAC installed. Search around at www.microsoft.com. The MDAC
> Component Checker can be a good thing to start with, although I've found
> that it can get confused, if you have some newer version it does not
> know about.

I have already tried re-installation of the MDAC 2.5 but it did not
help me.
Can you give me idea if the regional settinngs could have created the
problem.
If yes, what must be the settings that creates this problem.

Also i found out that the Active Code Pages (ACP) also has an affect
and could be a reason for this problem. Do you have any idea of Active
Code Pages.

Thanks & Regards,
Amit|||Amit (kumar_amit@.delhi.tcs.co.in) writes:
> I have already tried re-installation of the MDAC 2.5 but it did not
> help me.
> Can you give me idea if the regional settinngs could have created the
> problem.
> If yes, what must be the settings that creates this problem.
> Also i found out that the Active Code Pages (ACP) also has an affect
> and could be a reason for this problem. Do you have any idea of Active
> Code Pages.

I thought ACP was for ANSI Code Page, but I could be wrong.

I don't really see where the regional settings would come in here, but
I've been wrong before.

Anyway, I have to admit that I am bit stumped. Is it possible for you
to share the source code that is causing the problem, both on the client
side and SQL Server side?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Implicit conversion from datatype nvarchar to varbinary(MAX)

I am runnning SQL Server 2005...one of my columns is set to Varbinary(MAX), but when I try and set it to null, I get this error, when calling a stored procedure from C#.

'Implicit conversion from datatype nvarchar to varbinary(MAX) is not allowed. Use the CONVERT function to run this query.'

Thanks

Murph

are you sure you are passing null and not 'null' ?

take a look at this

--good
declare @.v Varbinary(MAX)
select @.v = null
select @.v
go

--bad, will fail
declare @.v Varbinary(MAX)
select @.v = 'null'
select @.v
go

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||I am trying to find out...I am using an ORMapper, and I am guessing it is doing something like you mentioned.|||

check your parameter datatype on your C# Code..