Creating a table from one server to another

I have two server Server1 and Server2
I need to create a table in Server2 from Server1
I do have a linked server
please advice how this can be done
Thanks
samay
The quickest and easiest way is to use DTS.
In SEM, expand your database node.
Right-click on the database you wish to import the table to. Choose
All-Tasks, Import Data.
Follow the wizard through. You can import just the table structure, or both
the structure and the data.
Note: If you just want the structure, using the Query Analyzer, find the
table in Server1, right-click and have it create the CREATE statement for
you. Copy and paste that statement in the query analyzer (choose the
appropriate database and server *before* you run the script.
Rick
RICHARD SAWTELL
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>
|||Number of options available..... I'd use DTS
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>
|||I am using the Script to backup data. and I was wondering if instead of 1st careting a table in Server2 and then the calculated script if i can create atable + insert the required records in it in one script.Is this posiible
PLease advice
2....another Question
Right now I am creating a tabl in server 2 and updating it from Server 1
and when i try to Update the Server2 Table A it gives me this Error
Could not open table "LinkedServer."TABLEA"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Please advice
"Simon" wrote:

> Number of options available..... I'd use DTS
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
>
>
|||Yes, if you want to do it that way. Build the empty table and then refer
to the populated table
from the linked server.
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:739516F9-E466-4DDE-8866-AD73BBB5E29D@.microsoft.com...
> I am using the Script to backup data. and I was wondering if instead of
1st careting a table in Server2 and then the calculated script if i can
create atable + insert the required records in it in one script.Is this
posiible
> PLease advice
> 2....another Question
> Right now I am creating a tabl in server 2 and updating it from Server 1
> and when i try to Update the Server2 Table A it gives me this Error
> Could not open table "LinkedServer."TABLEA"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...[vbcol=seagreen]
> Please advice
> "Simon" wrote:
<KritiVermahotmailcom@.discussions.microsoft.com> [vbcol=seagreen]
news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...[vbcol=seagreen]

Creating a table from one server to another

I have two server Server1 and Server2
I need to create a table in Server2 from Server1
I do have a linked server
please advice how this can be done
Thanks
samayThe quickest and easiest way is to use DTS.
In SEM, expand your database node.
Right-click on the database you wish to import the table to. Choose
All-Tasks, Import Data.
Follow the wizard through. You can import just the table structure, or both
the structure and the data.
Note: If you just want the structure, using the Query Analyzer, find the
table in Server1, right-click and have it create the CREATE statement for
you. Copy and paste that statement in the query analyzer (choose the
appropriate database and server *before* you run the script.
Rick
RICHARD SAWTELL
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||Number of options available..... I'd use DTS
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||Yes, if you want to do it that way. Build the empty table and then refer
to the populated table
from the linked server.
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:739516F9-E466-4DDE-8866-AD73BBB5E29D@.microsoft.com...
> I am using the Script to backup data. and I was wondering if instead of
1st careting a table in Server2 and then the calculated script if i can
create atable + insert the required records in it in one script.Is this
posiible
> PLease advice
> 2....another Question
> Right now I am creating a tabl in server 2 and updating it from Server 1
> and when i try to Update the Server2 Table A it gives me this Error
> Could not open table "LinkedServer."TABLEA"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Please advice
> "Simon" wrote:
> > Number of options available..... I'd use DTS
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> > > I have two server Server1 and Server2
> > >
> > > I need to create a table in Server2 from Server1
> > > I do have a linked server
> > >
> > > please advice how this can be done
> > >
> > > Thanks
> > > samay
> > >
> >
> >
> >

Creating a table from one server to another

I have two server Server1 and Server2
I need to create a table in Server2 from Server1
I do have a linked server
please advice how this can be done
Thanks
samayThe quickest and easiest way is to use DTS.
In SEM, expand your database node.
Right-click on the database you wish to import the table to. Choose
All-Tasks, Import Data.
Follow the wizard through. You can import just the table structure, or both
the structure and the data.
Note: If you just want the structure, using the Query Analyzer, find the
table in Server1, right-click and have it create the CREATE statement for
you. Copy and paste that statement in the query analyzer (choose the
appropriate database and server *before* you run the script.
Rick
RICHARD SAWTELL
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||Number of options available..... I'd use DTS
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||I am using the Script to backup data. and I was wondering if instead of 1st
careting a table in Server2 and then the calculated script if i can create
atable + insert the required records in it in one script.Is this posiible
PLease advice
2....another Question
Right now I am creating a tabl in server 2 and updating it from Server 1
and when i try to Update the Server2 Table A it gives me this Error
Could not open table "LinkedServer."TABLEA"' from OLE DB provider 'SQLOLEDB'
. The provider could not support a row lookup position. The provider indica
tes that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset r
eturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPST
ATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROP
STATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=T
rue STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Please advice
"Simon" wrote:

> Number of options available..... I'd use DTS
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com..
.
>
>|||Yes, if you want to do it that way. Build the empty table and then refer
to the populated table
from the linked server.
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:739516F9-E466-4DDE-8866-AD73BBB5E29D@.microsoft.com...
> I am using the Script to backup data. and I was wondering if instead of
1st careting a table in Server2 and then the calculated script if i can
create atable + insert the required records in it in one script.Is this
posiible
> PLease advice
> 2....another Question
> Right now I am creating a tabl in server 2 and updating it from Server 1
> and when i try to Update the Server2 Table A it gives me this Error
> Could not open table "LinkedServer."TABLEA"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=60
0
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...[vbcol=seagreen]
> Please advice
> "Simon" wrote:
>
<KritiVermahotmailcom@.discussions.microsoft.com>[vbcol=seagreen]
news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...[vbcol=seagreen]

creating a matching program

Hi,

I'm just looking for some general advice on how to approach something.

I have two tables A and B, containg common fields of product, sales
date and qtyo.
Some records contain the same data and I want to dump them in a new
table called C and leave Tables A and B containg only data that
doesn'r match.

How do i go about approaching this??

Regards,
CiarnOn 22 Nov 2004 02:18:39 -0800, Ciar?n wrote:

>Hi,
>I'm just looking for some general advice on how to approach something.
>I have two tables A and B, containg common fields of product, sales
>date and qtyo.
>Some records contain the same data and I want to dump them in a new
>table called C and leave Tables A and B containg only data that
>doesn'r match.
>How do i go about approaching this??
>Regards,
>Ciarn

Hi Ciarn,

INSERT INTO C
SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM A
INNER JOIN B
ON B.Column1 = A.Column1
AND B.Column2 = A.Column2
......
AND B.ColumnN = A.ColumnN

DELETE FROM A
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = A.Column1
AND C.Column2 = A.Column1
.......
AND C.ColumnN = A.ColumnN)

DELETE FROM B
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = B.Column1
AND C.Column2 = B.Column1
.......
AND C.ColumnN = B.ColumnN)

Enclose this all in a transaction, add error handling and you're set.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Cheers Hugo

Creating a Database for deployment in SQL Server

I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.

Should i use database creation scripts as I have previously used on other apps, or is there a more recognised modern way of creating the DB. I have heard mention of "Sql Server Management Studio" possibly being useful here. If so, will there be issues if one of the customers buying my application uses Oracle?

Any advice appreciated.

Cheers,

StewManagement Studio certainly won't work with Oracle. If your main objective is portability, I suppose scripts is still your best bet.

Creating a Database for deployment in SQL Server

I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.

Should i use database creation scripts as I have previously used on other apps, or is there a more recognised modern way of creating the DB. I have heard mention of "Sql Server Management Studio" possibly being useful here. If so, will there be issues if one of the customers buying my application uses Oracle?

Any advice appreciated.

Cheers,

StewManagement Studio certainly won't work with Oracle. If your main objective is portability, I suppose scripts is still your best bet.