SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Server Express from Access and Upsizing Wizard


Using SQL Server Express from Access and Upsizing Wizard

Author
Message
notquitexena
notquitexena
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1005 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
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)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15739 Visits: 18768
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search