September 23, 2011 at 9:29 am
I am trying to gather fragmentation information from my databases and I'm using the following query
USE ABPLP
SELECT GetDate() as DateChecked, 'ABPLP', OBJECT_NAME(G.object_id) as TableName, I.name as IndexName,I.index_id,
avg_fragmentation_in_percent, page_count, partition_number, fragment_count, is_primary_key, is_unique
FROM sys.dm_db_index_physical_stats(DB_ID('ABPLP'), NULL, NULL, NULL, NULL) G
INNER JOIN sys.indexes I
ON G.object_id = I.object_id
AND G.index_id = I.index_id
WHERE avg_fragmentation_in_percent > 5
AND name IS NOT NULL
It works quite nicely.
However, when I try this code
USE ABPMaster
SELECT GetDate() as DateChecked, 'ABPMaster', OBJECT_NAME(G.object_id) as TableName, I.name as IndexName,I.index_id,
avg_fragmentation_in_percent, page_count, partition_number, fragment_count, is_primary_key, is_unique
FROM sys.dm_db_index_physical_stats(DB_ID('ABPMaster'), NULL, NULL, NULL, NULL) G
INNER JOIN sys.indexes I
ON G.object_id = I.object_id
AND G.index_id = I.index_id
WHERE avg_fragmentation_in_percent > 5
AND name IS NOT NULL
I end up with the error: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '('.
All I've done is change the database and the name of the database in the code. Is there some setting in the database that causes this problem? I have 20 databases and this is the only one I'm having the problem on. It happens if I run it as a part of a large chain of these statements, in its own query or if I try to create a stored procedure with it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 23, 2011 at 9:35 am
Run the code taht fails but from the other db.
It fails because it's in compatibily mode 80.
Alternatively you could just swap DB_ID('ABPMaster') with the db id and the code will work just fine.
September 23, 2011 at 10:47 am
Ninja's_RGR'us (9/23/2011)
Run the code taht fails but from the other db.It fails because it's in compatibily mode 80.
Alternatively you could just swap DB_ID('ABPMaster') with the db id and the code will work just fine.
I'd already found the part about compatibility mode, thanks for the confirmation. I wasn't sure how to phrase my google search until I posted this. So DB_ID is the part that isn't compatible? I was worried it was the dm.
This is the only database on the server that I didn't create. I'm going to start by asking the owner if it needs to be in compatibility mode 80, I suspect that it doesn't.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 23, 2011 at 10:52 am
Ya in compat 80 you can't use a function as a parameter for another function.
Compat mode change the way tsql behaves. The "ironic" twist here is that if you call the same code from master (which is in 90+) you won't get the error and the code will run correctly ;-).
Don't worry about google, I found out the same way you did :-D.
Some searches are just really hard in google, even if you know what you want and how to express it.
September 23, 2011 at 10:56 am
Ninja's_RGR'us (9/23/2011)
Ya in compat 80 you can't use a function as a parameter for another function.Compat mode change the way tsql behaves. The "ironic" twist here is that if you call the same code from master (which is in 90+) you won't get the error and the code will run correctly ;-).
Don't worry about google, I found out the same way you did :-D.
Some searches are just really hard in google, even if you know what you want and how to express it.
Yeah, this was a particularly vexing one because the only error I got was that there was something wrong near (.
I thought if you call this from master you don't get any results at all. Is there a change in syntax I need to get it to run from master?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 23, 2011 at 10:59 am
yes remove the USE 😀
Then the join with sys.indexes won't work. I've never had the need for it, but I'm sure there's a way around it with the columns the dmv returns.
Worst case, just plug the dbname before dbo and it'll work.
September 23, 2011 at 11:01 am
Ninja's_RGR'us (9/23/2011)
yes remove the USE 😀Then the join with sys.indexes won't work. I've never had the need for it, but I'm sure there's a way around it with the columns the dmv returns.
Worst case, just plug the dbname before dbo and it'll work.
Heh. I don't have the USE in there when I try to run it from master. : -)
I'm still hoping for best case: I can change the compatibility level. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 23, 2011 at 11:09 am
Stefan Krzywicki (9/23/2011)
Ninja's_RGR'us (9/23/2011)
yes remove the USE 😀Then the join with sys.indexes won't work. I've never had the need for it, but I'm sure there's a way around it with the columns the dmv returns.
Worst case, just plug the dbname before dbo and it'll work.
Heh. I don't have the USE in there when I try to run it from master. : -)
I'm still hoping for best case: I can change the compatibility level. : -)
Agreed, but in the meantime... 🙂
September 23, 2011 at 11:11 am
Ninja's_RGR'us (9/23/2011)
Stefan Krzywicki (9/23/2011)
Ninja's_RGR'us (9/23/2011)
yes remove the USE 😀Then the join with sys.indexes won't work. I've never had the need for it, but I'm sure there's a way around it with the columns the dmv returns.
Worst case, just plug the dbname before dbo and it'll work.
Heh. I don't have the USE in there when I try to run it from master. : -)
I'm still hoping for best case: I can change the compatibility level. : -)
Agreed, but in the meantime... 🙂
Oh, I'm glad to have the fallback options, but I've just confirmed I can change it and have done so. Now to do the documentation that goes along with making a change. Shouldn't take more than 10 times the time it took to make the change. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply