Default database location?

I have the default location for Data setup to be G:\Microsoft SQL
Server\Data (Database Settings under server properties).
But, when I invoke "Copy Database" from Management, it places the data on
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
Why doesn't the Copy Database Wizard respect my setting?
OlavOlav
It probably takes it from a model database
SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
WHERE [name] = 'model'
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||I'm confused!
Why are there multiple places to configure the same kind of option?
Olav
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUaZJ8WXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Olav
> It probably takes it from a model database
> SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
> WHERE [name] = 'model'
>
>
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>|||You can view the default directory with the following :-
exec master..xp_regread
'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\Setup','SQLDataRoot'
It can be changed with EnterPrise Manager, but... this change may not stick
as you need sufficient permsion to write to registry. If you find it isn't
saving you will need to logon to the box as administrator.
HTH. Ryan
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||This is the result I got from the query on that machine:
RegQueryValueEx() returned error 2, 'The system cannot find the file
specified.'
Msg 22001, Level 1, State 1
(0 row(s) affected)
What does this indicate?
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...

> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>|||I checked in the Registry and it shows the following value for that key:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
In SQL Server Management Studio it shows:
G:\Microsoft SQL Server\Data
I'm still confused!
Why are there two different values for the same thing stored?
I'm running Management Studio logged in as an Administrator, so there should
be no problem writing to the registry.
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>

Default database location?

I have the default location for Data setup to be G:\Microsoft SQL
Server\Data (Database Settings under server properties).
But, when I invoke "Copy Database" from Management, it places the data on
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
Why doesn't the Copy Database Wizard respect my setting?
OlavOlav
It probably takes it from a model database
SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
WHERE [name] = 'model'
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||I'm confused!
Why are there multiple places to configure the same kind of option?
Olav
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUaZJ8WXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Olav
> It probably takes it from a model database
> SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
> WHERE [name] = 'model'
>
>
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>>I have the default location for Data setup to be G:\Microsoft SQL
>>Server\Data (Database Settings under server properties).
>> But, when I invoke "Copy Database" from Management, it places the data on
>> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
>> Why doesn't the Copy Database Wizard respect my setting?
>> Olav
>|||You can view the default directory with the following :-
exec master..xp_regread
'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLDataRoot'
It can be changed with EnterPrise Manager, but... this change may not stick
as you need sufficient permsion to write to registry. If you find it isn't
saving you will need to logon to the box as administrator.
--
HTH. Ryan
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||This is the result I got from the query on that machine:
RegQueryValueEx() returned error 2, 'The system cannot find the file
specified.'
Msg 22001, Level 1, State 1
(0 row(s) affected)
What does this indicate?
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>>I have the default location for Data setup to be G:\Microsoft SQL
>>Server\Data (Database Settings under server properties).
>> But, when I invoke "Copy Database" from Management, it places the data on
>> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
>> Why doesn't the Copy Database Wizard respect my setting?
>> Olav
>|||I checked in the Registry and it shows the following value for that key:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
In SQL Server Management Studio it shows:
G:\Microsoft SQL Server\Data
I'm still confused!
Why are there two different values for the same thing stored?
I'm running Management Studio logged in as an Administrator, so there should
be no problem writing to the registry.
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>>I have the default location for Data setup to be G:\Microsoft SQL
>>Server\Data (Database Settings under server properties).
>> But, when I invoke "Copy Database" from Management, it places the data on
>> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
>> Why doesn't the Copy Database Wizard respect my setting?
>> Olav
>

default database

I using the copy database wizard to copy databases from one server to
another. When I copy a db to the new server, it changes the default db
setting for all the users of that db to 'master'. Am I not running the
wizard correctly?
If this is how it works, how do I reset the setting to the db name I want?
I can do it manually but I have atleat 5 databases I have to copy and each
has a lot of users.
Look up sp_defaultdb in Books Online.
http://www.aspfaq.com/
(Reverse address to reply.)
"sql" <sql@.discussions.microsoft.com> wrote in message
news:F2413537-6302-4194-9D7E-4AEBCFBE28F4@.microsoft.com...
> I using the copy database wizard to copy databases from one server to
> another. When I copy a db to the new server, it changes the default db
> setting for all the users of that db to 'master'. Am I not running the
> wizard correctly?
> If this is how it works, how do I reset the setting to the db name I want?
> I can do it manually but I have atleat 5 databases I have to copy and each
> has a lot of users.
sql

