CREATE new table in a new schema: SQL Server 2008 R2

Question

CREATE new table in a new schema: SQL Server 2008 R2

SQL_ME_RICH

SSChampion

Points: 11200
More actions
March 28, 2011 at 10:54 am

#235772

Greetings -
I am currently converting 2 database over into SQL Server 2008 R2. One was in 2000 compatibility mode (80) and the other in 2005 compatibility mode (90).
The method I decided to go with was via T-SQL, creating a temp table by selecting the rows from the given table I would target for copying the data from. I then create the new table in the new version of the db for 2008 compatibility (100), and then INSERT INTO that new table from the temptable that has the old data.
This method has worked fine, but I've run into a snag now where I have tables in different schemas (other than dbo). I've done the following:
- Successfully created one table in the new schema by running a CREATE SCHEMA line, prior to the inserting of that data.
- Verified that I have dbo owner and public to the new databases that are receiving the old form data from the (80) and (90) databases as well as the login I am currently .
in with (local admin).
The problem however is that the other schemas are not being recognized when their turn in my conversion process comes up. I get errors like the following:
(0 row(s) affected)
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ImportLogID'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ProcessRunDate'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'EventID'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NewRecord'.
Any insight into what I should check into to continue doing my process with T-SQL would be appreicated. I realize that there are probably better ways to achieve what I have been doing (e.g. SSIS), but this is the only method of know of at the moment.
Thank you in advance...

Viewing 8 posts - 1 through 7 (of 7 total)

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

pdonley SSCommitted Points: 1597 More actions · Answer 1

Hi there,

I have limited experience, but I do know one thing that might help. SQL 2000 did not have schemas, but rather owners, so a fully qualified table name would be something like database.owner.tablename, wheras with SQL Server 2005, they introduced schemas, so now a fully qualified table name would be something like database.schema.tablename.

Owners in SQL Server 2000 corresponded to user names. Schemas in SQL Server 2005 and up correspond to LISTS of user names. Maybe what you need to do is make sure that you have users in your new database with the same names as the "owners" of the tables, and then grant those users rights to the new schema. Either that, or create schemas in your new database with the same name as users in your old database. Maybe that'll at least get you started. My gut feeling is that the fully qualified table name is getting messed up the T-SQL. What used to be database.owner.tablename is now database.schema.tablename, and schema != owner. Maybe you can trick it by creating schemas with the same name as the old owners.

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 2

Thank you very much for this important point! However - this database that I am getting the data from, and creating the table names and schema names from, is the 2005 version of the 2 db's that I am converting (as it has the Schemas that I am attempt to name in the 2008 R2 environment).

pdonley SSCommitted Points: 1597 More actions · Answer 3

Are you creating the new schemas in the new database within the T-SQL script you wrote, or before you run the script?

Lowell SSC Guru Points: 323504 More actions · Answer 4

could it be this simple?

i didn't see enough details to directly emoulate the error, so as i was trying something to recreate teh issue, i got this;

similar to the behavior of ALTER TABLE ADD COLUMN X, where you cannot use the new column until a GO command has occurred, I think CREATE SCHEMA [schemaName] requires a GO command before you can do your INSERT INTO.

'CREATE SCHEMA' must be the first statement in a query batch.

here's a simple code example that shows it:

CREATE TABLE dbo.[results] (

[RESULTSID] INT IDENTITY(1,1) NOT NULL,

[RESULTText] VARCHAR(1024) NULL)

insert into results

select 'stuff' union all select 'more stuff'

create schema 'bob'

select *

into bob.results

from dbo.[results]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pdonley SSCommitted Points: 1597 More actions · Answer 5

You just said what I was starting to get at in my own head... 🙂 Maybe the creation of a new schema can't happen within the same block of code as adding tables and data to that same schema.

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 6

BAH! Per usual...Typical newbie nonsense. My Windows Authenticated account was not the sysadmin I thought it was. I logged in with sa and had no issues.

😛

pdonley SSCommitted Points: 1597 More actions · Answer 7

Glad you got it figured out! That kinda stuff can happen to seasoned professionals, too. I'm a newbie at SQL, but I'm much more experience with C#, and I once spent an entire day trying to find a missing semicolon...