1,405
Are there any updates available for this Article ID: 872340?
How to move Great Plains Personal Data Keeper SQL logins to another server that is running Microsoft SQL Server
I am moving a GP 10.0 & PDK 10.0 sp5 system from SQL 2005 32-bit to SQL 2008 R2 64-bit and get the error...
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master..sysxlogins'.
when trying to copy the PDK users and passwords on the SQL 2005 server.
*This post is locked for comments
Has anyone come up with a solution to this problem (i.e. CONVERT not allowed) ?
I'm facing a problem where we moved the GP production databases from one SQL2005 server to a SQL2008R2. Most of the work was relying on the KB878449 Rev 17 from MS, and pretty much everything worked fine, except for PDK... There is nothing about the PDK users in this paper and now we have an issue to reset passwords in PDK.
When trying to do it, I receive an error message saying : "This user does not have a corresponding SQL Login. To create a SQL Login, enter a password and chose save."
After doing so, I get another error message stating the opposite : "A SQL Login with this user name already exists. Choose a different user name"...
What a joke ! This is very strange because I was able (as SA user) to reset a few user's password, but then I get this message on several others.
The KB872340 was apparently giving all the directions on how to do it properly.. but it doesn't exist anymore.
Microsoft, any hint ?
Thanks in advance for every suggestion to fix this quickly, our PDK users can't work anymore.
My mistake I think. Extra in the cut and paste. Sorry!
Try these instead...
/******* Part 1 **********/
SET QUOTED_IDENTIFIER OFF
if exists (select * from DYNAMICS..sysobjects
where id = Object_id('DYNAMICS..RecoveryMasterPDK') and type = 'U')
begin drop table DYNAMICS..RecoveryMasterPDK end
go
CREATE TABLE DYNAMICS..RecoveryMasterPDK (
name char(15) not null unique,
password varbinary(256) null,
DEX_ROW_ID int IDENTITY(1,1) not null )
go
/*****************/
/****** Part 2 ***********/
insert DYNAMICS..RecoveryMasterPDK (name, password)
select name, password from master..syslogins
where name in (select USERID from DYNAMICS..PDK40001)
and name not in ('sa', 'DYNSA')
/*****************/
PS Article ID: 872340 no longer comes up in CustomerSource
I tried the revised script by David M. Part 1 worked but I get the error when I ran Part 2.
" Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query."
any idea?
I took a look at the password field in the syslogins view and the data type is nvarchar.
I would continue with the changes you have made to the script as that seems valid and the varbinary is not what the password field is typically set to.
Please post back if you have further questions.
Just getting ready to move the production server this weekend. Fixing the typo does not resolve the problem. See the SQL code below...
Part 2 gets this error....
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.
/******* Part 1 **********/
SET QUOTED_IDENTIFIER OFF
if exists (select * from DYNAMICS..sysobjects
where id = Object_id('DYNAMICS..RecoveryMasterPDK') and type = 'U')
begin drop table DYNAMICS..RecoveryMasterPDK end
go
CREATE TABLE DYNAMICS..RecoveryMasterPDK (
name char(15) not null unique,
password varbinary(256) null,
DEX_ROW_ID int IDENTITY(1,1) not null )
go
/*****************/
/****** Part 2 ***********/
insert DYNAMICS..RecoveryMasterPDK (name, password)
select name, password from master..syslogins
where name in (select USERID from DYNAMICS..PDK40001)
and name not in ('sa', 'DYNSA')
/*****************/
This change seems to get passed the error, changing the create table "Password" field.
Will this work all the way through the move to the new server?
/*****************/
CREATE TABLE DYNAMICS..RecoveryMasterPDK (
name char(15) not null unique,
password nvarchar(256) null, /*<-------------*/
DEX_ROW_ID int IDENTITY(1,1) not null )
go
/*****************/
Thank you for posting your inquiry to the Community site David.
I believe that you are running the script select name, password from master..sysxlogins. They is a typo in this statement and it should be as follows.
select name, password from master..syslogins
I know a request has been made to have this changed but it may not have been completed yet.
Please post back if you have further questions.
Have a great day!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (