Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Using SQL Server Express from Access and Upsizing Wizard Expand / Collapse
Posted Wednesday, May 28, 2008 11:24 AM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 2, 2009 8:46 AM
Points: 875, Visits: 313
I have an existing Access data base with a lot of legacy code, and I recently posted asking for a "simple" way to move to SQL Server. I was advised about the Access Upsizing Wizard which sounded terrific. However when I tried to use it, I ran into a connection error. I would appreciate your help -- it is probably some step I missed. The following describes what I have observed:

I downloaded SQL Server Express, then took one of the data bases (XFN) and trimmed out most of the records to use the data base as a test case, moving the lobotomized data base to another location. I then opened the data base using Access and selected Tools -> Database Utilities -> Upsizing Wizard.

When the Upsizing Wizard started I selected to Create a new database, use (local) SQL Server, and Use Trusted Connection, with default name for the new database (XFNSQL).

The Wizard then took a few seconds, followed by a popup with the error message:

Connection Failed:
SQL State: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory] Connection Open (Connect())
Connection Failed:
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory] SQL Server does not exist or access denied.

I suspected that there was some step in the download process that I missed, so I verified that the download had put Microsoft SQL Server 2005 in my Programs list. When I checked that program, I see that it has Configuration Tools as the only subset. Within Configuration Tools there are three items:
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Surface Area Configuration.

When I select to start SQL Server Configuration Manager I get a window with two panes. The left pane has SQL Server Configuration Manager (local) on top with subcategories: SQL Server 2005 Services
SQL Server 2005 Network Configuration
SQL Native Client Configuration.

If SQL Server 2005 Services is highlighted the right pane shows
with State= Running
Start Mode=Automatic
Log On as=NT Authority
Process ID with Service Type=SQL Server;

also SQL Server Browser
with State=Running
Start Mode= Automatic
Log On as=NT Authority
Process ID with Service Type=SQL Server Browser.

SQL Server 2005 Network Configuration has subcategory Protocols for SQLEXPRESS. When this is highlighted the right panel shows
Shared Memory Enabled
Named Pipes Disabled
TCP/IP Disabled
VIA Disabled

SQL Native Client Configuration has subcategories
Client Protocols

When Client Protocols is highlighted the right panel shows
Shared Memory -> Order=1, Enabled
TCP/IP -> Order=2, Enabled
Named Pipes -> Order=3, Enabled
VIA Disabled

There are no Aliases.
Post #507878
Posted Wednesday, May 28, 2008 11:29 AM



Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 7,504, Visits: 17,943
You will likely want to enable TCP/IP and/or named pipes. That's done from within the Surface Area Configuration tool.

I would personally tell the client NOT to use Shared Memory - you should get a better response that way.

Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #507885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse