"PK_dbo.TableName" is not a valid name "with a link to a linked Access ODBC table with SQL Server

Question

I am trying to associate an Access 2003 database with tables in a SQL Server 2008 database using ODBC. When I try to connect to tables with the Primary Key installed, the following error message appears.

"Pk_dbo.Batch_Claims" is not a valid name. Make sure that it does not contain invalid characters or punctuation marks and that it is not long. "

Pk_dbo.Batch_Claims is the key value that I see when viewing a table through SSMS. I used Access for a while, but I'm a little new to SQL Server and connections using ODBC. Any help would be appreciated.

thanks

Answer 1

You need to rename the primary key by opening the SQL Server (or Azure) database in SQL Server Management Studio. See the “Working with Invalid Primary Key Names” section of this blog post: Associating Microsoft Access 2010 tables with an Azure SQL Database

Answer 2

I created a stored procedure, for which you need work. first we must delete all foreign keys, otherwise we will not be able to abandon the key key restriction. Change that we fixate on the keys and restore them

Create procedure proc_changepk As --first drop all references declare @sql nvarchar(max) declare cursRef cursor for SELECT 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']' as ref FROM sys.foreign_keys open cursRef fetch next from cursRef into @sql while @@fetch_status = 0 begin exec(@sql) fetch next from cursRef into @sql end close cursRef deallocate cursRef --drop and recreate primairy keys declare @pktable table (constraintname nvarchar(255),tablename nvarchar(255),colname nvarchar(255)) insert into @pktable(constraintname,tablename,colname) SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 declare @pkname nvarchar(255),@tablename nvarchar(255),@cols nvarchar(255) declare Mycurs cursor for --maybe more than one col for primairy key SELECT p.constraintname,p.tablename, STUFF((SELECT '. ' + colname from @pktable where constraintname=p.constraintname FOR XML PATH('')), 1, 1, '') [cols] FROM @pktable p GROUP BY constraintname,tablename open mycurs fetch next from mycurs into @pkname,@tablename,@cols while @@fetch_status = 0 begin --drop key set @sql='alter table ' + @tablename + ' drop CONSTRAINT [' + @pkname + ']' print @sql exec(@sql) --create key set @sql='alter table ' + @tablename + ' add CONSTRAINT [pk_' + @tablename + '] primary key NONCLUSTERED (' + ltrim(@cols) + ')' print @sql exec(@sql) fetch next from mycurs into @pkname,@tablename,@cols end close MyCurs deallocate MyCurs GO