Using SQL Express, Management Studio and Migration Assistant
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:
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.SQLEXPRESS0
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:
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.
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:
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.SQLEXPRESS0
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:
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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....
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....
ASKER
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?
>> 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.
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.
ASKER
Ah. Thank you, Raja. You guys have been an excellent source of information.
ASKER
Thank you all again. I appreciate the help.
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
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..