cloning a schema?

Is there a way to clone an entire database, without the data? Thanks for
any tips.Backup / restore + Clean-up the tables through scripts '
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||right click on the database in enterprise manager and generate sql script,
from there you can choose your options and get as detailed as you want.
thanks,
nivek
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Also, you can right-click on the database in the Query Analyzer and choose
Script Object to New Window As -> Create to generate a script for the schema
.
Changing options in the Query Analyzer will allow you to modify the amount
of detail generated (Tools -> Options -> Script Tab).
"Neil W." wrote:

> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>
>|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...

> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Thanks for the reply. Sorry to be dense, but which tool are you referring
to? I want to clone all aspects of the schema including triggers and
contraints.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||The article lists several tools, quite simply. Use the one which serves your
purpose and does what
you want it to do. If you want to do this from code, for instance, consider
writing an app that uses
DMO. Etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:%23WTtmWAJFHA.2852@.TK2MSFTNGP09.phx.gbl.
.
> Thanks for the reply. Sorry to be dense, but which tool are you referring
> to? I want to clone all aspects of the schema including triggers and
> contraints.
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
>
>

cloning a db under another name

I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.

> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.
|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>

cloning a db under another name

I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
--
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I have a database called Paris. I want to clone it but I want the new name to be Milan. I tried
>>creating a new database named Milan and restoring Paris over it but Management Studio would not
>>allow this. I had overwrite checked. How can I do this?
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>> --
>> Regards,
>> Gary Blakely
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
--
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>> I have a database called Paris. I want to clone it but I want the new
>> name to be Milan. I tried creating a new database named Milan and
>> restoring Paris over it but Management Studio would not allow this. I
>> had
>> overwrite checked. How can I do this?
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:
> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"? :)
> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan

cloning a db under another name

I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.

> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris
and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new nam
e
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:

> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:

> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"?

> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan

Cloned Drive

I have a machine named DOG with MSDE (computername=DOG). I clone the machine
to another and rename the machine CAT. When I do this, SQL Server reports
"Your SQL Server installation is either corrupt or has been tampered with
(unknown package id). Please rerun setup." I understand why this happens,
the computername changed.
Is there a way to change the package id to work with the new computername?
Of course I can reinstall MSDE, but that defeats the mai purpose of the
clone.
TIA,
Will
hi Will,
Will T wrote:
> I have a machine named DOG with MSDE (computername=DOG). I clone the
> machine to another and rename the machine CAT. When I do this, SQL
> Server reports "Your SQL Server installation is either corrupt or has
> been tampered with (unknown package id). Please rerun setup." I
> understand why this happens, the computername changed.
> Is there a way to change the package id to work with the new
> computername? Of course I can reinstall MSDE, but that defeats the
> mai purpose of the clone.
to the best of my knowledge, this was a problem with SQL Server 7.0 and MSDE
1.0(http://support.microsoft.com/default...b;en-us;229875), as SQL
Server 7.0 could be "managed" rerunning the setup (
http://databasejournal.com/features/...0894_2194711_3 ),
where no work around was available for MSDE 1.0...
for SQL Server 2000, if you change the computer name, you only need to
delete and re-create the server registration in Enterprise Manager, but as
MSDE doew not provide it, you need nothing on MSDE 2000...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Clone SQL SERVER question

Hello Guys,
I am working in a clone of SQL server and I have some question:
NEW SQLSERVER = Copy of OLD SQLSERVER
As the new sql server "NEW SQLSERVER" has the same configuration as
the "OLD SQLSERVER" all the jobs and others setups have still the old
"OLD SQLSERVER" settings. I have already done this operation for the
backups.
update backupset
set machine_name= 'NEW SQLSERVER'
where machine_name = 'OLD SQLSERVER'
what do you advice me to do in order to set up this machine currently
Any suggestion will be appreciate
Ina
Hi
Have you scripted the jobs and changed any references to the old server? Are
there any hard coded references in Stored procedure, triggers, views etc?
DTS packages can be exported using the DTS Backup tool
http://www.sqldts.com/default.aspx?242
Are you using replication?
If the server is not on the same domain or if you had local accounts for
logins, you will have to change them see
http://support.microsoft.com/kb/246133/. It may be necessary to also change
orphaned users http://support.microsoft.com/kb/274188/
John
"ina" wrote:

> Hello Guys,
> I am working in a clone of SQL server and I have some question:
> NEW SQLSERVER = Copy of OLD SQLSERVER
> As the new sql server "NEW SQLSERVER" has the same configuration as
> the "OLD SQLSERVER" all the jobs and others setups have still the old
> "OLD SQLSERVER" settings. I have already done this operation for the
> backups.
> update backupset
> set machine_name= 'NEW SQLSERVER'
> where machine_name = 'OLD SQLSERVER'
> what do you advice me to do in order to set up this machine currently
> Any suggestion will be appreciate
> Ina
>
|||Thanks John,
I haven't done anything concerning replication, views and triggers in
the old server, I had only setup up backups and maintenance plans.
Thanks for this documents I will get through.
Ina
Thank you a lot for all
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you scripted the jobs and changed any references to the old server? Are
> there any hard coded references in Stored procedure, triggers, views etc?
> DTS packages can be exported using the DTS Backup tool
> http://www.sqldts.com/default.aspx?242
> Are you using replication?
> If the server is not on the same domain or if you had local accounts for
> logins, you will have to change them see
> http://support.microsoft.com/kb/246133/. It may be necessary to also change
> orphaned users http://support.microsoft.com/kb/274188/
>
> John
> "ina" wrote:

Clone SQL SERVER question

Hello Guys,
I am working in a clone of SQL server and I have some question:
NEW SQLSERVER = Copy of OLD SQLSERVER
As the new sql server "NEW SQLSERVER" has the same configuration as
the "OLD SQLSERVER" all the jobs and others setups have still the old
"OLD SQLSERVER" settings. I have already done this operation for the
backups.
update backupset
set machine_name= 'NEW SQLSERVER'
where machine_name = 'OLD SQLSERVER'
what do you advice me to do in order to set up this machine currently
Any suggestion will be appreciate
InaHi
Have you scripted the jobs and changed any references to the old server? Are
there any hard coded references in Stored procedure, triggers, views etc?
DTS packages can be exported using the DTS Backup tool
http://www.sqldts.com/default.aspx?242
Are you using replication?
If the server is not on the same domain or if you had local accounts for
logins, you will have to change them see
http://support.microsoft.com/kb/246133/. It may be necessary to also change
orphaned users http://support.microsoft.com/kb/274188/
John
"ina" wrote:
> Hello Guys,
> I am working in a clone of SQL server and I have some question:
> NEW SQLSERVER = Copy of OLD SQLSERVER
> As the new sql server "NEW SQLSERVER" has the same configuration as
> the "OLD SQLSERVER" all the jobs and others setups have still the old
> "OLD SQLSERVER" settings. I have already done this operation for the
> backups.
> update backupset
> set machine_name= 'NEW SQLSERVER'
> where machine_name = 'OLD SQLSERVER'
> what do you advice me to do in order to set up this machine currently
> Any suggestion will be appreciate
> Ina
>|||Thanks John,
I haven't done anything concerning replication, views and triggers in
the old server, I had only setup up backups and maintenance plans.
Thanks for this documents I will get through.
Ina
Thank you a lot for all
John Bell wrote:
> Hi
> Have you scripted the jobs and changed any references to the old server? Are
> there any hard coded references in Stored procedure, triggers, views etc?
> DTS packages can be exported using the DTS Backup tool
> http://www.sqldts.com/default.aspx?242
> Are you using replication?
> If the server is not on the same domain or if you had local accounts for
> logins, you will have to change them see
> http://support.microsoft.com/kb/246133/. It may be necessary to also change
> orphaned users http://support.microsoft.com/kb/274188/
>
> John
> "ina" wrote:
> > Hello Guys,
> >
> > I am working in a clone of SQL server and I have some question:
> >
> > NEW SQLSERVER = Copy of OLD SQLSERVER
> >
> > As the new sql server "NEW SQLSERVER" has the same configuration as
> > the "OLD SQLSERVER" all the jobs and others setups have still the old
> > "OLD SQLSERVER" settings. I have already done this operation for the
> > backups.
> >
> > update backupset
> > set machine_name= 'NEW SQLSERVER'
> > where machine_name = 'OLD SQLSERVER'
> >
> > what do you advice me to do in order to set up this machine currently
> >
> > Any suggestion will be appreciate
> >
> > Ina
> >
> >

Clone SQL SERVER question

Hello Guys,
I am working in a clone of SQL server and I have some question:
NEW SQLSERVER = Copy of OLD SQLSERVER
As the new sql server "NEW SQLSERVER" has the same configuration as
the "OLD SQLSERVER" all the jobs and others setups have still the old
"OLD SQLSERVER" settings. I have already done this operation for the
backups.
update backupset
set machine_name= 'NEW SQLSERVER'
where machine_name = 'OLD SQLSERVER'
what do you advice me to do in order to set up this machine currently
Any suggestion will be appreciate
InaHi
Have you scripted the jobs and changed any references to the old server? Are
there any hard coded references in Stored procedure, triggers, views etc?
DTS packages can be exported using the DTS Backup tool
http://www.sqldts.com/default.aspx?242
Are you using replication?
If the server is not on the same domain or if you had local accounts for
logins, you will have to change them see
http://support.microsoft.com/kb/246133/. It may be necessary to also change
orphaned users http://support.microsoft.com/kb/274188/
John
"ina" wrote:

> Hello Guys,
> I am working in a clone of SQL server and I have some question:
> NEW SQLSERVER = Copy of OLD SQLSERVER
> As the new sql server "NEW SQLSERVER" has the same configuration as
> the "OLD SQLSERVER" all the jobs and others setups have still the old
> "OLD SQLSERVER" settings. I have already done this operation for the
> backups.
> update backupset
> set machine_name= 'NEW SQLSERVER'
> where machine_name = 'OLD SQLSERVER'
> what do you advice me to do in order to set up this machine currently
> Any suggestion will be appreciate
> Ina
>|||Thanks John,
I haven't done anything concerning replication, views and triggers in
the old server, I had only setup up backups and maintenance plans.
Thanks for this documents I will get through.
Ina
Thank you a lot for all
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you scripted the jobs and changed any references to the old server? A
re
> there any hard coded references in Stored procedure, triggers, views etc?
> DTS packages can be exported using the DTS Backup tool
> http://www.sqldts.com/default.aspx?242
> Are you using replication?
> If the server is not on the same domain or if you had local accounts for
> logins, you will have to change them see
> http://support.microsoft.com/kb/246133/. It may be necessary to also chang
e
> orphaned users http://support.microsoft.com/kb/274188/
>
> John
> "ina" wrote:
>

Clone SQL DB (using VB?)

Hi all,
I'm having a SQL Server database called X. For testing and training, I
would like to make a copy of the entire DB to Y. This copy should
include all data, properties, indexes, views, etc. Unfortunately, all
my attempts to automate this failed...
Server = MSDE2a
Original database is a database marked for replication so I can't make
an easy copy...
App = VB6
- SQL job: fails on certain tables due to large 'memo' fields (ntext >
10Mb).
- DAO: trying to copy the tables from dbX to dbY fails while
getting/setting properties. Even when getting the fields (for each
srcField in srcTable.Fields; set destField = srcField) gives errors...
- ADOX: Haven't been able to get all the properties and indexes across.
ADOX doesn't seem to have all methods available to accomplish that.
Current idea is to take a script from the DB, adjust the paths and
names and use that to generate the DB. Afterwards, loop through all
tables and copy the contents from dbX to dbY.
While I haven't looked into it, I might run into a problem because
probably not all tools are installed on the application PC and without
them so I can't run SQL scripts from the command line... Not sure if I
can distribute all tools (MSDE) to make that working.
Another thing might be that I need to find out what the relative path
is to the server in order to create the files at the correct locations.
They are different because the app is on PC1 and the SQLserver is on
PC2. So I need to update the script (that wouldn't be the big problem)
but I'm not sure on how this would be interpreted in the above
scenario...
Is this the correct way to copy a DB including indexes and properties
(like default values, etc.)? Or is there something easier?
Many thanks,
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Couldn't you simply backup the database on the source server and the copy
the backup file to a share on the destination server and do a restore?'
Jim
"Gijs Beukenoot" <Gijs.DOT.Beukenoot@.AT@.Wanadoo.DOT.nl> wrote in message
news:mn.ba4e7d52bd68e489.23024@.Wanadoo.DOT.nl...
> Hi all,
> I'm having a SQL Server database called X. For testing and training, I
> would like to make a copy of the entire DB to Y. This copy should include
> all data, properties, indexes, views, etc. Unfortunately, all my attempts
> to automate this failed...
> Server = MSDE2a
> Original database is a database marked for replication so I can't make an
> easy copy...
> App = VB6
> - SQL job: fails on certain tables due to large 'memo' fields (ntext >
> 10Mb).
> - DAO: trying to copy the tables from dbX to dbY fails while
> getting/setting properties. Even when getting the fields (for each
> srcField in srcTable.Fields; set destField = srcField) gives errors...
> - ADOX: Haven't been able to get all the properties and indexes across.
> ADOX doesn't seem to have all methods available to accomplish that.
> Current idea is to take a script from the DB, adjust the paths and names
> and use that to generate the DB. Afterwards, loop through all tables and
> copy the contents from dbX to dbY.
> While I haven't looked into it, I might run into a problem because
> probably not all tools are installed on the application PC and without
> them so I can't run SQL scripts from the command line... Not sure if I can
> distribute all tools (MSDE) to make that working.
> Another thing might be that I need to find out what the relative path is
> to the server in order to create the files at the correct locations. They
> are different because the app is on PC1 and the SQLserver is on PC2. So I
> need to update the script (that wouldn't be the big problem) but I'm not
> sure on how this would be interpreted in the above scenario...
> Is this the correct way to copy a DB including indexes and properties
> (like default values, etc.)? Or is there something easier?
> Many thanks,
> GB
> --
> Your eyes are weary from staring at the CRT. You feel sleepy. Notice how
> restful it is to watch the cursor blink. Close your eyes. The opinions
> stated above are yours. When I snap my fingers, you cannot imagine why you
> ever felt otherwise. <snap>
>
|||Jim Young drukte met precisie uit :
> Couldn't you simply backup the database on the source server and the copy the
> backup file to a share on the destination server and do a restore?'
> Jim
>
<<cut>>
Jim,
Both databases are on the same machine so that makes it a little easier
to do that. And, I've done that a few times (using Enterprise Manager
from another machine). The only problems I saw then is in the (odd)
naming of the databasefiles. I can't remember exactly, but I remember
the filenames where correct (dbY) but in some of the properties, the
internal name was still <path>\dbX. I'll admit, so far, this seems to
be the easiest way to accomplish all this. If I code it, the users
shouldn't be able to mess things up (like using EM to do this; it
really makes you read everything twice before you restore it the wrong
way...)
I'll guess I'll hop along that path then, thanks
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>

Clone new database from existing

How do I go about creating a cloned copy of an existing database on the same server? I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".

I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:

* Had full backups of production db
* Detached production database I wanted to clone
* From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_"
* Re-attached prodution db
* Attached "Testing_..." database

Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:\program files\microsoft sql server\mssql.1\data\...". I rename the datbase, removing full path and prefixing with "Testing_".

* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database.
* Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db.
* Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well.
* Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy.
* Detached cloned copy of "Testing_" db and moved it out of the data folder.
* Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree.
* Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db!
* Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.

Q1: What I am doing wrong? Sql Express was obviously as confused as me somewhere along the line.

Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.

TIA...

Rick

hi Rick,

when doing this kind of "sorcery", please be carefull when "re-registering" the database..

just to cross check, usually you should:
-detach the original db;

-copy it elsewhere or rename destination files (mdf, ldf and eventual ndf) say, with your prefix.. (orig.mdf becomes clone_of_orig.mdf and the like);

-reattach the original db via

USE [master]

GO

CREATE DATABASE [myDB] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB_log.ldf' )

FOR ATTACH

GO

-the original logical db name is correct, as the original physical file(s) pointers..

-attach the cloned db via

USE [master]

GO

CREATE DATABASE [CLONE_of_myDB] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB_log.ldf' )

FOR ATTACH

GO

as you can see, the logical name of the db is different, as are the referenced phisical file names..

-the internal logical names of each physical file are not considered to collide with other database's logical names, so you are not in trouble here..

regards

|||Hmmh... what you described is exactly what I did, with pecuilar results, except use Sql Express Manager.

- I detached original db
- Copied to new set and renamed (cloned)
- Reattached original db
- Attached cloned db

Is Sql Express Manager not up to this task?

Rick|||

Do you get an error message or what are you experiencing ? If you can′t get attach / detach to run for you, you can also use the backup / restore functionality for this.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||All I got was some meaningless generic "operation failed" type of error, plus, the odd behavior I described above when sanitizing (running update queries against) the cloned database.

I did try restoring the source db and specifying clone db as target, but was prevented from doing so with a message to the effect of "target database not in dataset so not allowable."

If somebody wouldn't mind trying to clone a db from the same server through Sql Express Manager, and let me know the results, I'd feel better about trying it again. When I tried it, I discovered I was simultaneously sanitizing both databases.

Rick|||The 'safest' (and easiest) method to do this is to do a backup, and then restore to a different name and/or location. Read up on RESTORE in Books Online.|||Alright, I was able to do that through Express Manager this time. Much easier and safer. Thanks, both of you.

Rick

Clone DB

Helloo

Can I backup DB programatically, and then restore it with different name also programatically?

meaning:
I have db2006, can I backup this db then restore it as db2007 programatically?using a stored procedure for example?

So by doing this the user will be using db2007 @. the begininng of the year and so on each year

I'm using sql2000

Please I need your help
Thank you

hi,

you can use sp_attach and sp_detach.

notice you can attach it with different db_name

Examples

EXEC sp_detach_db 'pubs', 'true'

copy the files and attach it with different dbname

EXEC sp_attach_db @.dbname = N'pubs1', 
 @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
 @.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

|||

Thank you for your reply

but I had explained in a wrong way

I need to create a dublicate for this database programatically
meaning if i have db2004, I need to create db2005 programatically same as db2004(tables and procedures, only structure)

Is that possible?

thank you

|||

you can right click the database in the Enterprise manager or management studio

and clcik on task>generate scripts

|||

Look into the smo objects in books online. You could probably build a program to do this pretty easily to script out the database, create a clone database, and apply the script.

If this is a production database, if you have maintain scripts of the database, that is probably the best way to go, just by applying the scripts (but that takes a good amount of discipline on all developers, which can be hard to maintain depending on corporate culture)

Clone a SQL Server

We are in the process of upgrading from SQL Server 7 to SQL Server 2000.

Before we do that we want to run our system on our new server in a test environment to iron out any issues which may arrive from the upgrade, and to use for future testing. Is there a way to 'clone' a SQL 7 server onto a 2nd physical machine.

ThanksYou could restore sql7 database on sql2k box and do whatever tests needed.|||Originally posted by oj_n
You could restore sql7 database on sql2k box and do whatever tests needed.

I need to make sure all of the DTS pacjkages go as well.|||Here is a good kb that should help you...

http://support.microsoft.com/?id=314546|||There is a utility called DTSBackup2000 that's available free from SQLDTS.com. It works quite well, though I don't know what impact migrating from 7.0 to 2000 might have.

Regards,

Hugh Scott

Originally posted by GregCrossan
I need to make sure all of the DTS pacjkages go as well.

Clone a set of rows from two-related-tables to a the same-two-related-tables

I want to implement a "cloning" feature in my application and this
involves cloning related data two.
Having this structure (simplified but representative of my problem)
CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename
VARCHAR(30) NOT NULL)
CREATE TABLE Department (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL, enteid INTEGER NOT NULL REFERENCES Enterprise
(enteid))
CREATE TABLE Employee (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES Department (deptid))
I want to:
Clone a complete Enterprise, having @.enteid as a parameter (the id of
the enterprise to clone ) :
1 - Copy one enterrpise row. Obtaining a @.new_enteid
2 - Copy all Departaments rows associated to @.enteid (@.deptid's).
Obtaining @.new_deptid's and associating this new rows to @.new_enteid
3 - Copy all Employee rows related to all @.deptid's and associating
this new rows to the correspondig @.new_deptid's
Is this possible to make in one SQL sentence
Best Regards
Fabio Cavassini
http://fabioon.blogspot.com/Fabio Cavassini wrote:
> I want to implement a "cloning" feature in my application and this
> involves cloning related data two.
> Having this structure (simplified but representative of my problem)
> CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename
> VARCHAR(30) NOT NULL)
> CREATE TABLE Department (deptid INTEGER IDENTITY PRIMARY KEY, deptname
> VARCHAR(30) NOT NULL, enteid INTEGER NOT NULL REFERENCES Enterprise
> (enteid))
> CREATE TABLE Employee (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL, employeename VARCHAR(30) NOT NULL, deptid INTEGER
> NOT NULL REFERENCES Department (deptid))
> I want to:
> Clone a complete Enterprise, having @.enteid as a parameter (the id of
> the enterprise to clone ) :
> 1 - Copy one enterrpise row. Obtaining a @.new_enteid
> 2 - Copy all Departaments rows associated to @.enteid (@.deptid's).
> Obtaining @.new_deptid's and associating this new rows to @.new_enteid
> 3 - Copy all Employee rows related to all @.deptid's and associating
> this new rows to the correspondig @.new_deptid's
> Is this possible to make in one SQL sentence
> Best Regards
> Fabio Cavassini
> http://fabioon.blogspot.com/
Why would you ever want to duplicate data in a table? Don't you have
any other keys? Take two enterprises of the same name for example - how
will you tell them apart? Duplicating the data in the Employee table
would violate Second Normal Form.
The right answer is to design a better normalized data model. The wrong
answer is to wreck data integrity even further by using the
SCOPE_IDENTITY() function, which will allow you to fill your tables
with redundant data in exactly the way you have proposed.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The example is only representative of the problem, in fact I extracted
the data model from an old post that you posted ;)
(http://groups.google.com/group/micr...618a7b00b?hl=en)
The real data model is the following:
My enterprise works with Service_Orders, these service orders are
populated with:
Sell_Articles: This is actually what my enterprise sells to it's
customers
and
Buy_Articles: This is what my enterprise buys to it's providers to get
the Sell Articles
This is the real data model:
CREATE TABLE OrderService (osid INTEGER IDENTITY PRIMARY KEY)
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname
VARCHAR(30) NOT NULL)
CREATE TABLE Buy_Article_Type (batid INTEGER IDENTITY PRIMARY KEY,
batname
VARCHAR(30) NOT NULL)
CREATE TABLE Sell_Article (said INTEGER IDENTITY PRIMARY KEY, satid
INTEGER NOT NULL REFERENCES Sell_Article_Type (satid), osid
INTEGER NOT NULL REFERENCES OrderService (osid), amount DECIMAL NOT
NULL)
CREATE TABLE Buy_Article (baid INTEGER IDENTITY PRIMARY KEY, batid
INTEGER NOT NULL REFERENCES Buy_Article_Type (batid),said
INTEGER NOT NULL REFERENCES Sell_Article (said), amount DECIMAL NOT
NULL)
Now, it's very common that most clients always requires same (or very
near) ServiceOrders, so that's why I need to implement a feature to
"clone" ServiceOrders, This clone will be used as a template for the
user to modify it if needed.
What I need is the SQL Statement that allows me to "clone" a service
order including all it's relationships: Sell_Articles and Buy_Articles.
Hope you can help me
Regards
Fabio|||Fabio Cavassini wrote:
> The example is only representative of the problem, in fact I extracted
> the data model from an old post that you posted ;)
> (http://groups.google.com/group/micr...618a7b00b?hl=en)
>
You did not. You changed it by taking out the alternate keys which
makes the model useless and your requirement highly suspect.

> The real data model is the following:
> My enterprise works with Service_Orders, these service orders are
> populated with:
> Sell_Articles: This is actually what my enterprise sells to it's
> customers
> and
> Buy_Articles: This is what my enterprise buys to it's providers to get
> the Sell Articles
> This is the real data model:
>
Again all the keys are missing. IDENTITY should not be the only key of
any table, that's what UNIQUE constraints are for. You need to fix this
first.

> Now, it's very common that most clients always requires same (or very
> near) ServiceOrders, so that's why I need to implement a feature to
> "clone" ServiceOrders, This clone will be used as a template for the
> user to modify it if needed.
>
A database designer is supposed to eliminate redundancy - not design it
in. With proper design the idea of cloning data in a table would be A)
impossible because it would violate integrity constraints, B)
unecessary. If you don't understand database design principles such as
normalization then I recommend you study before you try to implement
this. Alternatively, add the missing candidate keys to your DDL and
post again so we can help you better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>Again all the keys are missing. IDENTITY should not be the only key of
>any table, that's what UNIQUE constraints are for. You need to fix this
>first.
Can you tell me how would you change the model to insert these keys?
---
CREATE TABLE OrderService (osid INTEGER IDENTITY PRIMARY KEY)
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname
VARCHAR(30) NOT NULL)
CREATE TABLE Buy_Article_Type (batid INTEGER IDENTITY PRIMARY KEY,
batname
VARCHAR(30) NOT NULL)
CREATE TABLE Sell_Article (said INTEGER IDENTITY PRIMARY KEY, satid
INTEGER NOT NULL REFERENCES Sell_Article_Type (satid), osid
INTEGER NOT NULL REFERENCES OrderService (osid), amount DECIMAL NOT
NULL)
CREATE TABLE Buy_Article (baid INTEGER IDENTITY PRIMARY KEY, batid
INTEGER NOT NULL REFERENCES Buy_Article_Type (batid),said
INTEGER NOT NULL REFERENCES Sell_Article (said), amount DECIMAL NOT
NULL)
---

>A database designer is supposed to eliminate redundancy - not design it
>in. With proper design the idea of cloning data in a table would be A)
>impossible because it would violate integrity constraints, B)
>unecessary. If you don't understand database design principles such as
>normalization then I recommend you study before you try to implement
>this. Alternatively, add the missing candidate keys to your DDL and
>post again so we can help you better.
Well, I know normalization principles, but I think you missed some of
my description of the problem or maybe I should never used the world
"clon".
The idea it's not to make exact clones of set of Order Services, the
idea it's to use existing Order Services as "templates" for new ones,
but this will be different, not the same.
The idea is so simple as Word Templates are, when you use a Word
Template, the New Document you create includes the Template Code
(redundancy) and you can modify it at your desire. IMHO sometimes
redundancy it's necessary to keep a system simple
Regards
Fabio|||Fabio Cavassini wrote:
> Can you tell me how would you change the model to insert these keys?
> ---
>
For example:
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname VARCHAR(30) NOT NULL UNIQUE)
I don't know what the purpose of the OrderService table is. Apparently
it contains no real data. I could understand if it was just used to
generate a value used as a unique key in another table but that appears
not to be the case here.
I don't know about Sell_Article or Buy_Article either. Apparently they
represent different amounts for each type. At a guess the logical
candidate key in Sell_Article may be (satid, osid)? Possibly you have
some entities missing. It's very hard to guess the meaning just from a
list of column names.
The IDENTITY is called a *surrogate* key. That means it stands in for
some other candidate key in a foreign key reference. In your logical
model you should always be able to substitute the business key (aka
natural key or logical key) for the surrogate in the same table. To
enforce entity integrity you need to declare the business key as well
for the obvious reason that IDENTITY does not prevent duplicates in a
table - it just enumerates them.
Now do a little exercise with your logical model. Remove the IDENTITY
columns and try to normalize the schema to BCNF. The results may be
enlightening. :-)

