Close cursor in another procedure (part II)

<I'm sorry for new post but I couldn't reply to group because of errors>
Hi David,
I have a lot of reasons to using cursor and I don't know better way to do
this.
For example;
I control datas in the cursor like this:
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
SELECT @.BLABLA = BLABLA FROM BLABLA
IF @.MyVariable < @.BLABLA THEN
BEGIN
RAISEERROR(...)
..
END
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
And this is insert script: "INSERT INTO TableA(...) SELECT ... FROM xxx"
Better idea?CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
AS
IF EXISTS
(SELECT *
FROM blabla AS B
JOIN Inserted AS I
ON I.myfield < B.blabla
WHERE ... /* ' unspecified */)
BEGIN
EXEC my_error_sp
RAISERROR ...
END
GO
David Portas
SQL Server MVP
--|||Thanks David,
Let me ask last question.
I have a log system like this:
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
DECLARE @.RESULT BIGINT
DECLARE @.TABLE_NAME VARCHAR(128)
DECLARE @.OBJ_ID INT
SELECT @.OBJ_ID = parent_obj FROM sysobjects WHERE id = @.@.PROCID
SELECT @.TABLE_NAME = OBJECT_NAME(@.OBJ_ID)
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.TABLE_NAME, @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC @.RESULT = SpLog @.TABLE_NAME, @.MyVariable
//if log inserted successful then do other operations else rollback and
exit
IF @.RESULT = -1
BEGIN
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor
DEALLOCATE MyCursor
RETURN
END
DoSomething
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
END
--
DECLARE PROCEDURE SpLog (@.TABLE_NAME varchar(128), ID int)
...
INSERT INTO LOG_TABLE (TABLE_NAME, ID, OPERATION_DATE) values (...)
IF @.@.ERROR <> 0
RETURN -1
...
--
How can I do without any cursor?|||See if this helps:
How do I audit changes to SQL Server data?
http://www.aspfaq.com/show.asp?id=2448
AMB
"Tod" wrote:

> Thanks David,
> Let me ask last question.
> I have a log system like this:
> --
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> DECLARE @.RESULT BIGINT
> DECLARE @.TABLE_NAME VARCHAR(128)
> DECLARE @.OBJ_ID INT
> SELECT @.OBJ_ID = parent_obj FROM sysobjects WHERE id = @.@.PROCID
> SELECT @.TABLE_NAME = OBJECT_NAME(@.OBJ_ID)
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.TABLE_NAME, @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC @.RESULT = SpLog @.TABLE_NAME, @.MyVariable
> //if log inserted successful then do other operations else rollback and
> exit
> IF @.RESULT = -1
> BEGIN
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor
> DEALLOCATE MyCursor
> RETURN
> END
> DoSomething
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> END
> --
> DECLARE PROCEDURE SpLog (@.TABLE_NAME varchar(128), ID int)
> ...
> INSERT INTO LOG_TABLE (TABLE_NAME, ID, OPERATION_DATE) values (...)
> IF @.@.ERROR <> 0
> RETURN -1
> ...
> --
> How can I do without any cursor?
>
>|||The problem here is that you apparently want to call an SP for each row
in the INSERT. In itself, that is a weak concept for writing modular
data-manipulation code. You should aim to write set-based stored procs
that operate on SETS of data rather than one row at a time (except for
code supporting the UI where you often want to support single row
updates). Everything you want still can be possible without a cursor
but you may need to adjust the logic of your SpLog code. Since you
haven't told us what it does, I can't show you how to do it.
Also, note that if you need common code in triggers you can generate
the code semi-automatically from the metadata which reduces the need
for code re-use through procs. In general you want to minimize calling
other procs from a trigger.
David Portas
SQL Server MVP
--

Close cursor in another procedure

Hi,
I declared a cursor in an Insert trigger. In this trigger I called a sp. Can
I close this cursor in the sp?
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
CREATE MY_ERROR_SP AS
...
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
...
----Why have you put a cursor in a trigger? Cursors are rarely a good idea
and triggers are absolutely the last place you should use them. Updates
are set-based so triggers should be too.
The answer to your question is no, because your cursor is local. The
better answer is rewrite your trigger.
David Portas
SQL Server MVP
--|||Cursors by default hv global scope. Therefore, it is possible to create them
in 1 SP1 & close it in SP2 called from SP1.
Rakesh
"Tod" wrote:

> Hi,
> I declared a cursor in an Insert trigger. In this trigger I called a sp. C
an
> I close this cursor in the sp?
> ----
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> ...
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> ...
> END
> ...
> ----
> CREATE MY_ERROR_SP AS
> ...
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
> DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
> ...
> ----
>
>|||David is right.. since ur cursor is a local one, u will not be able to close
.
"Rakesh" wrote:
> Cursors by default hv global scope. Therefore, it is possible to create th
em
> in 1 SP1 & close it in SP2 called from SP1.
> Rakesh
> "Tod" wrote:
>

Close connection of SQLExpress!

Hi,

I write a .NET Windows Form that connect to SQLExpress datafile. After updating data, I want to zip the .mdf file and send email. However, I got an exeption that the .mdf file is used by other thread so I cant zip. Even I try to close all connection, I still cant zip.

Is there any way to detach/unlock .mdf file connecting by SQLExpress?

MA.

Hi,

if you are sure you closed down all conenctions, you can use the sp_detach command to detach the database. if you want to close all connection from the server side first you will have to use the ALTER DATABASE command first and change the state of the database to a SINGLe or admin mode.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Unfortunaterly, I dont control the connection but I want to force to close all connection from the client side (using C# code).

MA.

|||

Hi MA,

Jens is exactly correct, you'll need to force the database into single user mode:

ALTER DATABASE pubs
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

This will rollback all transactions and then you can detach the database as you like. I'm not sure what you mean you don't control the connections. If you can not connect to the database, then you can not close it's connections and you will not be able to close the connections.

Mike

Close all open connections

Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

I'm using SQL express, with visual studio pro 2005.

Thx for you quick responses

best regards

Luc N

please verify the code I've used

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim com As New SqlCommand("sp_detach_db", con)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

'com.CommandType = Data.CommandType.StoredProcedure

'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))

'com.ExecuteNonQuery()

'MsgBox(d.UserName.ToString())

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

'com.CommandText = "DROP DATABASE " & d.Name.ToString

'com.CommandType = CommandType.Text

'com.ExecuteNonQuery()

'End If

End If

Next

com.Connection.Close()

Why do you think that the information form the blog is not working for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I've still got the message 'cannot drop the database because.......................'

I've modified my code , which seems to be working,

please reply your comments on this code

thx Luc

Try

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

Exit For

End If

Next

If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then

svr.KillAllProcesses(d.Name)

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

con.Close()

End If

Catch ex As Exception

' MsgBox(ex.Message)

End Try

|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.

close all existing connections and processes to a database

Dear all

I created this trigger on a table that i think failed while execution. I tried to modify it and run it again but it seems that i cant do that. If i try and delete the database i also cant - saying that it is still in use. But i am not using it and ther are no other users connected to it. I think the trigger has probably hit a loop and that is holding the link.

To close that i know that a solution would be to restart the SQL server instance but that would be a bit hard since the SQL server where my test database resides is a production server and has few other databases that are important and few users use them.

Is there any way through a SQL statement that there can be forced a delete? Or force close all the connections? Or force close all the processes without actually restarting the SQL server instance.

I have tried all options that were offered on some other forums like forcing it to a single user but even that operation can not be performed saying that the database is still in use.

Thank you so much for all your help and time.

Sincerely

Dan

You could cycle through the SPID's in master.dbo.sysprocess and KILL off any SPID's using the test database_id.

And then you have learned why you 'should' not 'play' on a production server. Create yourself a local server for experiementation and testing. Get an 'old', decommissioned desktop and convert it to a test server. There is no excuse for mucking up a production server.

close

Below, I have pasted a portion of an MS MSDN article dealing with a specific issue we are having with Report Server performance. The URL (http://support.microsoft.com/kb/821438/en-us) is the MS KB article on the described fix. The fix mentioned is for "ASP.NET 1.1" but our BP Report Server is using "ASP.NET 2.0".

Has anyone encountered and resolved and how?

Running on box:
ASP.NET 2.0.50727;
SSRS 2005 Sp1 + Hotfix#2175;
Win Server 2003, R2, x64, SP1
Trend Micro OfficeScan Cleint for Win 2003/xp v7.3
IIS Version (I cannot find this #) - ? IE 6.0

===========================
Report Manager or the report server runs very slowly

In some circumstances, ASP.NET applications run very slowly on computers that are running anti-virus software. If the Report Server Web service is restarting frequently, and you are running anti-virus software, you can obtain an ASP.NET fix from Microsoft Customer Support Services.

Symptoms include Web applications or Application Domains restarting for no apparent reason, slow performance, session restarts, and more. For more information about the symptoms, cause, and resolution, see Microsoft Knowledge Base article 821438.

You can find out whether there are excessive server restarts by viewing the number of reportserver_<timestamp>.log files. A new log is created each time the server starts. A large collection of logs created at very short intervals is an indication that the conditions described in article 821438 exist on your server
===========================

It appears the issue goes away with SQL2K5 SP-2 load. Thanks to whoever.....

cloning servers with sql server on them

Anyone have any experience with cloning servers that have sql server
installed. I am wondering what will happen when the new virtual server comes
up using a different name. sql server takes it name from the server name.
Will it just automatically adjust or is there a process to deal with that?
Thanks.
If it's SQL Server 2005 and it has never run after installation, it will
"fix" itself upon first startup. Else, it's a simple process to
drop/register the new server name (just like in 2000). If you have other
objects in there name/server dependencies, well, that gets a little more
complicated.
joe.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:68DB416E-E2B6-4E21-996C-2A99B37D6C01@.microsoft.com...
> Anyone have any experience with cloning servers that have sql server
> installed. I am wondering what will happen when the new virtual server
> comes
> up using a different name. sql server takes it name from the server name.
> Will it just automatically adjust or is there a process to deal with that?
> Thanks.
>
|||Hello Jason,
If you rename a server with SQL 2000/2005 installed, you may want to use
sp_dropserver to drop old server name, and then run sp_addserver to add the
new server name.
303774BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/default.aspx?scid=kb;EN-US;303774
317241Renaming a host computer for SQL Server causes stored procedure
debug to fail
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317241
281642PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming Windows Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;281642
Please let me know if you have any comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

cloning servers with sql server on them

Anyone have any experience with cloning servers that have sql server
installed. I am wondering what will happen when the new virtual server come
s
up using a different name. sql server takes it name from the server name.
Will it just automatically adjust or is there a process to deal with that?
Thanks.If it's SQL Server 2005 and it has never run after installation, it will
"fix" itself upon first startup. Else, it's a simple process to
drop/register the new server name (just like in 2000). If you have other
objects in there name/server dependencies, well, that gets a little more
complicated.
joe.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:68DB416E-E2B6-4E21-996C-2A99B37D6C01@.microsoft.com...
> Anyone have any experience with cloning servers that have sql server
> installed. I am wondering what will happen when the new virtual server
> comes
> up using a different name. sql server takes it name from the server name.
> Will it just automatically adjust or is there a process to deal with that?
> Thanks.
>|||Hello Jason,
If you rename a server with SQL 2000/2005 installed, you may want to use
sp_dropserver to drop old server name, and then run sp_addserver to add the
new server name.
303774 BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/defaul...kb;EN-US;303774
317241 Renaming a host computer for SQL Server causes stored procedure
debug to fail
http://support.microsoft.com/defaul...kb;EN-US;317241
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming Windows Server
http://support.microsoft.com/defaul...kb;EN-US;281642
Please let me know if you have any comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

CLONING PACKAGES

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Besides copying the .dtsx file and editing the copy to suit your needs?|||You can create a program to generate SSIS packages. If you want to reverse your current packages into C# code, take a look at http://www.ivolva.com/ssis_code_generator.html.|||You could also create a template for each type of load process and store them in the \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems folder. I think that's the correct place.|||Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.|||jwelch, that's an interesting solution. I'll try to play with you and let you know if it works. But it seems it's what I need.|||

LoveDanger wrote:

Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.

Using expressions, you can build just ONE package to do this though.

Provided the structures of all of the tables (both sources and destinations) are all the same... That is, you can dynamically adjust the tables the OLE DB sources and destinations go against.|||Thanks Phil, this should work. I assume the transformation part would be tricky (I'm not sure how to map all the columns dynamically), but I'll check if that could be done. Also, I believe the solution posted by jwelch (http://www.ivolva.com/ssis_code_generator.html) should work if one still wants to generate numerous packages.

Thank you all!
|||Note: My solution works if you are looping through tables that have the same structure. If they don't, then my solution won't work. Once you build the package (mappings and all) you can start using expressions to dynamically change the table names.|||Yeah, in my case we have different set of attributes for each dimension that I'm not sure how to handle. I used your approach with BCP though - use only one package to transfer data from a number of flat files to SQL Server tables. Though you still have to generate format files for each that pretty much translates your column mappings.|||

LoveDanger wrote:

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Sounds to me like you want to use templates. Matt explained where to drop them elsewhere in this thread.

-Jamie

|||

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

Does template allow you to do search and replace for the entire package?

|||

LoveDanger wrote:

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

It sounds as though you'll have to do that regardless of the solution though?

LoveDanger wrote:

Does template allow you to do search and replace for the entire package?

No, not really. You could open up the package's XML (right-click on the package and select 'View Code') and do a Find-Replace that way of that's what you want to do.

-Jamie

|||Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia|||

LoveDanger wrote:

Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia

Cool. Take a copy of the package before you alter it

Cloning a table

In my .NET code, with SQL Server 2000 as the backend, I need to create an
exact copy, without data, of an existing table in the same database, that
the user selects from a combo box. Any ideas on how to do this?You can create a copy without constraints or indexes using SELECT ... INTO.
Specify a WHERE clause to exclude data too:

SELECT * INTO MyNewTable FROM MyTable WHERE 1 = 0

Note that the user will need CREATE TABLE permissions. This isn't the kind
of thing one usually does in database applications though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michael Jackson" <michaeldjackson@.cox.net> wrote in message
news:%MsXd.6532$Wy.1516@.okepread02...
> In my .NET code, with SQL Server 2000 as the backend, I need to create an
> exact copy, without data, of an existing table in the same database, that
> the user selects from a combo box. Any ideas on how to do this?

Cloning a SQL 2005 Database

hi,
I have cloned a VM of a SQL Server 2005 database to a new server with a
different name.
I have run the sp_dropserver and sp_addserver commands as well documented to
change the server name within the sql instance to the new cloned server name.
The issue i have is that the logins on the new cloned server still contains
the local group and local account details from the old machine.
I have renamed the groups and accounts within NT, but when i try to change
rename the logins within sql , i get the error.
TITLE: Microsoft SQL Server Management Studio
Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
(ObjectExplorer)
ADDITIONAL INFORMATION:
Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER' .
(Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Rename+Login&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The name change cannot be performed because the SID of the new name does not
match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476[ /url]
BUTTONS:
OK
Many Thanks
Jon
Hi Jon
"Jon Bird" wrote:

> hi,
> I have cloned a VM of a SQL Server 2005 database to a new server with a
> different name.
> I have run the sp_dropserver and sp_addserver commands as well documented to
> change the server name within the sql instance to the new cloned server name.
> The issue i have is that the logins on the new cloned server still contains
> the local group and local account details from the old machine.
> I have renamed the groups and accounts within NT, but when i try to change
> rename the logins within sql , i get the error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
> (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER' .
> (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Rename+Login&LinkId=20476[/url]
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The name change cannot be performed because the SID of the new name does not
> match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476[ /url]
> --
> BUTTONS:
> OK
> --
>
> Many Thanks
> Jon
>
Have you looked at using the script in:
http://support.microsoft.com/kb/246133/
Then dropping the logins and re-creating new ones with the same SIDs.
John

Cloning a SQL 2005 Database

hi,
I have cloned a VM of a SQL Server 2005 database to a new server with a
different name.
I have run the sp_dropserver and sp_addserver commands as well documented to
change the server name within the sql instance to the new cloned server name.
The issue i have is that the logins on the new cloned server still contains
the local group and local account details from the old machine.
I have renamed the groups and accounts within NT, but when i try to change
rename the logins within sql , i get the error.
TITLE: Microsoft SQL Server Management Studio
--
Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
(ObjectExplorer)
--
ADDITIONAL INFORMATION:
Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Login&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The name change cannot be performed because the SID of the new name does not
match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476
--
BUTTONS:
OK
--
Many Thanks
JonHi Jon
"Jon Bird" wrote:
> hi,
> I have cloned a VM of a SQL Server 2005 database to a new server with a
> different name.
> I have run the sp_dropserver and sp_addserver commands as well documented to
> change the server name within the sql instance to the new cloned server name.
> The issue i have is that the logins on the new cloned server still contains
> the local group and local account details from the old machine.
> I have renamed the groups and accounts within NT, but when i try to change
> rename the logins within sql , i get the error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
> (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Login&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The name change cannot be performed because the SID of the new name does not
> match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> Many Thanks
> Jon
>
Have you looked at using the script in:
http://support.microsoft.com/kb/246133/
Then dropping the logins and re-creating new ones with the same SIDs.
John

Cloning a SQL 2005 Database

hi,
I have cloned a VM of a SQL Server 2005 database to a new server with a
different name.
I have run the sp_dropserver and sp_addserver commands as well documented to
change the server name within the sql instance to the new cloned server name
.
The issue i have is that the logins on the new cloned server still contains
the local group and local account details from the old machine.
I have renamed the groups and accounts within NT, but when i try to change
rename the logins within sql , i get the error.
TITLE: Microsoft SQL Server Management Studio
--
Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSS
QLSERVER.
(ObjectExplorer)
ADDITIONAL INFORMATION:
Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MS
SQLSERVER'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...in&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The name change cannot be performed because the SID of the new name does not
match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
For help, click:
http://go.microsoft.com/fwlink?Prod...98&LinkId=20476
BUTTONS:
OK
--
Many Thanks
JonHi Jon
"Jon Bird" wrote:

> hi,
> I have cloned a VM of a SQL Server 2005 database to a new server with a
> different name.
> I have run the sp_dropserver and sp_addserver commands as well documented
to
> change the server name within the sql instance to the new cloned server na
me.
> The issue i have is that the logins on the new cloned server still contain
s
> the local group and local account details from the old machine.
> I have renamed the groups and accounts within NT, but when i try to change
> rename the logins within sql , i get the error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSS
QLSERVER.
> (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MS
SQLSERVER'
.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...in&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The name change cannot be performed because the SID of the new name does n
ot
> match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...98&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> Many Thanks
> Jon
>
Have you looked at using the script in:
http://support.microsoft.com/kb/246133/
Then dropping the logins and re-creating new ones with the same SIDs.
John

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

cloning a database

What is the process that would allow us to create a copy of our production
database that we could use for testing puproses.
Don
Hi
Backup the database and restore it on your test server.
Regards
Mike
"don" wrote:

> What is the process that would allow us to create a copy of our production
> database that we could use for testing puproses.
> --
> Don
|||This is also a good test of the backup procedures that you should have in
place. Knowing how to restore from a backup is important!
Keith
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:6C2998EA-E002-4919-954E-335060B62641@.microsoft.com...[vbcol=seagreen]
> Hi
> Backup the database and restore it on your test server.
> Regards
> Mike
> "don" wrote:
production[vbcol=seagreen]
|||Thanks, found the info in the sqlserver online books 'How to restore a
database with a new name' .
"Keith Kratochvil" wrote:

> This is also a good test of the backup procedures that you should have in
> place. Knowing how to restore from a backup is important!
> --
> Keith
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:6C2998EA-E002-4919-954E-335060B62641@.microsoft.com...
> production
>

cloneing?

I have a merge replication that is really fouled up. I need to get things
back in order. Right now I have three offices working on the database
locally(no replication going on) At the end of the day I want to get all of
the new data back to the publisher so we can create a new snapshot. I was
told by the software vendor of the interface of the database to clone
without hostnames or supporting records. I am kinda at a loss as to what
that means. I see that I can export the data to another server and choose to
append the data, is that what he meant? Any help would be very much
appreciated.
Curt
I suggest you contact the vendor and ask him exactly what he means. Say you
did a search on BOL one clone, cloning and supporting records and came up
with no results.
If you do a reinitialization you will be prompted to upload the changes from
the subscribers before reinitializing. Perhaps this is what the vendor
meant.
I suggest before you even do this you go through the conflicts table(s)
using the conflict viewer and resolve any conflicts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Curt Shaffer" <curt@.chilitech.net> wrote in message
news:ch4k8c01hif@.enews3.newsguy.com...
> I have a merge replication that is really fouled up. I need to get things
> back in order. Right now I have three offices working on the database
> locally(no replication going on) At the end of the day I want to get all
of
> the new data back to the publisher so we can create a new snapshot. I was
> told by the software vendor of the interface of the database to clone
> without hostnames or supporting records. I am kinda at a loss as to what
> that means. I see that I can export the data to another server and choose
to
> append the data, is that what he meant? Any help would be very much
> appreciated.
> Curt
>

Cloned SQL 2005 Install

I need a little help with a cloned SQL 2005 install. We have a couple of Virtual Machines that SQL 2005 was installed on. The install was orginally completed on <pc1> and then duped over to <pc2>. The problem now is that <pc2> is showing information from <pc1> everywhere. I have reviewd this document: http://msdn2.microsoft.com/en-us/library/ms143799.aspx, and it doesn't seem to help any. I have used the sp_dropserver command with 'droplogins' to try to recreate the logins for <pc2> but it's still showing the logins from <pc1>. Can someone please help me out, I'm not sure what I'm doing wrong here. Thanks!

Am I looking at a re-install here?

The doc says there is no need to re-install, but those commands just aren't taking care of the problem. When I run @.@.servername it does show the correct server as node 0, but the logins are still there from <pc1>. I deleted the 3 logins that were referenced to the old PC, now I just can't get them re-created for <pc2>

|||What logins are you referring to?

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
--