importing word document into sqlserver

chapter1
Delegates officially are to nominate Cheney as the GOP's vice presidential candidate before he addresses the group Wednesday night.
chapter2
"I think that the vice president's speech tonight is going to be about big issues, the big issues of this campaign -- the war on global terror, the president's education policy, the fact that the economy is turning up again," she told CNN's "American Morning."
chapter3
She said she had known her husband since he was 14 and planned to share anecdotes that many people have not heard before in her introduction.
Chapter4
Maverick Democratic Sen. Zell Miller of Georgia is scheduled to deliver Wednesday's keynote address -- a role he also played at the 1992 Democratic National Convention, which nominated President Clinton.
Chapter5
In the earlier speech, Miller, then governor of Georgia, said that "for 12 dark years, the Republicans have dealt in cynicism and skepticism. They have mastered the art of division and diversion, and they have robbed us of our hope."

I wanted to import the above word doc into my sql server DB.
I have two columns in the table
1.Chaptereid
2.chapter_notes
chater1,chater2,chapte2,chapter3,chapter4 and chapter5 should go into chaptered column and the text followed by the chapter id should be imported into chapter_notes column.

So how can I import this document.

Thanks.Well...

I would say that if you want to work with "Data", you'll need to save the document as pure text or at least as an rtf..

To store a word doc, you'd need to store it in a text or image column...

So assuming you save it to text, I'd start by loadinging in to a table with 2 columns.

One that is an identity column and one thats varchar

However, there is no gaurentee that the rows will "load" themselves in the correct order.

But lets say they do, you could probably find the "row numbers" that make up the segements you are looking for...

Just don't trust it to be 100% all the time.

To 100% your document or text file would need to be tagged with their relative positions.

Also, you might want to look into your content...damn left wing drivel...|||Well...

I would say that if you want to work with "Data", you'll need to save the document as pure text or at least as an rtf..

To store a word doc, you'd need to store it in a text or image column...

So assuming you save it to text, I'd start by loadinging in to a table with 2 columns.

One that is an identity column and one thats varchar

However, there is no gaurentee that the rows will "load" themselves in the correct order.

But lets say they do, you could probably find the "row numbers" that make up the segements you are looking for...

Just don't trust it to be 100% all the time.

To 100% your document or text file would need to be tagged with their relative positions.

Also, you might want to look into your content...damn left wing drivel...

Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.
This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields
I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:
In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"
I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] =
[OldDatabase].[dbo].[Table].[PK]
in which the primary key (an auto-increment integer) is identical
between the new and old tables.
This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.
The same error occurs with different techniques (joins, subqueries,
etc.)
John Hackert wrote:

> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] =
> [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =
Old.[PK]
Regards
Amish Shah
|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
>
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] =
> Old.[PK]
>
> Regards
> Amish Shah
|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:[vbcol=seagreen]
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
>
>
>
>
>
>
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah
|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegro ups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>

Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.
This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields
I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:
In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"
I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] =
[OldDatabase].[dbo].[Table].[PK]
in which the primary key (an auto-increment integer) is identical
between the new and old tables.
This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.
The same error occurs with different techniques (joins, subqueries,
etc.)John Hackert wrote:

> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] =
> [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =
Old.[PK]
Regards
Amish Shah|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
>
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] =
> Old.[PK]
>
> Regards
> Amish Shah|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:[vbcol=seagreen]
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah|||Amish,
I've used aliases in other contexts, but always with the keyword "AS"
- So the "AS" is an optional part of the syntax?
I see now from your example that the failure of the previous queries
related to not specifying the second table in the from clause.
Once again I'm grateful for your help
John
amish wrote:
> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> John
> Asterisk part is just an alias for the table.
> Once you specify alias for the table in form clause of the query you
> can refer it in other part of query instead of table name.
> You have to add second table also in form clause.
> You should change your query to
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] testnew,
> [TestOld].[dbo].[tblProcedure] testold
> WHERE [testnew].procedureID
> =TestOld.procedureID
>
> Regards
> Amish Shah|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegroups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>sql

Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.
This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields
I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:
In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"
I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] = [OldDatabase].[dbo].[Table].[PK]
in which the primary key (an auto-increment integer) is identical
between the new and old tables.
This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.
The same error occurs with different techniques (joins, subqueries,
etc.)John Hackert wrote:
> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] => [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =Old.[PK]
Regards
Amish Shah|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
> > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > had recently submitted this, if anyone would have noted, but I still
> > can't resolve the seemingly simple task of importing from one database
> > to another within even the same instance of SQL Server Express. I
> > would be so grateful if someone could help me:
> >
> > The specific example I was working on involved an attempt to run an
> > update query to place data from a specific field in an older copy of an
> > otherwise identical database into a more recent copy. Ultimately I
> > just plugged the small data series in manually using side-by-side views
> > because I could not overcome the syntax errors generated in the effort
> > to refer to the "external" database.
> >
> > This is the setup in question:
> > - An instance in the format of "MyComputerName\SQLExpress," and both an
> > - "OldDatabase" and
> > - "NewDatabase" attached and viewable from the Management Studio
> > Express, with identical fields
> >
> > I tried a few different query techniques as suggested in historical
> > posts, but to no avail. Here is such an example that I tried:
> >
> > In MSE I right clicked on the target table in question and chose:
> > "Script table as," then
> > "UPDATE to," then
> > "New Query Editor Window"
> >
> > I removed all the fields except that in question and added this Where
> > clause:
> > UPDATE [NewDatabase].[dbo].[Table]
> > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> > WHERE [NewDatabase].[dbo].[Table].[PK] => > [OldDatabase].[dbo].[Table].[PK]
> >
> > in which the primary key (an auto-increment integer) is identical
> > between the new and old tables.
> >
> > This produces the error:
> > Msg 4104, Level 16, State 1, Line 1
> > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> > bound.
> >
> > The same error occurs with different techniques (joins, subqueries,
> > etc.)
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] => Old.[PK]
>
> Regards
> Amish Shah|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
> > John Hackert wrote:
> > > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > > had recently submitted this, if anyone would have noted, but I still
> > > can't resolve the seemingly simple task of importing from one database
> > > to another within even the same instance of SQL Server Express. I
> > > would be so grateful if someone could help me:
> > > The specific example I was working on involved an attempt to run an
> > > update query to place data from a specific field in an older copy of an
> > > otherwise identical database into a more recent copy. Ultimately I
> > > just plugged the small data series in manually using side-by-side views
> > > because I could not overcome the syntax errors generated in the effort
> > > to refer to the "external" database.
> > > This is the setup in question:
> > > - An instance in the format of "MyComputerName\SQLExpress," and both an
> > > - "OldDatabase" and
> > > - "NewDatabase" attached and viewable from the Management Studio
> > > Express, with identical fields
> > > I tried a few different query techniques as suggested in historical
> > > posts, but to no avail. Here is such an example that I tried:
> > > In MSE I right clicked on the target table in question and chose:
> > > "Script table as," then
> > > "UPDATE to," then
> > > "New Query Editor Window"
> > > I removed all the fields except that in question and added this Where
> > > clause:
> > > UPDATE [NewDatabase].[dbo].[Table]
> > > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> > > WHERE [NewDatabase].[dbo].[Table].[PK] => > > [OldDatabase].[dbo].[Table].[PK]
> > > in which the primary key (an auto-increment integer) is identical
> > > between the new and old tables.
> > > This produces the error:
> > > Msg 4104, Level 16, State 1, Line 1
> > > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> > > bound.
> > > The same error occurs with different techniques (joins, subqueries,
> > > etc.)
> > try this
> > USE NewDatabase
> > UPDATE [NewDatabase].[dbo].[Table]
> > SET [Field] = Old.[Field]
> > from [NewDatabase].[dbo].[Table] New inner join
> > [OldDatabase].[dbo].[Table] Old
> > on
> > New.[PK] => > Old.[PK]
> > Regards
> > Amish Shah
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah|||Amish,
I've used aliases in other contexts, but always with the keyword "AS"
- So the "AS" is an optional part of the syntax?
I see now from your example that the failure of the previous queries
related to not specifying the second table in the from clause.
Once again I'm grateful for your help
John
amish wrote:
> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> > Amish,
> >
> > Thank you so much for your help. I set up two test databases,
> > "TestNew" and "TestOld" with parallel tables and test data. I found
> > that the syntax you proposed worked:
> >
> > UPDATE [TestNew].[dbo].[tblProcedure]
> > SET [CPT] = TestOld.[CPT]
> > FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> > [TestOld].[dbo].[tblProcedure] TestOld
> > on
> > TestNew.[procedureID]=TestOld.[procedureID]
> >
> > Whereas syntax such as this did not work:
> >
> > UPDATE [TestNew].[dbo].[tblProcedure]
> > SET [CPT] = TestOld.[CPT]
> > FROM [TestNew].[dbo].[tblProcedure]
> > WHERE [Testnew].[dbo].[tblProcedure].procedureID
> > =[TestOld].[dbo].[tblProcedure].procedureID
> >
> > The following error was generated:
> > Msg 4104, Level 16, State 1, Line 1
> > The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> > not be bound.
> >
> > SQL Server is relatively new to me, and the syntax/manipulations often
> > seem cryptic. May I ask you to help me understand the asterisked parts
> > of the construction:
> >
> > FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> > [TestOld].[dbo].[tblProcedure] *TestOld*
> > on
> > TestNew.[procedureID]=TestOld.[procedureID]
> >
> > Thanks,
> >
> > John
> >
> > amish wrote:
> > > John Hackert wrote:
> >
> > > > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > > > had recently submitted this, if anyone would have noted, but I still
> > > > can't resolve the seemingly simple task of importing from one database
> > > > to another within even the same instance of SQL Server Express. I
> > > > would be so grateful if someone could help me:
> >
> > > > The specific example I was working on involved an attempt to run an
> > > > update query to place data from a specific field in an older copy of an
> > > > otherwise identical database into a more recent copy. Ultimately I
> > > > just plugged the small data series in manually using side-by-side views
> > > > because I could not overcome the syntax errors generated in the effort
> > > > to refer to the "external" database.
> >
> > > > This is the setup in question:
> > > > - An instance in the format of "MyComputerName\SQLExpress," and both an
> > > > - "OldDatabase" and
> > > > - "NewDatabase" attached and viewable from the Management Studio
> > > > Express, with identical fields
> >
> > > > I tried a few different query techniques as suggested in historical
> > > > posts, but to no avail. Here is such an example that I tried:
> >
> > > > In MSE I right clicked on the target table in question and chose:
> > > > "Script table as," then
> > > > "UPDATE to," then
> > > > "New Query Editor Window"
> >
> > > > I removed all the fields except that in question and added this Where
> > > > clause:
> > > > UPDATE [NewDatabase].[dbo].[Table]
> > > > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> > > > WHERE [NewDatabase].[dbo].[Table].[PK] => > > > [OldDatabase].[dbo].[Table].[PK]
> >
> > > > in which the primary key (an auto-increment integer) is identical
> > > > between the new and old tables.
> >
> > > > This produces the error:
> > > > Msg 4104, Level 16, State 1, Line 1
> > > > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> > > > bound.
> >
> > > > The same error occurs with different techniques (joins, subqueries,
> > > > etc.)
> >
> > > try this
> >
> > > USE NewDatabase
> >
> > > UPDATE [NewDatabase].[dbo].[Table]
> > > SET [Field] = Old.[Field]
> > > from [NewDatabase].[dbo].[Table] New inner join
> > > [OldDatabase].[dbo].[Table] Old
> > > on
> > > New.[PK] => > > Old.[PK]
> >
> > > Regards
> > > Amish Shah
> John
> Asterisk part is just an alias for the table.
> Once you specify alias for the table in form clause of the query you
> can refer it in other part of query instead of table name.
> You have to add second table also in form clause.
> You should change your query to
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] testnew,
> [TestOld].[dbo].[tblProcedure] testold
> WHERE [testnew].procedureID
> =TestOld.procedureID
>
> Regards
> Amish Shah|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegroups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
>> > Amish,
>> >
>> > Thank you so much for your help. I set up two test databases,
>> > "TestNew" and "TestOld" with parallel tables and test data. I found
>> > that the syntax you proposed worked:
>> >
>> > UPDATE [TestNew].[dbo].[tblProcedure]
>> > SET [CPT] = TestOld.[CPT]
>> > FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
>> > [TestOld].[dbo].[tblProcedure] TestOld
>> > on
>> > TestNew.[procedureID]=TestOld.[procedureID]
>> >
>> > Whereas syntax such as this did not work:
>> >
>> > UPDATE [TestNew].[dbo].[tblProcedure]
>> > SET [CPT] = TestOld.[CPT]
>> > FROM [TestNew].[dbo].[tblProcedure]
>> > WHERE [Testnew].[dbo].[tblProcedure].procedureID
>> > =[TestOld].[dbo].[tblProcedure].procedureID
>> >
>> > The following error was generated:
>> > Msg 4104, Level 16, State 1, Line 1
>> > The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
>> > not be bound.
>> >
>> > SQL Server is relatively new to me, and the syntax/manipulations often
>> > seem cryptic. May I ask you to help me understand the asterisked parts
>> > of the construction:
>> >
>> > FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
>> > [TestOld].[dbo].[tblProcedure] *TestOld*
>> > on
>> > TestNew.[procedureID]=TestOld.[procedureID]
>> >
>> > Thanks,
>> >
>> > John
>> >
>> > amish wrote:
>> > > John Hackert wrote:
>> >
>> > > > I'm sorry to repost this from microsoft.public.sqlserver.msde where
>> > > > I
>> > > > had recently submitted this, if anyone would have noted, but I
>> > > > still
>> > > > can't resolve the seemingly simple task of importing from one
>> > > > database
>> > > > to another within even the same instance of SQL Server Express. I
>> > > > would be so grateful if someone could help me:
>> >
>> > > > The specific example I was working on involved an attempt to run an
>> > > > update query to place data from a specific field in an older copy
>> > > > of an
>> > > > otherwise identical database into a more recent copy. Ultimately I
>> > > > just plugged the small data series in manually using side-by-side
>> > > > views
>> > > > because I could not overcome the syntax errors generated in the
>> > > > effort
>> > > > to refer to the "external" database.
>> >
>> > > > This is the setup in question:
>> > > > - An instance in the format of "MyComputerName\SQLExpress," and
>> > > > both an
>> > > > - "OldDatabase" and
>> > > > - "NewDatabase" attached and viewable from the Management Studio
>> > > > Express, with identical fields
>> >
>> > > > I tried a few different query techniques as suggested in historical
>> > > > posts, but to no avail. Here is such an example that I tried:
>> >
>> > > > In MSE I right clicked on the target table in question and chose:
>> > > > "Script table as," then
>> > > > "UPDATE to," then
>> > > > "New Query Editor Window"
>> >
>> > > > I removed all the fields except that in question and added this
>> > > > Where
>> > > > clause:
>> > > > UPDATE [NewDatabase].[dbo].[Table]
>> > > > SET [Field] = [OldDatabase].[dbo].[Table].[Field]
>> > > > WHERE [NewDatabase].[dbo].[Table].[PK] =>> > > > [OldDatabase].[dbo].[Table].[PK]
>> >
>> > > > in which the primary key (an auto-increment integer) is identical
>> > > > between the new and old tables.
>> >
>> > > > This produces the error:
>> > > > Msg 4104, Level 16, State 1, Line 1
>> > > > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
>> > > > bound.
>> >
>> > > > The same error occurs with different techniques (joins, subqueries,
>> > > > etc.)
>> >
>> > > try this
>> >
>> > > USE NewDatabase
>> >
>> > > UPDATE [NewDatabase].[dbo].[Table]
>> > > SET [Field] = Old.[Field]
>> > > from [NewDatabase].[dbo].[Table] New inner join
>> > > [OldDatabase].[dbo].[Table] Old
>> > > on
>> > > New.[PK] =>> > > Old.[PK]
>> >
>> > > Regards
>> > > Amish Shah
>> John
>> Asterisk part is just an alias for the table.
>> Once you specify alias for the table in form clause of the query you
>> can refer it in other part of query instead of table name.
>> You have to add second table also in form clause.
>> You should change your query to
>> UPDATE [TestNew].[dbo].[tblProcedure]
>> SET [CPT] = TestOld.[CPT]
>> FROM [TestNew].[dbo].[tblProcedure] testnew,
>> [TestOld].[dbo].[tblProcedure] testold
>> WHERE [testnew].procedureID
>> =TestOld.procedureID
>>
>> Regards
>> Amish Shah
>

