Sunday, February 19, 2012
Data Type for multi line text?
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.
Hello Charles,
> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.
|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.co m...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony
Data Type for multi line text?
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.Hello Charles,
> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.com...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony
Tuesday, February 14, 2012
Data Transformation Services.Linked server Error while connecting to Oracle
Hi,
I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.
But when I give import data and give the parameters to hook up the Oracle Server its
throwing a "Linked Server Error and says insufficient previleges"...
Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?
Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error
Thanks in Advance
Kings Indian
Have you set a username and password on the DTS Package?
|||
Pace_uk wrote:
Have you set a username and password on the DTS Package?
Could you explain a bit more on how to do that?
The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.
Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.
Thanks in Advance
|||Pace_uk wrote:
Have you set a username and password on the DTS Package?
Could you explain a bit more on how to do that?
The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.
Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.
Thanks in Advance
|||.|||Kings Indian wrote:
Pace_uk wrote: Have you set a username and password on the DTS Package?
Could you explain a bit more on how to do that?
The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.
Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.
Thanks in Advance
Hello again Kings Indian, my apologise for the time it took to getting back to you.
Ok the best thing to do is go into Design mode of the DTS package.
Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.
I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791
You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.
Anyway I hope this works for you, if not come back and we shall see if we can progress further.
hth
Pace
Data Transformation Services.Linked server Error while connecting to Oracle
Hi,
I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.
But when I give import data and give the parameters to hook up the Oracle Server its
throwing a "Linked Server Error and says insufficient previleges"...
Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?
Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error
Thanks in Advance
Kings Indian
Have you set a username and password on the DTS Package?
|||
Pace_uk wrote:
Have you set a username and password on the DTS Package?
Could you explain a bit more on how to do that?
The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.
Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.
Thanks in Advance
|||Pace_uk wrote:
Have you set a username and password on the DTS Package?
Could you explain a bit more on how to do that?
The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.
Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.
Thanks in Advance
|||.|||Kings Indian wrote:
Pace_uk wrote: Have you set a username and password on the DTS Package?
Could you explain a bit more on how to do that?
The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.
Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.
Thanks in Advance
Hello again Kings Indian, my apologise for the time it took to getting back to you.
Ok the best thing to do is go into Design mode of the DTS package.
Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.
I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791
You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.
Anyway I hope this works for you, if not come back and we shall see if we can progress further.
hth
Pace