multi-part identifier "f.name" could not be bound

Question

multi-part identifier "f.name" could not be bound

ACDBA 421

Hi All,

I am running the below query to fetch inmemory file details using powershell.

SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name,f.name,f.physical_name,fg.type_desc,* FROM sys.filegroups FG   
               JOIN sys.database_files F  
			     ON FG.data_space_id = F.data_space_id  
     WHERE FG.type = ''FX'' AND F.type = 2

But it throws the below error on some instances.

Failed during execution | The multi-part identifier "f.name" could not be bound.

Can you please help?

Thanks,
ACDBA

Viorel 126.9K Reputation points

2022-10-27T14:46:25.6+00:00

The query seems almost correct. (Did you try it in Management Studio?) Maybe it is used incorrectly in PowerShell.
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-10-27T14:58:50.627+00:00

Don't think the case is the reason but give it a try.
ACDBA 421 Reputation points

2022-10-27T15:31:49.507+00:00

Thank you so much...It worked....
Ronen Ariely 15,221 Reputation points

2022-10-27T20:02:43.853+00:00

JingyangLi : Don't think the case is the reason but give it a try.

Actually, this is the exact reason. Case sensitivity of Object names depend on the COLLATE
LiHongMSFT-4306 31,621 Reputation points

2022-10-28T02:14:36.997+00:00

Hi @ACDBA
Glad to see that your problem has been resolved!
I have converted Jingyang Li's comment to an answer, please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-10-28T03:44:47.71+00:00

That is the way I responded to this question.
What I really wanted to say was: keep code consistent including CASE of the column names, variables etc.
That is why I posted modified version of the query, which worked.
Hope OP got the reasons from other postings for his initial non working code.
Ronen Ariely 15,221 Reputation points

2022-10-28T03:59:52.487+00:00

Well said

Answer accepted by question author

Ronen Ariely 15,221

I am not sure that a "solution" without understanding have a lot of value since you will have such issue in the future.

The explanation is very simple

case sensitivity of Object names depend on the COLLATE

Here is a simple example of two databases. First one is using COLLATE which is Case Sensitive. Therefore we can create two tables named T and t. In this database you query will returns error since T and t are not the same.

create database Hebrew_100_CS_AS COLLATE Hebrew_100_CS_AS  
use Hebrew_100_CS_AS  
CREATE TABLE T(id int)  
CREATE TABLE t(id int)  
-- No issue using 2 object with the same name with upper and lower case  
-- Since the database COLLATE is case sensitive  
  
SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name  
,f.name,f.physical_name,FG.type_desc,* FROM sys.filegroups FG  
JOIN sys.database_files F  
ON FG.data_space_id = F.data_space_id  
-- ERROR! The multi-part identifier "f.physical_name" could not be bound.  
----------------------------------------  
----------------------------------------  
create database Hebrew_100_CI_AS COLLATE Hebrew_100_CI_AS  
use Hebrew_100_CI_AS  
CREATE TABLE T(id int)  
CREATE TABLE t(id int)  
-- Error! There is already an object named 't' in the database.  

SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name  
,f.name,f.physical_name,FG.type_desc,* FROM sys.filegroups FG  
JOIN sys.database_files F  
ON FG.data_space_id = F.data_space_id  
-- No error :-)

Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2022-10-27T21:01:43.237+00:00
In addition, you can check the collation with this query:

SELECT databasepropertyex(db_name(), 'Collation')

1 additional answer

Your answer

Viorel 126.9K Reputation points

2022-10-27T14:46:25.6+00:00

The query seems almost correct. (Did you try it in Management Studio?) Maybe it is used incorrectly in PowerShell.
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-10-27T14:58:50.627+00:00

Don't think the case is the reason but give it a try.
ACDBA 421 Reputation points

2022-10-27T15:31:49.507+00:00

Thank you so much...It worked....
Ronen Ariely 15,221 Reputation points

2022-10-27T20:02:43.853+00:00

JingyangLi : Don't think the case is the reason but give it a try.

Actually, this is the exact reason. Case sensitivity of Object names depend on the COLLATE
LiHongMSFT-4306 31,621 Reputation points

2022-10-28T02:14:36.997+00:00

Hi @ACDBA
Glad to see that your problem has been resolved!
I have converted Jingyang Li's comment to an answer, please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-10-28T03:44:47.71+00:00

That is the way I responded to this question.
What I really wanted to say was: keep code consistent including CASE of the column names, variables etc.
That is why I posted modified version of the query, which worked.
Hope OP got the reasons from other postings for his initial non working code.
Ronen Ariely 15,221 Reputation points

2022-10-28T03:59:52.487+00:00

Well said
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2022-10-27T21:01:43.237+00:00

In addition, you can check the collation with this query:

SELECT databasepropertyex(db_name(), 'Collation')

Answer 1

Jingyang Li 5,901 Volunteer Moderator

SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name
,F.name,F.physical_name,FG.type_desc,* FROM sys.filegroups FG
JOIN sys.database_files F
ON FG.data_space_id = F.data_space_id
WHERE FG.type = ''FX'' AND F.type = 2

0 comments

Share via

multi-part identifier "f.name" could not be bound

1 additional answer

Your answer