Importing tables into SQLServer

Hi, I've just got my brand new shiny SQLServer and would like to set some new tables set up etc. But I'm really lazy and I was wondering if there is any way of importing table layouts, coulmns, whole structure type thing into SQLServer from Microsoft Access using DTS? I know it's a bit optimistic, but just wondered...

Any help gratefully accepted.

Thanks,
Paula.DTS could do it, but it would be ugly. I'd suggest the Upsizing Wizard (http://support.microsoft.com/default.aspx?scid=kb;en-us;325017) for this job.

-PatP

Importing multiple files to SQL

I have over three hundred text files that I need to import to SQL
Server.
Each is in the exact same format.
I want to import tham as seperate tables.
Is there any way to do it in one process?

Regards,
CiarnYou can use DTS:
http://www.sqldts.com/default.aspx?246

If all the files are the same format then why not import them to a
single table with an extra column to identify the source file? That
should be much more convenient than creating 300 separate tables.

--
David Portas
SQL Server MVP
--|||I use the above method, but if you want seperate tables:

You could add in dynamic properties and some scripts to the workflow of
the DTS. A script task sets a source filename dynamic property. A
create table task uses the dynamic property as the table name. Then
import into the new table from the file(again using the dynamic
properties to set the source filename and destination table name).

Using only one table will make some things much easier, and other
things harder. Consider that with multiple tables you will have to
either have a copy of all your queries for each table, or have a stored
procedure that allows the table name to be specified as a parameter. I
think it would be easier to put all the data in one table and have a
sproc that takes the code for the special column as one of the
filtering criteria. I think the syntax for specifying the criteria for
that column is no harder than specifying a table name.|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?

Importing MsAccess data To SqlServer

Hi
I am trying to importing the Access Tables and Queryes to Sql Server But I cannot eable to Import the Queryes. What could be the reason.
Should I make the queryes as some seperate format?
Please Help me.Sorry, they don't go. The syntax is different. When you upsize the database from access, the tables will go fine. As for the queries, here is something you can do. Open both access and SQL. Using the SQL pane copy each and every access query in SQL, then paste them into SQL. Then run the query and fix the small amount syntax difference in SQL and Access.

Hope this helps.

Terry

Importing MSAccess data into SQL Tables

I am attempting to import data from an MS Access 2000 table into a SQL
Server 2000 table, but NULL values in the Access table are causing the DTS
import to fail.
I thought that if I defined a column in SQL as 'NOT NULL', but assigned a
default value of '', then it would allow an import containing a NULL, which
it would convert to a ''. But I guess I was wrong? Is there is a way to
import MSAccess data containing nulls into a SQL table where the nulls would
be automatically be converted to ''?
Thanks!
Mark
Here is the definition for the SQL table:
CREATE TABLE [dbo].[creditcard] (
[CARD_NAME] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CARD_ABB] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACTIVE] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[creditcard] ADD
CONSTRAINT [DF_creditcard_CARD_NAME] DEFAULT ('') FOR [CARD_NAME],
CONSTRAINT [DF_creditcard_CARD_ABB] DEFAULT ('') FOR [CARD_ABB],
CONSTRAINT [DF_creditcard_ACTIVE] DEFAULT ('') FOR [ACTIVE]
GO
Mark,
make sure that on the options tab of the transform data task, 'check
constraints' is enabled.
HTH,
Paul Ibison
|||I'm sorry but I don't see the option 'check constraints'. I am running the
'Data Transformation Services Import/Export wizard'.
Here are the screens I see:
1) Choose a data source: I choose the access database
2) Choose a destination: I choose the target database
3) Specify Table Copy or Query: I choose 'Copy table(s) and view(s) from the
source database
4) Select Source Tables and Views: I select my source table and target table
here
5) Save, schedule and replicate package: I choose 'Run Immediately'
6) Completing the DTS Import/Export Wizard: I choose 'Finish' to run the
import.
At this point it appears to import a number of records and then I get the
popup indicating that it can not import the record with the NULL value in
the column.
In all of that I'm afraid I could not see a transform data task. I hate to
be dense, but could you elaborate on where I should look for that?
Many thanks!
Mark
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eNw%23ZyPQEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Mark,
> make sure that on the options tab of the transform data task, 'check
> constraints' is enabled.
> HTH,
> Paul Ibison
>
|||DTS can only carry over constraint if you go between SQL Servers. I think that Access comes with some upsizing
wizard, you might want to check in an Access groups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark Findlay" <mfindlay@.speakeasy.org> wrote in message news:%238kPFkQQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I'm sorry but I don't see the option 'check constraints'. I am running the
> 'Data Transformation Services Import/Export wizard'.
> Here are the screens I see:
> 1) Choose a data source: I choose the access database
> 2) Choose a destination: I choose the target database
> 3) Specify Table Copy or Query: I choose 'Copy table(s) and view(s) from the
> source database
> 4) Select Source Tables and Views: I select my source table and target table
> here
> 5) Save, schedule and replicate package: I choose 'Run Immediately'
> 6) Completing the DTS Import/Export Wizard: I choose 'Finish' to run the
> import.
> At this point it appears to import a number of records and then I get the
> popup indicating that it can not import the record with the NULL value in
> the column.
> In all of that I'm afraid I could not see a transform data task. I hate to
> be dense, but could you elaborate on where I should look for that?
> Many thanks!
> Mark
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eNw%23ZyPQEHA.3708@.TK2MSFTNGP10.phx.gbl...
>
|||Mark,
the package uses a data transform task in the background which has loads of
properties that are not exposed in the wizard, so the best thing to do is to
save the package without running it. After that, open it and double-click
the data transform task. On the final tab, you'll see the option I'm talking
about. The check constraints option should initiate the default's use.
HTH,
Paul Ibison

