Using SQL Express, Management Studio and Migration Assistant

eantar
eantar used Ask the Experts™
on
I'm having a hard time understanding MS SQL Express.

I'm an MS Access developer and I currently have many apps that connect to SQL server. But my understanding in setting up my own version is limited.

I've downloaded SQL Express 2017, which seems to be a 64 bit edition. But it seems that now I have several versions of SQL Express. Take a look at the Configuration Console:

SQL-Issues-01.jpg
As you can see, the only one running is SQLExpress01. But I don't know what version of SQL this is. Is there a way to tell? Also, is that the latest one?

it loads from "C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS01 which looks like MSSQL Version 14. Why isn't it 17?

The other ones are even older version like MSSQL11.

Using SQL Server Migration Assistant I was able to migrate an old mdb over to SQL, but I can't call up the design in the Management Studio. I get this message when I right click on a table and try and get the design:

SQL-Issues-02.jpg
Why don't I have the ability to update the tables through Management Studio?

Any help or resources to help me understand these issues would be greatly appreciated.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
these days you no longer need the Express version, you can download and use the full version of SSMS.

Download SQL Server Management Studio (SSMS)
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> As you can see, the only one running is SQLExpress01. But I don't know what version of SQL this is. Is there a way to tell? Also, is that the latest one?

Yes, if no one is using those 2 instances then it would be better to uninstall those 2 instances to avoid any confusions with the instances.

>> Using SQL Server Migration Assistant I was able to migrate an old mdb over to SQL, but I can't call up the design in the Management Studio. I

As explained by Ryan above, kindly use the latest version of SSMS with the link provided by him to get rid of that error message.

>> But I don't know what version of SQL this is

From SSMS, in new query window run this script and use the number received with the below link to understand SQL Server version and internal numbering
SELECT @@VERSION

Open in new window

https://support.microsoft.com/en-ie/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

>> which looks like MSSQL Version 14. Why isn't it 17

FYI, SQL Server internal number is 14 for SQL Server 17..
You can get the mapping of this numbering from the link I've shared above..
John TsioumprisSoftware & Systems Engineer

Commented:
It seems that you have 3 SQL servers installed...(that's quite a lot ) ...
Probably you have applications that push each time their installation of SQL Server...
Anyway as others mentioned using the most recent version of SSMS will resolve probably most of your problems.
You will be able to connect just fine and review your migrated Access tables....
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thank you John, Raja and Ryan. Thank you so much. So I've installed SSMS 18 and I'm suddenly able to use the designer. By my question is... Ryan you said that I don't need to use Express on my local machine. But aren't I still using SQL Express 17? My understanding is that the Management Studio is the only thing that has changed, correct?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Ryan you said that I don't need to use Express on my local machine

What he mentioned is that you don't need to end using the SSMS that comes with the Express installer(which is basic version of SSMS) and suggested to go with the latest version of SSMS(link provided above) which has all complete features of SSMS.

Author

Commented:
Ah. Thank you, Raja. You guys have been an excellent source of information.

Author

Commented:
Thank you all again. I appreciate the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial