GOING OUT OF MY FRIGGIN MIND!!!
-
I am befuddled at the moment. I am new to SQL 2005 and have the task of fixing a collation problem with a new install (2000 to 2005).
My plan is simple, I wanted to create a "Temp" db, and INSERT all records from the original DB to the temp DB.
easy enough right?
Well I got the table scripted out, with all the objects...created the database and all the tables easy enough.
I was going to do a simple INSERT INTO SELECT... statement to pull records out of the original to the temp db. However I am running into all sorts of issues. I am getting the follow errors...
Msg 1088, Level 16, State 11, Line 1
Cannot find the object "Temp_DB" because it does not exist or you do not have permissions.
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'Temp_DB.dbo.Ext19' can only be specified when a column list is used and IDENTITY_INSERT is ON.
For the second Error I found that you need to input the following code.
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.EXTRUSION_temp ON
GO
I have attempted to run the inserts as "SA", I have created a local account, gave it DBO rights to the new database...still getting the first error. I don't know what the heck I am doing wrong or if I am missing something SUPER obvious...
Any advice is appreciated.
leeland
-
Could you post the code you are using for your INSERT INTO? Also, where are you running it from and where is the original database?
-
OK I got it to work...but this is really going to stink if I have to do this for each table in the database
I was originally doing something like this
SET IDENTITY_INSERT EXTRUSION_temp ON <---where i had the DB listed vs. having the table listed.
GO
INSERT INTO EXTRUSION_temp.dbo.ext19 SELECT * FROM EXTRUSION.dbo.ext19
Which bombed out basically saying:
An explicit value for the identity column in table 'EXTRUSION_temp.dbo.ext19' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I guess what this is saying is since the table has an IDENTITY column I have to "EXPLICITLY" define the column_list for the table it is going into as well as in the SELECT statement for the fields coming out of the original table...
This code worked and I think I realized my error after I got it to work...
SET IDENTITY_INSERT EXTRUSION_temp.dbo.Ext19 ON
GO
INSERT EXTRUSION_temp.dbo.Ext19 (RecordNumber,
BlendDate,
BlendTime,
Line,
Blender,
Recipe,
JobNumber,
A_Set,
A_Act,
B_Set,
B_Act,
C_Set,
C_Act,
D_Set,
D_Act,
E_Set,
E_Act,
BatchWt,
Color_Set,
Color_Act,
A_Weight,
B_Weight,
C_Weight,
D_Weight,
E_Weight)
SELECT
RecordNumber,
BlendDate,
BlendTime,
Line,
Blender,
Recipe,
JobNumber,
A_Set,
A_Act,
B_Set,
B_Act,
C_Set,
C_Act,
D_Set,
D_Act,
E_Set,
E_Act,
BatchWt,
Color_Set,
Color_Act,
A_Weight,
B_Weight,
C_Weight,
D_Weight,
E_Weight
FROM EXTRUSION.dbo.Ext19
-
How many tables in the database?
-
It is actually two seperate databases I am working on...and the first one is only 8 tables (no biggy)...however the second database has like 100+ tables (WAY TOO MANY)...
So I am on to learning Integration Services and using the IMPORT Wizard which "appears" to be working so far...more trial and error than anything at the moment. I got the first database to go through just fine! Now I am working on the second.
The problem for me at the moment is I have had no formal training on 2005 but have a couple projects that have sprung up that require it...
how does the old saying go..."First to go...Last to know..."
Thanks for the inquries Lynn...I think I am on the right path...
Leeland
-
I could put together a script that will build your insert into commands and run them dynamically if you would like. I have built several scripts that dynamically build some tables, schema bound views and Insert, Update, and delete stored procs based on the stage tables from our source systems.
They work great, creating 180 tabes, 180 views, and 540 stored proces very quickly.
-
Try this code (I would comment out the exec statements first and see what the output of the print statements are first):
use DWODS /* probably want to set this to your source database, but if the table
structures are identical, may not matter */
go
set nocount on
declare @SQLCmd varchar(max),
@schemaid int,
@objectid int,
@loopcnt int,
@DestinationDB sysname,
@SourceDB sysname
set @DestinationDB = 'destdb'
set @SourceDB = 'sourcedb'
set @loopcnt = 0;
select
tab.schema_id,
tab.object_id
into
#InsertBuild
from
sys.tables tab
where
schema_name(tab.schema_id) in ('dbo')
order by
tab.schema_id,
object_name(tab.object_id);
select top 1 @schemaid = schema_id, @objectid = object_id from #InsertBuild;
while (select count(*) from #InsertBuild) > 0
begin
select @SQLCmd = 'SET IDENTITY_INSERT ' + @DestinationDB + '.dbo.' + tables.name + ' ON GO;' + char(13) + char(10) +
'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + '(' + char(13) + char(10)
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
select @SQLCmd = @SQLCmd + ' ' + columns.name + ', ' + char(13) + char(10) +
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
inner join sys.columns
on (tables.object_id = columns.object_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
order by
tables.name
,columns.column_id;
set @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + ')' + char(13) + char(10) +
'select' + char(13) + char(10)
select @SQLCmd = @SQLCmd + ' ' + columns.name + ',' + char(13) + char(10)
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
inner join sys.columns
on (tables.object_id = columns.object_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
order by
tables.name
,columns.column_id;
set @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + char(13) + char(10) + 'GO' + char(13) + char(10);
print @SQLCmd;
exec (@SQLCmd);
delete from #InsertBuild where schema_id = @schemaid and object_id = @objectid;
select top 1 @schemaid = schema_id, @objectid = object_id from #ViewBuild;
end
drop table #InsertBuild
set nocount off
-
Any reason why you can't do a backup/restore?
-
If the database was setup with the wrong collation, a backup and restore won't fix that.
-
I found a problem, my cut and paste was incomplete. Here is the full code:
set nocount on
declare @SQLCmd varchar(max),
@schemaid int,
@objectid int,
@loopcnt int,
@DestinationDB sysname,
@SourceDB sysname
set @DestinationDB = 'destdb'
set @SourceDB = 'sourcedb'
set @loopcnt = 0;
select
tab.schema_id,
tab.object_id
into
#InsertBuild
from
sys.tables tab
where
schema_name(tab.schema_id) in ('dbo')
order by
tab.schema_id,
object_name(tab.object_id);
select top 1 @schemaid = schema_id, @objectid = object_id from #InsertBuild;
while (select count(*) from #InsertBuild) > 0
begin
select @SQLCmd = 'SET IDENTITY_INSERT ' + @DestinationDB + '.dbo.' + tables.name + ' ON GO;' + char(13) + char(10) +
'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + '(' + char(13) + char(10)
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
select @SQLCmd = @SQLCmd + ' ' + columns.name + ', ' + char(13) + char(10)
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
inner join sys.columns
on (tables.object_id = columns.object_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
order by
tables.name
,columns.column_id;
set @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + ')' + char(13) + char(10) +
'select' + char(13) + char(10)
select @SQLCmd = @SQLCmd + ' ' + columns.name + ',' + char(13) + char(10)
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
inner join sys.columns
on (tables.object_id = columns.object_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
order by
tables.name
,columns.column_id;
set @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + char(13) + char(10)
select @SQLCmd = @SQLCmd + 'FROM ' + char(13) + char(10) + @SourceDB + '.dbo.' + tables.name
+ char(13) + char(10)+ 'GO'
from
sys.schemas
inner join sys.tables
on (schemas.schema_id = tables.schema_id)
where
schemas.schema_id = @schemaid
and tables.object_id = @objectid
print @SQLCmd;
-- exec (@SQLCmd);
delete from #InsertBuild where schema_id = @schemaid and object_id = @objectid;
select top 1 @schemaid = schema_id, @objectid = object_id from #InsertBuild;
end
drop table #InsertBuild
set nocount off
-
Lynn Beat me too it...from what I understand I can change the Collation on the DB...but it will not fix the current table collations...it will fix any "New" tables that are created.
-
Hey Lynn that script is very nice...where did you come up with that?
-
We are building a data warehouse. Part of it includes what I will call an ODS (operational data store) even if it doesn't really fit that definition. It will be storing all changes of data from the source systems. We currently have 180 tables in our stage database. I really didn't want to have to manually create 180 tables, adding 3 necessary columns, plus adding a primary key index, an alternate key index (the primary index from the stage table), and an index on the added date columns + the 180 schema bound views to show "current data" + the 3 stored procs for doing the insert, update, and delete operations on each table.
I came up with a template for each component, and then just built the rest using the system views to get the information I needed to create the necessary component.
I find this enjoyable.
-
Oh, and a few years ago I would have done it with nested cursors. learned some tricks at a couple of interviews that have paid off nicely!
-
oops missed the part about doing it to fix collation. NM me
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply