March 30, 2011 at 8:06 am
I'm (slowly, very slowly...) converting my A2007 FE/BE to SS2005 BE, leaving the A2007 FE. It's much harder than I thought. 103 tables, about half a gig. Biggest table is 800,000 rows, most tables under 1,000 rows. Fully normalized. FE has 100 forms, 300 queries.
Anyway, what I'd like to do is dump the BE from A2007 everyday to the tables I've created on SS2005. I dabbled a bit with SSIS, but it's been difficult to create a package that properly copies the data. Basically, I want to delete all the data in the SS db, then copy up all the A2007 tables (which should work fine, since I created the tables using SSMA....)
1. I can't seem to clear all the tables before starting. I suppose I could just set up a stored proc to do this....
2. I don't know how to deal with the autonumber primary keys and the relationships. How does SSMA do it?
What's the strategy? Is there a resource I can read?
Thanks, guys, I'm really glad I found your site. I enjoy reading your blogs and posts. Unfortunately, it's a firehose for the moment, and I can't digest it all....
Jim
Jim
March 30, 2011 at 10:53 am
Sounds like a fun project...and that you're approaching it carefully and in your own time. That is a breath of fresh air. It sounds like SSMA has helped you get your Access schema mimicked in SQL Server. I would approach the task of "refreshing" the data in SQL Server by creating an SSIS package that does the following:
1. Use an Execute SQL Task to call a SQL Server stored procedure that does the following:
- drop all Foreign Keys *
- issue the series of TRUNCATE TABLE statements to remove all data from the SQL Server tables
2. Use a Data Flow task to copy all data from your Access tables into the related SQL Server tables. On the destination components in your Data Flow task make sure it is set to "Keep Identity" which will maintain your keys from your Access tables when copying into the SQL Server tables.
3. Use an Execute SQL Task to call a SQL Server stored procedure that does the following:
- Re-implement all Foreign Keys *
* The task of scripting the drop and add commands for all foreign keys in your SQL Server database can be achieved using the script in this article: http://www.sqlservercentral.com/scripts/T-SQL/61463/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 5, 2011 at 2:04 pm
Thank you so much....
OK, I finally got around to copying that script over to my client's development machine, and then over to my client's SQL Server machine (dedicated to my project....) and executed it. Before I go too far, one thing I've found is that SSMA seems to be creating constraints with outrageously long and complicated names (apparently including the path of the original ACCESS2007 backend db....)
Do you know how to control that in SSMA so I get kinder, shorter constraint names? I don't want to go in to this casting those huge names into the SS database.... I.E.:
ALTER TABLE [dbo].[tblPicklistMaster] WITH NOCHECK ADD CONSTRAINT [tblPicklistMaster$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblBoMtblPicklistMaster] FOREIGN KEY (PicklistBoMID) REFERENCES [dbo].[tblBoM] (BoMID) ON DELETE NO ACTION ON UPDATE NO ACTION
Jim
April 5, 2011 at 2:28 pm
I am not sure if that is configurable in SSMA but you can always rename then in bulk after the initial implementation.
Here is a script that deals with standardizing FK names.
http://www.sqlservercentral.com/Forums/FindPost873429.aspx
You can take a similar approach for standardizing index and other constraint names as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 5, 2011 at 2:46 pm
Wow, you responded to my first message before I could flounder my way into opening up VS2008 and starting up a "package". When I did that, I found your "Execute SQL Task", and dragged it into the package. It's looking for a SQL statement. I copied in the statement you suggested. It creates a couple of tables. How do I execute the "lines" of a table within that task?
Do you also have a reference for truncating all the tables? I assume I use a similar script. Can you get me started there?
I will catch on, however slowly. Thanks for your help!
Jim
April 5, 2011 at 4:27 pm
Given that you'll only be generating the schema once I was thinking you would just run the FK script in SSMS, copy the output to a new query window and execute it to standardize the FK names.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 5, 2011 at 5:19 pm
Oops, sorry. I figured that one out. I was asking about the script to remove and replace the FKs. It produces two tables, one after the other. Do I make it a SP and use its output as the script?
Jim
April 6, 2011 at 5:12 am
How about using an adp? I know this would be a problem if you have a bunch of vba to update to ado, but to me it seems easier and more stable than doing data dumps every day. I might be missing something though.
April 6, 2011 at 6:40 am
@nacarls: That's not a horrible idea, but I'm using A2007, have 103 tables, over 100 forms, over 100 queries, etc. I don't use unique A2007 data structures, but I do use A2007 form constructs that would be lost if I back-converted.
When I started this project, the use of SQL Server was not an option from my client. Now, the conversion is expensive, and I'm their only resource. I could outsource it, but where's the fun in that?
Jim
April 6, 2011 at 9:04 am
JimS-Indy (4/5/2011)
Oops, sorry. I figured that one out. I was asking about the script to remove and replace the FKs. It produces two tables, one after the other. Do I make it a SP and use its output as the script?
OK, I went for the wrong FK topic 🙂
For dropping the FKs, yes, I would use the output from the first table and turn that into a stored proc (step 1 from my earlier post). It will look something like this...abbreviated of course:
CREATE PROC dbo.migration_drop_foreign_keys
AS
BEGIN
ALTER TABLE [dbo].[table_name_1] DROP CONSTRAINT [FK1_name]
ALTER TABLE [dbo].[table_name_2] DROP CONSTRAINT [FK2_name]
ALTER TABLE [dbo].[table_name_3] DROP CONSTRAINT [FK3_name]
ALTER TABLE [dbo].[table_name_4] DROP CONSTRAINT [FK4_name]
END
For re-adding the FKs I would use the output from the second table and turn that into a second stored proc (step 3 from my earlier post).
CREATE PROC dbo.migration_add_foreign_keys
AS
BEGIN
ALTER TABLE [dbo].[table_name_1] WITH CHECK ADD CONSTRAINT [fk1_name] FOREIGN KEY (some_id) REFERENCES [dbo].[table_name_10] (some_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [dbo].[table_name_2] WITH CHECK ADD CONSTRAINT [fk2_name] FOREIGN KEY (some_id) REFERENCES [dbo].[table_name_11] (some_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [dbo].[table_name_3] WITH CHECK ADD CONSTRAINT [fk3_name] FOREIGN KEY (some_id) REFERENCES [dbo].[table_name_12] (some_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [dbo].[table_name_4] WITH CHECK ADD CONSTRAINT [fk4_name] FOREIGN KEY (some_id) REFERENCES [dbo].[table_name_13] (some_id) ON DELETE NO ACTION ON UPDATE NO ACTION
END
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2011 at 9:37 am
Sorry, this is my first foray into SQL generates SQL. I have the query that creates two temp tables. At the end of the query, it simply selects all the rows from those tables. How do I turn the output of those two selects into a "Create Proc" statement? That's my issue. The rest I think I understand (until the next thing pops up....)
Jim
April 6, 2011 at 9:56 am
You'll want to copy the text from the output window containing the DROP FK commands, then in a new query window, paste the content and make a proc out of it using the example I gave in my last post.
You may want to do some reading on T-SQL at this point just to get some fundamentals down: http://msdn.microsoft.com/en-us/library/ms190669.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2011 at 10:03 am
OK, I've been focusing on having it totally automated...taking the output from the FK query and executing those commands immediately, rather than cust/paste. Lemme rescope....
Thanks.
Jim
April 6, 2011 at 10:11 am
Just concatenate the code in varchar(max) var and exec that... fully automated.
April 6, 2011 at 10:36 am
Just tried that. Exceeds nvarchar(max) limit....
Probly need to execute one at a time.
On second thought. It simply didn't print the whole command. Maybe it's a limitation of the print command....?
Jim
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply