Sp_msforeachdb syntax error

Question

Post reply

Sp_msforeachdb syntax error

TheGlossBoss

SSC Veteran

Points: 244
More actions
April 4, 2013 at 6:53 am

#291773

Good day all,
I am having a small issue with a line of code for a column which is being used in a sp_msforeachdb in a SSIS package. I cannot get it run with out throwing a syntax error. It works if I run it from MS raw however within Visual Studio it bombs. Thanks in advance.
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

looks like an incomplete paste from your actual source? can you edit and put the whole command that is failing?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheGlossBoss SSC Veteran Points: 244 More actions · Answer 2

TheGlossBoss

SSC Veteran

Points: 244

April 4, 2013 at 7:52 am

#1603563

Server Hangup trying to respond.

TheGlossBoss SSC Veteran Points: 244 More actions · Answer 3

Insert into [tempdb].[dbo].[RESULT_STRING_INDEX_MISSING]

Exec master..sp_MSforeachdb 'Use [?] SELECT ''?'' as DBName,

'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

+ ' ON ' + mid.statement

+ ' (' + ISNULL (mid.equality_columns,'')

+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

+ ISNULL (mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC';

--=============Alter Table temp

Alter TABLE [tempdb].[dbo].[RESULT_STRING_INDEX_MISSING]

Add ServerName nvarchar(128) NULL ;

--============Update temp

Update [tempdb].[dbo].[RESULT_STRING_INDEX_MISSING]

Set Servername = CONVERT(nvarchar(128), SERVERPROPERTY('Servername'))

--=============== Select all from Temp

Select *

from [tempdb].[dbo].[RESULT_STRING_INDEX_MISSING]

SET NoCount OFF

--=============== Error

Msg 103, Level 15, State 4, Line 35

The identifier that starts with ''missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

+ '_' + LEFT (PARSE' is too long. Maximum length is 128.

Msg 102, Level 15, State 1, Line 36

Incorrect syntax near '

+ '.