Lỗi index was outside the bounds of the array microsoft.sqlserver.smo năm 2024

Argent for SQL Server could not retrieve the version of Microsoft SQL Server 2012 because of an exception thrown by the SQL Server Management Object [SMO] component

Technical Background

Argent for SQL Server uses SQL Server Management Object [SMO] for connecting and retrieving Microsoft SQL Server information

Before executing a Rule, Argent checks the version and edition of Microsoft SQL Server to make sure that the version of SQL Server is compatible to the Rule to be executed

Unfortunately one Microsoft product fails to check for the presence of another Microsoft product and SMO blithely assumes that the SQL Server Management Studio is installed

When connecting to the Microsoft SQL Server 2012 machine, the SMO fails if the installed SMO version is wrong

In this case, SMO raises an exception ‘Index was outside the bounds of the array’ and hence Argent is not able to collect the required information

Argent recommends SMO version 10.5.6000.34 [included in Microsoft SQL Server 2008 R2 SP3 Feature Pack] for best results

I think I've had this error when connecting to a SQL 2012 instance using the wrong version of SSMS. Are you using the 2012 SSMS version to connect?

---------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall. Howard Zinn

VirusDetected

SSC-Addicted

Points: 426

I am using 2008 and I see that SSMS 2008 has limited options. Is there any way to bypass because I installed backward compatibility tool.

patricklambin

SSCrazy Eights

Points: 9994

Please , could you tell us the exact version [ last SP + CU installed ] on your both SQL Server 2012 [ 1st post ] and 2008 [ used SSMS in your last post ] ?

It is always possible to use a SSMS 2012 to open a SQL Server 2008 or SQL Server 2008 R2 [ if you are looking to the facets of an object like database ,view , table , you could get the value "not supported in this version" or a similar message ].

If you try to connect to a SQL Server 2012 thru a SSMS 2008/2008 R2 , Microsoft does not give any warranty that the connection will succeed. I have got even an error caused by an unknown value in an enumeration [ this value appeared in 2012 and was not existing in 2008 ] .

Example :

the value Version110 [ corresponding to SQL Server 2012 ] is not supported in a SQL Server 2008 SSMS [ not defined in the SMO namespace ].

Same problem with Visual Studio which is unable to load the project created with a newer version.

I think that you should replace your SSMS 2008 by the SSMS Express 2012 SP1 which is the full version supporting Entreprise Edition

Database diagrams can be very useful in terms of visually understanding the SQL Server database structure. It is possible to create a diagram for the whole database as well as for specific tables of the database. This illustration can be very helpful in easily understanding the table’s structure of the database and their relationships.

Creating a diagram is possible in SQL Server Management Studio [SSMS] prior to version 17. In the case of creating a diagram in version 17 of SSMS the following error message is received:

Index was outside the bounds of the array.

This is because in version 17, in spite of having the "Database Diagram" option under each database, it was actually removed and Microsoft considered it as a depreciated feature. This feature, however, is very useful for many developers and administrators and its absence can be quite uncomfortable. While it is possible to use many third-party tools to create database diagrams, we probably all agree that it is easier to have this feature integrated into SSMS.

Using older versions of SSMS will also solve the problem. Nevertheless, what if we want to use newer versions of SSMS with the "Database Diagram" feature?

Solution

Fortunately, despite expectations, the "Database Diagrams" feature is available with SSMS 18.1 and later. Thus, if we are using SSMS 17 thru 18.0, we can just upgrade to 18.1 and newer versions and have this feature available again.

We will illustrate this process step-by-step, but before that let’s see the problem in practice.

For instance, assume we need to create a database diagram for our sample TestDB database. In SSMS, we locate "Database Diagrams" just under the database, right-click on it and choose "New Database Diagram":

After that, we will receive the following error message:

Now, let’s check the version of SSMS.

To do that, go to Help > About:

The version is 17.8.1:

Update SSMS to Version 18.1 or later to solve database diagram error message

Let’s update SSMS.

To do so, close SSMS and follow this link to download the latest version.

At the time of writing this tip, the latest version of SSMS is 18.4 as shown below:

Click the download link and after downloading, locate the EXE file and double-click on it to install:

After that, click on "Install" to start the installation [you can change the location by clicking "Change" and choosing the desired location]:

After installation, a restart is needed, so click "Restart":

When the computer is restarted, we can search for the new version of SQL Server Management Studio by typing ssms:

As we can see above, version 18 is installed, but the version 17 is still available. Hence, installing the version 18 does not override version 18. If we do not need the older version, we can just uninstall it.

Let’s start version 18 by clicking on it. As we are using it for the first time, we are asked whether to import settings from SSMS 17 or not. We will click "Import from SSMS 17" to import:

We can see that the SSMS version is 18.4:

Create Database Diagram

If in this new version of SSMS we click on "New Database Diagram", a window for creating a new diagram will be successfully opened. We can choose tables which will be added into the diagram [we have chosen both tables in our database] by clicking "Add":

Now we can see our tables structures and their references in our diagram:

We choose a name for the diagram and click the "Save" button. Now, it is stored permanently.

The diagram can be found under "Database Diagrams":

Conclusion

In conclusion, although creating a database diagram is a quite useful feature among many developers and DBAs, it is unavailable in the version 17 of SSMS. In newer versions of SSMS, however, this feature is available again. Thus, we have fixed the problem of creating a database diagram in SSMS 17 by upgrading it to SSMS 18.4.

Next Steps

To find more information about the discussed topic, please follow the links below:

• //docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
• //docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms?view=sql-server-ver15

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, February 17, 2024 - 6:13:27 AM - Dimitris [91985] The same problem exists in version 19.x of MSSMS. The only solution is to close and re-open MSSMS and go straight to creating a new diagram before doing anything else. Wednesday, May 10, 2023 - 8:26:36 PM - devCERO [91180] Does not work for me. My SSMS is 19.0.2. Friday, October 14, 2022 - 10:04:24 AM - Courtney [90601] I'm on SSMS 18.2 and I'm still getting this error. I made the diagram and every once in a while I update it by adding new tables to the structure. I've gotten this error before, but I can usually save the diagram, close out and reopen and then it works. Now I just keep getting the error. Anything else I can try? Thursday, November 4, 2021 - 5:39:24 AM - awsan [89408] Close the sql server is one of the solutions for this problem. Friday, October 29, 2021 - 2:42:07 PM - Todd [89386] I was still experiencing this problem in 18.10. I was operating under sa. I tried restarting the server. The problem was still occurring. I finally closed SSMS and restarted it. The problem went away. Evidently the user interface gets confused. Hopefully this resolution will help someone else. Thursday, August 26, 2021 - 2:54:44 PM - Gabrie [89174] Restart the sql server is one of the solutions for this problem. Friday, May 28, 2021 - 10:38:34 AM - Sergey Gigoyan [88760] Matt, did you see this in the article? "Fortunately, despite expectations, the "Database Diagrams" feature is available with SSMS 18.1 and later. Thus, if we are using SSMS 17 thru 18.0, we can just upgrade to 18.1 and newer versions and have this feature available again." Can you really do it in SSMS 18.0, for example? Thursday, May 27, 2021 - 11:34:42 AM - Matt [88754] There is no indication this is a deprecated feature.

This error comes up if you are not using "sa".

For instance, I was getting this error even with my domain admin account which was also in the sysadmin role in SQL Server.

After connecting using "sa" things worked just fine in the very latest version of SSMS.

The whole premise of this article is incorrect.

Friday, April 2, 2021 - 10:45:58 AM - Carl Prothman [88485] In order to create SQL Database Diagrams, you must use "SA" as owner of the database. Right click on the database, and select Properties. Then click on Files. Then enter "sa" as owner. And then click OK. Tuesday, February 23, 2021 - 6:21:49 PM - Frank [88285] As Bill Edgbert wrote, running it as Administrator fixed the issue, but seems a mistake by Microsoft on listing tables with user DB rights... Thursday, January 28, 2021 - 6:11:05 AM - Dom [88115] SSMS v18.8 Same issue Also works when running SSMS as administrator as pointed out Bill Edgbert Sunday, January 17, 2021 - 9:21:31 AM - Bill Edgbert [88058] Run SSMS as administrator and the database diagram will work. Tuesday, November 24, 2020 - 5:45:50 PM - Juan [87838] Still exists in 18.7.1 if anyone's wondering, i can open any diagramas and edit them but i can't make new ones. Thursday, August 13, 2020 - 4:13:09 AM - Piquet [86300] It is quite unbelievable that Microsoft would even consider deprecating such a valuable & useful feature in SSMS - astounding! I am using SSMS v18.6 and this "Index was outside the bounds of the array" error still occurs when I try to create a New Database Diagram :-[. - I've experienced this at multiple sites on multiple workstations [SQL 2019]. Interestingly I can open and modify EXISTING database diagrams without any problems - I just get errors if I try to create a NEW diagram... Most annoying... Monday, May 25, 2020 - 6:07:43 AM - ArnoNym [85760]

The issue still exists in SSMS 18.4. Any additional steps to help resolve it?

Friday, March 13, 2020 - 9:16:23 AM - Kevin [85103]

Does this also apply to the Query Designer? I have a user with a background in Access that does all of his queries in the designer out of habit.