BCP syslogins cmd line error
-
I am attempting to bcp syslogins from production to my development server and am getting the following error. I recently went from sql server 2000 sp4 back to sp3A. I did a backup of master beforehand but it will not allow me to restore it because it's a different version. I do not have a copy of the backup before the sp3A install as someone as did it. I am attempting to bcp the sysusers from production to the development server so that they won't all have to be manually re-entered. I am able to bcp the users out of the production database into a text file fine but when I try to load them into the development table I get the error below. I have ran sp_config to allow updates and the reconfig statement. Any suggestions?
BCP MASTER..SYSLOGINS IN MASTER.TXT -c -t, -b 1 -U sa
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]All bound columns are read-only
Thanks,
Cathy
-
You will not be able to bcp in syslogins (it is actually a view). The underlying table is actually sysxlogins. Again you will not be abkle to bcp in to this table due to the computed columns it contains. Also, MS has put in an 'exit' to prevent this.
You need to transfer logins, well there are a couple of choices. One is DTS. The other is sp_help_revlogin. Below is the link:
http://support.microsoft.com/default.aspx/kb/246133
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
IUf you are transferring logins, soimetimes users within databases need to be 'tweaked' as well. Below is a SP to assist:
---
--- fix_users_login.sql
---
use master
go
set quoted_identifier off
go
if exists (select * from dbo.sysobjects where id = object_id('dbo.sp_fix_users_login'))
begin
print 'dropping old procedure dbo.sp_fix_users_login'
drop procedure dbo.sp_fix_users_login
end
go
---
create procedure sp_fix_users_login
as
---
set quoted_identifier off
---
declare @name varchar(64)
---
declare user_csr cursor for
select name
from sysusers
where issqluser = 1
and name not in ('public','dbo','guest')
order by name
---
open user_csr
fetch user_csr
into @name
print ''
print char(9) + 'Fixing database users ...' + char(13) + char(13)
---
while (@@FETCH_STATUS = 0)
begin
print char(9) + 'Adjusting user ' + @name
exec sp_change_users_login @Action='Auto_Fix', @UserNamePattern=@name
fetch user_csr
into @name
end
print ''
print char(9) + 'All database users fixed' + char(13)
close user_csr
deallocate user_csr
---
--- clean up and exit
---
return
go
use master
go
if exists (select * from dbo.sysobjects where id = object_id('dbo.sp_fix_users_login'))
begin
print 'procedure dbo.sp_fix_users_login created'
end
go
---
--- fix_users_login.sql
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Both ideas worked thanks very much for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply