Import PARADOX Problems

Hello,

I've the following problem. I've to read out the data of a custom
application. I think (I'm not sure) this application is using a
Paradox DB to store it's information. I don't know it exactly cause
I'm not familar with paradox at all, but I've found files like *.db,
*.mb, *.px, *.xg*, *.yg* !!!
I need the infomation from this files to combine it with other data
stored in SQL 2000. I'm tried DTS to import the Data but I've got an
"Microsoft JET Database" Error telling me that the table could not be
found or that the table has not the asumed format. It depends on which
*.db File i try to open. Description: Error calling the OPENROWSET
method on the provider.

My Questions, are there some special drivers i need, not included in
MS MDAC Components or must I use a complete Databaseserver in the
Backgroud hosting the DB Files. My developer station has the custom
application NOT installed. The db files seems to have no password set.
I've got the same error when using MS Access to import the data. I
only set the path to the dictionary where the files are in but leave
username and password blank.

HOW TO GET THIS DATA OUT ?

Thank you

TonyTony,

Before wasting a lot more time, get a copy of Paradox and see if
you can open these files. If not, they probably aren't Paradox files.
The only evidence you give that these are unprotected Paradox files
is that they have Paradox extensions and "seem to have no password
set", whatever that means.

The evidence that they are not unprotected Paradox files seems
stronger - dts doesn't understand them.

I've used the Import/Export Data wizard with the Microsoft Driver
for Paradox successfully, but if you still think these may be Paradox
files, see if Paradox agrees with you.

-- Steve Kass
-- Drew University
-- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104

TonyMontana wrote:
> Hello,
> I've the following problem. I've to read out the data of a custom
> application. I think (I'm not sure) this application is using a
> Paradox DB to store it's information. I don't know it exactly cause
> I'm not familar with paradox at all, but I've found files like *.db,
> *.mb, *.px, *.xg*, *.yg* !!!
> I need the infomation from this files to combine it with other data
> stored in SQL 2000. I'm tried DTS to import the Data but I've got an
> "Microsoft JET Database" Error telling me that the table could not be
> found or that the table has not the asumed format. It depends on which
> *.db File i try to open. Description: Error calling the OPENROWSET
> method on the provider.
> My Questions, are there some special drivers i need, not included in
> MS MDAC Components or must I use a complete Databaseserver in the
> Backgroud hosting the DB Files. My developer station has the custom
> application NOT installed. The db files seems to have no password set.
> I've got the same error when using MS Access to import the data. I
> only set the path to the dictionary where the files are in but leave
> username and password blank.
> HOW TO GET THIS DATA OUT ?
> Thank you
> Tony|||Hello Steve,

thanks for your help. I've found a bde installation and now i can
access the tables via the SQL Tool for BDE by Stefan Bodingh www.bodingh.se
so I can assume that the data is not encrypted and not currupt.
Nevertheless i can't
access them via ODBC or the MS PAradox driver. I've tried it on 2 machines.
Is the a command line tool out there which i can use to export the tables to
text [csv] Files? Pherhaps a special one for bde without so much
incompability as the ODBC connection seems to have.

regards

Tony

"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:yGt5b.27082$Om1.16634@.newsread2.news.atl.eart hlink.net...
> Tony,
> Before wasting a lot more time, get a copy of Paradox and see if
> you can open these files. If not, they probably aren't Paradox files.
> The only evidence you give that these are unprotected Paradox files
> is that they have Paradox extensions and "seem to have no password
> set", whatever that means.
> The evidence that they are not unprotected Paradox files seems
> stronger - dts doesn't understand them.
> I've used the Import/Export Data wizard with the Microsoft Driver
> for Paradox successfully, but if you still think these may be Paradox
> files, see if Paradox agrees with you.
> -- Steve Kass
> -- Drew University
> -- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104
> TonyMontana wrote:
> > Hello,
> > I've the following problem. I've to read out the data of a custom
> > application. I think (I'm not sure) this application is using a
> > Paradox DB to store it's information. I don't know it exactly cause
> > I'm not familar with paradox at all, but I've found files like *.db,
> > *.mb, *.px, *.xg*, *.yg* !!!
> > I need the infomation from this files to combine it with other data
> > stored in SQL 2000. I'm tried DTS to import the Data but I've got an
> > "Microsoft JET Database" Error telling me that the table could not be
> > found or that the table has not the asumed format. It depends on which
> > *.db File i try to open. Description: Error calling the OPENROWSET
> > method on the provider.
> > My Questions, are there some special drivers i need, not included in
> > MS MDAC Components or must I use a complete Databaseserver in the
> > Backgroud hosting the DB Files. My developer station has the custom
> > application NOT installed. The db files seems to have no password set.
> > I've got the same error when using MS Access to import the data. I
> > only set the path to the dictionary where the files are in but leave
> > username and password blank.
> > HOW TO GET THIS DATA OUT ?
> > Thank you
> > Tony|||Tony,

I think you might find some people who know in a Paradox
newsgroup. I'm not sure why the MS driver doesn't work.
It only lists Paradox versions up to 5.0, though, so perhaps
can't handle recent versions.

SK

Tony.Montana wrote:
> Hello Steve,
> thanks for your help. I've found a bde installation and now i can
> access the tables via the SQL Tool for BDE by Stefan Bodingh www.bodingh.se
> so I can assume that the data is not encrypted and not currupt.
> Nevertheless i can't
> access them via ODBC or the MS PAradox driver. I've tried it on 2 machines.
> Is the a command line tool out there which i can use to export the tables to
> text [csv] Files? Pherhaps a special one for bde without so much
> incompability as the ODBC connection seems to have.
> regards
> Tony
> "Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
> news:yGt5b.27082$Om1.16634@.newsread2.news.atl.eart hlink.net...
>>Tony,
>>
>> Before wasting a lot more time, get a copy of Paradox and see if
>>you can open these files. If not, they probably aren't Paradox files.
>> The only evidence you give that these are unprotected Paradox files
>>is that they have Paradox extensions and "seem to have no password
>>set", whatever that means.
>>
>> The evidence that they are not unprotected Paradox files seems
>>stronger - dts doesn't understand them.
>>
>> I've used the Import/Export Data wizard with the Microsoft Driver
>>for Paradox successfully, but if you still think these may be Paradox
>>files, see if Paradox agrees with you.
>>
>>-- Steve Kass
>>-- Drew University
>>-- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104
>>
>>TonyMontana wrote:
>>
>>>Hello,
>>>
>>>I've the following problem. I've to read out the data of a custom
>>>application. I think (I'm not sure) this application is using a
>>>Paradox DB to store it's information. I don't know it exactly cause
>>>I'm not familar with paradox at all, but I've found files like *.db,
>>>*.mb, *.px, *.xg*, *.yg* !!!
>>>I need the infomation from this files to combine it with other data
>>>stored in SQL 2000. I'm tried DTS to import the Data but I've got an
>>>"Microsoft JET Database" Error telling me that the table could not be
>>>found or that the table has not the asumed format. It depends on which
>>>*.db File i try to open. Description: Error calling the OPENROWSET
>>>method on the provider.
>>>
>>>My Questions, are there some special drivers i need, not included in
>>>MS MDAC Components or must I use a complete Databaseserver in the
>>>Backgroud hosting the DB Files. My developer station has the custom
>>>application NOT installed. The db files seems to have no password set.
>>>I've got the same error when using MS Access to import the data. I
>>>only set the path to the dictionary where the files are in but leave
>>>username and password blank.
>>>
>>>HOW TO GET THIS DATA OUT ?
>>>
>>>Thank you
>>>
>>>Tony
>>|||Steve,

I am almost sure the Microsoft stuff read only to what are called LEVEL 5
databases (basically Paradox Version 5 and below). There was Paradox 7, 9,
and 10. Which I think are LEVEL 7 databases. Probably what happend was
that the developer put some field types that are only supported in LEVEL 7
and up.

Oscar...
"Steve Kass" <skass@.drew.edu> wrote in message
news:mpN5b.30725$Om1.4521@.newsread2.news.atl.earth link.net...
> Tony,
> I think you might find some people who know in a Paradox
> newsgroup. I'm not sure why the MS driver doesn't work.
> It only lists Paradox versions up to 5.0, though, so perhaps
> can't handle recent versions.
> SK
> Tony.Montana wrote:
> > Hello Steve,
> > thanks for your help. I've found a bde installation and now i can
> > access the tables via the SQL Tool for BDE by Stefan Bodingh
www.bodingh.se
> > so I can assume that the data is not encrypted and not currupt.
> > Nevertheless i can't
> > access them via ODBC or the MS PAradox driver. I've tried it on 2
machines.
> > Is the a command line tool out there which i can use to export the
tables to
> > text [csv] Files? Pherhaps a special one for bde without so much
> > incompability as the ODBC connection seems to have.
> > regards
> > Tony
> > "Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
> > news:yGt5b.27082$Om1.16634@.newsread2.news.atl.eart hlink.net...
> >>Tony,
> >>
> >> Before wasting a lot more time, get a copy of Paradox and see if
> >>you can open these files. If not, they probably aren't Paradox files.
> >> The only evidence you give that these are unprotected Paradox files
> >>is that they have Paradox extensions and "seem to have no password
> >>set", whatever that means.
> >>
> >> The evidence that they are not unprotected Paradox files seems
> >>stronger - dts doesn't understand them.
> >>
> >> I've used the Import/Export Data wizard with the Microsoft Driver
> >>for Paradox successfully, but if you still think these may be Paradox
> >>files, see if Paradox agrees with you.
> >>
> >>-- Steve Kass
> >>-- Drew University
> >>-- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104
> >>
> >>TonyMontana wrote:
> >>
> >>>Hello,
> >>>
> >>>I've the following problem. I've to read out the data of a custom
> >>>application. I think (I'm not sure) this application is using a
> >>>Paradox DB to store it's information. I don't know it exactly cause
> >>>I'm not familar with paradox at all, but I've found files like *.db,
> >>>*.mb, *.px, *.xg*, *.yg* !!!
> >>>I need the infomation from this files to combine it with other data
> >>>stored in SQL 2000. I'm tried DTS to import the Data but I've got an
> >>>"Microsoft JET Database" Error telling me that the table could not be
> >>>found or that the table has not the asumed format. It depends on which
> >>>*.db File i try to open. Description: Error calling the OPENROWSET
> >>>method on the provider.
> >>>
> >>>My Questions, are there some special drivers i need, not included in
> >>>MS MDAC Components or must I use a complete Databaseserver in the
> >>>Backgroud hosting the DB Files. My developer station has the custom
> >>>application NOT installed. The db files seems to have no password set.
> >>>I've got the same error when using MS Access to import the data. I
> >>>only set the path to the dictionary where the files are in but leave
> >>>username and password blank.
> >>>
> >>>HOW TO GET THIS DATA OUT ?
> >>>
> >>>Thank you
> >>>
> >>>Tony
> >sql

Import of XML data.

Dear all,
I have been asked to look into the following.
Data is delivered in XML format.
(Assume wel formed XML and the stylesheet is present).
This data has to be imported in the database.
What are the possibilities in 2005?
What are the possibilities in 2000?
Especially are there enough possibilities to import the XML data 2000. Or do
we need a special workaround voor 2000 ?
My experience with XML is very limited. Once the data is present in the
database I'll will be able to transform the data in such a way that it fits
in the target tables.
Thanks for your time and attention,
Ben Brugman"ben brugman" <ben@.niethier.nl> wrote in message
news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
> Dear all,
> I have been asked to look into the following.
> Data is delivered in XML format.
> (Assume wel formed XML and the stylesheet is present).
> This data has to be imported in the database.
> What are the possibilities in 2005?
> What are the possibilities in 2000?
> Especially are there enough possibilities to import the XML data 2000. Or
> do we need a special workaround voor 2000 ?
> My experience with XML is very limited. Once the data is present in the
> database I'll will be able to transform the data in such a way that it
> fits in the target tables.
> Thanks for your time and attention,
> Ben Brugman
>
2005 has more XML related features, but you can import the data into 2000 as
well.
Take a look at the OPENXML command in the Books Online. You can use those
queries to pull the data from the XML document into whatever format you
wish.
Rick Sawtell|||Thank you, I hadn't thought about this possibility.
Does this say that I can not import directly from XML into 2000?
Because then I do not have to look further into that ally.
Thanks for your time and suggestion,
Ben Brugman
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> schreef in bericht
news:Op%232VF9oIHA.4912@.TK2MSFTNGP03.phx.gbl...
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
>> Dear all,
>> I have been asked to look into the following.
>> Data is delivered in XML format.
>> (Assume wel formed XML and the stylesheet is present).
>> This data has to be imported in the database.
>> What are the possibilities in 2005?
>> What are the possibilities in 2000?
>> Especially are there enough possibilities to import the XML data 2000. Or
>> do we need a special workaround voor 2000 ?
>> My experience with XML is very limited. Once the data is present in the
>> database I'll will be able to transform the data in such a way that it
>> fits in the target tables.
>> Thanks for your time and attention,
>> Ben Brugman
> 2005 has more XML related features, but you can import the data into 2000
> as well.
> Take a look at the OPENXML command in the Books Online. You can use those
> queries to pull the data from the XML document into whatever format you
> wish.
>
> Rick Sawtell
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:7c959$480ce938$53557893$11072@.cache90.multikabel.net...
> Thank you, I hadn't thought about this possibility.
> Does this say that I can not import directly from XML into 2000?
> Because then I do not have to look further into that ally.
> Thanks for your time and suggestion,
> Ben Brugman
>
That depends on your needs. You could put an XML column into a text/ntext
or sufficiently large varchar/nvarchar field in SQL Server 2000. But then
you would be treating the XML as a single column in a table. This can also
be done in 2005, but as an XML data type rather than string datatypes listed
above. 2005 also has other advantages like binding an XSD to the XML data
type.
In order to map the columns in a database table(s) to specific nodes in the
xml object, you would need to use the OPENXML with some XPATH queries.
It is relatively straightforward.
As far as outputting XML, there are several avenues for you to pursue. This
includes the SELECT ... FOR XML scenario as well as some others.
HTH
Rick Sawtell

import of multiple CSV files in one MS SQL table

Hi all,

I have de following application to do :

I receive several .csv files from another application in a determined folder
of my PC.

Those files are named with the format log1.csv logs2.csv logs...
The number of file is variable but the internal format is always : time_sec;level
So the files content a field that may be used as unique key in the target database.

I'm trying to build a DTS package that should import periodically
all the CSV's present in the folder and then destroy them if done
successfully.

Apparently its not so simple than I supposed. I have always to give the name
of the table I want to import.

any idea?"Laurent" <autplc@.hotmail.com> wrote in message
news:e8bac87e.0411100552.5af177ca@.posting.google.c om...
> I receive several .csv files from another application in a determined
folder
> of my PC.
> Those files are named with the format log1.csv logs2.csv logs...
> The number of file is variable but the internal format is always :
time_sec;level
> So the files content a field that may be used as unique key in the target
database.
>
> I'm trying to build a DTS package that should import periodically
> all the CSV's present in the folder and then destroy them if done
> successfully.

Two Options:

Option 1:
Rename the file to a temporary name (in an ActiveX task) and use the
temporary name for the Data Pump

Option 2:
Use a Dynamic Properties Task to change the Data Source Name in the Data
Pump Task.

Regards,
Jim

IMPORT new Data since last IMPORT - DTS/Stored Procs?

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!I wouldn't use a trigger.

You can use DTS to call stored procedures, so you can just do it all in one DTS package.

'a week ago and leave behin the OLD data' You need to filter the data coming in. You can pass variables to DTS jobs to alter the way they execute. I think you could pass in a variable to alter the where clause within the query you specify for DTS. I haven't used variables for that reason, so I 'm not sure. Anyway, schedule that with a job and pass in the appropriate date for your filter. That would get the import into SQL. Then run whatever procedures you need to clean up the data, check for validity, etc. Then call a step to export records based on whatever criteria. I'd do it all in one DTS package.

Here's a place to start
http://www.swynk.com/friends/green/DTSHowTo3.asp|||What DTS book do you recommend I acquire to learn more about passing variables to DTS and other related topics?

I also referenced the URL you gave, but I didn't believe it was too helpful in my case.

Thanks.|||I'm a google junkie; I don't have any books on DTS.

http://www.databasejournal.com/features/mssql/article.php/1461501

This link is on the page of the first link and gets a little bit more there. You have to use the same concept as in these examples.

What I would do is this:

1) create a DTS package that uses a query to pull from the oracle database. Just a canned one that has your desired date range specified so you can see what your where clause will look like.

2) save the package to a Visual Basic File. Open that file in Notepad and find the query you specified and you can see what all the object names/details are that you'll need to reference

3) Follow the examples in these two links to create an activex task in your package that will alter the query with variable that you pass into it. Actually, you don't need to pass in variables if you don't want. You could just put the logic into the activex task that determines, based on the current date, what the date range should be. It's just VBScript, so you have all the date/time functions available.

I know this is a little sketchy, but I used these examples to figure it out the first time so they do help. Swynk has some good stuff so you could search their site or join their forum also.

import multiple XML files at once

Hi,

I have about 300-400 XML files I want to load in my SQL database (2005). The following code will load one (1) file. How do i do a mulitple collections?

INSERT INTO MEL (DATA)SELECT *FROM OPENROWSET (BULK
'C:\Temp\CHAPTER1.xml', SINGLE_BLOB)AS TEMP

Thanks,

http://www.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp

See this article.. i think ur schema should be same for each file...

|||

Thank you for the help. This script runs in VBS, how do I do this in VB.NET or from with SQL (stored procedure)? Also I am looking to import the XML as RAW XML. My XML files are large and complex and I want to store them in a table with TYPE of the field "XML".

Thanks Again,

Bones

|||

Example to use the below stored proc

-- Listing 2

CREATE TABLE #Files (MyFile varchar(200))

CREATE TABLE #Lines (MyLine varchar(8000))

DECLARE @.MyFile varchar(200), @.SQL varchar(2000), @.Path varchar(400)

SET @.Path = 'C:\Program Files\Microsoft SQL Server\MSSQL\'

EXECUTE sp_ListFiles @.Path,'#Files','%.txt',NULL,0

SELECT @.MyFile = MyFile FROM #Files WHERE MyFile LIKE 'README%'

SET @.SQL = 'BULK INSERT #Lines FROM ' + CHAR(39) + @.Path + @.MyFile + CHAR(39)

EXECUTE (@.SQL)

SELECT * FROM #Lines

DROP TABLE #Files

DROP TABLE #Lines

1---------------------23StoredProcedure:sp_ListFiles45---------------------6789USE master10GO11CREATE PROCEDURE dbo.sp_ListFiles12 @.PCWritevarchar(2000),13 @.DBTablevarchar(100)=NULL,14 @.PCIntravarchar(100)=NULL,15 @.PCExtravarchar(100)=NULL,16 @.DBUltrabit = 017AS1819SET NOCOUNT ON2021DECLARE @.Return int22DECLARE @.Retainint23DECLARE @.Statusint2425SET @.Status = 02627DECLARE @.Taskvarchar(2000)2829DECLARE @.Work varchar(2000)3031DECLARE @.Wishvarchar(2000)3233SET @.Work ='DIR ' +'"' + @.PCWrite +'"'3435CREATE TABLE #DBAZ (Name varchar(400),Work int IDENTITY(1,1))3637INSERT #DBAZEXECUTE @.Return = master.dbo.xp_cmdshell @.Work3839SET @.Retain =@.@.ERROR4041IF @.Status = 0SET @.Status = @.Retain42IF @.Status = 0SET @.Status = @.Return4344IF (SELECTCOUNT(*)FROM #DBAZ) < 44546BEGIN4748 SELECT @.Wish =Name FROM #DBAZWHERE Work = 14950IF @.WishISNULL5152BEGIN5354 RAISERROR ('General error [%d]',16,1,@.Status)5556END5758 ELSE5960 BEGIN6162 RAISERROR (@.Wish,16,1)6364END6566 END6768ELSE6970 BEGIN7172 DELETE #DBAZWHEREISDATE(SUBSTRING(Name,1,10)) = 0ORSUBSTRING(Name,40,1) ='.'ORNameLIKE'%.lnk'7374IF @.DBTableISNULL7576BEGIN7778 SELECTSUBSTRING(Name,40,100)AS Files79FROM #DBAZ80WHERE 0 = 081AND (@.DBUltra = 0ORNameLIKE'%<DIR>%')82AND (@.DBUltra != 0ORNameNOT LIKE'%<DIR>%')83AND (@.PCIntraISNULL ORSUBSTRING(Name,40,100)LIKE @.PCIntra)84AND (@.PCExtraISNULL ORSUBSTRING(Name,40,100)NOT LIKE @.PCExtra)85ORDER BY 18687END8889 ELSE9091 BEGIN9293 SET @.Task =' INSERT ' +REPLACE(@.DBTable,CHAR(32),CHAR(95))94 +' SELECT SUBSTRING(Name,40,100) AS Files'95 +' FROM #DBAZ'96 +' WHERE 0 = 0'97 +CASEWHEN @.DBUltra = 0THEN''ELSE' AND Name LIKE ' +CHAR(39) +'%<DIR>%' +CHAR(39)END98 +CASEWHEN @.DBUltra != 0THEN''ELSE' AND Name NOT LIKE ' +CHAR(39) +'%<DIR>%' +CHAR(39)END99 +CASEWHEN @.PCIntraISNULLTHEN''ELSE' AND SUBSTRING(Name,40,100) LIKE ' +CHAR(39) + @.PCIntra +CHAR(39)END100 +CASEWHEN @.PCExtraISNULLTHEN''ELSE' AND SUBSTRING(Name,40,100) NOT LIKE ' +CHAR(39) + @.PCExtra +CHAR(39)END101 +' ORDER BY 1'102103IF @.Status = 0EXECUTE (@.Task)SET @.Return =@.@.ERROR104105IF @.Status = 0SET @.Status = @.Return106107 END108109 END110111DROP TABLE #DBAZ112113SET NOCOUNT OFF114115RETURN (@.Status)116117GO118119-- Listing 2120

The above proc will return the list of files in a folder. Use the resulSet of this proc and then run a a cursor or loop to execute your procedure

|||

Thanks Satya

You've been very helpful.

Bones

Import large xml document into sql server 2005

Hi,
I am trying to import a large xml document into sql server 2005 from a c#
client.
On the server side, the database has the following structure:
urn varchar[80] : some identifier
xmlCol xml : the xml data
The following code works well for small xml documents, but I get an
OutOfMemoryException with large ones
FileStream sr = new FileStream(@."doc.xml", FileMode.Open);
string urn = @."urn:x-test:111";
SqlCommand cmd = wDbConn.CreateCommand();
cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@.urn, @.xmlCol)";
SqlParameter firstColParameter =
cmd.Parameters.Add("@.urn",SqlDbType.VarChar);
firstColParameter.Value = urn;
SqlParameter secondColParameter = cmd.Parameters.Add("@.xmlCol",
SqlDbType.Variant);
secondColParameter.Value = new SqlXml(sr); ;
cmd.ExecuteNonQuery();
Is there a way to solve this problem ?
Best regards,
Vincent Brunie"Vincent Brunie" <VincentBrunie@.discussions.microsoft.com> wrote in message
news:87DBBD77-9C01-4BCC-BD0C-577576D161C1@.microsoft.com...
> Hi,
> I am trying to import a large xml document into sql server 2005 from a c#
> client.
> On the server side, the database has the following structure:
> urn varchar[80] : some identifier
> xmlCol xml : the xml data
> The following code works well for small xml documents, but I get an
> OutOfMemoryException with large ones
> FileStream sr = new FileStream(@."doc.xml", FileMode.Open);
> string urn = @."urn:x-test:111";
>
> SqlCommand cmd = wDbConn.CreateCommand();
> cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@.urn,
> @.xmlCol)";
>
>
> SqlParameter firstColParameter =
> cmd.Parameters.Add("@.urn",SqlDbType.VarChar);
>
Why are you using VarChar instead of XML for the parameter type?
David|||Use Ntext for the XML parameter if your .Net version is earlier than 2.0.
Pohwan Han. Seoul. Have a nice day.
"Vincent Brunie" <VincentBrunie@.discussions.microsoft.com> wrote in message
news:87DBBD77-9C01-4BCC-BD0C-577576D161C1@.microsoft.com...
> Hi,
> I am trying to import a large xml document into sql server 2005 from a c#
> client.
> On the server side, the database has the following structure:
> urn varchar[80] : some identifier
> xmlCol xml : the xml data
> The following code works well for small xml documents, but I get an
> OutOfMemoryException with large ones
> FileStream sr = new FileStream(@."doc.xml", FileMode.Open);
> string urn = @."urn:x-test:111";
>
> SqlCommand cmd = wDbConn.CreateCommand();
> cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@.urn,
> @.xmlCol)";
>
>
> SqlParameter firstColParameter =
> cmd.Parameters.Add("@.urn",SqlDbType.VarChar);
> firstColParameter.Value = urn;
>
> SqlParameter secondColParameter = cmd.Parameters.Add("@.xmlCol",
> SqlDbType.Variant);
> secondColParameter.Value = new SqlXml(sr); ;
>
> cmd.ExecuteNonQuery();
>
> Is there a way to solve this problem ?
>
> Best regards,
> Vincent Brunie
>|||Hi all,
Hi,
I tried with SqlDbType.xml, SqlDbType.Varchar and SqlDbType.Text and I have
the same problem.
I work with .NET Framework 2.0.
I have the feeling that the whole xml document is loaded into memory before
being sent to the server. Is there a way to avoid this ?
Regards,
Vincent
"Vincent Brunie" wrote:

> Hi,
> I am trying to import a large xml document into sql server 2005 from a c#
> client.
> On the server side, the database has the following structure:
> urn varchar[80] : some identifier
> xmlCol xml : the xml data
> The following code works well for small xml documents, but I get an
> OutOfMemoryException with large ones
> FileStream sr = new FileStream(@."doc.xml", FileMode.Open);
> string urn = @."urn:x-test:111";
>
> SqlCommand cmd = wDbConn.CreateCommand();
> cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@.urn, @.xmlCol)
";
>
>
> SqlParameter firstColParameter =
> cmd.Parameters.Add("@.urn",SqlDbType.VarChar);
> firstColParameter.Value = urn;
>
> SqlParameter secondColParameter = cmd.Parameters.Add("@.xmlCol",
> SqlDbType.Variant);
> secondColParameter.Value = new SqlXml(sr); ;
>
> cmd.ExecuteNonQuery();
>
> Is there a way to solve this problem ?
>
> Best regards,
> Vincent Brunie
>|||Hello Vincent,

> I tried with SqlDbType.xml, SqlDbType.Varchar and SqlDbType.Text and I
> have the same problem.
> I work with .NET Framework 2.0.
> I have the feeling that the whole xml document is loaded into memory
> before being sent to the server. Is there a way to avoid this ?
No, not really, because the instance of XML has to be both valid and complet
e
at the end of the transaction.
However, if you're working with SQL Server 2005 and you can get the file
on to that server, you might try a SQL Query like this:
use scratch
go
create table dbo.xmlLoadExample
(
pkid tinyint identity(1,1) primary key
, doc xml
)
go
insert into dbo.xmlLoadExample(doc)
select * from OpenRowset(bulk N'c:\some.xml',SINGLE_BLOB) as useless
go
select doc from dbo.XmlLoadExample
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Kent,
Thank you for your answer. Do you if there could be a way to put this query
into a stored procedure and to have the procedure read the data from a strea
m
coming from the client instead of a local file ?
Regards,
Vincent
"Kent Tegels" wrote:

> Hello Vincent,
>
> No, not really, because the instance of XML has to be both valid and compl
ete
> at the end of the transaction.
> However, if you're working with SQL Server 2005 and you can get the file
> on to that server, you might try a SQL Query like this:
> use scratch
> go
> create table dbo.xmlLoadExample
> (
> pkid tinyint identity(1,1) primary key
> , doc xml
> )
> go
> insert into dbo.xmlLoadExample(doc)
> select * from OpenRowset(bulk N'c:\some.xml',SINGLE_BLOB) as useless
> go
> select doc from dbo.XmlLoadExample
> go
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello Vincent,

> Thank you for your answer. Do you if there could be a way to put this
> query into a stored procedure and to have the procedure read the data
> from a stream coming from the client instead of a local file ?
Putting the code into a stored procedure is easy. Having the procedure read
from a stream isn't. You can't really pass a stream to SQL Server as there's
no streaming data type.
I'll keep pondering on this though.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?
Do you have an extra line in your text file?

>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>
|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:

>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>
|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> using DTS.
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> last line in the TAB
> into [SIC_ID] for
> is it trying to
> insert?
|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
,
TAB
for
>
|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>
|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
NULL
> ,
> TAB
for
>

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] f
or
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?Do you have an extra line in your text file?

>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:

>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx
.gbl...
> Do you have an extra line in your text file?
>
> using DTS.
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> last line in the TAB
> into [SIC_ID] for
> is it trying to
> insert?|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.
4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
>
,
TAB
for
>|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL[
/color]
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
NULL
> ,
> TAB
for
>

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?Do you have an extra line in your text file?
>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> >--Original Message--
> >I am trying to import data into a table in my database
> using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_80] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very
> last line in the TAB
> >file, giving an error that it cannot insert a NULL value
> into [SIC_ID] for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why
> is it trying to
> >insert a NULL value when none exist in the TAB file to
> insert?
> >
> >
> >.
> >|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
> >I am trying to import data into a table in my database using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very last line in the
TAB
> >file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why is it trying to
> >insert a NULL value when none exist in the TAB file to insert?
> >
>|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> > I am trying to import data into a table in my database using DTS.
> >
> > The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> > The import procedure works, but then fails at the very last line in the
> TAB
> > file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> > the last line in the TAB file.
> >
> > This is correct as the fields are all NOT NULL, but why is it trying to
> > insert a NULL value when none exist in the TAB file to insert?
> >
> >
>

import from Excel error

While attempting to import data from Excel using SSMS and the import/export wizard, I received the following error:

TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{11CD789E-0DCD-48C8-81F9-1065D87B5ADF}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({BB3EBEA7-7F0E-4346-A8A5-60E176732365})

Any clues as what causes this and how to resolve the problem. I am using Office XP.

Do you have Microsoft Jet OLE DB provider installed? It should be there by default. Check whether you are missing msjetoledb40.dll, or whether it is correctly registered may help.

HTH

wenyang

|||The dll is installed and I ran regsvr32; it registered successfully. However, the problem still exists.

Import dedicated Records from a Flatfile to a DB

Hello all

I got a Problem when I try to store Data from a Flatfile to a DB.

The following Error appears in the Progress Control:

An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".

I have a Flat File Source, and would like to store the needed records in a DB.

In Column 0 in the Flatfile I have multiple Entries with equal Values.

In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to matchwith the Flatfile?

Thanks in advance for any answer

Chaepp

Chaepp wrote:

Hello all

I got a Problem when I try to store Data from a Flatfile to a DB.

The following Error appears in the Progress Control:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".

I have a Flat File Source, and would like to store the needed records in a DB.

In Column 0 in the Flatfile I have multiple Entries with equal Values.

In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to match with the Flatfile?

Thanks in advance for any answer

Chaepp

your problem seems to cry out for a lookup transformation. you would need to lookup the primary key in the source to see if it exists. if the key already exists, then redirect the row to the error output where you can stage the data for further processing.|||

Hello,

Thanks for your quick answer.

The DB Table is empty and I like to load the data from the Flatfile....

Then I get the error. I tried to go with Lookup, but I have still the same problem...

Could you give me an idea how to solve the problem?

Thanks for an early answer.

Regards

Chaepp

|||

Chaepp wrote:

Hello,

Thanks for your quick answer.

The DB Table is empty and I like to load the data from the Flatfile....

Then I get the error. I tried to go with Lookup, but I have still the same problem...

Could you give me an idea how to solve the problem?

Thanks for an early answer.

Regards

Chaepp

ok, i'll take another stab at it. you could try to use the ole db transformation to insert the rows, and redirect the error rows.|||

Sorry, I'm a beginner.

What do you mean with 'ole db transformation'? The Ole DB Command?

Regards

Chaepp

|||Hi ,
I like to see how this one pans out, as i cannot see how a Lookup would work. By definition Chaepp is importing to a table where the data does not already exist thus the lookup would fail. He could redirect this 'failure' as his error output, but then all his errors would be cached and thus still fail for the same reason as he would have multiple failures on the same data and thus when the data flow passes into the destination to actually write to his database table, it will still fall over. I could see a Lookup working if it would write each row it did not find to the destination table and thus subsequent finds , ie PK violations, could be re-directed, but only if the table is being updated in real time from the lookup which it does not do. I'm thinking a Script component here, not lookup.

Dave|||I tested this in a script component and got it to work. The input iwas a simple flat file of one column that had a couple of duplicates. Destination was a one colum table with a PK on it. The arraylist checks if the current row exists in the array already, if it does it gets ignored, otherwise we add a row to the output buffer which is then sent down the line to the destination task. This works but I could not get it to work from scratch - I had to cut and paste a script component I'd used before into the package because i could not get a new one to generate the Public Class Output0Buffer.... statements. (These are in the file with ' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! at the top so they are system generated as part of the Script Task, I just need to figure out how i got them generated the first time and then we can get the code below to work from a new Script component)

Dim alist As New ArrayList

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not alist.Contains(Row.Name) Then
alist.Add(Row.Name)
Output0Buffer.AddRow()
Output0Buffer.NameOut = Row.Name
End If
End Sub

the Name property of Row comes from the flat file as the Input column
NameOut of Outpu0Buffer is the Script Output column|||

Hi

Thank you very much for your help.

I have found another solution:

I simply use the Aggregate function and it works perfect....

Regards

Chaepp

|||

Chaepp wrote:

Hi

Thank you very much for your help.

I have found another solution:

I simply use the Aggregate function and it works perfect....

Regards

Chaepp

can you please be a little more specific? i don't see how the aggregate transformation would solve your problem.

btw, by "ole db" i meant "ole db command". sorry about the confusion.

import data from excel to SqlServer

Hello,

I want to import data from an excel sheet to SqlServer...
I use a linked server...
I execute the following code:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyExcel.xls',NULL,
'Excel 5.0'
GO

sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL
GO

SELECT * FROM ExcelSource...Sheet1$
GO

and I get the error:

Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider 'ExcelSource' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ExcelSource', TableName='Sheet1$'].

When I execute the command:

select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls',Sheet1$)

I get the same error...

Can anyone help me?

