4

When trying to create sp_BlitzIndex I got the error "Syntax error or access violation"

I narrowed this down to column alias names like:

SELECT
       ...,
       index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
       ...

Removing the nested square brackets solved this, but the "]]" quoting looks fine to me :(

I checked my version-control repository and the previous version also had the nested Square Brackets, so presumably it ran OK for me previously.

Not sure what has changed here that prevents this running now, but the sp_BlitzIndex scripts starts with a long list of SET options (they appear unchanged from previous version) so doesn't seem like it would be any of them

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Here's a trivial use-case which also fails in Query Analyser

SELECT 'xxx' AS [xxx xxx]] {xxx} xxx]
SELECT 'xxx' AS []]{}]

The double close square brackets AND the open and close curly-brackets are ALL required, if any single element of those is left out then QA works OK. As does changing the order - e.g. this works OK:

SELECT 'xxx' AS [{}]]]

My guess is that it's a bug in Query Analyser. The frustration is that the "Syntax error or access violation" error gives NO line number, so it is hard to find the cause (I located it by trying to execute the first half of the code block and then doing a binary-chop to narrow down the location of the culprit)

2

Ah ... found the answer. I was running this in an old version of Query Analyser (and got the error above), but it runs fine in SSMS, so error is (falsely) generated by a parsing error in Query Analyser.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.