?
Solved

For each syntax with Sharepoint databases

Posted on 2014-01-19
3
Medium Priority
?
435 Views
Last Modified: 2014-01-20
There's incorrect syntax in the query below:

sp_MSforeachdb 'USE ?
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
AND ps.database_id NOT IN (71,11,25,51,76)
ORDER BY ps.avg_fragmentation_in_percent DESC, page_count DESC'



There errors:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_a96f6dbc'. No entry found with that name. Make sure that the name is entered correctly.

Note that:
'SharePoint_AdminContent_a96f6dbc is db_id = 11
0
Comment
Question by:barnesco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 400 total points
ID: 39793350
This rings a bell...

I think I had a similar issue with those big long sharpoint DB names.
To solve it I think I put some "[" and "]" around the questionmark.

So.....

sp_MSforeachdb 'USE [?]
0
 
LVL 35

Accepted Solution

by:
David Todd earned 1600 total points
ID: 39793393
Hi,

Try this:
execute master.dbo.sp_MSforeachdb '
USE [?]; 
SELECT 
	ps.database_id
	, ps.OBJECT_ID
	, ps.index_id
	, b.name
	, ps.avg_fragmentation_in_percent
	, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b 
	ON ps.OBJECT_ID = b.OBJECT_ID
	AND ps.index_id = b.index_id
WHERE 
	1 = 1
	--and ps.database_id = DB_ID()
	AND ps.database_id NOT IN (71,11,25,51,76)
ORDER BY 
	ps.avg_fragmentation_in_percent DESC
	, page_count DESC
'

Open in new window


What I did:
Put square brackets around the ? in the use,
Added a semicolon after the use statement
Added a dummy clause to the where
Commented out the ps.database_id clause in the where.
Formatted the code so it is easily read.

In my book there isn't any reason for dynamic code to be unreadable, just because its dynamic code.

Or in other words, because dynamic code has at least one extra layer of complexity, more effort should be put into its readability.

HTH
  David

PS Thanks to the guys above for the hint of the brackets, and that code did run on my SQL 2012 instance.
0
 

Author Closing Comment

by:barnesco
ID: 39794645
Thanks, I appreciate the help.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

765 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