Insert xml into mssql 2000

Hi,

I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.

What is the most efficient way to do this using C#2005 and mssql2000?

Any good article or tutorial?

http://www.databasejournal.com/features/mssql/article.php/2204421|||http://aspalliance.com/477

Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/col...realsqlguy.com

Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> > Hi,
> > we have a local server with a database say A on it . we also have a
> > linked server which has a database B.
> > now we are trying to insert into a table in a using data from the
> > database B. both of the tables in both the database are the same in
> > structure .
> > now when i use a query like
> > insert into a.Table1
> > ( No,
> > Name
> > )
> > select
> > no,
> > name
> > frrom
> > host_sever.B.dbo.table1
> > where <some condition >
> >
> > the above query fails and the error says a nested distributed
> > transaction cannot be started
> > both the tables have a trigger attached to it .
> > we found that first inserting the data into a temp table and then
> > copying that data into the main table in local server in database A
> > works fine.
> > also i tested some scenario with no trigger and it works fine .
> >
> > is this how it is when there are triggers attached and is there any way
> > we can succesfully run the query ableve without temp tables.
> >
> > thanks
> > ravinder
> >
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
>> avravinder@.gmail.com wrote:
>> Hi,
>> we have a local server with a database say A on it . we also have a
>> linked server which has a database B.
>> now we are trying to insert into a table in a using data from the
>> database B. both of the tables in both the database are the same in
>> structure .
>> now when i use a query like
>> insert into a.Table1
>> ( No,
>> Name
>> )
>> select
>> no,
>> name
>> frrom
>> host_sever.B.dbo.table1
>> where <some condition >
>> the above query fails and the error says a nested distributed
>> transaction cannot be started
>> both the tables have a trigger attached to it .
>> we found that first inserting the data into a temp table and then
>> copying that data into the main table in local server in database A
>> works fine.
>> also i tested some scenario with no trigger and it works fine .
>> is this how it is when there are triggers attached and is there any way
>> we can succesfully run the query ableve without temp tables.
>> thanks
>> ravinder
>> Is the MSDTC service running on both machines? Is it enabled for
>> network access?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/columnists/ckempster/debuggingmsdtcissues.asp
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Insert problem with linked server

Both servers running SQL 2000

I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.

The following three queries work:

insert into LinkedServer.dbname.dbo.Table2
select *
from LinkedServer.dbname.dbo.Table1

select *
into LocalTable
from LinkedServer.dbname.dbo.Table1

insert into LocalTable
select *
from LinkedServer.dbname.dbo.Table1

This query, which is what we really want to do, does not work:

insert into LinkedServer.dbname.dbo.Table1
select *
from LocalTable

and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'

I am new to all this and would welcome some help.

AdrianI believe I have now resolved this

In fact the example below would work

> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable

I was trying to insert into a table on the linked server that was not owned
by the dbo but by the remote username. It seems that providing the owner of
the table is dbo it will be OK.

Adrian.

"Adrian" <NoSpam@.hotmail.com> wrote in message
news:ANReb.6576$8_4.54623402@.news-text.cableinet.net...
> Both servers running SQL 2000
> I have set up on our local SQL server (using Enterprise Manager) a linked
> server running on our ISP. Just did new linked server and added remote
> password and login.
> The following three queries work:
> insert into LinkedServer.dbname.dbo.Table2
> select *
> from LinkedServer.dbname.dbo.Table1
> select *
> into LocalTable
> from LinkedServer.dbname.dbo.Table1
> insert into LocalTable
> select *
> from LinkedServer.dbname.dbo.Table1
>
> This query, which is what we really want to do, does not work:
> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable
> and returns the error: 'The cursor does not include the table being
modified
> or the table is not updatable through the cursor.'
> I am new to all this and would welcome some help.
> Adrian