Migrated DB to 2016, having ODBC Access problem

Question

Migrated DB to 2016, having ODBC Access problem

DaveBriCam

SSCarpal Tunnel

Points: 4294
More actions
January 29, 2018 at 8:43 am

#403007

Thanks in advance for any clues on this problem. I'm migrating several SQL Server databases from SQL2005 to SQL2016. One database is visible (I can connect to the tables) in a MS-Access frontend, and another database is not though I followed, I think, the same method for each. I retrieved a .bak file and loaded it on the C:\ drive of the host machine and from there I loaded it into SQL2016 using this script and all my tables are there with the expected data. The first line of code displayed that the logical data and log files were named ATCB_DB_Data and ATCB_DB_LOG and I can't seem to change that on my SQL2005 backup.

restore filelistonly from disk = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
RESTORE DATABASE CPCE_DB
       FROM DISK = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
      WITH
       MOVE 'ATCB_DB_Data' TO 'D:\Databases\Data\CPCE_DB_Data.MDF',
       MOVE 'ATCB_DB_LOG' TO 'D:\Databases\Data\CPCE_DB_Log.LDF',
       REPLACE
      ;
The only other issue is when I go to the 'dbo' user it lists sqlAgentProxy rather than my active directory name:

USE [CPCE_DB]
GO
/****** Object: User [dbo] Script Date: 1/29/2018 10:41:36 AM ******/
CREATE USER [dbo] FOR LOGIN [NBCC-US\sqlAgentProxy] WITH DEFAULT_SCHEMA=[dbo]
GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

briancampbellmcad - Monday, January 29, 2018 8:43 AM
Thanks in advance for any clues on this problem. I'm migrating several SQL Server databases from SQL2005 to SQL2016. One database is visible (I can connect to the tables) in a MS-Access frontend, and another database is not though I followed, I think, the same method for each. I retrieved a .bak file and loaded it on the C:\ drive of the host machine and from there I loaded it into SQL2016 using this script and all my tables are there with the expected data. The first line of code displayed that the logical data and log files were named ATCB_DB_Data and ATCB_DB_LOG and I can't seem to change that on my SQL2005 backup.

restore filelistonly from disk = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
RESTORE DATABASE CPCE_DB
       FROM DISK = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
      WITH
       MOVE 'ATCB_DB_Data' TO 'D:\Databases\Data\CPCE_DB_Data.MDF',
       MOVE 'ATCB_DB_LOG' TO 'D:\Databases\Data\CPCE_DB_Log.LDF',
       REPLACE
      ;
The only other issue is when I go to the 'dbo' user it lists sqlAgentProxy rather than my active directory name:

USE [CPCE_DB]
GO
/****** Object: User [dbo] Script Date: 1/29/2018 10:41:36 AM ******/
CREATE USER [dbo] FOR LOGIN [NBCC-US\sqlAgentProxy] WITH DEFAULT_SCHEMA=[dbo]
GO

What I'm seeing in Access is this message:

RandomStream Hall of Fame Points: 3735 More actions · Answer 2

What is the definition of dbo_school?
Check its properties and see which server/database/table (or view) it is pointing to. Then on the source database server, check access permission on database and table (or view).

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 3

RandomStream - Monday, January 29, 2018 10:59 AM
What is the definition of dbo_school?
Check its properties and see which server/database/table (or view) it is pointing to. Then on the source database server, check access permission on database and table (or view).

Do you mean the DDL of dbo_schools like below? There are no explicit permissions set at the table level as far as I can tell.
USE [CPCE_DB]
GO

