SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SQL_ME_RICH
SQL_ME_RICH
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1306 Visits: 1596
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...
pdonley
pdonley
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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.
SQL_ME_RICH
SQL_ME_RICH
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1306 Visits: 1596
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
pdonley
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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?
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37127 Visits: 40280
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
pdonley
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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.
SQL_ME_RICH
SQL_ME_RICH
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1306 Visits: 1596
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.

:-P
pdonley
pdonley
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search