Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CREATE new table in a new schema: SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Monday, March 28, 2011 10:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
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...
Post #1085065
Posted Monday, March 28, 2011 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 27, 2012 5:53 AM
Points: 63, Visits: 53
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.
Post #1085120
Posted Monday, March 28, 2011 1:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
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).
Post #1085168
Posted Monday, March 28, 2011 2:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 27, 2012 5:53 AM
Points: 63, Visits: 53
Are you creating the new schemas in the new database within the T-SQL script you wrote, or before you run the script?
Post #1085177
Posted Monday, March 28, 2011 2:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1085196
Posted Monday, March 28, 2011 2:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 27, 2012 5:53 AM
Points: 63, Visits: 53
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.
Post #1085199
Posted Tuesday, March 29, 2011 12:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
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.

Post #1085316
Posted Tuesday, March 29, 2011 6:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 27, 2012 5:53 AM
Points: 63, Visits: 53
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...
Post #1085466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse