Upsize Questions

[jeffcullina]

I have read through a bunch of stuff in preparation for upsizing my db from Access to SQL Server 2000. I still have some questions though. The first page on the upsize wizard asks whether I want to create a new db or add to an existing db - I can handle that (I want a new one). The next page has me a little stumped. It asks for 1) the name of the server, 2) authentication, and 3) the name of the new database. I just barely have enough immagination to come up with a name for the new db, so I have #3 covered. My questions cover #1 and #2.

1) It appears as though I need SQL Server installed on the same machine that I am using to perform the conversion. Is that true? Otherwise, I do not have a name to use for the server. If I do not need SQL Server on that machine, how do I get around this entry.

2) I have just installed SQL on a machine (though it is not the one on which Access is installed - I may have to move SQL Server to the other machine as a result of #1 above) that does not require any permissions. I was not required to create a username and password when I set up SQL Server. So how do I complete the authentication entries. FYI: For the time being, I am working on a personal computer that is not connected to a network, so I do not have any required login accounts.

Thanks, Jeff

 

[LogicalmanUS]

Jeff,

Regarding the name of the server, this (usually) the netword address - computer name and instance name (for example, my instances are - PCXP500\SQL2K1 on this machine, and PCU00393 on my other development machine).

When create an installation of SQL2K, you may only be prompted for the sa (System Admin) password.
sa is one of three default logins (public and guest are the other two), and, as the name suggests, has ALL RIGHTS to the instance of the server.
Once you have created the instance, open up Enterprise Manager and add the server registration, login using sa account. You will find that the New Registration dialogue box will search for all instances of SQL Server on it's own network. If your instance is not present, then just type the name in. If you are unsure of the instance name, click Start-Programs-sql server-service manager, and you can copy and paste the name from there.
Once the instance is registered, expand the server, click Security - Logins, right click Logins and click Add Login
Enter the New Login Name and password, select a default data base and (this is important) click the databases tab and select the database by clicking the check box.
Once done, click OK, confirm the password and then the new login is created.
You now have an instance registered, and a login to use for your access project.

Logicalman

 

[jeffcullina]

Thanks. The help guides do not say anything about that. I have set up permissions. By the way, I am setting this up on a development version. I now have four accounts for this instance:

BUILTIN\Administrators Windows Group
CDI-MARINE\jcullina Windows User
jc Standard
sa Standard

And the default db for all of them is "master".

Now when I go to the Upsize Wizard, I do seem to get a connection (I was getting an error message before that said it could not find a connection). Now I get an error message that just says "Overflow". What does that mean?

Jeff

 

[LogicalmanUS]

jeffcullina,

Without knowing exactly at what stage the error is appearing, it's difficult to say.

One thing I will say though, that is I never use the upsizing wizard. It causes more problems than solutions when converting table data types.

Depending on the number of tables you have, I have always professed it is good practice to create your own tables in EM, not only can you be assured the schema is exactly how you want it, but it is also very good practice.

The same goes for views and Stored Procedures.

If you want to go that way I can, and will, assist you in every step.

Logicalman

 

[jeffcullina]

Logicalman,

I have seen your gripe about the upsize wizard before. I have heard from other programmers, though, that it will do a decent job as long as the tables are prepared (data types are set to those that will convert). I was at least going to give it a try and see what happens because my database consists of only related tables. But it looks like you are right, it is going to be more trouble than it is worth. I only have about twenty tables, so setting it up should not be too much trouble. I am going to have to figure out how to move the data though. I could use some advise on that.

As for building the db, I am expecting to be able to get through the macro-scale issues with the books I have. We'll see.

Jeff

 

[LogicalmanUS]

Jeff,

Yep, sorry if I have preached too much about the UW, and, as you rightly say, other programmers have successfully used it without many problems. You are doing the right thing not just listening to my gripes, but getting a wider point of view.

Regarding transferring the data, no great problem.
If you create the tables in the server to accept the data from the Jet tables, then do the following.

Make sure you have permissions on all the tables you create for one particular login.
Open Access, click File - Get external Data - Link tables.
Once the file dialogue box opens, select ODBC Databases ()from the file type. This will open a Data Source Dialogue box. If you have not already created one, this is where you create a DSN (Data Source name).
Click New - Select SQL Server - Click Next - Type in a name to use - Click Next - confirm the details as shown and click Finish.
You will now be presented with a server connection dialogue box. Enter a description for the data source, then type in the server (remember it will need to be the full name e.g. myServer/myInstance) - Select 'with SQL Server authentication' - enter the Login and password - click Next - Select 'Change the default database to..' and select your database from the list - Clcik Next - leave all defaults for this page - then click Finish. You will then be presented with a summary screen. Check all the data is correct, and click 'Test Data Source'. Hopefully you will receive the message 'Tests completed successfully' - Click OK - click OK, and you will be returned to the DSN Dialogue box.
Select the DSN you just made, click OK. You will then see another list box appear with a list of all the tables in the database. Select the ones you want, and select 'Save Password', then click OK.
Your tables will be linked, and the icon is an arrow pointing to a world. If you get a box appear asking you to select the ID field, then this simply means there is no Primary Key stated on the table schema you are connecting to, just select the PK for the table. Jet needs this to connect succesfully.
Once you have these tables linked, you can create simple queries of the INSERT INTO linkedtable (destination column list) SELECT (source column list) from localtable.
This will transfer the data for you, and your away.

Hope this assists you, if you have ANY problems at all, please ask.

Logicalman

 

[LeonAtRC]

jeff,

There is a problem with upsizing Access to SQL2000 that Microsoft knows about. Check this article and you may be able to get around the problem:

http://support.microsoft.com/default.aspx?kbid=272384

Having done many upsizings and built many SQL table in EM I completely agree with Logicalman. You are MUCH better off working in EM to build your databases and table. Then take a good look at DTS. For large amounts of data it can't be beat!

Good luck.
--Leon

 

[LogicalmanUS]

jeffcullina,

I admit, I totally forgot about using DTS for the importing, nice one Leon! Just goes to show it's easy to overlook an obvious tool.

Thanks for the reminder Leon,

Logicalman

 

[jeffcullina]

Thanks Logicalman and Leon. My schedule is all out of whack, so I did not get to read your messages until now. They are both very helpful. I am abonding the upsize wizard. It will only take a few hours for me to build all of the tables. And if I do it through EM, I will know exactly how the tables were built. Besides, it is a good opportunity for me to audit my tables. So I am going to set these things up and try to move the data. I am sure I will have more questions then - Like how to I push the database to the production server? I will deal with that when I get to it.

Jeff

 

[LogicalmanUS]

Jeff,

Thanks for the update.
One extra tip. Once you have built the tables, right click on one of them, select Generate Script, and then click preview. This will show you the exact TSQL statement for dropping and re-creating the table. It's very useful for seeing how it should be scripted for later use.
If you then use the Copy button, and paste the result in a new QA window, you can save this off, should you ever need to drop and re-create the table quickly at a later date.

BTW, this is a way to script the whole database and re-create it elsewhere also (just a tip in lieu of your next question)

Logicalman

 

[jeffcullina]

Logicalman,

That was indeed my next question. Creating all of the tables and relationships did not take very long at all. I just spent some time performing some searches on copying my database to create a test database. I learned that I could detach the db, copy the data files in windows explorer, and then reattach both the primary db and the test db.

I am struggling with DTS now. It is not clear to me how to use it. Most of the MS stuff is pretty intuitive, but it looks like I am going to have to put in some time to understand DTS. I am going to see what I can figure out, and then I will post some specific questions if I need to.

Jeff

 

[LogicalmanUS]

Jeff,

Quick and Dirty DTS is not hard. Again, you use the wizard to get you going with a template, of kinds.

Once you have created and populated a table on the server, re-create the table, empty, with another name on the same server.

Right click on the new table and select All Tasks - Import Data. The Import Data wizard will open. Click Next on the opening screen.

The first screen is the Source tab, enter or select the server, enter the details of the account to use (usually sa), and select the database. Click Next.
The next screen is the destination tab. Enter data as before and select the same database (it usually defaults all the same). Click Next.
The next screen is the object and data screen. Select 'Copy Table(s) and View(s) ..' and click Next.
Check the Source Table on the left side, and then select the destination table from the drop down next to it. Click Next.
Uncheck 'Run Immediately' and Check 'Save DTS Package' - Sql Server and click Next.
Give the package a Name, and leave all other items to default and click Next.
The summary screen will appear. Here you can check the basic items of what the DTS wizard will do. Click 'Finish' and it will save the package locally for you.
Once completed the 'Executing Package' screen will have the button 'Done' displayed. Click 'Done'.

The wizard will close and return you to EM.
Expand 'Data Transformation Services' and select 'Local Packages'.
On the right side you will see the package you just created. Right click it and select 'Design Package'.

The DTS Design window will appear with three items in it.
Connection 1, Connection 2, and a line in between.
Double Click Connection 1, and you will see the same information as when you selected the items for the Source. The same goes for Connection 2, for the destination.

The Item you are interested in is the line between. This is a Transform Data Task. (The DTS package is made up of a number of tasks, all of which use connections you create).
Double Click this task to open the dialogue box.
You will see 5 tabs. Source - Destination - Transformations - Lookups and Options. You need only care about the first 3 at this stage.
The Source tab shows you the Connection to use, and also the SQL statement to use to get the data.
The Destination tab shows you the Connection to use, the destination table and also what columns are selected to receive that data in the destination table.
The Transformations tab shows what columns are mapped to what columns.
You may see that all the lines appear to nerge together. If you want you can delete the current transformation, and create new, individual, transformations for each column. In this way it allows you to be exact on what you copy over, and how it is copied.
An example of this was I recently created a DTS Package to copy large amounts of data from a file into a table. I only wanted the first 100 characters of a particular field, so I created a transformations solely for that column, and specified that only the LEFT(sourcecolumn,1000) should be copied.
In this way I was able to control what went into the destination table.

One more thing for this package. Back in the design window, clcik Task - Execute SQL Task. In the SQL statement you can write TSQL statement directly in there. For example, you may wish to run a SProc following transferring the data over, so you would simply type 'EXEC usp_MySproc'.
Position the task object wherever you want in the design window, then click Connection 2, hold the Ctrl button down and select the new task you created, then click 'Workflow' - 'On Success'.
Now when the package runs, it will execute the data transformation, and then immediately kick off the SProc once the data has been transferred successfully.

To run this package from design view, simply click the 'Play' button (right pointing green triangle), or click Package - Execute.
You can also schedule this package to run at specific times/dates/periods, which utilizes the SQL Server Agent service, but that's for another time.

Hope this assists,

Logicalman

 

[jeffcullina]

I found the wizard and set up a DTS package. About half of the transfers failed. Is there an error log somewhere that tells me why it failed. I know I am going to have to prepare the data in the Access tables. I just need to know what is inconsistent.

Jeff

 

[LogicalmanUS]

Jeff,

Run the DTS again. You will see a progress window appear, with a line for each task. If the task fails, it turns red. Once the package finishes, and the error message box appears, click OK, and then double click the line having the error. An error box will appear informing you of what occurred.

Get the error message and post it on here, and I will do what I can to fathom it for you,

Logicalman

 

[jeffcullina]

I saw the window, but I closed it. I was trying to figure out how to save the error log. I did eventually figure it out. What was happening, is the wizard ordered the tasks alphabetically. That is, data is being copied into the database one table at a time according to the table name. Problem is, I already had relationships defined so data was being copied first to tables that depend on data in other tables (that has not yet been copied). I found that I could define the tasks to ignore the constraints and that took care of the problem. I had a few other inconsistencies, but I eventually figured them all out. I am ready to post the test db to the production server and begin some tests on it.

By the way. My interest in this project is to migrate a production Access db to SQL Server because the number of users is about to triple. My application (and front end) is a website, and I am only using the db to hold data. I am running all of my queries (which are mostly basic SELECT, INSERT and UPDATE queries) from the web pages. I think I have adequately figured out the process for pushing the db to SQL Server. Once I get my web pages modified to link to the new db and test the test db, I will freeze the website and do this all again - hopefully much faster.

 

[LogicalmanUS]

Jeff,

Data Tasks can be ordered using the workflow properties of DTS.
These can regulate the order at which tasks are performed, but the drawback is that the tasks are separate tasks, and must be coded as such.

Concerning the ASP/SQL project.
I have found that by using SProcs on the server side to complete all tasks for SELECT - INSERT - UPDATE - DELETE I was able to provide a buffer between the Application and the data, which provided a number of advantages, including:

1. Schema changes on the server did not affect the Applications at all, as the SProcs were coded to provide the same output, even after the data sources were altered.
2. When changes were required, all the testing could be done using the Query Ana

 

[LogicalmanUS]

Jeff,

Sorry about not completing my previous answer, here goes....


1. Schema changes on the server did not affect the Applications at all, as the SProcs were coded to provide the same output, even after the data sources were altered.
2. When changes were required, all the testing could be done using the Query Analyzer and therefore no downtime required to the application and more intuitive error messages (errors - surely not!!)
3. Many Applications could use the same SProcs and therefore introduces commonailty between Applications looking at the same data.
4. No database schems (tables, columns etc) were ever kept on ASPs.

Hope this assist you, and good luck on the application. Our department is responsible for writing a large number of Intranet Webs for our company use in different countries, and it is one of the most enjoyable and satisfying aspects of my work.

Logicalman

 

[jeffcullina]

Well to be honest, I am an engineer. While I have particular skill set in numerical modeling and simulation, I am not a programmer per se. I am definitely not a DBA. We sort of stumbled onto this project while supporting the Coast Guard, and I told them I could figure out how to create a web-based application for CG units to post warranty claims. I started it in Access (with a lot of help from Tek-Tips). Since the site has received a lot of positive feedback, it is being expanded to support a much larger number of boats (and more users - hence the move to SQL Server). It has been interesting at the least. And so far no major blunders. Your help is setting up this new DB is going to do a lot to keep it that way. Your help is invaluable and I really appreciate it.

Up next. Move the test db to the production server, change the connection string on my web pages, and sound the hell out of the pages.

 

[LogicalmanUS]

Jeff,

No problem. I was a copper in the UK for 15 years before I took up programming from being a hobby to a profession. It's all a knack.

If this is going to be a longer project, if you wish, we can take this out of the forum.

I can supply a contact email address if this would be easier.

Regarding changing the connect strings on the ASPs, if you are not already doing so, you can use a Global.asa file, or simply an 'include' file. Therefore you need not change the connection strings in each page, just in one location that all the pages have reference to.

Logicalman

 

[jeffcullina]

Logicalman,

Sorry to leave you hanging. I have several different things going on and I tend to bounce from on to the other.

I am using an include file, so all I have to do is determine the format of the connection string. I will find that in a search.

So what's a copper?

Jeff