Thanks
KorinaTry using OPENQUERY.

SELECT * FROM OPENQUERY('ExcelSource','SELECT * FROM SHEET1$')|||I get the error:

Server: Msg 7403, Level 16, State 1, Line 2
Could not locate registry entry for OLE DB provider 'c:\book1.xls'.
OLE DB error trace [Non-interface error: Provider not registered.].

What I am doing wrong?|||Make the following changes.

sp_addlinkedserver 'ExcelSource6',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyExcel.xls',
NULL,
'Excel 8.0'

SELECT * FROM OPENQUERY(ExcelSource6,'SELECT * FROM [Sheet1$]')|||and now I get the error:

Server: Msg 7399, Level 16, State 1, Line 8
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].|||Mine works. A couple of questions for you.

Do you or someone else or another program have the file open?

Do you realize the path you define (c:\MyExcel.xls) is relative to the server and not to your client machine? This the servers C:\ drive.|||The file is close and it is placed on the specified server drive...
Do you have any other idea?

I would be gratefull because I need it as soon as possible.

Thanks|||Take a look at this:

http://support.microsoft.com/default.aspx?scid=314530

Ahhh Google.... Who needs to know anything anymore? programming before high speed internet access was such a pain.

Import CSV file into SQL TABLE

Hi,
i try to import a csv file with tabulation field separator and linefeed as
rowfield.
I've write the following "stored procedure" but it don't import nothing.
My csv file have 41 colums and n rows; the colums is separated by "tab" and
some fields are blank.
Thank you for help!
Gianluca
CREATE PROCEDURE Import
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @.SQL varchar(200)
TRUNCATE TABLE table_csv
SET @.SQL = "BULK INSERT table_csv
FROM 'G:\file_csv.dat'
WITH
( FIELDTERMINATOR = '\t' ,
ROWTERMINATOR = '\n' ,
FIRSTROW = 2
)"
--Step 2: Execute BULK INSERT statement
EXEC (@.SQL)
GOHi,
Try executing the BULK INSERT from Query Analyzer or Query Window (SQL
2005). This will through the exact error.
THanks
Hari
SQL Server MVP
"Gianluca Floris" <gianluca.flo_NOSPAM_ris@.tiscali.it> wrote in message
news:45055ce9$0$998$5fc30a8@.news.tiscali.it...
> Hi,
> i try to import a csv file with tabulation field separator and linefeed as
> rowfield.
> I've write the following "stored procedure" but it don't import nothing.
> My csv file have 41 colums and n rows; the colums is separated by "tab"
> and some fields are blank.
> Thank you for help!
> Gianluca
> --
> CREATE PROCEDURE Import
> AS
> --Step 1: Build Valid BULK INSERT Statement
> DECLARE @.SQL varchar(200)
> TRUNCATE TABLE table_csv
> SET @.SQL = "BULK INSERT table_csv
> FROM 'G:\file_csv.dat'
> WITH
> ( FIELDTERMINATOR = '\t' ,
> ROWTERMINATOR = '\n' ,
> FIRSTROW = 2
> )"
> --Step 2: Execute BULK INSERT statement
> EXEC (@.SQL)
> GO
>
> --
>
>

Import CSV file into SQL TABLE

Hi,
i try to import a csv file with tabulation field separator and linefeed as
rowfield.
I've write the following "stored procedure" but it don't import nothing.
My csv file have 41 colums and n rows; the colums is separated by "tab" and
some fields are blank.
Thank you for help!
Gianluca
--
CREATE PROCEDURE Import
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @.SQL varchar(200)
TRUNCATE TABLE table_csv
SET @.SQL = "BULK INSERT table_csv
FROM 'G:\file_csv.dat'
WITH
( FIELDTERMINATOR = '\t' ,
ROWTERMINATOR = '\n' ,
FIRSTROW = 2
)"
--Step 2: Execute BULK INSERT statement
EXEC (@.SQL)
GO
--Hi,
Try executing the BULK INSERT from Query Analyzer or Query Window (SQL
2005). This will through the exact error.
THanks
Hari
SQL Server MVP
"Gianluca Floris" <gianluca.flo_NOSPAM_ris@.tiscali.it> wrote in message
news:45055ce9$0$998$5fc30a8@.news.tiscali.it...
> Hi,
> i try to import a csv file with tabulation field separator and linefeed as
> rowfield.
> I've write the following "stored procedure" but it don't import nothing.
> My csv file have 41 colums and n rows; the colums is separated by "tab"
> and some fields are blank.
> Thank you for help!
> Gianluca
> --
> CREATE PROCEDURE Import
> AS
> --Step 1: Build Valid BULK INSERT Statement
> DECLARE @.SQL varchar(200)
> TRUNCATE TABLE table_csv
> SET @.SQL = "BULK INSERT table_csv
> FROM 'G:\file_csv.dat'
> WITH
> ( FIELDTERMINATOR = '\t' ,
> ROWTERMINATOR = '\n' ,
> FIRSTROW = 2
> )"
> --Step 2: Execute BULK INSERT statement
> EXEC (@.SQL)
> GO
>
> --
>
>