 |
|
|
| Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
Hi,
I am getting the following error since I have installed the version 2.0.210.0. Do you know what I have to do to correct this?
SqlAssistSSMS2005, Version=2.0.210.0, Culture=neutral, PublicKeyToken=0e04414172b5aec1
SqlAssistException in SqlParser::UpdateElementsFromDatabaseThreadProc (System.Data.SqlClient.SqlException) Line 2: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
Line 17: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
Line 30: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
System.Data.SqlClient.SqlException: Line 2: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
Line 17: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
Line 30: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
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.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at SqlAssist.DSDatasets.DSDatabaseInfoTableAdapters.ParameterListTableAdapter.FillSql2000(ParameterListDataTable dataTable)
at b4.b() |
|
|
|
 | |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
What is the collation settings on your server and on that particular database? This is probably due to a collation setting that I've not accounted for in my DB parsing queries. I don't think there is anything you can do to work around the issue, but send me your settings and I can fix the bug...
~Steve |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
Here is the collation configured on the database: SQL_Latin1_General_CP1_CI_AS We are using this setting on multiple databases and I have problems only with one of those. One additional thing is that the other person using this database doesn't encounter any error using the same version sqlassist. In his case, he has a fresh install of SSMS 2005 and it is the first time he installed sql assist. In my case I have updated from version 1.... to 2.0.210.0. Is there any settings cached that may cause this problem? It was working ok when I was using the old version of sql assist. Let me know if you need more information |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
Wow, that's unusual to say the least. Off the top of my head, I'm at a loss. Let's try this... run the query below on that database from your machine inside SSMS (doesn't matter if SqlAssist is on or off). Let me know what you get in the output messages. If you get an error, run the same query from the other person's system on that same database. Maybe I can learn something from that...
/* stored procedure parameters*/
SELECT sp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ParentName, param.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ParameterName, baset.name + ' (' + CASE WHEN baset.name IN (N'char', N'varchar', N'binary', N'varbinary',
N'nchar', N'nvarchar') THEN ltrim(str(param.prec)) ELSE ltrim(str(param.length)) END + CASE WHEN baset.name IN (N'decimal')
THEN ', ' + ltrim(str(param.xprec)) ELSE '' END + ')' COLLATE SQL_Latin1_General_CP1_CI_AS AS DataType,
CAST(CASE param.isoutparam WHEN 1 THEN param.isoutparam WHEN 0 THEN CASE param.name WHEN '' THEN 1 ELSE 0 END END AS bit)
AS IsOutputParameter, 1 AS ParameterSourceType,
param.colid as 'ParameterID'
FROM sysobjects AS sp INNER JOIN
sysusers AS ssp ON ssp.uid = sp.uid INNER JOIN
syscolumns AS param ON param.number = 1 AND param.id = sp.id LEFT OUTER JOIN
systypes AS baset ON baset.xusertype = param.xtype AND baset.xusertype = baset.xtype
WHERE (sp.xtype = N'P' OR
sp.xtype = N'RF') AND (ssp.name = N'dbo')
UNION
SELECT udf.name AS ParentName, param.name AS ParameterName, baset.name + ' (' + CASE WHEN baset.name IN (N'char', N'varchar', N'binary',
N'varbinary', N'nchar', N'nvarchar') THEN ltrim(str(param.prec)) ELSE ltrim(str(param.length)) END + CASE WHEN baset.name IN (N'decimal')
THEN ', ' + ltrim(str(param.xprec)) ELSE '' END + ')' COLLATE SQL_Latin1_General_CP1_CI_AS AS DataType, 0 AS IsOutParameter,
2 AS ParameterSourceType,
param.colid as 'ParameterID'
FROM sysobjects AS udf INNER JOIN
sysusers AS sudf ON sudf.uid = udf.uid INNER JOIN
syscolumns AS param ON (param.number = 1 OR
param.number = 0 AND 1 = OBJECTPROPERTY(param.id, N'IsScalarFunction') AND ISNULL(param.name, '') <> '') AND
param.id = udf.id LEFT OUTER JOIN
systypes AS baset ON baset.xusertype = param.xtype AND baset.xusertype = baset.xtype
WHERE (udf.xtype IN ('TF', 'FN', 'IF')) AND (udf.name NOT LIKE N'#%%') AND (sudf.name = N'dbo')
UNION
SELECT sp.name AS ParentName, param.name AS ParameterName, baset.name + ' (' + CASE WHEN baset.name IN (N'char', N'varchar', N'binary', N'varbinary',
N'nchar', N'nvarchar') THEN ltrim(str(param.prec)) ELSE ltrim(str(param.length)) END + CASE WHEN baset.name IN (N'decimal')
THEN ', ' + ltrim(str(param.xprec)) ELSE '' END + ')' COLLATE SQL_Latin1_General_CP1_CI_AS AS DataType,
CAST(CASE param.isoutparam WHEN 1 THEN param.isoutparam WHEN 0 THEN CASE param.name WHEN '' THEN 1 ELSE 0 END END AS bit)
AS IsOutputParameter, 1 AS ParameterSourceType,
param.colid as 'ParameterID'
FROM master..sysobjects AS sp INNER JOIN
master..sysusers AS ssp ON ssp.uid = sp.uid INNER JOIN
master..syscolumns AS param ON param.number = 1 AND param.id = sp.id LEFT OUTER JOIN
master..systypes AS baset ON baset.xusertype = param.xtype AND baset.xusertype = baset.xtype
WHERE (sp.xtype = N'P' OR
sp.xtype = N'RF') AND (ssp.name = N'dbo') AND (DB_NAME(DB_ID()) <> 'master')
order by ParentName, ParameterID
|
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
OK, I got some usefull information now. Finally, my friend is using version 1.5 (that explain why it works on his computer since with this version it was working also on my computer). The query you sent me is not working on both computer for this database. I have tried the same query on a database that is working with SQL assist and the queries work OK. Another point is that all our servers are sql server 2000. Here is the error I get when I run your queries
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
I was able to get the collation name by executing the following so it seems to be present in the system
*
FROM
::fn_helpcollations()
where
name = 'SQL_Latin1_General_CP1_CI_AS'
SELECT |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
After some search, we found that the compatibility of the db has been set to sqlserver 7.0 for this particular database. Unfortunately, we can't change this setting as there is a third party database on this server that requires not to change anything on the database configuration. Is there something you can do to support old db format?
Thanks, |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
I've verified that the 7.0 compatibility is indeed the problem. I can repro the bug here as well. I'm looking into how much work it would be to support that mode. I had to write separate sets of DB parsing queries to support both Sql2005 and Sql2000. If I have to write a new set for 7.0, I'm sorry to say but I probably won't do that since (until now) I've had no requests from users to support that version.
If I find though that I can tweak the query that is crashing, I can add it in fairly quickly. Do you happen know what the statement is that I can use to ask the server what compatibility mode the current DB is running in?
~Steve |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
You can try one of the following. Not sure if either will meet your needs.
/* The following will return a message
"The current compatibility mode is <60,65,70,80,90>." */
DECLARE @currentDB NVARCHAR(128)
SET @currentDB = db_name()
EXEC sp_dbcmptlevel @currentDB
-- The following returns the cmptlevel direct from sysdatabases
SELECT cmptlevel FROM master.dbo.sysdatabases WHERE [name] = db_name() |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
yes, this looks like it will work nicely. I was able to fix the query that was crashing. I'm testing to see if others have trouble as well... Thanks for the cmptlevel query.
~Steve |
|
|
|
| |
|
|
| Re: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'. |
|
|
Thank you for looking into this Steve. FYI, we really enjoy sqlassist
Jonathan |
|
|
|
| |