Using SQL Server Express from Access and Upsizing Wizard

• notquitexena

  SSCrazy

  Points: 2674

  More actions

  May 28, 2008 at 11:24 am

  #207821

  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

  SQL Server (SQLEXPRESS)

  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

  Aliases

  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.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 28, 2008 at 11:29 am

  #821350

  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?