default database

I using the copy database wizard to copy databases from one server to
another. When I copy a db to the new server, it changes the default db
setting for all the users of that db to 'master'. Am I not running the
wizard correctly?
If this is how it works, how do I reset the setting to the db name I want?
I can do it manually but I have atleat 5 databases I have to copy and each
has a lot of users.Look up sp_defaultdb in Books Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"sql" <sql@.discussions.microsoft.com> wrote in message
news:F2413537-6302-4194-9D7E-4AEBCFBE28F4@.microsoft.com...
> I using the copy database wizard to copy databases from one server to
> another. When I copy a db to the new server, it changes the default db
> setting for all the users of that db to 'master'. Am I not running the
> wizard correctly?
> If this is how it works, how do I reset the setting to the db name I want?
> I can do it manually but I have atleat 5 databases I have to copy and each
> has a lot of users.

Deep copy of child rows + referential integrity?

I have a database of basically this structure:

tblG

-GKey (PK)

tblA

-AKey (PK)
-GKey (FK -> tblG)
-data

tblB

-BKey (PK)
-GKey (FK -> tblG)
-data

tblAB

-AKey (FK -> tblA)
-BKey (FK -> tblB)
-data

I'm trying to write a procedure that will take a tblG.GKey and
clone all of its children rows in tblA and tblB (with new PKs),
and then clone all of their children rows in tblAB (using the
new PKs for tblA and tblB).

Does anybody have any suggestions on how this might be done sanely?

ThanksWhat are the primary keys? Are they identity columns? How are they generated? Also, what version of SQL Server are you using?|||I'm in SQL Server 2005.

The primary keys (marked PK) are identity columns and are generated automatically.
Using "select IDENT_CURRENT('tblA')" works as I would expect it to.

I feel like I need to traverse all the rows in tblA and tblB that need to be copied, insert them, then grab the IDENT_CURRENT off that row and insert into a temporary table along with the original. Then go through all the rows in tblAB and replace the FKs with look-ups from the temporary table. I just have no idea how I could implement that.|||

In my mind it's still not totally clear what you're trying to do.

Is there a chance you could post a short example (around 4 rows from each table) of the data you'd expect to see in your tables before and after the changes have been made?

Thanks
Chris

|||Sure. Here are the tables before the copy:

"tblG"
-
GKey |
-
1

"tblA"
-
AKey | GKey | data
1 | 1 | abc
2 | 1 | def
3 | 1 | ghi

"tblB"

-

BKey | GKey | data

1 | 1 | jkl

2 | 1 | mno

3 | 1 | oqr

"tblAB"

-

AKey | BKey | data

1 | 3 | tuv

1 | 2 | xyz

3 | 1 | aaa
2 | 1 | bbb

And now after the copy:

"tblG"

-

GKey |

-

1

2

"tblA"

-

AKey | GKey | data

1 | 1 | abc

2 | 1 | def

3 | 1 | ghi

4 | 2 | abc

5 | 2 | def

6 | 2 | ghi

"tblB"

-

BKey | GKey | data

1 | 1 | jkl

2 | 1 | mno

3 | 1 | oqr

4 | 2 | jkl

5 | 2 | mno

6 | 2 | oqr

"tblAB"

-

AKey | BKey | data

1 | 3 | tuv

1 | 2 | xyz

3 | 1 | aaa

2 | 1 | bbb

4 | 6 | tuv

4 | 5 | xyz

6 | 4 | aaa

5 | 4 | bbb

Does that make sense?|||

Although I generally favour set-based approaches, I can't think of set-based method that wouldn't require a modification to your existing tables. The cursor-based example below returns the results as you stipulated in your previous post.

Essentially, one row at a time is inserted into tblA and the old and new IDENTITY values stored away in a table variable. The same is done for tblB. It's then a simple matter of joining the two table variables onto tblAB to copy the relationships that exist in tblAB.

If you desperately need a set-based method, and can make changes to your tables, then you could add an extra column to both tblA and tblB to store the ID of the row from which the current row was copied. As long as this column is maintained during subsequent INSERTs then you can simply modify and use the final INSERT statement of the code I've included below to populate tblAB.

Chris

/*

CREATE TABLE dbo.tblG

(

GKey INT

)

CREATE TABLE dbo.tblA

(

AKey INT IDENTITY(1, 1) PRIMARY KEY,

GKey INT,

Data VARCHAR(3)

)

CREATE TABLE dbo.tblB

(

BKey INT IDENTITY(1, 1) PRIMARY KEY,

GKey INT,

Data VARCHAR(3)

)

CREATE TABLE dbo.tblAB

(

AKey INT,

BKey INT,

Data VARCHAR(3)

)

INSERT INTO dbo.tblG

VALUES(1)

SET IDENTITY_INSERT dbo.tblA ON

INSERT INTO dbo.tblA(AKey, GKey, Data)

SELECT 1, 1, 'abc' UNION

SELECT 2, 1, 'def' UNION

SELECT 3, 1, 'ghi'

SET IDENTITY_INSERT dbo.tblA OFF

SET IDENTITY_INSERT dbo.tblB ON

INSERT INTO dbo.tblB(BKey, GKey, Data)

SELECT 1, 1, 'jkl' UNION

SELECT 2, 1, 'mno' UNION

SELECT 3, 1, 'pqr'

SET IDENTITY_INSERT dbo.tblB OFF

INSERT INTO dbo.tblAB(AKey, BKey, Data)

SELECT 1, 3, 'tuv' UNION

SELECT 1, 2, 'xyz' UNION

SELECT 3, 1, 'aaa' UNION

SELECT 2, 1, 'bbb'

*/

DECLARE @.OldGKey INT

DECLARE @.NewGKey INT

DECLARE @.OldAKey INT

DECLARE @.NewAKey INT

DECLARE @.OldBKey INT

DECLARE @.NewBKey INT

DECLARE @.AKeyNewOld TABLE (OldAKey INT, NewAKey INT)

DECLARE @.BKeyNewOld TABLE (OldBKey INT, NewBKey INT)

DECLARE @.Data VARCHAR(3)

SELECT @.OldGKey = MAX(GKey), @.NewGKey = MAX(GKey + 1)

FROM dbo.tblG

INSERT INTO dbo.tblG

VALUES(@.NewGKey)

DECLARE curAKeys CURSOR FAST_FORWARD LOCAL FOR

SELECT AKey, Data

FROM dbo.tblA

WHERE GKey = @.OldGKey

ORDER BY AKey

OPEN curAKeys

FETCH NEXT FROM curAKeys INTO @.OldAKey, @.Data

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO tblA(GKey, Data)

VALUES(@.NewGKey, @.Data)

SET @.NewAKey = SCOPE_IDENTITY()

INSERT INTO @.AKeyNewOld(OldAKey, NewAKey)

VALUES(@.OldAKey, @.NewAKey)

FETCH NEXT FROM curAKeys INTO @.OldAKey, @.Data

END

CLOSE curAKeys

DEALLOCATE curAKeys

DECLARE curBKeys CURSOR FAST_FORWARD LOCAL FOR

SELECT BKey, Data

FROM dbo.tblB

WHERE GKey = @.OldGKey

ORDER BY BKey

OPEN curBKeys

FETCH NEXT FROM curBKeys INTO @.OldBKey, @.Data

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO tblB(GKey, Data)

VALUES(@.NewGKey, @.Data)

SET @.NewBKey = SCOPE_IDENTITY()

INSERT INTO @.BKeyNewOld(OldBKey, NewBKey)

VALUES(@.OldBKey, @.NewBKey)

FETCH NEXT FROM curBKeys INTO @.OldBKey, @.Data

END

CLOSE curBKeys

DEALLOCATE curBKeys

INSERT INTO dbo.tblAB(AKey, BKey, Data)

SELECT akno.NewAKey,

bkno.NewBKey,

tab.Data

FROM dbo.tblAB tab

INNER JOIN @.AKeyNewOld akno ON akno.OldAKey = tab.AKey

INNER JOIN @.BKeyNewOld bkno ON bkno.OldBKey = tab.BKey

SELECT * FROM tbLG

SELECT * FROM tbLA

SELECT * FROM tbLB

SELECT * from tblAB

|||Works like a charm. Thanks a million