mssql中的无效对象错误 - 消息208 [英] Invalid object error in mssql - msg 208

问题描述

执行查询时出现无效对象错误。



请帮忙！

========== =====================

There is an Invalid Object error while executing query.

Please help !
===============================

USE HR_Employee
GO
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(ADA_DATE)
FROM (SELECT DISTINCT  ADA_DATE FROM WrkDailyAttend_Web) AS PivotExample
 
SELECT   @PivotColumns

SET   @SQLQuery = N'SELECT EMP_ID, ' +   @PivotColumns + '
    FROM [HR_Employee].[dbo].[PivotExample] 
    PIVOT (MAX(TIME_IN) FOR ADA_DATE IN (' + @PivotColumns + ')) AS P'

SELECT   @SQLQuery

EXEC sp_executesql @SQLQuery

================== =====================

结果：



（1排） （s）受影响）



（受影响的1行）

消息208，级别16，状态1，行1

无效的对象名称'HR_Employee.dbo.PivotExample'。

=======================================
Result :

(1 row(s) affected)

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 1
Invalid object name 'HR_Employee.dbo.PivotExample'.

推荐答案

数据库中是否有一个名为PivotExample的表？如果有，请检查写作和所有者，并用适当的所有者替换dbo并更正拼写中可能存在的差异。



如果PivotExample只是你用过的别名在上一个查询中，您无法引用先前执行的查询。执行查询不会在数据库中创建任何永久对象，这意味着在执行查询后，别名不再退出。



如果是后者，则需要在@SQLQuery变量中嵌入一个完整，正确的查询。



使用 PRINT [ ^ ]查看变量内部的内容，以便捕获语句并尝试运行它。

Is there a table called PivotExample in the database? If there is, check the writing and the owner and replace dbo with proper owner and correct possible differences in spelling.

If the PivotExample is just the alias you have used in the previous query, you cannot reference a query executed earlier. Executing a query does not create any permanent object into the database meaning that after the query is executed the aliases exit no more.

If the latter is the case, you need to embed a full, proper query inside the @SQLQuery variable.

It's a good idea to use PRINT[^] to see what's inside the variables so you can capture the statement and try running it.

这篇关于mssql中的无效对象错误 - 消息208的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！