asked on

SQL Express

I have an application that was designed using Access, but I needed to migrate the data to SQL 2005, and in this instance Express version. There are a number of hierarchical structure that need to be dealt with and CTE's seem to be the best option. After a truly exhausting experience finding a way to move the tables to express. I need to modify the database connection string to attach to the sql database in VB6.

I have visited about 25-30 web search sites to get the syntax correct and so far no luck.
The database location by default is ridiculously deep in the tree.

1. How do I change the location of the database structures?

2. I need to create using vb6 deployment wizard the ability to install the vb app and the databases and the redisributable version of express.

I would like to build the final structure on my desktop and have the install duplicate for my client.

Can anyone outline the process, and most importantly can anyone give me a connection string that works. For

dwkor

I can try to answer some of the questions.

0. Speaking of connection string - check http://www.connectionstrings.com/sql-server-2005. When you run Sql Express on the same machine, you can use trusted connection syntax. I have not worked with Express for a while but as far as I remember it uses named instance SQLExpress. So Server name/data source would be .\SQLEXPRESS -> Data Source=.\SQLEXPRESS;Initial Catalog=myDataBase;Integrated Security=SSPI;

If you need to connect to the server from remote pc - you can use "Standard security" connection string if you decided to use sql server authentication or again, trusted connection with windows authentication. Don't forget that SQL Server has network protocols disabled by default (you can enable them in configuration manager) and you also need to add exception to the firewall when you connected remotely.

1. Location of database structures - well, are you talking about the database location? You can place the database to the any place on your hard drives (again, we're not talking about best practices here).

2. Deployment and installation is the biggest question. Basically to deploy the database you have 3 options:
a. Deploy the database files and attach them to the instance
b. Deploy database backup and restore it on the box
c. Have database creation sql script as part of your installation, create the database on the server and run the script.

Each of those methods have own pros and cons and also depend on amount of data you need to have in the database after creation. Also you need to keep in mind possible implications of the deployments if you plan to install database to existing server in the corporations. DBA could be quite picky about the method :)

Unfortunately I don't have enough experience with the tools that build installation scripts and cannot help you here.

Last, but not least, why have you chosen 2005 express instead of 2008 r2 one? Even if we keep all other arguments out of scope, 2008R2 express allows you to have db up to 10Gb while 2005 limits you to 4Gb.

Larry Rungren

ASKER

This has to be installed on the PC partition on a MAC and I don't see any compatibility listing for 2008.

Have you ever installed on a MAC?

ASKER CERTIFIED SOLUTION

dwkor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Rungren

ASKER

Ok Thanks

Larry Rungren

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for Jeff_Kingston's comment http:/Q_27431389.html#37093730

for the following reason:

Rapid response.. solved issue

Larry Rungren

ASKER

I clicked the wrong message

dwkor

Wrong button clicked..

Larry Rungren

ASKER

Excellent!