copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and then
switched to the destination dbase and table and selected script object to new
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctly.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),>)
Paul G
Software engineer.
If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.
|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.
|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:

> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>
|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:

> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the data
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the data
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:

> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>

copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and then
switched to the destination dbase and table and selected script object to new
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctly.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),>)
--
Paul G
Software engineer.If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:
> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
> > --
> > Paul G
> > Software engineer.
>
>|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:
> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the data
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the data
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
> > --
> > Paul G
> > Software engineer.
>|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:
> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
>
>

copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and the
n
switched to the destination dbase and table and selected script object to ne
w
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctl
y.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),> )
--
Paul G
Software engineer.If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocald
atabase
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),> )
> --
> Paul G
> Software engineer.|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),> )
> --
> Paul G
> Software engineer.|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),> )|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:

> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinloca
ldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:

> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the dat
a
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY
it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the dat
a
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:

> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way to
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
--
Paul G
Software engineer.The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficient.
"Aaron [SQL Server MVP]" wrote:
> The stored procedures don't really have any meaning outside of the database,
> and you should only see MDF/NDF/LDF files in windows explorer (they are not
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:
> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Yes, it does, try it out...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each
> one individually.
> "Wayne Snyder" wrote:
> > You can generate a script of the stored procedures. In SQL Enterprise
> > manager, right click your database name and go to SQL Scripting... It
will
> > walk you through the process..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > > I have a project with around 50 stored procedures, can not seem to see
the
> > > dbase when using explorer but have access to it with enterprize
manager
> > and
> > > visual studio.net server explorer. Just wondering if there is an easy
way
> > to
> > > copy the stored procedures onto the workstation possibly using
enterprize
> > > manager or .net server explorer? thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >

copying data from 1 table to another

just wondering the best way to copy data from one database to another after
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the data
using this tool.
Thanks.
Paul G
Software engineer.
It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:

> just wondering the best way to copy data from one database to another after
> doing a dbase port? The destination database works with a .NET application
> and the source database came from a My SQL database (used a mysql driver and
> access to get the data to SQL2000 dbase). Also the source database does not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have the
> Query Analizer tool so was thinking there may be an easy way to copy the data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.
|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with insert
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could be
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure you
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
|||Thanks for the response. The source database was MySQL but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:

> Hi
> If the source database is outside SQL Server you may want to look at DTS or
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These could
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>
|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/default...n-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert[vbcol=seagreen]
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]
|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints like
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:

> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore to
> another database on the same computer then you will need to rename data and
> log files (similarly if they are in a different location on the destination
> computer you will have to do the same).
> http://support.microsoft.com/default...n-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax of
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>
|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]
|||Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:

> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>

copying data from 1 table to another

just wondering the best way to copy data from one database to another after
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the dat
a
using this tool.
Thanks.
--
Paul G
Software engineer.It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:

> just wondering the best way to copy data from one database to another afte
r
> doing a dbase port? The destination database works with a .NET applicatio
n
> and the source database came from a My SQL database (used a mysql driver a
nd
> access to get the data to SQL2000 dbase). Also the source database does n
ot
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have t
he
> Query Analizer tool so was thinking there may be an easy way to copy the d
ata
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with inser
t
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could b
e
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure yo
u
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
>|||Thanks for the response. The source database was mysql but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:

> Hi
> If the source database is outside SQL Server you may want to look at DTS o
r
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These cou
ld
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/defaul...en-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert[vbcol=seagreen]
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
>
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints lik
e
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:

> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore
to
> another database on the same computer then you will need to rename data an
d
> log files (similarly if they are in a different location on the destinatio
n
> computer you will have to do the same).
> http://support.microsoft.com/defaul...en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax o
f
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
>
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]|||Thanks for he additional information. Staging or temporary tables seems ver
y
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to b
e
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:

> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Serv
er
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this i
s
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>

copying data from 1 table to another

just wondering the best way to copy data from one database to another after
doing a dbase port? The destination database works with a .NET application
and the source database came from a My SQL database (used a mysql driver and
access to get the data to SQL2000 dbase). Also the source database does not
have constraints setup but the the destination tables do, so just want to
fill the destination tables with the data from the source tables. I have the
Query Analizer tool so was thinking there may be an easy way to copy the data
using this tool.
Thanks.
--
Paul G
Software engineer.It depends with which tool you are the most confortable. Quick way could be
to use:
-insert into <destinationTable> select * from <SourceTable>. Make sure you
try a on a test database first.
You could also use the bulk copy utility. It depends how much data and how
many tables you have. Note that the first solution will increase your
transaction log size if you have a lot of data.
There is also DTS.
"Paul" wrote:
> just wondering the best way to copy data from one database to another after
> doing a dbase port? The destination database works with a .NET application
> and the source database came from a My SQL database (used a mysql driver and
> access to get the data to SQL2000 dbase). Also the source database does not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have the
> Query Analizer tool so was thinking there may be an easy way to copy the data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> just wondering the best way to copy data from one database to another
after
> doing a dbase port? The destination database works with a .NET
application
> and the source database came from a My SQL database (used a mysql driver
and
> access to get the data to SQL2000 dbase). Also the source database does
not
> have constraints setup but the the destination tables do, so just want to
> fill the destination tables with the data from the source tables. I have
the
> Query Analizer tool so was thinking there may be an easy way to copy the
data
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the information. The largest table has around 16k records of
about 10 fields, one other table has around 8k records and the remaining 8
tables are very small. Thinking of just using the query analyser with insert
into <database1.table1>
select * from <database2.table1> since I am somewhat comfortable with the
tool.
I am assuming you can copy from 1 dbase to another.
"Sasan Saidi" wrote:
> It depends with which tool you are the most confortable. Quick way could be
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure you
> try a on a test database first.
> You could also use the bulk copy utility. It depends how much data and how
> many tables you have. Note that the first solution will increase your
> transaction log size if you have a lot of data.
> There is also DTS.
>
> "Paul" wrote:
> > just wondering the best way to copy data from one database to another after
> > doing a dbase port? The destination database works with a .NET application
> > and the source database came from a My SQL database (used a mysql driver and
> > access to get the data to SQL2000 dbase). Also the source database does not
> > have constraints setup but the the destination tables do, so just want to
> > fill the destination tables with the data from the source tables. I have the
> > Query Analizer tool so was thinking there may be an easy way to copy the data
> > using this tool.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Thanks for the response. The source database was MySQL but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:
> Hi
> If the source database is outside SQL Server you may want to look at DTS or
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These could
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> > just wondering the best way to copy data from one database to another
> after
> > doing a dbase port? The destination database works with a .NET
> application
> > and the source database came from a My SQL database (used a mysql driver
> and
> > access to get the data to SQL2000 dbase). Also the source database does
> not
> > have constraints setup but the the destination tables do, so just want to
> > fill the destination tables with the data from the source tables. I have
> the
> > Query Analizer tool so was thinking there may be an easy way to copy the
> data
> > using this tool.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
There are several ways to copy a compelete database from one location to
another. The easiest if probably to backup/restore. If you backup/restore to
another database on the same computer then you will need to rename data and
log files (similarly if they are in a different location on the destination
computer you will have to do the same).
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
If you are doing this from Query Analyser check out the WITH MOVE syntax of
the RESTORE command in Books online.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> Thanks for the information. The largest table has around 16k records of
> about 10 fields, one other table has around 8k records and the remaining 8
> tables are very small. Thinking of just using the query analyser with
insert
> into <database1.table1>
> select * from <database2.table1> since I am somewhat comfortable with the
> tool.
> I am assuming you can copy from 1 dbase to another.
> "Sasan Saidi" wrote:
> > It depends with which tool you are the most confortable. Quick way could
be
> > to use:
> > -insert into <destinationTable> select * from <SourceTable>. Make sure
you
> > try a on a test database first.
> >
> > You could also use the bulk copy utility. It depends how much data and
how
> > many tables you have. Note that the first solution will increase your
> > transaction log size if you have a lot of data.
> >
> > There is also DTS.
> >
> >
> >
> > "Paul" wrote:
> >
> > > just wondering the best way to copy data from one database to another
after
> > > doing a dbase port? The destination database works with a .NET
application
> > > and the source database came from a My SQL database (used a mysql
driver and
> > > access to get the data to SQL2000 dbase). Also the source database
does not
> > > have constraints setup but the the destination tables do, so just want
to
> > > fill the destination tables with the data from the source tables. I
have the
> > > Query Analizer tool so was thinking there may be an easy way to copy
the data
> > > using this tool.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||Hi thanks for the additional information. Since I have made some changes to
a few of the tables, added additional fields and added table constraints like
(no duplicate records allowed) probably should not do a complete dbase copy
but good information to know.
"John Bell" wrote:
> Hi
> There are several ways to copy a compelete database from one location to
> another. The easiest if probably to backup/restore. If you backup/restore to
> another database on the same computer then you will need to rename data and
> log files (similarly if they are in a different location on the destination
> computer you will have to do the same).
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax of
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > Thanks for the information. The largest table has around 16k records of
> > about 10 fields, one other table has around 8k records and the remaining 8
> > tables are very small. Thinking of just using the query analyser with
> insert
> > into <database1.table1>
> > select * from <database2.table1> since I am somewhat comfortable with the
> > tool.
> > I am assuming you can copy from 1 dbase to another.
> > "Sasan Saidi" wrote:
> >
> > > It depends with which tool you are the most confortable. Quick way could
> be
> > > to use:
> > > -insert into <destinationTable> select * from <SourceTable>. Make sure
> you
> > > try a on a test database first.
> > >
> > > You could also use the bulk copy utility. It depends how much data and
> how
> > > many tables you have. Note that the first solution will increase your
> > > transaction log size if you have a lot of data.
> > >
> > > There is also DTS.
> > >
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > just wondering the best way to copy data from one database to another
> after
> > > > doing a dbase port? The destination database works with a .NET
> application
> > > > and the source database came from a My SQL database (used a mysql
> driver and
> > > > access to get the data to SQL2000 dbase). Also the source database
> does not
> > > > have constraints setup but the the destination tables do, so just want
> to
> > > > fill the destination tables with the data from the source tables. I
> have the
> > > > Query Analizer tool so was thinking there may be an easy way to copy
> the data
> > > > using this tool.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
>
>|||Hi Paul
Now you are talking about dbase copy! I was talking about copying SQL Server
databases!!!
In some cirumstances when alot of manipulation has to be done it is often
easier to load everything into staging tables and then do whatever work is
needed using them. A single DTS package may be created to do all of this is
you wanted a reproducable process, this which is often the case as you may
to several trial runs before you have the data in the form you want. It
could also reduce the downtime when you do implement the final data
migration.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> Hi thanks for the additional information. Since I have made some changes
to
> a few of the tables, added additional fields and added table constraints
like
> (no duplicate records allowed) probably should not do a complete dbase
copy
> but good information to know.
> "John Bell" wrote:
> > Hi
> >
> > There are several ways to copy a compelete database from one location to
> > another. The easiest if probably to backup/restore. If you
backup/restore to
> > another database on the same computer then you will need to rename data
and
> > log files (similarly if they are in a different location on the
destination
> > computer you will have to do the same).
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> >
> > If you are doing this from Query Analyser check out the WITH MOVE syntax
of
> > the RESTORE command in Books online.
> >
> > John
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > > Thanks for the information. The largest table has around 16k records
of
> > > about 10 fields, one other table has around 8k records and the
remaining 8
> > > tables are very small. Thinking of just using the query analyser with
> > insert
> > > into <database1.table1>
> > > select * from <database2.table1> since I am somewhat comfortable with
the
> > > tool.
> > > I am assuming you can copy from 1 dbase to another.
> > > "Sasan Saidi" wrote:
> > >
> > > > It depends with which tool you are the most confortable. Quick way
could
> > be
> > > > to use:
> > > > -insert into <destinationTable> select * from <SourceTable>. Make
sure
> > you
> > > > try a on a test database first.
> > > >
> > > > You could also use the bulk copy utility. It depends how much data
and
> > how
> > > > many tables you have. Note that the first solution will increase
your
> > > > transaction log size if you have a lot of data.
> > > >
> > > > There is also DTS.
> > > >
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > just wondering the best way to copy data from one database to
another
> > after
> > > > > doing a dbase port? The destination database works with a .NET
> > application
> > > > > and the source database came from a My SQL database (used a mysql
> > driver and
> > > > > access to get the data to SQL2000 dbase). Also the source
database
> > does not
> > > > > have constraints setup but the the destination tables do, so just
want
> > to
> > > > > fill the destination tables with the data from the source tables.
I
> > have the
> > > > > Query Analizer tool so was thinking there may be an easy way to
copy
> > the data
> > > > > using this tool.
> > > > > Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> >
> >
> >
> >|||Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.
"John Bell" wrote:
> Hi Paul
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@.microsoft.com...
> > Hi thanks for the additional information. Since I have made some changes
> to
> > a few of the tables, added additional fields and added table constraints
> like
> > (no duplicate records allowed) probably should not do a complete dbase
> copy
> > but good information to know.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > There are several ways to copy a compelete database from one location to
> > > another. The easiest if probably to backup/restore. If you
> backup/restore to
> > > another database on the same computer then you will need to rename data
> and
> > > log files (similarly if they are in a different location on the
> destination
> > > computer you will have to do the same).
> > >
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> > >
> > > If you are doing this from Query Analyser check out the WITH MOVE syntax
> of
> > > the RESTORE command in Books online.
> > >
> > > John
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> > > > Thanks for the information. The largest table has around 16k records
> of
> > > > about 10 fields, one other table has around 8k records and the
> remaining 8
> > > > tables are very small. Thinking of just using the query analyser with
> > > insert
> > > > into <database1.table1>
> > > > select * from <database2.table1> since I am somewhat comfortable with
> the
> > > > tool.
> > > > I am assuming you can copy from 1 dbase to another.
> > > > "Sasan Saidi" wrote:
> > > >
> > > > > It depends with which tool you are the most confortable. Quick way
> could
> > > be
> > > > > to use:
> > > > > -insert into <destinationTable> select * from <SourceTable>. Make
> sure
> > > you
> > > > > try a on a test database first.
> > > > >
> > > > > You could also use the bulk copy utility. It depends how much data
> and
> > > how
> > > > > many tables you have. Note that the first solution will increase
> your
> > > > > transaction log size if you have a lot of data.
> > > > >
> > > > > There is also DTS.
> > > > >
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > just wondering the best way to copy data from one database to
> another
> > > after
> > > > > > doing a dbase port? The destination database works with a .NET
> > > application
> > > > > > and the source database came from a My SQL database (used a mysql
> > > driver and
> > > > > > access to get the data to SQL2000 dbase). Also the source
> database
> > > does not
> > > > > > have constraints setup but the the destination tables do, so just
> want
> > > to
> > > > > > fill the destination tables with the data from the source tables.
> I
> > > have the
> > > > > > Query Analizer tool so was thinking there may be an easy way to
> copy
> > > the data
> > > > > > using this tool.
> > > > > > Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > >
> > >
> > >
> > >
>
>

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
Paul G
Software engineer.
You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.
|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:

> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> one
> vs.net
> do
>
|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar[vbcol=seagreen]
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
file.[vbcol=seagreen]
system[vbcol=seagreen]
at[vbcol=seagreen]
changed[vbcol=seagreen]
If[vbcol=seagreen]
you[vbcol=seagreen]
from[vbcol=seagreen]
have to[vbcol=seagreen]
|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:

> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> familiar
> file.
> system
> at
> changed
> If
> you
> from
> have to
>
|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
[vbcol=seagreen]
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the[vbcol=seagreen]
> database with EM? paul.
> "Keith Kratochvil" wrote:
Query[vbcol=seagreen]
have[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
back[vbcol=seagreen]
necessary.[vbcol=seagreen]
encourage[vbcol=seagreen]
procedures[vbcol=seagreen]
analyzer, or[vbcol=seagreen]
|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:

> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> the
> Query
> have
> the
> a
> the
> back
> necessary.
> encourage
> procedures
> analyzer, or
>

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
--
Paul G
Software engineer.You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:
> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > Hi just wondering if there is an easy way to copy stored procedures from
> one
> > dbase to another on the same server, possibly using query analyzer, or
> vs.net
> > server explorer? There are around 100 procedures so hoping do not have to
> do
> > each one individually. Thanks.
> > --
> > Paul G
> > Software engineer.
>|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
> > You could use Enterprise Manager to script all stored procedures to a
file.
> > You could then execute that file within your other database.
> >
> > If you have your stored procedures stored within a version control
system
> > you could simply grab the most recent files and execute them within the
> > appropriate database. Source control is nice because you can look back
at
> > the changes of the stored procedure. You can see what changed, who
changed
> > it, and why. You can also roll back to a previous version if necessary.
If
> > you don't store your stored procedures within source control I encourage
you
> > to do so.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > Hi just wondering if there is an easy way to copy stored procedures
from
> > one
> > > dbase to another on the same server, possibly using query analyzer, or
> > vs.net
> > > server explorer? There are around 100 procedures so hoping do not
have to
> > do
> > > each one individually. Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:
> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > Thanks for the response. I have enterprise manager but am not too
> familiar
> > with it, not even sure how to connect to the desired database. Also have
> > visual source safe on my machine, not sure if vss would have to be on the
> > server.
> > Paul.
> > "Keith Kratochvil" wrote:
> >
> > > You could use Enterprise Manager to script all stored procedures to a
> file.
> > > You could then execute that file within your other database.
> > >
> > > If you have your stored procedures stored within a version control
> system
> > > you could simply grab the most recent files and execute them within the
> > > appropriate database. Source control is nice because you can look back
> at
> > > the changes of the stored procedure. You can see what changed, who
> changed
> > > it, and why. You can also roll back to a previous version if necessary.
> If
> > > you don't store your stored procedures within source control I encourage
> you
> > > to do so.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > Hi just wondering if there is an easy way to copy stored procedures
> from
> > > one
> > > > dbase to another on the same server, possibly using query analyzer, or
> > > vs.net
> > > > server explorer? There are around 100 procedures so hoping do not
> have to
> > > do
> > > > each one individually. Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >
>|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also have
> > > visual source safe on my machine, not sure if vss would have to be on the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within the
> > > > appropriate database. Source control is nice because you can look back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if necessary.
> > If
> > > > you don't store your stored procedures within source control I encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
> > thanks for the additional information, just wondering how to connect to the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also have
> > > > visual source safe on my machine, not sure if vss would have to be on the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within the
> > > > > appropriate database. Source control is nice because you can look back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if necessary.
> > > If
> > > > > you don't store your stored procedures within source control I encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using
Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also
have
> > > visual source safe on my machine, not sure if vss would have to be on
the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to
a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within
the
> > > > appropriate database. Source control is nice because you can look
back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if
necessary.
> > If
> > > > you don't store your stored procedures within source control I
encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored
procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query
analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:
> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> > thanks for the additional information, just wondering how to connect to
> the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using
> Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also
> have
> > > > visual source safe on my machine, not sure if vss would have to be on
> the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to
> a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within
> the
> > > > > appropriate database. Source control is nice because you can look
> back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if
> necessary.
> > > If
> > > > > you don't store your stored procedures within source control I
> encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored
> procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query
> analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >
>