Transfer Logins From Sql2000 To Sql2005
Sep 20, 2005Hi,
View 4 RepliesHi,
View 4 RepliesI have versions of sp_hexadecimal and sp_help_revlogin I used with SQL 2000 to transfer logins. Will they work with SQL 2005 as-is? Might I need updated versions?
View 2 Replies View RelatedI just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies View Relatedi have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies View RelatedHI,
If i transfer logins from one server to another server using dts, I am getting all the logins and permissions... How will i get passwords too..
-thank u.
--Siva
Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated
View 1 Replies View RelatedI would like to know how I can transfer all Login and password and access records from one SQL Server to another, without transferring the databases. I know I can do this via DTS, but I can not figure out the right combination for bringing over the login names. passwords, and roles, with out transferring the databases. The databases have been transfered already via a backup and restore process. Now I need all of the login infomation from the old server to come across. In DTS I go through the Wizard saying to transfer the Master Database from one server to the other, and transfer objects and Data between SQL Server databases. Under the selcet options to transfer page, I uncheck the "Use Default Options" , and on the options page I select all of the "Security options" and deselect all of the "Table options" (Indexes, triggers, keys, etc). The process runs for a short time before bombing out with a failure. I can not find a log file under the Log folder to tell me where it has failed. Can anyone one lend me a hand with this?
TIA
Ken Merrigan
Hi all
In the process of migration of a database from one SQL 2000 server to another, I am transfering the logins using the microsoft documented script
http://support.microsoft.com/kb/246133/
When i am executing the script on the source server, I am getting the following error.
Server: Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
In all the past migrations , I have used this without any issues, but not sure what have changed.
Or am i doing something really stupid.
Can you guys pls execute this in your server and see whether you are able to create the sp_help_revlogin stored procedure.
Thanks in advance
Sreenath
Hi everyone ,
i am trying to find logins which has blank password in SQL2000 . i wrote cursor to find these logins. But i cannot catch the logins.Beacuse sp_password sistem procedure does not return @@ERROR.
I can easily do it with SQL2005 (BEGIN -TRY -CATCH).
How can i catch sp_password error in SQL Server 2000??
or are there any suggestions to find these logins.
How can i find blank password logins in SQL2000???
sp_password '','','loginname'
select @@error ''' it is always ZERO
Declare @sql as varchar(255)
Declare @login as varchar(50)
Declare Cur_Login CURSOR scroll FOR
Select name from syslogins where isntuser=0
open Cur_Login
fetch next from Cur_Login INTO @Login
WHILE @@FETCH_STATUS = 0
begin
set @sql = 'sp_password '''','''',''@login'' '
set @sql = replace(@sql ,'@login',@login)
exec (@sql)
if @@ERROR <> 0
print 'Password is not blank'
fetch next from Cur_Login INTO @Login
end
close Cur_Login
deallocate Cur_Login
Could anybody tell me whetherTransfer logins task transfers passwords for logins or not?
View 1 Replies View RelatedUnder sql 2000 I could connect to a server as a speciifc sql user. How can I emulate that under sql 2005 Query Editor. (It doesn't appear to let me define the user to log in as.)
TIA,
Barkingdog
Hi all,
I just wonder can we install both sql2000 & sql2005
into one server, any problems?
thanks for advice.
I have two SQL servers setup; A - SQL 2005, and B - SQL 2000.
I would like to create an account on Server A which has access to the results from one view via a linked server on Server B. I don't want the user on Server A to be able to access any databases, tables or even columns on Server B with the exception of those contained within this view.
Is this possible, and how would I go about doing it? (Permission-wise)
I have just loaded SQL2005(server only)on my production box.
This box also is running SQL2000.
When I install the SQL2005 SP2 it says that if services are locked they will cause a reboot. I stop all the SQL 2005 services but it also want to stop SQL2000 Server for "backward compatiblilty".
I thought installing a separate instance of SQL2005 would not have any effect on my SQL2000 instance.
Thanks for your help
R/P
Hello all
I have an odd problem with sql 2005. I'm a long time sql 2000 user trying to migrate to 2005. My company uses CSVs to import data from our clients db into our SQL db. In 2000 I just create a txt source and pick whichever CSV and transform to a temp sql table. This has always worked just fine with no problems.
So we have a new server with sql 2005 installed. I go through the "migrate dts 2000" wizard and pull in all my DTSs from sql 2000. The first thing I see that needs to be fixed is "connection 1" which is my CSV connection manager along with the flat file source.
I went through both the connection and the flat file source and tried to copy exactly what I do in sql 2000. When I preview the data or even execute this package I get bad data.
Here is what my CSV looks like:
"ACCT ","ACCOUNT DESCRIPTION ","ACCT TYPE","SCH NO","SC"
"9999Z","Balance By Source Offset",9
"87B","UTILITIES(NOT PHONE)U-C",7
72,"ADM-LEGAL/AUDIT/COLL EXP",7
315,"SALES TAX - VEHICLES LOCAL 1",3
"90A","INTEREST(NON FPLAN/MORT)/N-C",7
73,"MEALS & ENTERTAINMENT-ALL",7
"210A","REBATES RECEIVABLE",2,17,4
"5A@","# OF EXT WARR SALES-UC",10
"51N","N/C-VEHICLE INV MAINTENANCE",7
"87D","UTILITIES(NOT PHONE)SVC",7
"90B","INTEREST(NON FPLAN/MORT)U-C",7
317,"ACCRUED OTHER",3,75,4
This is what it looks like when I preview it inside the flat file connection manager:
ACCT ACCT DESC ACCT TYPE SCH NO SCTYPE
9999z blance by source... 9– – "87B" UTILITIES(NO... 7
So it is basically putting two rows of csv data into one row of SQL data. Now I realize my CSV isn't in the correct format because it doesn't have the extra commas at the end of a row IF the columns are null. It seems that 2005 doesn't recognize the carriage return/line feed. It puts the actual ascii characters in the preview. Once I execute the package it changes the ascii characters into blank spaces. My big issue is, why did this work just fine in 2000 but I can't get clean data in 2005? I'm completely clueless now, I have no idea where to go with this. Its impossible to change the output of the CSVs I get. There is literally no other options. I kinda get what I get with them. Why is this happening?
As we all know logins and jobs aren't replicated when using AllwaysOn. What i wanna know is if the "tranfer login taks" will be enough to cover the transfer of logins between servers.
View 7 Replies View RelatedI€™m using express edition of SQL server 2005. 9.00.1399.06 (Intel X86)
I need to transfer all SQL logins and passwords from one server to another.
The script at http://support.microsoft.com/kb/246133 (designed for sqlserver 7.0 and 2000) This script references a system table (master..syslogins) that may not exits in sqlserver 2005. I get the following error:
EXEC master..sp_help_revlogin
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
Would SMO be something to try for transfering logins and passwords?
Thanks,
Karl
Any one knows if I can restore a SQL2000 database backup file on SQL2005? and the DB structure will be upgraded?
Many thanks.
Hi,
Can a sever with OS win2003 install both sql2000 & sql2005? is there any problems with 2 differenct versions in one sever.
Thank you. :)
I have installed MSDE2000 (name: mypcsql2000, port 1433) and sql2005 Express (mypcsql2005, port 1434) on my WinXP.
I'm developing some application and I need to test both server.
It has worked fine about 1 year, but last month i got error message.
When i'm trying to connect to SQL2005 from my application or import data from Excel, i get error message:
[DBNETLIB][ConnectionOpen (Invalid Connection()).]Invalid connection.
I can access SQL2005 with Management Studio or cmd line (sqlcmd). But not with Excel (or with my application).
MSDE2000 works fine, i haven't any problems.
Last weekend I have reinstalled my WinXP and also MSDE2000, SQL2005 Express.
Worked fine, but when turn on automatic updates ON and afterwards installed latest updates, the problem is here again...
Any idea to solve this problem?
(sorry for my english...)
Hi,
Is there any difficulties I should be aware of? I'm not running DTS and I think my DB is relatively simple. I've googled and not found anything that sticks out as a problem.
We are getting a new server because we want more RAM, and better upgrade options. The hosting company recommended 64bit SQL, so I am thinking of following their recommendations.
I am upgrading from a 2 x p4 2.8ghz xeons to 1 x Intel 5130 dual core 64 bit chip. I also will be going from 2 gigs to 4 gigs of RAM.
Any thoughts I would love to hear.
Thanks!
Hello!!
we use SQL2000(64bit) and we are considering our system to apply SQL2005
so i tested two SQL-Server on One Machine by installing each instance of them.
The RESULT..
i can't find difference of performance between two instance.
is there no impoving in Database engine?
* i'm sorry, for my poor sentence...
I am a IT Manager.I have question:
There is a database was created on SQL Server.
I want to learn if I restore the database to SQL 2005 then I want to get backup then I want to restore to SQL 2000.
shortly: SQL 2000 sever-> SQL 2005->SQL 2000 SERVER
Can I do that?
If I do that is there someting missed?
http://www.narcv.com/
I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802
Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.
Has anyone else seen an error similiar to this? Any insight would be appreciated.
Thanks in advance,
Max
Hi,
I need to install SQL2000 onto a machine (W2K3 R2, SP2) that already has SQL2005, SP1 running. I can't disturb the SQL2005 installation.
Are there any problems or pitfalls to avoid? I've googled and looked on Technet but not yet found an answer.
Has anyone done this?
I know that if I install a separate instance of SQL2000 alongside a default installation of SQL2000 then I'll get a totally separate installation which won't affect the other one at all, so I reckon it could be done. But I have to be sure before I start!
Thanks in advance,
Steve
Hi,
We have two systems, one working on SQL2000 and other working on SQL2005. In SQL2000, there is JOB which needs to execute a JOB in SQL2005, get feedback based (success or failure) and proceed accordingly.
I am not sure the best practice to call SQL2005 job from SQL2000 job. Both the server's run undersame domain account.
please help, thanks
atul
Hello,
Restoring a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:
TITLE: Microsoft SQL Server Enterprise Edition
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
Hi,
I'm running a VB6 app with MDAC 2.8 on Windows Server 2003 SP1.
I create an ADO recordset with a SELECT statement which returns an empty recordset.
I then add 2 new records to it with AddNew and later perform an UpdateBatch.
Before the SQL upgrade it worked fine but now errors with:
'Cannot convert a char value to money. The char value has incorrect syntax.'
I did a SQL Trace to find out what the Updatebatch was actualling sending to SQL (listed below).
This works fine when I run it on Query Analyser using SQL 2000 but fails on SQL 2005.
I worked out the problem is linked to the columns NewValue and OldValue both are varchar 70.
The first insert has them defined varchar(7) and varchar(1). This insert succeeds, but the second insert has the columns defined as varchar(18) and varchar(12). This one fails (unless they only contains numbers!).
I'm confused.
I've read that sp_executesql has changed for SQL2005.
Any ideas anyone?
Thanks
Howard
Code Snippet
exec sp_executesql N'INSERT INTO "pit1_lamda".."Planalt"
("ElNo","Item","MvmtDate","NewValue","OldValue","PlanAltId","PlanNo","Timestamp","User_Id","RBNo","CreatorId","CreatorTypeCode","CreatedDate","ExitNo","PBTxnTriggerId","PremHistNo","RegContractPremium","WaiverPrem","SinglePremTot","PlanMvtId","SingMvtID","PLFAID","TrancheId")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23); INSERT INTO
"pit1_lamda".."Planalt"
("ElNo","Item","MvmtDate","NewValue","OldValue","PlanAltId","PlanNo","Timestamp","User_Id","RBNo","CreatorId","CreatorTypeCode","CreatedDate","ExitNo","PBTxnTriggerId","PremHistNo","RegContractPremium","WaiverPrem","SinglePremTot","PlanMvtId","SingMvtID","PLFAID","TrancheId")
VALUES (@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46)',N'
@P1 varchar(6),@P2 int,@P3 datetime,@P4 varchar(7),@P5 varchar(1),@P6 int,@P7 varchar(12),@P8 int,@P9 varchar(8),@P10 varchar(4),@P11
varchar(12),@P12 tinyint,@P13 datetime,@P14 int,@P15 int,@P16 int,@P17 money,@P18 money,@P19 money,@P20 int,@P21 int,@P22 int,@P23 int,
@P24 varchar(6),@P25 int,@P26 datetime,@P27 varchar(18),@P28 varchar(12),@P29 int,@P30 varchar(12),@P31 int,@P32 varchar(8),@P33 varchar(4),@P34
varchar(12),@P35 tinyint,@P36 datetime,@P37 int,@P38 int,@P39 int,@P40 money,@P41 money,@P42 money,@P43 int,@P44 int,@P45 int,@P46 int',
'000000',80,'01-01-2008','1463286','0',27425002,'043902305X2 ',57153,'mtstdat4','0000',
'mtstdat4 ',1,'03-05-2008',0,0,0,$0.0000,$0.0000,$0.0000,4250207,0,0,3001752,
'000000',11,'03-02-2008','10 In Force (V011)','3 AwaitIssue',27425003,'043902305X2 ',57155,'mtstdat4','0000',
'mtstdat4 ',1,'03-05-2008',0,0,0,$0.0000,$0.0000,$0.0000,4250207,0,0,3001752
I want to replicate from SQL2000 to SQL2005. I had configured distributor, publisher in SQL2000 and also subscriber in SQL2005. But so far no replication was done on SQL2005. I wonder SQL2000 (SP2) and SQL2005 replication will it work? Is it true that I should install Service Pack 3 and above in order to replicate my database to SQL2005.
Thanks
Wint
Hi
How to transfer logins from 2000 to 2005?
Raj.
I need to transfer the logins and passwords from my prod server to my DR server. I've seen a number of articles about how to transfer logins; but, I'm not finding anything on how to transfer both logins and their passwords. SQL server R2008 R2
View 4 Replies View RelatedLinked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the tables under it so could not query anything.
----
USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerNameInstanceName', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc',@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerNameInstanceName', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteUser', @rmtpassword = N'remotePassword'
Hello,
I am currently using vs2005 with sql2000 and have a parameter query getting records form my sql2000 db.
'Load existing data to textboxes for editingDim strSQL1 As String = "SELECT id, LogDate, LogTime, LogDD, StatDD, LogEvent FROM DEMO_DailyOccurrence WHERE completed = 0 AND RecordIdentify = @strOutdate ORDER BY id ASC "Dim scnnNW1 As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("SQLconnection"))Dim scmd1 As New SqlCommand(strSQL1, scnnNW1)With scmd1.Parameters.Add(New SqlParameter("@strOutdate", SqlDbType.DateTime)).Value = strOutdate1End WithDim sda1 As New SqlDataAdapter(scmd1)Dim ds1 As New Data.DataSetTrysda1.Fill(ds1)Catch ex As ExceptionEnd Try
strOutdate1 is a date and has been tested and proven to be OK.
When I run the same code against my sql2005 db, the query returns nothing.
Do I have to handle something differently for sql2005.
Thanks
Peter