sql server db: situation

Showing posts with label situation. Show all posts

Tuesday, March 20, 2012

Copying over permissions on all tables..... ?

We have a situation here were we always loose out permissions and then have to reapply them (and this is a LARGE company! with many group permissions.... Ughhh). I had thought at first that the permissions would be copied with the database when I copied it from one Server the other... not so I found out! So I applied ALL the permissions! Well we took a copy of Server #1 and replaced it on the other Server and all our permissions were gone.

Is there anyway that we can make sure that all the permissions come across. We are moving a database on NT 4 to another one on Win2000 they have different names... is it possible to make sure all the rights come across??

Why do we loose the current permissions (on the Win2000 Server) when we do a complete restore (move over) of the NT 4 systems database to the Win2000 systems database?? Its irritating having to redo all those permissions!

Ponypoor:mad:Your permissions were not gone. They are located on the Master db and you probably copied only the production db.|||I am not quite sure what you mean - We are taking the backup of the whole database (which contains many tables...) and then applying it to the new SQL Server... were do I find the 'master' one... ?|||Hi,

Permissions on a object for a user depends on three tables
a. SYSLOGINS/sysxlogins
b. SYSUSERS
c. syspermissions
d. sysprotects
e. sysobjects

syslogins reside on master database and others reside on user db.

all tables are linked with each other on the basis of suid of sys(x)logins.

once you copy user database from one server to another server all the links are messedup. since suid of one server is different from other.

To RESOLVE this use sp_change_users_login to correct the problem. Read more on BOL

Best of luck|||Thank you very much for the information I will try it out...

Ponypoor|||Try selecting the "generate sql script..." option from enterprise manager and on the "options" tab select "Script database users and roles", "Script SQL Server logins" and "Script object-level permissions".

Some combination of these should at least generate a script that you can apply after restoring the backup.

I'm always leery of restoring a master database from one server to another. No specific reason why but it just seems like it could cause problems.

Sunday, March 11, 2012

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

Saturday, February 25, 2012

Copy/Paste fails in SQL Server Management Studio

Has anyone experience the much frustrated situation of having the copy/paste
functionality fail within the Management Studio query editor environment?
I will be working fine, doing copy/paste within the query editor and then
for some reason the "copy" quits. I can copy from an outside source, e.g.
NotePad or Word and paste within Studio, but it seems like the "copy"
function stops working.
Like-wise, the ability to generate a script into a new window also stops
working.
Any ideas?
Thanks,
DaveDave
You are probably have SQL Server 2005 installed as a named instance, right?
It consumes memory as well
Close SSMS and open again ,see what is going on
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:053E7262-0707-4065-A0DA-CCC86924441A@.microsoft.com...
> Has anyone experience the much frustrated situation of having the
> copy/paste
> functionality fail within the Management Studio query editor environment?
> I will be working fine, doing copy/paste within the query editor and then
> for some reason the "copy" quits. I can copy from an outside source, e.g.
> NotePad or Word and paste within Studio, but it seems like the "copy"
> function stops working.
> Like-wise, the ability to generate a script into a new window also stops
> working.
> Any ideas?
> Thanks,
> Dave|||Thanks for the response.
I did close and reopen and it then works fine. I also had a whole day wwhere
I never experienced any problems.
So, you think it is a memry issue? I was watching my client (PC) memory and
it did not change much when running a series of tests. I really don't think
it is a memory issue but I also haven't beable to figure out any sequence o
r
pattern to the problem.
Dave
"Uri Dimant" wrote:

> Dave
> You are probably have SQL Server 2005 installed as a named instance, right
?
> It consumes memory as well
> Close SSMS and open again ,see what is going on
>
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:053E7262-0707-4065-A0DA-CCC86924441A@.microsoft.com...
>
>|||Dave
Each instance you have on your computer will counsume memory for its needs,
how much memory do you have?
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:B5D0BDC2-6112-4B02-8C87-74C02D15C2A2@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I did close and reopen and it then works fine. I also had a whole day
> wwhere
> I never experienced any problems.
> So, you think it is a memry issue? I was watching my client (PC) memory
> and
> it did not change much when running a series of tests. I really don't
> think
> it is a memory issue but I also haven't beable to figure out any sequence
> or
> pattern to the problem.
> Dave
>
> "Uri Dimant" wrote:
>|||I have 1 gb of memory. I have monitored the memory usage while doing many
copy/paste scenarios and I haven't noticed any change in usage.
I will shut all my services down tomorrow and see if I run into any problems
.
This problem is very sparatic. Most of the early morning I was running into
this pretty frequently and quickly after shutting down management Studeo and
reopening it. And now for awhile I have been doing many times as many
copy/paste and have not run into the problem.
"Uri Dimant" wrote:

> Dave
> Each instance you have on your computer will counsume memory for its needs
,
> how much memory do you have?
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:B5D0BDC2-6112-4B02-8C87-74C02D15C2A2@.microsoft.com...
>
>|||I've experienced similar with terminal services. Just reconnect/recreate the
session seems to help.
-oj
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:06C6807E-B946-43A4-B95D-BE17B38DD135@.microsoft.com...[vbcol=seagreen]
>I have 1 gb of memory. I have monitored the memory usage while doing many
> copy/paste scenarios and I haven't noticed any change in usage.
> I will shut all my services down tomorrow and see if I run into any
> problems.
> This problem is very sparatic. Most of the early morning I was running
> into
> this pretty frequently and quickly after shutting down management Studeo
> and
> reopening it. And now for awhile I have been doing many times as many
> copy/paste and have not run into the problem.
> "Uri Dimant" wrote:
>|||I don't beleive this has anything to do with memory. I stopped all SQL
services locally on my PC that I am not using as I am doing my work against
a
differetn sql server on a different server using Managementb Studio.
So, after stopping a bunch of services that freed up
200+ MB of memory, and just after a dozen or so copy/paste, the ability to
do a copy stops.
I didn't think it had to do with memory since I was watching it usage and it
didn't jump when doing a sweries of copies.
I guess it is time to engage a Microsoft support call.
"oj" wrote:

> I've experienced similar with terminal services. Just reconnect/recreate t
he
> session seems to help.
> --
> -oj
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:06C6807E-B946-43A4-B95D-BE17B38DD135@.microsoft.com...
>
>|||Hi Dave,
I wasn't suggesting it's a memory problem. I've seen similar behavior
through remote desktop ( I do it quite a bit to access the office). Simply
reconnecting helps.
If you contact PSS and have a definite answer, I would love to know for
reference.
-oj
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:276C89F0-67EA-416F-AC70-94C95D8A3CF5@.microsoft.com...[vbcol=seagreen]
>I don't beleive this has anything to do with memory. I stopped all SQL
> services locally on my PC that I am not using as I am doing my work
> against a
> differetn sql server on a different server using Managementb Studio.
> So, after stopping a bunch of services that freed up
> 200+ MB of memory, and just after a dozen or so copy/paste, the ability to
> do a copy stops.
> I didn't think it had to do with memory since I was watching it usage and
> it
> didn't jump when doing a sweries of copies.
> I guess it is time to engage a Microsoft support call.
>
> "oj" wrote:
>

Tuesday, March 20, 2012

Copying over permissions on all tables..... ?

Sunday, March 11, 2012

Copying databases between two SQL Servers

Copying databases between two SQL Servers

Copying databases between two SQL Servers

Saturday, February 25, 2012

Copy/Paste fails in SQL Server Management Studio

sql server db

Blog Archive

About Me