Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.
Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools
sql

Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools

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 DT_DBDATE into a SQL TAble with datatype of datetime but without the timestamp

I created a SSIS package and creating a derived column named: Date...set datatype as DT_DBDATE....I do not want the timestamp on date...then I want to load this Date into a SQL server database table, with datatype of datetime, but it will load here with the timestamp which I do not want. Any ideas? I did change datatype of the SQL Server Destination datatype to DT_DBDATE but it will change it back to DT_DBTimestamp. thxSQL Server has no data type that contains just a date. If you insert just a date (insert into table (your_datefield) values ('07/16/2007')) you'll get a result stored in the database as '07/16/2007 00:00:00' or something like that. No way around that at the moment. Keep your eyes on SQL Server 2008 though.

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..