/****** Object: Table [dbo].[Schools] Script Date: 1/29/2018 1:04:36 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Schools](
    [P_Id] [int] IDENTITY(1,1) NOT NULL,
    [Institution] [varchar](255) NULL,
    [Address1] [varchar](255) NULL,
    [City] [varchar](255) NULL,
    [State] [varchar](255) NULL,
    [Zip] [varchar](255) NULL,
    [country] [varchar](225) NULL,
    [Email] [varchar](255) NULL,
    [Department] [varchar](255) NULL,
    [Contact] [varchar](255) NULL,
    [Phd] [int] NULL,
    [Ma] [int] NULL,
    [Ba] [int] NULL,
    [Assoc] [int] NULL,
    [Cert] [int] NULL,
    [Field] [varchar](255) NULL,
    [CSHSE_Member] [int] NULL,
    [CSHSE_Accredited] [int] NULL,
    [Approved] [int] NULL,
    [Description] [varchar](max) NULL,
    [Website] [varchar](max) NULL,
    [firstname] [varchar](max) NULL,
    [lastname] [varchar](max) NULL,
    [Address2] [varchar](50) NULL,
    [Type] [varchar](12) NULL,
    [DeptChair] [varchar](50) NULL,
    [MiddleName] [varchar](15) NULL,
    [Title] [varchar](255) NULL,
    [DrMrMs] [varchar](4) NULL,
    [Notes] [nvarchar](max) NULL,
    [LastUpdated] [datetime] NULL,
    [TimeZone] [varchar](12) NULL,
    [SContactTitle] [varchar](50) NULL,
    [SContactPhone] [varchar](100) NULL,
    [ContactEmail] [varchar](50) NULL,
    [SiteID] [varchar](5) NOT NULL,
    [Alert] [varchar](50) NULL,
    [SContactName] [varchar](100) NULL,
    [ContractSent] [datetime] NULL,
    [Phone] [varchar](50) NULL,
    [Fax] [varchar](50) NULL,
    [UPSTransitTime] [varchar](50) NULL,
    [SchoolID] [int] NULL,
    [New] [int] NULL,
CONSTRAINT [PK__Schools__47E69B3D] PRIMARY KEY CLUSTERED
(
    [P_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Sue_H SSC Guru Points: 90860 More actions · Answer 4

briancampbellmcad - Monday, January 29, 2018 11:15 AM
Do you mean the DDL of dbo_schools like below? There are no explicit permissions set at the table level as far as I can tell.
USE [CPCE_DB]
GO
/****** Object: Table [dbo].[Schools] Script Date: 1/29/2018 1:04:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schools](
    [P_Id] [int] IDENTITY(1,1) NOT NULL,
    [Institution] [varchar](255) NULL,
    [Address1] [varchar](255) NULL,
    [City] [varchar](255) NULL,
    [State] [varchar](255) NULL,
    [Zip] [varchar](255) NULL,
    [country] [varchar](225) NULL,
    [Email] [varchar](255) NULL,
    [Department] [varchar](255) NULL,
    [Contact] [varchar](255) NULL,
    [Phd] [int] NULL,
    [Ma] [int] NULL,
    [Ba] [int] NULL,
    [Assoc] [int] NULL,
    [Cert] [int] NULL,
    [Field] [varchar](255) NULL,
    [CSHSE_Member] [int] NULL,
    [CSHSE_Accredited] [int] NULL,
    [Approved] [int] NULL,
    [Description] [varchar](max) NULL,
    [Website] [varchar](max) NULL,
    [firstname] [varchar](max) NULL,
    [lastname] [varchar](max) NULL,
    [Address2] [varchar](50) NULL,
    [Type] [varchar](12) NULL,
    [DeptChair] [varchar](50) NULL,
    [MiddleName] [varchar](15) NULL,
    [Title] [varchar](255) NULL,
    [DrMrMs] [varchar](4) NULL,
    [Notes] [nvarchar](max) NULL,
    [LastUpdated] [datetime] NULL,
    [TimeZone] [varchar](12) NULL,
    [SContactTitle] [varchar](50) NULL,
    [SContactPhone] [varchar](100) NULL,
    [ContactEmail] [varchar](50) NULL,
    [SiteID] [varchar](5) NOT NULL,
    [Alert] [varchar](50) NULL,
    [SContactName] [varchar](100) NULL,
    [ContractSent] [datetime] NULL,
    [Phone] [varchar](50) NULL,
    [Fax] [varchar](50) NULL,
    [UPSTransitTime] [varchar](50) NULL,
    [SchoolID] [int] NULL,
    [New] [int] NULL,
CONSTRAINT [PK__Schools__47E69B3D] PRIMARY KEY CLUSTERED
(
    [P_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I have no idea how Access front ends to SQL Server work anymore so this may not apply but the error is for dbo_schools. The table definition is dbo.schools.

Sue

RandomStream Hall of Fame Points: 3735 More actions · Answer 5

I meant the properties of dbo_schools in MS Access, not the definition of dbo.schools in MS SQL.

The error msg suggests it either cannot find the underlying table dbo.schools, or it does not have permission to select from that table. So I suspect that it is not pointing to the correct database. Since you did a 'restore' of database from 2005 to SQL 2016, chances are that the SQL 2016 logins have not been mapped to the users in each database. This is why I'd try focus on permission issue first.

RandomStream Hall of Fame Points: 3735 More actions · Answer 6

Sue_H - Monday, January 29, 2018 12:12 PM
briancampbellmcad - Monday, January 29, 2018 11:15 AM
Do you mean the DDL of dbo_schools like below? There are no explicit permissions set at the table level as far as I can tell.
USE [CPCE_DB]
GO
/****** Object: Table [dbo].[Schools] Script Date: 1/29/2018 1:04:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schools](
    [P_Id] [int] IDENTITY(1,1) NOT NULL,
    [Institution] [varchar](255) NULL,
    [Address1] [varchar](255) NULL,
    [City] [varchar](255) NULL,
    [State] [varchar](255) NULL,
    [Zip] [varchar](255) NULL,
    [country] [varchar](225) NULL,
    [Email] [varchar](255) NULL,
    [Department] [varchar](255) NULL,
    [Contact] [varchar](255) NULL,
    [Phd] [int] NULL,
    [Ma] [int] NULL,
    [Ba] [int] NULL,
    [Assoc] [int] NULL,
    [Cert] [int] NULL,
    [Field] [varchar](255) NULL,
    [CSHSE_Member] [int] NULL,
    [CSHSE_Accredited] [int] NULL,
    [Approved] [int] NULL,
    [Description] [varchar](max) NULL,
    [Website] [varchar](max) NULL,
    [firstname] [varchar](max) NULL,
    [lastname] [varchar](max) NULL,
    [Address2] [varchar](50) NULL,
    [Type] [varchar](12) NULL,
    [DeptChair] [varchar](50) NULL,
    [MiddleName] [varchar](15) NULL,
    [Title] [varchar](255) NULL,
    [DrMrMs] [varchar](4) NULL,
    [Notes] [nvarchar](max) NULL,
    [LastUpdated] [datetime] NULL,
    [TimeZone] [varchar](12) NULL,
    [SContactTitle] [varchar](50) NULL,
    [SContactPhone] [varchar](100) NULL,
    [ContactEmail] [varchar](50) NULL,
    [SiteID] [varchar](5) NOT NULL,
    [Alert] [varchar](50) NULL,
    [SContactName] [varchar](100) NULL,
    [ContractSent] [datetime] NULL,
    [Phone] [varchar](50) NULL,
    [Fax] [varchar](50) NULL,
    [UPSTransitTime] [varchar](50) NULL,
    [SchoolID] [int] NULL,
    [New] [int] NULL,
CONSTRAINT [PK__Schools__47E69B3D] PRIMARY KEY CLUSTERED
(
    [P_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have no idea how Access front ends to SQL Server work anymore so this may not apply but the error is for dbo_schools. The table definition is dbo.schools.
Sue

MS Access does not allow '.' in object name. So you can safely assume that the object in MS SQL is dbo.schools. I can't wait to see the end of MS Access. It's like driving a Ferrari with a side-car.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 7

RandomStream - Monday, January 29, 2018 12:21 PM
Sue_H - Monday, January 29, 2018 12:12 PM
briancampbellmcad - Monday, January 29, 2018 11:15 AM
Do you mean the DDL of dbo_schools like below? There are no explicit permissions set at the table level as far as I can tell.
USE [CPCE_DB]
GO
/****** Object: Table [dbo].[Schools] Script Date: 1/29/2018 1:04:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schools](
    [P_Id] [int] IDENTITY(1,1) NOT NULL,
    [Institution] [varchar](255) NULL,
    [Address1] [varchar](255) NULL,
    [City] [varchar](255) NULL,
    [State] [varchar](255) NULL,
    [Zip] [varchar](255) NULL,
    [country] [varchar](225) NULL,
    [Email] [varchar](255) NULL,
    [Department] [varchar](255) NULL,
    [Contact] [varchar](255) NULL,
    [Phd] [int] NULL,
    [Ma] [int] NULL,
    [Ba] [int] NULL,
    [Assoc] [int] NULL,
    [Cert] [int] NULL,
    [Field] [varchar](255) NULL,
    [CSHSE_Member] [int] NULL,
    [CSHSE_Accredited] [int] NULL,
    [Approved] [int] NULL,
    [Description] [varchar](max) NULL,
    [Website] [varchar](max) NULL,
    [firstname] [varchar](max) NULL,
    [lastname] [varchar](max) NULL,
    [Address2] [varchar](50) NULL,
    [Type] [varchar](12) NULL,
    [DeptChair] [varchar](50) NULL,
    [MiddleName] [varchar](15) NULL,
    [Title] [varchar](255) NULL,
    [DrMrMs] [varchar](4) NULL,
    [Notes] [nvarchar](max) NULL,
    [LastUpdated] [datetime] NULL,
    [TimeZone] [varchar](12) NULL,
    [SContactTitle] [varchar](50) NULL,
    [SContactPhone] [varchar](100) NULL,
    [ContactEmail] [varchar](50) NULL,
    [SiteID] [varchar](5) NOT NULL,
    [Alert] [varchar](50) NULL,
    [SContactName] [varchar](100) NULL,
    [ContractSent] [datetime] NULL,
    [Phone] [varchar](50) NULL,
    [Fax] [varchar](50) NULL,
    [UPSTransitTime] [varchar](50) NULL,
    [SchoolID] [int] NULL,
    [New] [int] NULL,
CONSTRAINT [PK__Schools__47E69B3D] PRIMARY KEY CLUSTERED
(
    [P_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have no idea how Access front ends to SQL Server work anymore so this may not apply but the error is for dbo_schools. The table definition is dbo.schools.
Sue
MS Access does not allow '.' in object name. So you can safely assume that the object in MS SQL is dbo.schools. I can't wait to see the end of MS Access. It's like driving a Ferrari with a side-car.

correct the error had '' just to highlight

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

RandomStream - Monday, January 29, 2018 12:21 PM

<snip>
I can't wait to see the end of MS Access. It's like driving a Ferrari with a side-car.

Horses for courses............."it all depends"

🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

RandomStream Hall of Fame Points: 3735 More actions · Answer 9

J Livingston SQL - Monday, January 29, 2018 1:10 PM
RandomStream - Monday, January 29, 2018 12:21 PM

<snip>
I can't wait to see the end of MS Access. It's like driving a Ferrari with a side-car.
Horses for courses............."it all depends"
🙂

Okay. It's like running a big race with a pony. LOL