Importing MS Access queries into SQLServer?

Hello,
I have an Access db with several tables and several queries defined. Is
there a way to import the results of a query into SQLServer, or do I first
have to save the result of the query as a table and import that?
Thanks.
You might find it easier to perform a make table and then import the
results.
Another solution that you might be able to look at would involve moving the
query into SQL Server and let SQL Server (not MS Access) combine the data
and create (or populate) a table.
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:%23TG4NxcwEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an Access db with several tables and several queries defined. Is
> there a way to import the results of a query into SQLServer, or do I first
> have to save the result of the query as a table and import that?
> Thanks.
>
|||Unfortunately, moving the queries into SQLServer is not an option.
Thanks for the reply.

> You might find it easier to perform a make table and then import the
> results.
> Another solution that you might be able to look at would involve moving
the
> query into SQL Server and let SQL Server (not MS Access) combine the data
> and create (or populate) a table.
|||Hi,
Have you tried "Import and Export Data" in SQL Server? What's your
concerns? Would you please show us more detailed scenario about your issue?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Our application imports the user's data (vendors and customers, for example)
from their datasources into SQLServer for the application to use. The user
can select to import from Access, Excel, or an ODBC datasource. When Access
is selected, we create an OleDbConnection object; the ConnectionString
property is something like:
@."Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\SrcDataSmall.mdb;User Id=;Password=;""
To get the list of tables in the db, we call:
DataTable schemaTable =
dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.T ables, new object[] {null,
null, null, "TABLE"});
Is there a way to get a list of queries in the db, and import the result set
as though it were a table?
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:FQ5ZqQlwEHA.3984@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you tried "Import and Export Data" in SQL Server? What's your
> concerns? Would you please show us more detailed scenario about your
issue?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Developer,
The following statement returns all the views in an Access database.
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
HTH.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
|||Thank you, Kevin, I'll try that. As I looked in to GetOleDbSchemaTable(), I
wondered if "View" was what I needed to use.
"Kevin Yu [MSFT]" <v-kevy@.online.microsoft.com> wrote in message
news:$0skStjxEHA.1884@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> The following statement returns all the views in an Access database.
> DataTable dt =
> this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
> object[] {null, null, null, "VIEW"});
> HTH.
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
|||Hi Developer,
I would like to follow up on this issue and see if any progress has been
made. I haven't heard from you in 2 days, were you able to check my reply?
Should you have any questions, please feel free to post here.
Looking forward to your reply!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I will use Kevin's suggestion:
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:eRfbpKKyEHA.764@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> I would like to follow up on this issue and see if any progress has been
> made. I haven't heard from you in 2 days, were you able to check my reply?
> Should you have any questions, please feel free to post here.
> Looking forward to your reply!
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi,
Great to hear that, I am just checking whether Kevin's suggestion work fine
for you and it seems it does :-)
Free feel to let us know whenever you have any questions or concnerns, we
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Importing More Than 10'000 Rows

Hi,
I have been tasked with moving a company database from MS Access to SQL
Server 2000. The problem I have is that there are some very large tables
which I need to import from the Access version to SQL Server. One of these,
for example, is just under 33'000 records in size. Is there a way that I can
import all of these records in one go. I can only import 10'000 at a time.
Many thanks in advance to anyone who might be able to help
Hi
Have you look into DTS?
"Tomsky02" <Tomsky02@.discussions.microsoft.com> wrote in message
news:2EE92E2F-D6B2-461D-9D2F-743DD2030522@.microsoft.com...
> Hi,
> I have been tasked with moving a company database from MS Access to SQL
> Server 2000. The problem I have is that there are some very large tables
> which I need to import from the Access version to SQL Server. One of
> these,
> for example, is just under 33'000 records in size. Is there a way that I
> can
> import all of these records in one go. I can only import 10'000 at a time.
> Many thanks in advance to anyone who might be able to help
|||Hi Uri,
Thanks for the reply. I solved the problem. I was being a bit of an idiot
and had the number of records to be displayed in my forms as 10000. I upped
this and everything is ok now.
Thanks,
Tom.
"Uri Dimant" wrote:

> Hi
> Have you look into DTS?
> "Tomsky02" <Tomsky02@.discussions.microsoft.com> wrote in message
> news:2EE92E2F-D6B2-461D-9D2F-743DD2030522@.microsoft.com...
>
>

Importing More Than 10'000 Rows

Hi,
I have been tasked with moving a company database from MS Access to SQL
Server 2000. The problem I have is that there are some very large tables
which I need to import from the Access version to SQL Server. One of these,
for example, is just under 33'000 records in size. Is there a way that I can
import all of these records in one go. I can only import 10'000 at a time.
Many thanks in advance to anyone who might be able to helpHi
Have you look into DTS?
"Tomsky02" <Tomsky02@.discussions.microsoft.com> wrote in message
news:2EE92E2F-D6B2-461D-9D2F-743DD2030522@.microsoft.com...
> Hi,
> I have been tasked with moving a company database from MS Access to SQL
> Server 2000. The problem I have is that there are some very large tables
> which I need to import from the Access version to SQL Server. One of
> these,
> for example, is just under 33'000 records in size. Is there a way that I
> can
> import all of these records in one go. I can only import 10'000 at a time.
> Many thanks in advance to anyone who might be able to help|||Hi Uri,
Thanks for the reply. I solved the problem. I was being a bit of an idiot
and had the number of records to be displayed in my forms as 10000. I upped
this and everything is ok now.
Thanks,
Tom.
"Uri Dimant" wrote:

> Hi
> Have you look into DTS?
> "Tomsky02" <Tomsky02@.discussions.microsoft.com> wrote in message
> news:2EE92E2F-D6B2-461D-9D2F-743DD2030522@.microsoft.com...
>
>

Importing ISAM data into SQLServer

Hello,
My company has some old ISAM datasets we need to resuscitate. We
would like to import the data into SQLServer. It looks like there are
some ISAM ODBC drivers available, but I was wondering how well it
works.
There would seem to be a step where you have to specify how the input
record is parsed into SQLServer columns. Is this easier in some
drivers than other?
Any input from people who have gone through this exercise would be
appreciated.
Bart
There are different types of ISAM files so it all depends.
The drivers can make a big difference in performance, ease
of use, etc.whenever you work with any other flavors of data
sources. I've done some that have been straightforward and
pretty easy. I've done others that have been painful. Most
of the vendors will give you trial versions - that's usually
the best way to find out what's going to work best for your
needs.
-Sue
On 8 May 2007 14:16:17 -0700, barthome1@.comcast.net wrote:

>Hello,
>My company has some old ISAM datasets we need to resuscitate. We
>would like to import the data into SQLServer. It looks like there are
>some ISAM ODBC drivers available, but I was wondering how well it
>works.
>There would seem to be a step where you have to specify how the input
>record is parsed into SQLServer columns. Is this easier in some
>drivers than other?
>Any input from people who have gone through this exercise would be
>appreciated.
>Bart

Importing ISAM data into SQLServer

Hello,
My company has some old ISAM datasets we need to resuscitate. We
would like to import the data into SQLServer. It looks like there are
some ISAM ODBC drivers available, but I was wondering how well it
works.
There would seem to be a step where you have to specify how the input
record is parsed into SQLServer columns. Is this easier in some
drivers than other?
Any input from people who have gone through this exercise would be
appreciated.
BartThere are different types of ISAM files so it all depends.
The drivers can make a big difference in performance, ease
of use, etc.whenever you work with any other flavors of data
sources. I've done some that have been straightforward and
pretty easy. I've done others that have been painful. Most
of the vendors will give you trial versions - that's usually
the best way to find out what's going to work best for your
needs.
-Sue
On 8 May 2007 14:16:17 -0700, barthome1@.comcast.net wrote:

>Hello,
>My company has some old ISAM datasets we need to resuscitate. We
>would like to import the data into SQLServer. It looks like there are
>some ISAM ODBC drivers available, but I was wondering how well it
>works.
>There would seem to be a step where you have to specify how the input
>record is parsed into SQLServer columns. Is this easier in some
>drivers than other?
>Any input from people who have gone through this exercise would be
>appreciated.
>Bartsql

Importing DB2 ixf files into SQLServer 2000

Does anybody know of an application / tool that will allow me to import some DB2 ixf format files into a SQL Server 2000 database ?
Many thanksOriginally posted by shopper
Does anybody know of an application / tool that will allow me to import some DB2 ixf format files into a SQL Server 2000 database ?

Many thanks

Hello you can use FoxPro import and from there send it to SQL2000

my two cents

marcos oliva

importing data to sqlserver express

I would like to import data to a sqlserver express database. The database I am interested in importing was created in MSDE. I tried backing it up and using the restore function in sqlserver express but with no success. I have not been able to find an import function in the Management Studio Express interface. Is it possible to import this information, or should I get busy with my data entry?Restoring the MSDE Database should be fine, which error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for the reply. The error tells me the restore failed for the following reason;

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'TestData' database.

In considering this, I realized that I failed to create all of the tables located in the original database. Could that be the problem?

|||I solved my own problem. In thinking about my options, I remembered this from a long ago project. I moved the .mdf and .ldf files into the MSSql\Data folder on the computer with Sqlserver Express. Then I Attached the database by pointing at the .mdf file. I now have my complete database on SqlServer Express. Thanks.

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

Please let me know.

Thanks

MSDAORA does not support 10g, have a look at INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
Try using OraOLEDB.Oracle instead.|||

Hi Anton,

Thanks for speedy reply.

Anton in my procedure if I remove transaction(Begin Tran, Commit, Rollback) then this procedure is working fine, but removal of it not possible, so it is not related with provider, I hope.

If I put tranaction it shows above maintain error.

Thanks

|||Is there any reason you cannot try Oracle's provider?|||

Hi Anton,

I am using OLEDB.ORACLE but it is not allowing me to make new Linked server.

It shows me this error:

TITLE: Microsoft SQL Server Management Studio

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "OraOLEDB.Oracle" for linked server "AA" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "AA". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

BUTTONS:

&Yes
&No

|||Have you enabled AllowInProcess option in the provider options?|||

Hi Anton,

Yes, I did but it returns another error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||But MSDAORA is not an Oracle's provider. Did you try it for the OraOLEDB?|||

Hi Anton,

Yes, I did

But it is not allowing me to make a new linked Server.

Presently I am facing this problem:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||

Hi Anton,

Now I solved ORA-12154 Error but when I enabled "Allow InProcess" this gives ORA-12154 Error again.

After disable this option, I can connect Oracle server successfully but when I used to fire my procedure that is used to import records from oracle 10g to sqlserver 2005 using linked server.

It Shows me this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" reported an error. The provider did not give any information about the error.
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" was unable to begin a distributed transaction.

Thanks for your nice support.

|||http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701|||

Hi Anton,

I got 2 kind of error:

checked "Allow inprocess" in the provider options, then it Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

If NOT CHECKED then Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

I used later one because by this i can access records from oracle but when i used this linked server in my Procedure using transaction then this error is reflected.

I find out a link of support.microsoft.com/kb/906954 but it can not make me out from this trouble.

Please advise me the solution.

Thanks for your support.

Importing CVS to SQLServer 2005 in a web application

Importing CVS to SQLServer 2005 in a web application:

I need to import CSV file to a table. This is a .Net 2.0 web application running in the WebServer which has .Net 2.0 installed. SQL Server 2005 is a database in another machine. What are my options? Would SSIS work in WebServer if no database installed in that machine.

Thanks

>>.Importing CVS to SQLServer 2005 in a web application: << Do you mean CSV?

>>Would SSIS work in WebServer if no database installed in that machine.<< is SQL client and SQL Management studio was installed on the web server and you could log on to the web server.

>>What are my options? << You have specified if this is a on-off import or a regular job. One way of handling it would be to convert the csv to either a series in inserts to the table or calls to a stored procedure to do the job.

Importing an XML file with hirarchial data

Hello,

I would like to import an XML file containing hierarchial data into a table in SQLserver. I guess that I am supposed to use the XML source editor and connect it to the xml file containing the following:

<?xml version="1.0"?>
<VariantFamilies>
<VariantFamily FamilyID="XXX">
<FamilyDescription Language="en-GB">Variant Family Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1XX">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2XX">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
<VariantFamily FamilyID="XYY">
<FamilyDescription Language="en-GB">Variant Family 2 Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning 2 p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1YY">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2YY">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
</VariantFamilies>

Following columns exist in the table: WK, VariantFamily, VariantFamilyDescription, VariantID, VariantDescription.
wk is automatic,
Variant family should contain XYY,
VariantFamilyDescription should contain Variant Family 2 Description in English,
VariantID should contain 2YY,
VariantDescription should contain Variant Description 2 in English

in the outputname I can only choose one of the attributes at a time (the system has created two new items for each ID to identify the "parent". Now how do I go about to put the actual information into the columns? NOT the system created IDs. I would like to have one row per smalles grain (VariantID) hence having redundant data in Variant family column.

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I seem to be in a similar conundrum. However, what I want to do is move data elements from my XML file into multiple SQL Server database tables. I've created the XML data flow source and multiple data flow destinations, each pointing to their own table.

As Wenyang Hu pointed out, the XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table. I've now got over 50 tables, but can only select one (from the XML Source Editor columns form). Subsequently, when I go to add a data flow path to my data flow destination, I can only select a single output, which doesn't provide me with all the data I need to load a single database table.

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

That's true one xmlsrc output can be hooked with only one downstream, hence if you want several xmlsrc outputs to be loaded to one single destination, then as said above you'll need other transformation(s) either in between XMLSrc and destination, or, in another dataflow to manipulate.

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

To Sort on various key columns then join on those key columns at MergeJoin to eventually merge these 3 xmlsrc outputs into one destination. Since you have 50 outputs from xmlsrc, you can also save them into 50 destionation tables first, then use another dataflow with those tables as sources, then use other SSIS transforms to process based on your specific data hierarchy.

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering if the 'Union All' transformation task might work. The help on that task indicates it can have multiple inputs. If I map multiple inputs from my XMLSrc outputs, then maybe this will work. Then I can have about 10 union all task to map to my 10 SQL tables. Thoughts? I'm hoping to understand the union all as the help doesn't indicate that it does anything unusual.
thanks.
|||

UnionAll unions all its input rows but a) the column metadata will be built up based only on the primary input(the first one hooked up with unionAll) b)it does not "join" on key columns which you may need to merge those hierarchical data from xml file into one destination without loosing your data logic. So unless your multiple xmlsrc outputs have identical column structure, unionall won't fit your need I think.

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

<Property>
<PropertyId>TheIdINeed</PropertyId>
....
....
<Buildings>
<Building id="123">
.....
......
<Rooms>
<Room id ="ABC">
.....
.....
<Beds>
<Bed id="cfg">
.....
.....
</Bed>
</Beds>
</Room>
</Rooms>
</Building>
</Buildings>
</Property>

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Importing an XML file with hirarchial data

Hello,

I would like to import an XML file containing hierarchial data into a table in SQLserver. I guess that I am supposed to use the XML source editor and connect it to the xml file containing the following:

<?xml version="1.0"?>
<VariantFamilies>
<VariantFamily FamilyID="XXX">
<FamilyDescription Language="en-GB">Variant Family Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1XX">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2XX">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
<VariantFamily FamilyID="XYY">
<FamilyDescription Language="en-GB">Variant Family 2 Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning 2 p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1YY">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2YY">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
</VariantFamilies>

Following columns exist in the table: WK, VariantFamily, VariantFamilyDescription, VariantID, VariantDescription.
wk is automatic,
Variant family should contain XYY,
VariantFamilyDescription should contain Variant Family 2 Description in English,
VariantID should contain 2YY,
VariantDescription should contain Variant Description 2 in English