> The idea it's not to make exact clones of set of Order Services, the
> idea it's to use existing Order Services as "templates" for new ones,
> but this will be different, not the same.
Even if this was desirable from a logical point of view (it clearly
isn't) it would be very impractical since it would prevent you from
ever declaring any unique keys. The problem is you are trying to
implement GUI functionality in the database. I suggest you persist the
real data when the user has something real to store - not before.
Creating a redundant row and then updating it isn't likely scale well.

> IMHO sometimes
> redundancy it's necessary to keep a system simple
If you seriously think that redundancy simplifies databases then just
trawl the archives of this group for the thousands of posts from those
desperately trying to eliminate redundancy from bad data models. Better
read some books on relational design too. I recommend Date's "Database
in Depth" BTW.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To answer the question in your original post, if you look at my replies
in the link you posted you'll see two solutions for inserting the rows
to the referencing table. The solution with the alternate keys uses
single set-based INSERT statements. The solution without the keys
requires a WHILE loop, several statements and row-at-a-time INSERTs.
I'll leave you to decide which is "simpler". I know which I prefer...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Clone a record

Hello --
I want to create a new record in the "sales_quotes" table so the salesperson
can explore alternate quote(s) to present to a customer.
The new record will have these characteristics:
- identical values for all fields with the exception of the primary key
field
- the primary key value of the new record will have a user-entered value,
validated to assure it is unique
Can someone suggest how to do this ...
- updatable query
- create record in a temp table with identical structure, modify the primary
key field, then append to quotes table
- other
Thanks for any help.
Larry Mehl
L Mehl wrote:

>Hello --
>I want to create a new record in the "sales_quotes" table so the salesperso
n
>can explore alternate quote(s) to present to a customer.
>The new record will have these characteristics:
>- identical values for all fields with the exception of the primary key
>field
>
Identical for all fields of what row? I don't see the point of
inserting a new sales quote that is identical to an existing quote
except for the primary key (which appears to be entirely artificial).
But if you have some real need to do this, it would look something like
insert into sales_quotes
select
@.new_primary_key_value,
column_A,
column_B,
column_C,
.
from sales_quotes
where primary_key_column = @.old_primary_key_value
It makes more sense to me that you would want to add a new
row to a quotes table when there is a new, different quote, not
when there is no new quote.
Steve Kass
Drew University

>- the primary key value of the new record will have a user-entered value,
>validated to assure it is unique
>Can someone suggest how to do this ...
>- updatable query
>- create record in a temp table with identical structure, modify the primar
y
>key field, then append to quotes table
>- other
>Thanks for any help.
>Larry Mehl
>
>|||No reason to copy a row in your table. I guess what you mean is that you
want to present that data to the salesperson so that she can review it,
modify some data and then save a *modified* version of the row - not just
modify the key, which would serve no obvious purpose. Do this the same way
the enter any new row - the "cloned" values just become the defaults in the
user's entry form. This is client-side functionality.
David Portas
SQL Server MVP
--|||If [sales_quotes] is a table seperate from sales, then just do a select into
[sales_quotes] from [sales] and then let the salesperson can edit the new
record using a form. I don't see why the primary key should be user entered;
instead let them enter a descriptive text or key from cutomer and date
entered.
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:LGWMd.556$mG6.504@.newsread1.news.pas.earthlink.net...
> Hello --
> I want to create a new record in the "sales_quotes" table so the
> salesperson
> can explore alternate quote(s) to present to a customer.
> The new record will have these characteristics:
> - identical values for all fields with the exception of the primary key
> field
> - the primary key value of the new record will have a user-entered value,
> validated to assure it is unique
> Can someone suggest how to do this ...
> - updatable query
> - create record in a temp table with identical structure, modify the
> primary
> key field, then append to quotes table
> - other
> Thanks for any help.
> Larry Mehl
>|||Steve --
Thanks. Sorry for the brief description of the need for this process.
The reason for the cloned record is so that the sales person's manager can
perform sensitivity analysis on the quote, with the possible result that it
becomes the quote that is submitted to the customer, and the "original"
quote is discarded.
I was aware of the method you show. I was looking for a way to avoid having
to enter all the non-primary-key values.
Larry
"Steve Kass" <skass@.drew.edu> wrote in message
news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
>
> L Mehl wrote:
>
salesperson
> Identical for all fields of what row? I don't see the point of
> inserting a new sales quote that is identical to an existing quote
> except for the primary key (which appears to be entirely artificial).
> But if you have some real need to do this, it would look something like
> insert into sales_quotes
> select
> @.new_primary_key_value,
> column_A,
> column_B,
> column_C,
> ..
> from sales_quotes
> where primary_key_column = @.old_primary_key_value
> It makes more sense to me that you would want to add a new
> row to a quotes table when there is a new, different quote, not
> when there is no new quote.
> Steve Kass
> Drew University
>
primary|||All --
Thank you for the suggestions.
Larry Mehl
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:LGWMd.556$mG6.504@.newsread1.news.pas.earthlink.net...
> Hello --
> I want to create a new record in the "sales_quotes" table so the
salesperson
> can explore alternate quote(s) to present to a customer.
> The new record will have these characteristics:
> - identical values for all fields with the exception of the primary key
> field
> - the primary key value of the new record will have a user-entered value,
> validated to assure it is unique
> Can someone suggest how to do this ...
> - updatable query
> - create record in a temp table with identical structure, modify the
primary
> key field, then append to quotes table
> - other
> Thanks for any help.
> Larry Mehl
>|||Steve --
I am a newbie concerning SQL queries in VB connected to a SQL Server
database.
In an ACCESS/VBA application I used SQL as in your example "insert into
sales_quotes ..." below, in a command
CurrentDb.Execute "insert into sales_quotes ...", dbFailOnError
Can you tell me how to do the same thing in VB connected to a SQL Server
database, or suggest some articles or other resoulced from which I can learn
how?
I can connect to the db and populate a datagrid using ADO. Is your example
"compatible" with ADO, or is it another technique entirely?
I will eventually convert my app to dotNET, so I would appreciate knowing if
your method will work well on that platform.
Thank you for any more help.
Larry
"Steve Kass" <skass@.drew.edu> wrote in message
news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
>
> L Mehl wrote:
>
salesperson
> Identical for all fields of what row? I don't see the point of
> inserting a new sales quote that is identical to an existing quote
> except for the primary key (which appears to be entirely artificial).
> But if you have some real need to do this, it would look something like
> insert into sales_quotes
> select
> @.new_primary_key_value,
> column_A,
> column_B,
> column_C,
> ..
> from sales_quotes
> where primary_key_column = @.old_primary_key_value
> It makes more sense to me that you would want to add a new
> row to a quotes table when there is a new, different quote, not
> when there is no new quote.
> Steve Kass
> Drew University
>
primary|||The answer appears to be
connection.Execute
After I understand the third parameter 'Options' I think it will work.
Does anyone have examples or resources for using connection.Execute?
Larry Mehl
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:94CNd.2141$UX3.1163@.newsread3.news.pas.earthlink.net...
> Steve --
> I am a newbie concerning SQL queries in VB connected to a SQL Server
> database.
> In an ACCESS/VBA application I used SQL as in your example "insert into
> sales_quotes ..." below, in a command
> CurrentDb.Execute "insert into sales_quotes ...", dbFailOnError
> Can you tell me how to do the same thing in VB connected to a SQL Server
> database, or suggest some articles or other resoulced from which I can
learn
> how?
> I can connect to the db and populate a datagrid using ADO. Is your
example
> "compatible" with ADO, or is it another technique entirely?
> I will eventually convert my app to dotNET, so I would appreciate knowing
if
> your method will work well on that platform.
> Thank you for any more help.
> Larry
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
> salesperson
value,
> primary
>