Correct way of finding a tables primary keys?

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.

I thought that this code did the trick.

***

DECLARE @.c varchar(4000), @.t varchar(128)
SET @.c = ''
SET @.t='contact_pmc_contact_relations'
Select @.c = @.c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @.t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@.c, 1, Datalength(@.c) - 1)

***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.

Here is a dump from one of the tabels where it doesn't work.

SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table

***

id indid colid keyno

933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.

If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?

Regards

/Anders

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

|||

SQL Server 2000 ou 2005 ?

|||Currently we'r using sql server 2000, but if there is a way that works on both 2000 and 2005 it would be really great!|||

Madhu K Nair wrote:

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

Either im missunderstanding or that doesn't work. I want to get the columns that compose the primarykey. Not the primary key itself.

|||

i got the issue. Since there is no direct method (Single select statement), I will give u a workaround. If u check sp_helpconstraint , it give u the details. U can use sp_helptext to find the sourcecode behind this SP and do the required modification adn create u r own SP.

Madhu

|||

select * from INFORMATION_SCHEMA.[CONSTRAINT_COLUMN_USAGE]

where constraint_type='PRIMARY KEY'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is this you are looking for?

Code Snippet

SELECT CCU.TABLE_NAME

, CCU.COLUMN_NAME

, ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON TC.TABLE_NAME = CCU.TABLE_NAME

AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY CCU.TABLE_NAME, ORDINAL_POSITION

Just expanding to what others meant to answer to your question.

Correct syntax on this SQL INNER JOIN Query.

Hi :)

Trying to get this sql - query to run under Query Analyzer but not sure how to correct anything in it to the right :)

______INFO_______
Table : iptable
Fields : ip_start, ip_end, location

Table : PageLog
Fields : pl_ipaddress, pl_sessionid, pl_remotehost
_________________

______________CODE_______________
select
iptable.location
, count(pl_ipaddress)
from (
select distinct
pl_sessionid
, pl_ipaddress
, pl_remotehost
from PageLog
where pl_datetime
between '2003-12-25 00:00:00'
and '2003-12-25 23:59:59'
and pl_ipaddress <> ''
) as dt_pagelog
inner
join iptable
on dt_pagelog.pl_ipaddress
between iptable.ip_start
and iptable.ip_end
group
by iptable.location
order
by count(pl_ipaddress) desc
___________END CODE_________________

1) If i run this i get :
--> "Column dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function or in the GROUP BY clause.

2) If i include it in the GROUP BY i get :
--> The text, ntext and image datatypes cannot be used in WHERE, HAVING or ON clause, except with the LIKE or IS NULL predicate.

Soo.. how on earth should i put this right to get to use it with a INNER JOIN, since that has to have a ON to it ?

Not very familiar with INNER JOIN's so any help will be very much appreciated..

Best regards
Mirador.select a.col1, a.col2, b.col1, b.colxxxx
from table1 as a
inner join table2 as b
on a.col1 = b.col1
where a.col2 = value|||Hi Rushi and thanx for your reply..

What am i to do with the SELECT DISTINCT ......... FROM PageLog ?

Should i add PageLog.fieldname to each of the sentences in the select there to ?

Mirador.|||select distinct a.location, .........
from pagelog as a
inner join iptable as b
on a.--- = b.========

In inner join syntax
you have to join the common fields of the 2 tables in inner join clause and the actual where condition in where clause.|||the error message doesn't make sense

"dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function"

dt_pagelog.pl_ipaddress is contained in a aggregate function -- the COUNT()

maybe it's the subquery's DISTINCT, although i seriously doubt it

try this:select iptable.location
, count(dt_pagelog.pl_ipaddress)
from (
select pl_sessionid
, pl_ipaddress
, pl_remotehost
from PageLog
where pl_datetime
between '2003-12-25 00:00:00'
and '2003-12-25 23:59:59'
and pl_ipaddress <> ''
group
by pl_sessionid
, pl_ipaddress
, pl_remotehost
) as dt_pagelog
inner
join iptable
on dt_pagelog.pl_ipaddress
between iptable.ip_start
and iptable.ip_end
group
by iptable.location
order
by count(dt_pagelog.pl_ipaddress) desc|||Hi again Rudy :)

This is indeed a bit weird...
Tried the exact query u posted and got this errormsg :

-----Error------

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
------------|||okay, see if you can understand where i'm going with this...

which one of your columns is text, ntext, or image?

and just to give you a little advnace notice, my next question will be why

session id, ip address, remote host, location -- those all sound like varchars to me|||Oh my... :( i finally got it...

The ip_start and ip_end was text while the others were varchar..

As soon as i put all of them to varchar it worked...

Well.. ended up with a easy solution after all!... I thought this had to be something really really tricky stuff..

Now i know :) hehe.. varchars dont match very good with text when it comes to comparing..

Thanx for all your help Rudy...

Best regards
Terje.

correct me??

I've created C#.net program (behind code style).

when I run it in Internet explorer, the following error occurs in IE window.

pls instruct me how to handle and correct this error.

And how to initialize the connectionstring... Great thank!

Server Error in '/' Application.

------------------------

The ConnectionString property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: The ConnectionString property has not been initialized.]
System.Data.SqlClient.SqlConnection.Open() +809
CodeBox.BehindCode.getSubject() +80
CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +29
System.Web.UI.Page.ProcessRequestMain() +724

------------------------

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

Hi,
You haven't posted the code that you are trying to execute.
From the error, it seems that you havent set the connection string property for your connection object.
You can initialize the connection string as follows:-
SqlConnection objCon = new SqlConnection("put your connection string here");
Thanks.|||if you still have the problem show us the code !!|||

hi! thanks...
but I still have problem...
I want to show you my code and error message...
here is my code...
SqlDataReader getSubject(){
//create instance of connection and command object

//SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
constring ="DSN=fisca;User ID=sa;Password=admin;";
SqlConnection myconnection = new SqlConnection(constring);

SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}

here is error message. I sure that dsn is really existing and userid, password of sql server is true....
and I also sure that variable constring is declared.

Server Error in '/' Application.

---

Unknown connection option in connection string: dsn.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.ArgumentException: Unknown connection option in connection string: dsn.
Source Error:

Line 77: //SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);Line 78: constring ="DSN=fisca;User ID=sa;Password=admin;";Line 79: SqlConnection myconnection = new SqlConnection(constring);Line 80: Line 81: SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);

Source File:c:\inetpub\wwwroot\CourseRequisition.cs Line:79
Stack Trace:

[ArgumentException: Unknown connection option in connection string: dsn.] System.Data.SqlClient.ConStringUtil.ParseStringIntoHashtable(String conString, Hashtable values) +673 System.Data.SqlClient.ConStringUtil.ParseConnectionString(String connectionString) +55 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +375 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +164 CodeBox.BehindCode.getSubject() in c:\inetpub\wwwroot\CourseRequisition.cs:79 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CourseRequisition.cs:36 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724

---

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0|||

here is my code...

SqlDataReader getSubject(){
//create instance of connection and command object
SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
but now I can find it out and I've corrected it myself.
cause I initialize connectionstring in web.config file. It can put away error.
so now I want to know that is there many ways to connect to sql server?...
how many way?
I just know two way,
1. Sqlconnection constring=new Sqlconnection("connection string");
2. SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
so pls let me know. cause I willingly want to know C#.net thoroughly.
I also learning it from book and creating C#.net application..
Have a bright and wonderful day!

|||the connection string that you provided is not correct !!!
look atwww.connstingSttings.com and see the proper contents of the connection string try to look the connetion string and if you still have problem get back to me

Correct connection string for sql express2005 using VS2003

Hi,

I am using VS2003. I have installed .net2.0 so that I can run SQL Express. I am using XP Pro SP2.

My server is called ikitsch. The path to my database is

ikitsch\sqlexpress\Databases\Test1

I am using windows authentication. For some reason I can't seem to connect to the database. What would be a correct connection string to

connect to this database on my local machine?

Thanks

Hi

Take a look at http://www.connectionstrings.com/ for all kinds of connection strings.

Hope it helps.

|||Thanks a lot...one of ems gotta work!

Copying my existing database to another server

Hi

I am running my sql 2000 database server and I just want to copy that database and paste or put on another server which has also run same version of sql 2000 database. I tried to copy and paste the ldf & mdf extensioned files which are located in "C:\Program Files\Microsoft SQL Server\MSSQL\Data" into my 2 server, but when I open up the enterprise manager, I cant see the new database. So this way dont work, I tried to do something with export and import wizard but it just creates another database and copies the data in that database in the same server. I want to copy that data and put onto another system, please guide me. I'm confused, Thanks a lot

Use the copy database wizard-http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_onmigwiz_0z50.asp|||

That's it,

Thanks thanks thanks :)

|||One thing to keep in mind: Copy Database Wizard is only available in SQL2005, and the destination data source must be SQL2005. For SQL2000 you can use Backup/Restore to move databases. You may take a look at:Copying Databases to Other Servers|||Actually, it's available in SQL Server 2000 as well, see the link in my first post.|||Thanks Mudface, you teach me a lotsql

Copying databases between two SQL Servers

Hi all,
This is the situation. We have some new SQL Servers being installed. The
existing SQL servers will be retired. The existing servers run Windows2000
Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new servers
are running Windows2003 (SP1) Server (Standard Edition) with SQLServer2000
(SP3). All of the SQLServers are part of our AD domain. The SQLSERVER
service and the SQLAGENT servers run as domain accounts. These domain
accounts are members of the local administrator group on each of the SQL
servers.
The problem we are having is that when the DBA attempts to copy the
databases from the Win2000/SQLServer to one of the Win2003 servers the dts
package fails with the following
Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
Step Error Description: Unspecified Error
Step Error Code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Under more info it says
Failed to create the share OMWWIZD
We have gone through kb article 274663 which convers this but as far as we
can see we meet all the requirements for this to work, so we are a little
stumped at this point. We thought it may be something to do with Win2003
SP1, so we built a Win2003 server with no service packs and tried to copy
some databases and we received the exact same error.
I can manually create a share between the Win2000 and Win2003 servers using
the same domain account the SQLServer process use. Once manually created I
can create and delete files, so at a filesystem level the interopability
between the servers appears fine. Running out of ideas at this point.
If anyone has any suggestions they would be greatly welcomed.
regards
CraigHi,
Easy approches to copy the databases are:-
1. Backup the production database using (BACKUP DATABASE command)
2. COpy the backup file to test server
3. Restore the database in test server (RESTORE DATABASE)
See Backup and Restore commad in books online.
Note:
you can also detach and attach databases. See SP_DETACH_DB and SP_ATTACH_DB
Thanks
Hari
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> This is the situation. We have some new SQL Servers being installed. The
> existing SQL servers will be retired. The existing servers run Windows2000
> Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> servers are running Windows2003 (SP1) Server (Standard Edition) with
> SQLServer2000 (SP3). All of the SQLServers are part of our AD domain. The
> SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> domain accounts are members of the local administrator group on each of
> the SQL servers.
> The problem we are having is that when the DBA attempts to copy the
> databases from the Win2000/SQLServer to one of the Win2003 servers the dts
> package fails with the following
> Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> Step Error Description: Unspecified Error
> Step Error Code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
> Under more info it says
> Failed to create the share OMWWIZD
> We have gone through kb article 274663 which convers this but as far as we
> can see we meet all the requirements for this to work, so we are a little
> stumped at this point. We thought it may be something to do with Win2003
> SP1, so we built a Win2003 server with no service packs and tried to copy
> some databases and we received the exact same error.
> I can manually create a share between the Win2000 and Win2003 servers
> using the same domain account the SQLServer process use. Once manually
> created I can create and delete files, so at a filesystem level the
> interopability between the servers appears fine. Running out of ideas at
> this point.
> If anyone has any suggestions they would be greatly welcomed.
> regards
> Craig
>|||In additon to Hari's resonse you can also take a look at
sp_detach_db,sp_attach_db,sp_attach_sing
le_file_db
system stored procedures
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Easy approches to copy the databases are:-
> 1. Backup the production database using (BACKUP DATABASE command)
> 2. COpy the backup file to test server
> 3. Restore the database in test server (RESTORE DATABASE)
>
> See Backup and Restore commad in books online.
>
> Note:
>
> you can also detach and attach databases. See SP_DETACH_DB and
SP_ATTACH_DB
> --
> Thanks
> Hari
>
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
Windows2000[vbcol=seagreen]
The[vbcol=seagreen]
dts[vbcol=seagreen]
we[vbcol=seagreen]
little[vbcol=seagreen]
copy[vbcol=seagreen]
>|||Hi Uri and Hari,
thanks for your replies.
Yes, doing a backup/restore is one of the options I suggested to our DBA but
he is reluctant to do that because he says (and I'm no DBA) he loses all the
ownership rigts and has to recreate SQLServer accounts and so on. To me that
doesn't sound to much effort, we are not a big company so there's probably
only a hanfull of accounts, however he is the DBA so I have to go with his
recomedations pertaining to the databases.
One thing I have just found out is that when this server was originally
built it was named server-a. Later on and after Sqlserver was installed they
renamed the server to server-b. Server-a and server-b. Just wondering if
this is part of the problem?
Anyway we will explore a few other avenues and if all else fails we will
have to revert to the ol backup/restore option. I will direct our DBA to
those stored procedures you two have mentioned.
thanks for the assistance
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> In additon to Hari's resonse you can also take a look at
> sp_detach_db,sp_attach_db,sp_attach_sing
le_file_db
> system stored procedures
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> SP_ATTACH_DB
> Windows2000
> The
> dts
> we
> little
> copy
>|||Craig
Your DBA is not quite correct.
There are two stored procedures that provided by Microsoft to migrate
users/logins with their original SID
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi Uri and Hari,
> thanks for your replies.
> Yes, doing a backup/restore is one of the options I suggested to our DBA
but
> he is reluctant to do that because he says (and I'm no DBA) he loses all
the
> ownership rigts and has to recreate SQLServer accounts and so on. To me
that
> doesn't sound to much effort, we are not a big company so there's probably
> only a hanfull of accounts, however he is the DBA so I have to go with his
> recomedations pertaining to the databases.
> One thing I have just found out is that when this server was originally
> built it was named server-a. Later on and after Sqlserver was installed
they
> renamed the server to server-b. Server-a and server-b. Just wondering if
> this is part of the problem?
> Anyway we will explore a few other avenues and if all else fails we will
> have to revert to the ol backup/restore option. I will direct our DBA to
> those stored procedures you two have mentioned.
> thanks for the assistance
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
of[vbcol=seagreen]
the[vbcol=seagreen]
Package[vbcol=seagreen]
as[vbcol=seagreen]
manually[vbcol=seagreen]
>|||Hi Uri,
ok, thanks for the information. I'll pass it on to him. To be fair to him
he's really an Oracle DBA who has had SQLServer dumped on him and he hasn't
had a great deal of training on it yet.
One other thing has come to light and as of yet I am not to sure if it is
the cause or not, we still need to do some more testing. To cut a long and
boring story short we have one AD domain, let's call it oz.com.au. AD is DNS
intergrated. At some point we created some sub domains within abc.com,
namely dev.abc.com and test.abc.com. These new subdomains also have their
own unique ip address range
abc.com 192.168.1.x
dev.abc.com 192.168.2.x
test.abc.com 192.168.3.x
So the fully qualified domain names for our servers would look like this
server1.abc.com
server2.abc.com
server3.dev.abc.com
server4.dev.abc.com
server5.test.abc.com
server6.test.abc.com
however we only have one single AD domain, in this case abc.com.
What we have now found is that we can copy databases between two servers who
share the same FQDN domain part. ie. server1.abc.com to server2.abc.com
works, however copying between two servers who do not share the same domain
part does not and generates the error mentioned in the original post. ie.
server1.abc.com to server3.dev.abc.com
The odd thing is that this setup used to work under Win2000, but obviously
something in Win2003 is different. Might have to bounce this off some people
in the windows groups.
If anyone has any suggestions I'm all ears.
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Craig
> Your DBA is not quite correct.
> There are two stored procedures that provided by Microsoft to migrate
> users/logins with their original SID
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> but
> the
> that
> they
> of
> the
> Package
> as
> manually
>|||Hi all,
ok, it turned out that the fully qualified hostnames were in fact causing
the problem. As soon as we put the machines in the same internet domain name
space the DTS copies worked. I assume that something within SQLServer/DTS
assumes that if the FQDN contain different domain parts then therefore they
are in different AD domains. The fix was to simply change the
- FQDN to the same domain
- Copy the databases
- Revert the servers's FQDN back.
Thanks to all who offered various solutions. Now off to the MS support site
to see if there is something I should be configuring in AD when I do this. I
wouldn't have thought so but it looks like there is.
Regards
Craig
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%232V5e$3hFHA.3912@.tk2msftngp13.phx.gbl...
> Hi Uri,
> ok, thanks for the information. I'll pass it on to him. To be fair to him
> he's really an Oracle DBA who has had SQLServer dumped on him and he
> hasn't had a great deal of training on it yet.
> One other thing has come to light and as of yet I am not to sure if it is
> the cause or not, we still need to do some more testing. To cut a long and
> boring story short we have one AD domain, let's call it oz.com.au. AD is
> DNS intergrated. At some point we created some sub domains within abc.com,
> namely dev.abc.com and test.abc.com. These new subdomains also have their
> own unique ip address range
> abc.com 192.168.1.x
> dev.abc.com 192.168.2.x
> test.abc.com 192.168.3.x
> So the fully qualified domain names for our servers would look like this
> server1.abc.com
> server2.abc.com
> server3.dev.abc.com
> server4.dev.abc.com
> server5.test.abc.com
> server6.test.abc.com
> however we only have one single AD domain, in this case abc.com.
> What we have now found is that we can copy databases between two servers
> who share the same FQDN domain part. ie. server1.abc.com to
> server2.abc.com works, however copying between two servers who do not
> share the same domain part does not and generates the error mentioned in
> the original post. ie. server1.abc.com to server3.dev.abc.com
> The odd thing is that this setup used to work under Win2000, but obviously
> something in Win2003 is different. Might have to bounce this off some
> people in the windows groups.
> If anyone has any suggestions I'm all ears.
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>

Copying databases between two SQL Servers

Hi all,
This is the situation. We have some new SQL Servers being installed. The
existing SQL servers will be retired. The existing servers run Windows2000
Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new servers
are running Windows2003 (SP1) Server (Standard Edition) with SQLServer2000
(SP3). All of the SQLServers are part of our AD domain. The SQLSERVER
service and the SQLAGENT servers run as domain accounts. These domain
accounts are members of the local administrator group on each of the SQL
servers.
The problem we are having is that when the DBA attempts to copy the
databases from the Win2000/SQLServer to one of the Win2003 servers the dts
package fails with the following
Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
Step Error Description: Unspecified Error
Step Error Code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Under more info it says
Failed to create the share OMWWIZD
We have gone through kb article 274663 which convers this but as far as we
can see we meet all the requirements for this to work, so we are a little
stumped at this point. We thought it may be something to do with Win2003
SP1, so we built a Win2003 server with no service packs and tried to copy
some databases and we received the exact same error.
I can manually create a share between the Win2000 and Win2003 servers using
the same domain account the SQLServer process use. Once manually created I
can create and delete files, so at a filesystem level the interopability
between the servers appears fine. Running out of ideas at this point.
If anyone has any suggestions they would be greatly welcomed.
regards
CraigHi,
Easy approches to copy the databases are:-
1. Backup the production database using (BACKUP DATABASE command)
2. COpy the backup file to test server
3. Restore the database in test server (RESTORE DATABASE)
See Backup and Restore commad in books online.
Note:
you can also detach and attach databases. See SP_DETACH_DB and SP_ATTACH_DB
--
Thanks
Hari
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> This is the situation. We have some new SQL Servers being installed. The
> existing SQL servers will be retired. The existing servers run Windows2000
> Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> servers are running Windows2003 (SP1) Server (Standard Edition) with
> SQLServer2000 (SP3). All of the SQLServers are part of our AD domain. The
> SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> domain accounts are members of the local administrator group on each of
> the SQL servers.
> The problem we are having is that when the DBA attempts to copy the
> databases from the Win2000/SQLServer to one of the Win2003 servers the dts
> package fails with the following
> Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> Step Error Description: Unspecified Error
> Step Error Code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
> Under more info it says
> Failed to create the share OMWWIZD
> We have gone through kb article 274663 which convers this but as far as we
> can see we meet all the requirements for this to work, so we are a little
> stumped at this point. We thought it may be something to do with Win2003
> SP1, so we built a Win2003 server with no service packs and tried to copy
> some databases and we received the exact same error.
> I can manually create a share between the Win2000 and Win2003 servers
> using the same domain account the SQLServer process use. Once manually
> created I can create and delete files, so at a filesystem level the
> interopability between the servers appears fine. Running out of ideas at
> this point.
> If anyone has any suggestions they would be greatly welcomed.
> regards
> Craig
>|||In additon to Hari's resonse you can also take a look at
sp_detach_db,sp_attach_db,sp_attach_single_file_db
system stored procedures
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Easy approches to copy the databases are:-
> 1. Backup the production database using (BACKUP DATABASE command)
> 2. COpy the backup file to test server
> 3. Restore the database in test server (RESTORE DATABASE)
>
> See Backup and Restore commad in books online.
>
> Note:
>
> you can also detach and attach databases. See SP_DETACH_DB and
SP_ATTACH_DB
> --
> Thanks
> Hari
>
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > This is the situation. We have some new SQL Servers being installed. The
> > existing SQL servers will be retired. The existing servers run
Windows2000
> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> > servers are running Windows2003 (SP1) Server (Standard Edition) with
> > SQLServer2000 (SP3). All of the SQLServers are part of our AD domain.
The
> > SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> > domain accounts are members of the local administrator group on each of
> > the SQL servers.
> >
> > The problem we are having is that when the DBA attempts to copy the
> > databases from the Win2000/SQLServer to one of the Win2003 servers the
dts
> > package fails with the following
> >
> > Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> > Step Error Description: Unspecified Error
> > Step Error Code: 80004005
> > Step Error Help File:sqldts80.hlp
> > Step Error Help Context ID:1100
> >
> > Under more info it says
> >
> > Failed to create the share OMWWIZD
> >
> > We have gone through kb article 274663 which convers this but as far as
we
> > can see we meet all the requirements for this to work, so we are a
little
> > stumped at this point. We thought it may be something to do with Win2003
> > SP1, so we built a Win2003 server with no service packs and tried to
copy
> > some databases and we received the exact same error.
> >
> > I can manually create a share between the Win2000 and Win2003 servers
> > using the same domain account the SQLServer process use. Once manually
> > created I can create and delete files, so at a filesystem level the
> > interopability between the servers appears fine. Running out of ideas at
> > this point.
> >
> > If anyone has any suggestions they would be greatly welcomed.
> >
> > regards
> >
> > Craig
> >
>|||Hi Uri and Hari,
thanks for your replies.
Yes, doing a backup/restore is one of the options I suggested to our DBA but
he is reluctant to do that because he says (and I'm no DBA) he loses all the
ownership rigts and has to recreate SQLServer accounts and so on. To me that
doesn't sound to much effort, we are not a big company so there's probably
only a hanfull of accounts, however he is the DBA so I have to go with his
recomedations pertaining to the databases.
One thing I have just found out is that when this server was originally
built it was named server-a. Later on and after Sqlserver was installed they
renamed the server to server-b. Server-a and server-b. Just wondering if
this is part of the problem?
Anyway we will explore a few other avenues and if all else fails we will
have to revert to the ol backup/restore option. I will direct our DBA to
those stored procedures you two have mentioned.
thanks for the assistance
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> In additon to Hari's resonse you can also take a look at
> sp_detach_db,sp_attach_db,sp_attach_single_file_db
> system stored procedures
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Easy approches to copy the databases are:-
>> 1. Backup the production database using (BACKUP DATABASE command)
>> 2. COpy the backup file to test server
>> 3. Restore the database in test server (RESTORE DATABASE)
>>
>> See Backup and Restore commad in books online.
>>
>> Note:
>>
>> you can also detach and attach databases. See SP_DETACH_DB and
> SP_ATTACH_DB
>> --
>> Thanks
>> Hari
>>
>> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> > Hi all,
>> >
>> > This is the situation. We have some new SQL Servers being installed.
>> > The
>> > existing SQL servers will be retired. The existing servers run
> Windows2000
>> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
>> > servers are running Windows2003 (SP1) Server (Standard Edition) with
>> > SQLServer2000 (SP3). All of the SQLServers are part of our AD domain.
> The
>> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
>> > These
>> > domain accounts are members of the local administrator group on each of
>> > the SQL servers.
>> >
>> > The problem we are having is that when the DBA attempts to copy the
>> > databases from the Win2000/SQLServer to one of the Win2003 servers the
> dts
>> > package fails with the following
>> >
>> > Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
>> > Step Error Description: Unspecified Error
>> > Step Error Code: 80004005
>> > Step Error Help File:sqldts80.hlp
>> > Step Error Help Context ID:1100
>> >
>> > Under more info it says
>> >
>> > Failed to create the share OMWWIZD
>> >
>> > We have gone through kb article 274663 which convers this but as far as
> we
>> > can see we meet all the requirements for this to work, so we are a
> little
>> > stumped at this point. We thought it may be something to do with
>> > Win2003
>> > SP1, so we built a Win2003 server with no service packs and tried to
> copy
>> > some databases and we received the exact same error.
>> >
>> > I can manually create a share between the Win2000 and Win2003 servers
>> > using the same domain account the SQLServer process use. Once manually
>> > created I can create and delete files, so at a filesystem level the
>> > interopability between the servers appears fine. Running out of ideas
>> > at
>> > this point.
>> >
>> > If anyone has any suggestions they would be greatly welcomed.
>> >
>> > regards
>> >
>> > Craig
>> >
>>
>|||Craig
Your DBA is not quite correct.
There are two stored procedures that provided by Microsoft to migrate
users/logins with their original SID
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi Uri and Hari,
> thanks for your replies.
> Yes, doing a backup/restore is one of the options I suggested to our DBA
but
> he is reluctant to do that because he says (and I'm no DBA) he loses all
the
> ownership rigts and has to recreate SQLServer accounts and so on. To me
that
> doesn't sound to much effort, we are not a big company so there's probably
> only a hanfull of accounts, however he is the DBA so I have to go with his
> recomedations pertaining to the databases.
> One thing I have just found out is that when this server was originally
> built it was named server-a. Later on and after Sqlserver was installed
they
> renamed the server to server-b. Server-a and server-b. Just wondering if
> this is part of the problem?
> Anyway we will explore a few other avenues and if all else fails we will
> have to revert to the ol backup/restore option. I will direct our DBA to
> those stored procedures you two have mentioned.
> thanks for the assistance
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> > In additon to Hari's resonse you can also take a look at
> > sp_detach_db,sp_attach_db,sp_attach_single_file_db
> > system stored procedures
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> >> Hi,
> >>
> >> Easy approches to copy the databases are:-
> >>
> >> 1. Backup the production database using (BACKUP DATABASE command)
> >> 2. COpy the backup file to test server
> >> 3. Restore the database in test server (RESTORE DATABASE)
> >>
> >>
> >> See Backup and Restore commad in books online.
> >>
> >>
> >> Note:
> >>
> >>
> >> you can also detach and attach databases. See SP_DETACH_DB and
> > SP_ATTACH_DB
> >>
> >> --
> >> Thanks
> >> Hari
> >>
> >>
> >> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> >> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> >> > Hi all,
> >> >
> >> > This is the situation. We have some new SQL Servers being installed.
> >> > The
> >> > existing SQL servers will be retired. The existing servers run
> > Windows2000
> >> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> >> > servers are running Windows2003 (SP1) Server (Standard Edition) with
> >> > SQLServer2000 (SP3). All of the SQLServers are part of our AD domain.
> > The
> >> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
> >> > These
> >> > domain accounts are members of the local administrator group on each
of
> >> > the SQL servers.
> >> >
> >> > The problem we are having is that when the DBA attempts to copy the
> >> > databases from the Win2000/SQLServer to one of the Win2003 servers
the
> > dts
> >> > package fails with the following
> >> >
> >> > Step Error Sourse: Microsoft Data Transformation Services (DTS)
Package
> >> > Step Error Description: Unspecified Error
> >> > Step Error Code: 80004005
> >> > Step Error Help File:sqldts80.hlp
> >> > Step Error Help Context ID:1100
> >> >
> >> > Under more info it says
> >> >
> >> > Failed to create the share OMWWIZD
> >> >
> >> > We have gone through kb article 274663 which convers this but as far
as
> > we
> >> > can see we meet all the requirements for this to work, so we are a
> > little
> >> > stumped at this point. We thought it may be something to do with
> >> > Win2003
> >> > SP1, so we built a Win2003 server with no service packs and tried to
> > copy
> >> > some databases and we received the exact same error.
> >> >
> >> > I can manually create a share between the Win2000 and Win2003 servers
> >> > using the same domain account the SQLServer process use. Once
manually
> >> > created I can create and delete files, so at a filesystem level the
> >> > interopability between the servers appears fine. Running out of ideas
> >> > at
> >> > this point.
> >> >
> >> > If anyone has any suggestions they would be greatly welcomed.
> >> >
> >> > regards
> >> >
> >> > Craig
> >> >
> >>
> >>
> >
> >
>|||Hi Uri,
ok, thanks for the information. I'll pass it on to him. To be fair to him
he's really an Oracle DBA who has had SQLServer dumped on him and he hasn't
had a great deal of training on it yet.
One other thing has come to light and as of yet I am not to sure if it is
the cause or not, we still need to do some more testing. To cut a long and
boring story short we have one AD domain, let's call it oz.com.au. AD is DNS
intergrated. At some point we created some sub domains within abc.com,
namely dev.abc.com and test.abc.com. These new subdomains also have their
own unique ip address range
abc.com 192.168.1.x
dev.abc.com 192.168.2.x
test.abc.com 192.168.3.x
So the fully qualified domain names for our servers would look like this
server1.abc.com
server2.abc.com
server3.dev.abc.com
server4.dev.abc.com
server5.test.abc.com
server6.test.abc.com
however we only have one single AD domain, in this case abc.com.
What we have now found is that we can copy databases between two servers who
share the same FQDN domain part. ie. server1.abc.com to server2.abc.com
works, however copying between two servers who do not share the same domain
part does not and generates the error mentioned in the original post. ie.
server1.abc.com to server3.dev.abc.com
The odd thing is that this setup used to work under Win2000, but obviously
something in Win2003 is different. Might have to bounce this off some people
in the windows groups.
If anyone has any suggestions I'm all ears.
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Craig
> Your DBA is not quite correct.
> There are two stored procedures that provided by Microsoft to migrate
> users/logins with their original SID
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> Hi Uri and Hari,
>> thanks for your replies.
>> Yes, doing a backup/restore is one of the options I suggested to our DBA
> but
>> he is reluctant to do that because he says (and I'm no DBA) he loses all
> the
>> ownership rigts and has to recreate SQLServer accounts and so on. To me
> that
>> doesn't sound to much effort, we are not a big company so there's
>> probably
>> only a hanfull of accounts, however he is the DBA so I have to go with
>> his
>> recomedations pertaining to the databases.
>> One thing I have just found out is that when this server was originally
>> built it was named server-a. Later on and after Sqlserver was installed
> they
>> renamed the server to server-b. Server-a and server-b. Just wondering if
>> this is part of the problem?
>> Anyway we will explore a few other avenues and if all else fails we will
>> have to revert to the ol backup/restore option. I will direct our DBA to
>> those stored procedures you two have mentioned.
>> thanks for the assistance
>> Craig
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
>> > In additon to Hari's resonse you can also take a look at
>> > sp_detach_db,sp_attach_db,sp_attach_single_file_db
>> > system stored procedures
>> >
>> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> > news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >>
>> >> Easy approches to copy the databases are:-
>> >>
>> >> 1. Backup the production database using (BACKUP DATABASE command)
>> >> 2. COpy the backup file to test server
>> >> 3. Restore the database in test server (RESTORE DATABASE)
>> >>
>> >>
>> >> See Backup and Restore commad in books online.
>> >>
>> >>
>> >> Note:
>> >>
>> >>
>> >> you can also detach and attach databases. See SP_DETACH_DB and
>> > SP_ATTACH_DB
>> >>
>> >> --
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> >> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi all,
>> >> >
>> >> > This is the situation. We have some new SQL Servers being installed.
>> >> > The
>> >> > existing SQL servers will be retired. The existing servers run
>> > Windows2000
>> >> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
>> >> > servers are running Windows2003 (SP1) Server (Standard Edition) with
>> >> > SQLServer2000 (SP3). All of the SQLServers are part of our AD
>> >> > domain.
>> > The
>> >> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
>> >> > These
>> >> > domain accounts are members of the local administrator group on each
> of
>> >> > the SQL servers.
>> >> >
>> >> > The problem we are having is that when the DBA attempts to copy the
>> >> > databases from the Win2000/SQLServer to one of the Win2003 servers
> the
>> > dts
>> >> > package fails with the following
>> >> >
>> >> > Step Error Sourse: Microsoft Data Transformation Services (DTS)
> Package
>> >> > Step Error Description: Unspecified Error
>> >> > Step Error Code: 80004005
>> >> > Step Error Help File:sqldts80.hlp
>> >> > Step Error Help Context ID:1100
>> >> >
>> >> > Under more info it says
>> >> >
>> >> > Failed to create the share OMWWIZD
>> >> >
>> >> > We have gone through kb article 274663 which convers this but as far
> as
>> > we
>> >> > can see we meet all the requirements for this to work, so we are a
>> > little
>> >> > stumped at this point. We thought it may be something to do with
>> >> > Win2003
>> >> > SP1, so we built a Win2003 server with no service packs and tried to
>> > copy
>> >> > some databases and we received the exact same error.
>> >> >
>> >> > I can manually create a share between the Win2000 and Win2003
>> >> > servers
>> >> > using the same domain account the SQLServer process use. Once
> manually
>> >> > created I can create and delete files, so at a filesystem level the
>> >> > interopability between the servers appears fine. Running out of
>> >> > ideas
>> >> > at
>> >> > this point.
>> >> >
>> >> > If anyone has any suggestions they would be greatly welcomed.
>> >> >
>> >> > regards
>> >> >
>> >> > Craig
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Hi all,
ok, it turned out that the fully qualified hostnames were in fact causing
the problem. As soon as we put the machines in the same internet domain name
space the DTS copies worked. I assume that something within SQLServer/DTS
assumes that if the FQDN contain different domain parts then therefore they
are in different AD domains. The fix was to simply change the
- FQDN to the same domain
- Copy the databases
- Revert the servers's FQDN back.
Thanks to all who offered various solutions. Now off to the MS support site
to see if there is something I should be configuring in AD when I do this. I
wouldn't have thought so but it looks like there is.
Regards
Craig
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%232V5e$3hFHA.3912@.tk2msftngp13.phx.gbl...
> Hi Uri,
> ok, thanks for the information. I'll pass it on to him. To be fair to him
> he's really an Oracle DBA who has had SQLServer dumped on him and he
> hasn't had a great deal of training on it yet.
> One other thing has come to light and as of yet I am not to sure if it is
> the cause or not, we still need to do some more testing. To cut a long and
> boring story short we have one AD domain, let's call it oz.com.au. AD is
> DNS intergrated. At some point we created some sub domains within abc.com,
> namely dev.abc.com and test.abc.com. These new subdomains also have their
> own unique ip address range
> abc.com 192.168.1.x
> dev.abc.com 192.168.2.x
> test.abc.com 192.168.3.x
> So the fully qualified domain names for our servers would look like this
> server1.abc.com
> server2.abc.com
> server3.dev.abc.com
> server4.dev.abc.com
> server5.test.abc.com
> server6.test.abc.com
> however we only have one single AD domain, in this case abc.com.
> What we have now found is that we can copy databases between two servers
> who share the same FQDN domain part. ie. server1.abc.com to
> server2.abc.com works, however copying between two servers who do not
> share the same domain part does not and generates the error mentioned in
> the original post. ie. server1.abc.com to server3.dev.abc.com
> The odd thing is that this setup used to work under Win2000, but obviously
> something in Win2003 is different. Might have to bounce this off some
> people in the windows groups.
> If anyone has any suggestions I'm all ears.
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> Craig
>> Your DBA is not quite correct.
>> There are two stored procedures that provided by Microsoft to migrate
>> users/logins with their original SID
>>
>> USE master
>> GO
>> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
>> DROP PROCEDURE sp_hexadecimal
>> GO
>> CREATE PROCEDURE sp_hexadecimal
>> @.binvalue varbinary(256),
>> @.hexvalue varchar(256) OUTPUT
>> AS
>> DECLARE @.charvalue varchar(256)
>> DECLARE @.i int
>> DECLARE @.length int
>> DECLARE @.hexstring char(16)
>> SELECT @.charvalue = '0x'
>> SELECT @.i = 1
>> SELECT @.length = DATALENGTH (@.binvalue)
>> SELECT @.hexstring = '0123456789ABCDEF'
>> WHILE (@.i <= @.length)
>> BEGIN
>> DECLARE @.tempint int
>> DECLARE @.firstint int
>> DECLARE @.secondint int
>> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
>> SELECT @.firstint = FLOOR(@.tempint/16)
>> SELECT @.secondint = @.tempint - (@.firstint*16)
>> SELECT @.charvalue = @.charvalue +
>> SUBSTRING(@.hexstring, @.firstint+1, 1) +
>> SUBSTRING(@.hexstring, @.secondint+1, 1)
>> SELECT @.i = @.i + 1
>> END
>> SELECT @.hexvalue = @.charvalue
>> GO
>> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
>> DROP PROCEDURE sp_help_revlogin
>> GO
>> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
>> DECLARE @.name sysname
>> DECLARE @.xstatus int
>> DECLARE @.binpwd varbinary (256)
>> DECLARE @.txtpwd sysname
>> DECLARE @.tmpstr varchar (256)
>> DECLARE @.SID_varbinary varbinary(85)
>> DECLARE @.SID_string varchar(256)
>> IF (@.login_name IS NULL)
>> DECLARE login_curs CURSOR FOR
>> SELECT sid, name, xstatus, password FROM master..sysxlogins
>> WHERE srvid IS NULL AND name <> 'sa'
>> ELSE
>> DECLARE login_curs CURSOR FOR
>> SELECT sid, name, xstatus, password FROM master..sysxlogins
>> WHERE srvid IS NULL AND name = @.login_name
>> OPEN login_curs
>> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
>> IF (@.@.fetch_status = -1)
>> BEGIN
>> PRINT 'No login(s) found.'
>> CLOSE login_curs
>> DEALLOCATE login_curs
>> RETURN -1
>> END
>> SET @.tmpstr = '/* sp_help_revlogin script '
>> PRINT @.tmpstr
>> SET @.tmpstr = '** Generated '
>> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
>> PRINT @.tmpstr
>> PRINT ''
>> PRINT 'DECLARE @.pwd sysname'
>> WHILE (@.@.fetch_status <> -1)
>> BEGIN
>> IF (@.@.fetch_status <> -2)
>> BEGIN
>> PRINT ''
>> SET @.tmpstr = '-- Login: ' + @.name
>> PRINT @.tmpstr
>> IF (@.xstatus & 4) = 4
>> BEGIN -- NT authenticated account/group
>> IF (@.xstatus & 1) = 1
>> BEGIN -- NT login is denied access
>> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
>> PRINT @.tmpstr
>> END
>> ELSE BEGIN -- NT login has access
>> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
>> PRINT @.tmpstr
>> END
>> END
>> ELSE BEGIN -- SQL Server authentication
>> IF (@.binpwd IS NOT NULL)
>> BEGIN -- Non-null password
>> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
>> IF (@.xstatus & 2048) = 2048
>> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
>> ELSE
>> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
>> PRINT @.tmpstr
>> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
>> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
>> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
>> END
>> ELSE BEGIN
>> -- Null password
>> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
>> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
>> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
>> END
>> IF (@.xstatus & 2048) = 2048
>> -- login upgraded from 6.5
>> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
>> ELSE
>> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
>> PRINT @.tmpstr
>> END
>> END
>> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
>> END
>> CLOSE login_curs
>> DEALLOCATE login_curs
>> RETURN 0
>> GO
>> sp_help_revlogin
>> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> Hi Uri and Hari,
>> thanks for your replies.
>> Yes, doing a backup/restore is one of the options I suggested to our DBA
>> but
>> he is reluctant to do that because he says (and I'm no DBA) he loses all
>> the
>> ownership rigts and has to recreate SQLServer accounts and so on. To me
>> that
>> doesn't sound to much effort, we are not a big company so there's
>> probably
>> only a hanfull of accounts, however he is the DBA so I have to go with
>> his
>> recomedations pertaining to the databases.
>> One thing I have just found out is that when this server was originally
>> built it was named server-a. Later on and after Sqlserver was installed
>> they
>> renamed the server to server-b. Server-a and server-b. Just wondering if
>> this is part of the problem?
>> Anyway we will explore a few other avenues and if all else fails we will
>> have to revert to the ol backup/restore option. I will direct our DBA to
>> those stored procedures you two have mentioned.
>> thanks for the assistance
>> Craig
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
>> > In additon to Hari's resonse you can also take a look at
>> > sp_detach_db,sp_attach_db,sp_attach_single_file_db
>> > system stored procedures
>> >
>> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> > news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >>
>> >> Easy approches to copy the databases are:-
>> >>
>> >> 1. Backup the production database using (BACKUP DATABASE command)
>> >> 2. COpy the backup file to test server
>> >> 3. Restore the database in test server (RESTORE DATABASE)
>> >>
>> >>
>> >> See Backup and Restore commad in books online.
>> >>
>> >>
>> >> Note:
>> >>
>> >>
>> >> you can also detach and attach databases. See SP_DETACH_DB and
>> > SP_ATTACH_DB
>> >>
>> >> --
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> >> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi all,
>> >> >
>> >> > This is the situation. We have some new SQL Servers being
>> >> > installed.
>> >> > The
>> >> > existing SQL servers will be retired. The existing servers run
>> > Windows2000
>> >> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The
>> >> > new
>> >> > servers are running Windows2003 (SP1) Server (Standard Edition)
>> >> > with
>> >> > SQLServer2000 (SP3). All of the SQLServers are part of our AD
>> >> > domain.
>> > The
>> >> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
>> >> > These
>> >> > domain accounts are members of the local administrator group on
>> >> > each
>> of
>> >> > the SQL servers.
>> >> >
>> >> > The problem we are having is that when the DBA attempts to copy the
>> >> > databases from the Win2000/SQLServer to one of the Win2003 servers
>> the
>> > dts
>> >> > package fails with the following
>> >> >
>> >> > Step Error Sourse: Microsoft Data Transformation Services (DTS)
>> Package
>> >> > Step Error Description: Unspecified Error
>> >> > Step Error Code: 80004005
>> >> > Step Error Help File:sqldts80.hlp
>> >> > Step Error Help Context ID:1100
>> >> >
>> >> > Under more info it says
>> >> >
>> >> > Failed to create the share OMWWIZD
>> >> >
>> >> > We have gone through kb article 274663 which convers this but as
>> >> > far
>> as
>> > we
>> >> > can see we meet all the requirements for this to work, so we are a
>> > little
>> >> > stumped at this point. We thought it may be something to do with
>> >> > Win2003
>> >> > SP1, so we built a Win2003 server with no service packs and tried
>> >> > to
>> > copy
>> >> > some databases and we received the exact same error.
>> >> >
>> >> > I can manually create a share between the Win2000 and Win2003
>> >> > servers
>> >> > using the same domain account the SQLServer process use. Once
>> manually
>> >> > created I can create and delete files, so at a filesystem level the
>> >> > interopability between the servers appears fine. Running out of
>> >> > ideas
>> >> > at
>> >> > this point.
>> >> >
>> >> > If anyone has any suggestions they would be greatly welcomed.
>> >> >
>> >> > regards
>> >> >
>> >> > Craig
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>

Copying databases between two SQL Servers

Hi all,
This is the situation. We have some new SQL Servers being installed. The
existing SQL servers will be retired. The existing servers run Windows2000
Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new servers
are running Windows2003 (SP1) Server (Standard Edition) with SQLServer2000
(SP3). All of the SQLServers are part of our AD domain. The SQLSERVER
service and the SQLAGENT servers run as domain accounts. These domain
accounts are members of the local administrator group on each of the SQL
servers.
The problem we are having is that when the DBA attempts to copy the
databases from the Win2000/SQLServer to one of the Win2003 servers the dts
package fails with the following
Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
Step Error Description: Unspecified Error
Step Error Code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Under more info it says
Failed to create the share OMWWIZD
We have gone through kb article 274663 which convers this but as far as we
can see we meet all the requirements for this to work, so we are a little
stumped at this point. We thought it may be something to do with Win2003
SP1, so we built a Win2003 server with no service packs and tried to copy
some databases and we received the exact same error.
I can manually create a share between the Win2000 and Win2003 servers using
the same domain account the SQLServer process use. Once manually created I
can create and delete files, so at a filesystem level the interopability
between the servers appears fine. Running out of ideas at this point.
If anyone has any suggestions they would be greatly welcomed.
regards
Craig
Hi,
Easy approches to copy the databases are:-
1. Backup the production database using (BACKUP DATABASE command)
2. COpy the backup file to test server
3. Restore the database in test server (RESTORE DATABASE)
See Backup and Restore commad in books online.
Note:
you can also detach and attach databases. See SP_DETACH_DB and SP_ATTACH_DB
Thanks
Hari
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> This is the situation. We have some new SQL Servers being installed. The
> existing SQL servers will be retired. The existing servers run Windows2000
> Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> servers are running Windows2003 (SP1) Server (Standard Edition) with
> SQLServer2000 (SP3). All of the SQLServers are part of our AD domain. The
> SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> domain accounts are members of the local administrator group on each of
> the SQL servers.
> The problem we are having is that when the DBA attempts to copy the
> databases from the Win2000/SQLServer to one of the Win2003 servers the dts
> package fails with the following
> Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> Step Error Description: Unspecified Error
> Step Error Code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
> Under more info it says
> Failed to create the share OMWWIZD
> We have gone through kb article 274663 which convers this but as far as we
> can see we meet all the requirements for this to work, so we are a little
> stumped at this point. We thought it may be something to do with Win2003
> SP1, so we built a Win2003 server with no service packs and tried to copy
> some databases and we received the exact same error.
> I can manually create a share between the Win2000 and Win2003 servers
> using the same domain account the SQLServer process use. Once manually
> created I can create and delete files, so at a filesystem level the
> interopability between the servers appears fine. Running out of ideas at
> this point.
> If anyone has any suggestions they would be greatly welcomed.
> regards
> Craig
>
|||In additon to Hari's resonse you can also take a look at
sp_detach_db,sp_attach_db,sp_attach_single_file_db
system stored procedures
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Easy approches to copy the databases are:-
> 1. Backup the production database using (BACKUP DATABASE command)
> 2. COpy the backup file to test server
> 3. Restore the database in test server (RESTORE DATABASE)
>
> See Backup and Restore commad in books online.
>
> Note:
>
> you can also detach and attach databases. See SP_DETACH_DB and
SP_ATTACH_DB[vbcol=seagreen]
> --
> Thanks
> Hari
>
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
Windows2000[vbcol=seagreen]
The[vbcol=seagreen]
dts[vbcol=seagreen]
we[vbcol=seagreen]
little[vbcol=seagreen]
copy
>
|||Hi Uri and Hari,
thanks for your replies.
Yes, doing a backup/restore is one of the options I suggested to our DBA but
he is reluctant to do that because he says (and I'm no DBA) he loses all the
ownership rigts and has to recreate SQLServer accounts and so on. To me that
doesn't sound to much effort, we are not a big company so there's probably
only a hanfull of accounts, however he is the DBA so I have to go with his
recomedations pertaining to the databases.
One thing I have just found out is that when this server was originally
built it was named server-a. Later on and after Sqlserver was installed they
renamed the server to server-b. Server-a and server-b. Just wondering if
this is part of the problem?
Anyway we will explore a few other avenues and if all else fails we will
have to revert to the ol backup/restore option. I will direct our DBA to
those stored procedures you two have mentioned.
thanks for the assistance
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> In additon to Hari's resonse you can also take a look at
> sp_detach_db,sp_attach_db,sp_attach_single_file_db
> system stored procedures
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> SP_ATTACH_DB
> Windows2000
> The
> dts
> we
> little
> copy
>
|||Craig
Your DBA is not quite correct.
There are two stored procedures that provided by Microsoft to migrate
users/logins with their original SID
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi Uri and Hari,
> thanks for your replies.
> Yes, doing a backup/restore is one of the options I suggested to our DBA
but
> he is reluctant to do that because he says (and I'm no DBA) he loses all
the
> ownership rigts and has to recreate SQLServer accounts and so on. To me
that
> doesn't sound to much effort, we are not a big company so there's probably
> only a hanfull of accounts, however he is the DBA so I have to go with his
> recomedations pertaining to the databases.
> One thing I have just found out is that when this server was originally
> built it was named server-a. Later on and after Sqlserver was installed
they[vbcol=seagreen]
> renamed the server to server-b. Server-a and server-b. Just wondering if
> this is part of the problem?
> Anyway we will explore a few other avenues and if all else fails we will
> have to revert to the ol backup/restore option. I will direct our DBA to
> those stored procedures you two have mentioned.
> thanks for the assistance
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
of[vbcol=seagreen]
the[vbcol=seagreen]
Package[vbcol=seagreen]
as[vbcol=seagreen]
manually
>
|||Hi Uri,
ok, thanks for the information. I'll pass it on to him. To be fair to him
he's really an Oracle DBA who has had SQLServer dumped on him and he hasn't
had a great deal of training on it yet.
One other thing has come to light and as of yet I am not to sure if it is
the cause or not, we still need to do some more testing. To cut a long and
boring story short we have one AD domain, let's call it oz.com.au. AD is DNS
intergrated. At some point we created some sub domains within abc.com,
namely dev.abc.com and test.abc.com. These new subdomains also have their
own unique ip address range
abc.com 192.168.1.x
dev.abc.com 192.168.2.x
test.abc.com 192.168.3.x
So the fully qualified domain names for our servers would look like this
server1.abc.com
server2.abc.com
server3.dev.abc.com
server4.dev.abc.com
server5.test.abc.com
server6.test.abc.com
however we only have one single AD domain, in this case abc.com.
What we have now found is that we can copy databases between two servers who
share the same FQDN domain part. ie. server1.abc.com to server2.abc.com
works, however copying between two servers who do not share the same domain
part does not and generates the error mentioned in the original post. ie.
server1.abc.com to server3.dev.abc.com
The odd thing is that this setup used to work under Win2000, but obviously
something in Win2003 is different. Might have to bounce this off some people
in the windows groups.
If anyone has any suggestions I'm all ears.
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Craig
> Your DBA is not quite correct.
> There are two stored procedures that provided by Microsoft to migrate
> users/logins with their original SID
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> but
> the
> that
> they
> of
> the
> Package
> as
> manually
>
|||Hi all,
ok, it turned out that the fully qualified hostnames were in fact causing
the problem. As soon as we put the machines in the same internet domain name
space the DTS copies worked. I assume that something within SQLServer/DTS
assumes that if the FQDN contain different domain parts then therefore they
are in different AD domains. The fix was to simply change the
- FQDN to the same domain
- Copy the databases
- Revert the servers's FQDN back.
Thanks to all who offered various solutions. Now off to the MS support site
to see if there is something I should be configuring in AD when I do this. I
wouldn't have thought so but it looks like there is.
Regards
Craig
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%232V5e$3hFHA.3912@.tk2msftngp13.phx.gbl...
> Hi Uri,
> ok, thanks for the information. I'll pass it on to him. To be fair to him
> he's really an Oracle DBA who has had SQLServer dumped on him and he
> hasn't had a great deal of training on it yet.
> One other thing has come to light and as of yet I am not to sure if it is
> the cause or not, we still need to do some more testing. To cut a long and
> boring story short we have one AD domain, let's call it oz.com.au. AD is
> DNS intergrated. At some point we created some sub domains within abc.com,
> namely dev.abc.com and test.abc.com. These new subdomains also have their
> own unique ip address range
> abc.com 192.168.1.x
> dev.abc.com 192.168.2.x
> test.abc.com 192.168.3.x
> So the fully qualified domain names for our servers would look like this
> server1.abc.com
> server2.abc.com
> server3.dev.abc.com
> server4.dev.abc.com
> server5.test.abc.com
> server6.test.abc.com
> however we only have one single AD domain, in this case abc.com.
> What we have now found is that we can copy databases between two servers
> who share the same FQDN domain part. ie. server1.abc.com to
> server2.abc.com works, however copying between two servers who do not
> share the same domain part does not and generates the error mentioned in
> the original post. ie. server1.abc.com to server3.dev.abc.com
> The odd thing is that this setup used to work under Win2000, but obviously
> something in Win2003 is different. Might have to bounce this off some
> people in the windows groups.
> If anyone has any suggestions I'm all ears.
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>