in the outputname I can only choose one of the attributes at a time (the system has created two new items for each ID to identify the "parent". Now how do I go about to put the actual information into the columns? NOT the system created IDs. I would like to have one row per smalles grain (VariantID) hence having redundant data in Variant family column.

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,

Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I seem to be in a similar conundrum. However, what I want to do is move data elements from my XML file into multiple SQL Server database tables. I've created the XML data flow source and multiple data flow destinations, each pointing to their own table.

As Wenyang Hu pointed out, the XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table. I've now got over 50 tables, but can only select one (from the XML Source Editor columns form). Subsequently, when I go to add a data flow path to my data flow destination, I can only select a single output, which doesn't provide me with all the data I need to load a single database table.

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

That's true one xmlsrc output can be hooked with only one downstream, hence if you want several xmlsrc outputs to be loaded to one single destination, then as said above you'll need other transformation(s) either in between XMLSrc and destination, or, in another dataflow to manipulate.

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

To Sort on various key columns then join on those key columns at MergeJoin to eventually merge these 3 xmlsrc outputs into one destination. Since you have 50 outputs from xmlsrc, you can also save them into 50 destionation tables first, then use another dataflow with those tables as sources, then use other SSIS transforms to process based on your specific data hierarchy.

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering

if the 'Union All' transformation task might work.

The help on that task indicates it can have multiple inputs. If I

map multiple inputs from my XMLSrc outputs, then maybe this will

work. Then I can have about 10 union all task to map to my 10 SQL

tables. Thoughts? I'm hoping to understand the union

all as the help doesn't indicate that it does anything unusual.

thanks.|||

UnionAll unions all its input rows but a) the column metadata will be built up based only on the primary input(the first one hooked up with unionAll) b)it does not "join" on key columns which you may need to merge those hierarchical data from xml file into one destination without loosing your data logic. So unless your multiple xmlsrc outputs have identical column structure, unionall won't fit your need I think.

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

<Property>
<PropertyId>TheIdINeed</PropertyId>
....
....
<Buildings>
<Building id="123">
.....
......
<Rooms>
<Room id ="ABC">
.....
.....
<Beds>
<Bed id="cfg">
.....
.....
</Bed>
</Beds>
</Room>
</Rooms>
</Building>
</Buildings>
</Property>

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Importing an XML file with hirarchial data

Hello,

I would like to import an XML file containing hierarchial data into a table in SQLserver. I guess that I am supposed to use the XML source editor and connect it to the xml file containing the following:

<?xml version="1.0"?>
<VariantFamilies>
<VariantFamily FamilyID="XXX">
<FamilyDescription Language="en-GB">Variant Family Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1XX">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2XX">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
<VariantFamily FamilyID="XYY">
<FamilyDescription Language="en-GB">Variant Family 2 Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning 2 p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1YY">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2YY">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
</VariantFamilies>

Following columns exist in the table: WK, VariantFamily, VariantFamilyDescription, VariantID, VariantDescription.
wk is automatic,
Variant family should contain XYY,
VariantFamilyDescription should contain Variant Family 2 Description in English,
VariantID should contain 2YY,
VariantDescription should contain Variant Description 2 in English

in the outputname I can only choose one of the attributes at a time (the system has created two new items for each ID to identify the "parent". Now how do I go about to put the actual information into the columns? NOT the system created IDs. I would like to have one row per smalles grain (VariantID) hence having redundant data in Variant family column.

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,

Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I seem to be in a similar conundrum. However, what I want to do is move data elements from my XML file into multiple SQL Server database tables. I've created the XML data flow source and multiple data flow destinations, each pointing to their own table.

As Wenyang Hu pointed out, the XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table. I've now got over 50 tables, but can only select one (from the XML Source Editor columns form). Subsequently, when I go to add a data flow path to my data flow destination, I can only select a single output, which doesn't provide me with all the data I need to load a single database table.

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

That's true one xmlsrc output can be hooked with only one downstream, hence if you want several xmlsrc outputs to be loaded to one single destination, then as said above you'll need other transformation(s) either in between XMLSrc and destination, or, in another dataflow to manipulate.

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

To Sort on various key columns then join on those key columns at MergeJoin to eventually merge these 3 xmlsrc outputs into one destination. Since you have 50 outputs from xmlsrc, you can also save them into 50 destionation tables first, then use another dataflow with those tables as sources, then use other SSIS transforms to process based on your specific data hierarchy.

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering

if the 'Union All' transformation task might work.

The help on that task indicates it can have multiple inputs. If I

map multiple inputs from my XMLSrc outputs, then maybe this will

work. Then I can have about 10 union all task to map to my 10 SQL

tables. Thoughts? I'm hoping to understand the union

all as the help doesn't indicate that it does anything unusual.

thanks.|||

UnionAll unions all its input rows but a) the column metadata will be built up based only on the primary input(the first one hooked up with unionAll) b)it does not "join" on key columns which you may need to merge those hierarchical data from xml file into one destination without loosing your data logic. So unless your multiple xmlsrc outputs have identical column structure, unionall won't fit your need I think.

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

<Property>
<PropertyId>TheIdINeed</PropertyId>
....
....
<Buildings>
<Building id="123">
.....
......
<Rooms>
<Room id ="ABC">
.....
.....
<Beds>
<Bed id="cfg">
.....
.....
</Bed>
</Beds>
</Room>
</Rooms>
</Building>
</Buildings>
</Property>

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus