Solved

Get Properties on MSSQL2000 database using SQL Server Management Studio results in "(Microsoft SQL Server, Error: 170)"

Posted on 2007-03-28
9
309 Views
Last Modified: 2009-07-29
Using SQL Server Management Studio, and right-clicking any of about 20 databases residing on a SQL Server 2000 instance and selecting 'Properties' results in this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Line 14: Incorrect syntax near '1'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'. (Microsoft SQL Server, Error: 170)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476



A Google search on "Microsoft SQL Server, Error: 170" brings up exactly 1 hit, seemingly unrelated.  There is absolutely nothing wrong with the MSSQL2000 server databases; a number of other developers here are using Management Studio and/or Enterprise Manager just fine, and the databases are supporting many applications just fine.  I can "Get Properties" on a different MSSQL2005 server just fine.  So there is something about my desktop implementation that is problematic.  I can use TOAD's SQL Server freeware tool on the MSSQL2000 server just fine.  


"Show Technical Details" displays this:

==================================

Cannot show requested dialog.

===================================

Cannot show requested dialog. (SqlMgmt)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)
   at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
   at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
   at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
   at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
   at Microsoft.SqlServer.Management.Smo.Environment.GetData()
   at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
   at Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.DatabaseData..ctor(CDataContainer context, String databaseName)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.LoadDefinition(String newName)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype..ctor(CDataContainer context)
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer context)

===================================

Line 14: Incorrect syntax near '1'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476

------------------------------
Server Name: (OURSERVERNAME)
Error Number: 170
Severity: 15
State: 1
Line Number: 14
------------------------------
Program Location:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

0
Comment
Question by:MichaelRLevy
  • 4
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18811138
what service pack is that sql server?
0
 

Author Comment

by:MichaelRLevy
ID: 18811639
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

returns:

8.00.760      SP3      Standard Edition
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18811697
can you check which compatibility mode those databases are running under?

anyhow, did you consider reinstalling your sql management desktop?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:MichaelRLevy
ID: 18811843
There are about 40 databases on that MS SQL 2000 server.  Two are in 6.5 compatibility mode.  The rest are on 2000 compatibility mode.  (Of course I could not determine that using Management Studio on my desktop because it doesn't work!  But I can hop to my neighbor's desktop and use her Management Studio, and I can use Toad on mine to determine that.  Plus, I just remember from when I used to use EM.)

I completely uninstalled all SQL Server 2005 components on my desktop including management server, rebooted, and reinstalled all of them.  Twice.  No help.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18814796
I think you have only 2 more ways:
* post in the microsoft forums
* open a case with microsoft directly
0
 

Accepted Solution

by:
MichaelRLevy earned 0 total points
ID: 18814954
Angel, thanks for your attention!  I appreciate the effort.

It sure seems like a weird problem, especially since Googling "Microsoft SQL Server, Error: 170" (with the quotes) results in exactly one hit, seemingly unrelated.  I've never before seen a Microsoft error message that didn't result in quite a few Google hits.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18822424
no objections
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question