Querying SQL Server Database Results in "Invalid Object name" error, when it is definitely valid
I have a C# app that hits a couple of different databases to do some analysis. However, for some strange reason, I keep getting an "Invalid object name" error with one of my queries, when I know for a fact that the object exists and is being called properly. This is the query in question (Excuse the one line... it's generated dynamically)
;with bqs as ( select data_set_date ,'LSRON' as 'laser_state' ,'control_mode'= case when cntrl_mode='service' then 'PADDLE1' when cntrl_mode='Exposure' then 'SCANNER' end ,null as gas_status ,'shutter_status' = case when cntrl_mode='Exposure' then 'OPEN' when cntrl_mode='service' then 'CLOSED' end ,'BQ' as Event from [FDS_RAM_MTBE].[dbo].[Fault_summ] where serial_number = '61040' and data_set_date > '6/21/2010 12:00:00 AM' and data_set_date < '6/28/2010 12:00:00 AM'), st as(SELECT serial_number ,data_set_date ,S_1 as laser_state ,S_2 as control_mode ,'gas_status' = case when s_28 in ('OPEN','CLOSED','UNKNOWN')then s_27 when s_37 in ('OPEN','CLOSED','UNKNOWN')then s_36 when s_36 like '%TW%' OR s_36 like '%1W%' OR S_39 IN ('0','1','2') then s_38 when s_437 in ('OPEN','CLOSED','UNKNOWN','0','1','2')then s_436 else null end ,'shutter_state'= case when s_28 in ('OPEN','CLOSED','UNKNOWN')then s_28 when s_37 in ('OPEN','CLOSED','UNKNOWN')then s_37 when s_36 like '%TW%' OR s_36 like '%1W%' OR S_39 IN ('0','1','2')then cast(s_39 AS varchar) when s_437 in ('OPEN','CLOSED','UNKNOWN','0','1','2')then s_437 else null end , s_45 as Event FROM CSDFDS2.FDS.DW.FDS_statuses_trx_fvw where serial_number = '61040' and data_set_date > '6/21/2010 12:00:00 AM' and data_set_date < '6/28/2010 12:00:00 AM'), st1 as ( select data_set_date ,Laser_state ,'control_mode'=case when control_mode= 'STEPPER' then 'SCANNER' else control_mode end ,gas_status ,'shutter_status'= case when shutter_state like '1%' then 'OPEN' when shutter_state like '2%' then 'CLOSED' when shutter_state like '0%' then 'UNKNOWN' else shutter_state end ,Event from st) ,un1 as (select * from st1 union select * from bqs)select *from un1 order by data_set_date desc;
The error in question is this:
Invalid object name 'FDS_RAM_MTBE.dbo.Fault_summ'.
Stack Trace at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, Str开发者_C百科ing srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at RAM_UPTIME.DatabaseHandler.executeDataset(String queryString, String tableName) in C:\Documents and Settings\L502570\Desktop\RAM UPTIME\Data Classes\DatabaseHandler.cs:line 216
But catch this... If I run:
SELECT * from FDS_RAM_MTBE.dbo.Fault_summ
WHERE serial_number = '61872'
It works fine. Pulls the data properly. What in the world is going on?
Here is a more formatted version of that query although with variables still left in..
;with bqs as (
select
data_set_date
,'LSRON' as 'laser_state'
,'control_mode'= case
when cntrl_mode='service' then 'PADDLE1'
when cntrl_mode='Exposure' then 'SCANNER' end
,null as gas_status
,'shutter_status' = case
when cntrl_mode='Exposure' then 'OPEN'
when cntrl_mode='service' then 'CLOSED' end
,'BQ' as Event
from [FDS_RAM_MTBE].[dbo].[Fault_summ]
where serial_number = ' + laserSerialNo' and data_set_date > ' + startDate' and data_set_date < ' + endDate')
, st as(SELECT
serial_number
,data_set_date
,S_1 as laser_state
,S_2 as control_mode
,'gas_status' = case
when s_28 in ('OPEN','CLOSED','UNKNOWN')then s_27
when s_37 in ('OPEN','CLOSED','UNKNOWN')then s_36
when s_36 like '%TW%' OR s_36 like '%1W%' OR S_39 IN ('0','1','2') then s_38
when s_437 in ('OPEN','CLOSED','UNKNOWN','0','1','2')then s_436 else null end
,'shutter_state'= case
when s_28 in ('OPEN','CLOSED','UNKNOWN')then s_28
when s_37 in ('OPEN','CLOSED','UNKNOWN')then s_37
when s_36 like '%TW%' OR s_36 like '%1W%' OR S_39 IN ('0','1','2')then cast(s_39 AS varchar)
when s_437 in ('OPEN','CLOSED','UNKNOWN','0','1','2')then s_437 else null end
, s_45 as Event
FROM CSDFDS2.FDS.DW.FDS_statuses_trx_fvw
where serial_number = ' + laserSerialNo' and data_set_date > ' + startDate' and data_set_date < ' + endDate')
, st1 as (
select
data_set_date
,Laser_state
,'control_mode'=case
when control_mode= 'STEPPER' then 'SCANNER' else control_mode end
,gas_status
,'shutter_status'= case
when shutter_state like '1%' then 'OPEN'
when shutter_state like '2%' then 'CLOSED'
when shutter_state like '0%' then 'UNKNOWN' else shutter_state end
,Event
from st
) ,un1 as (select * from st1 union select * from bqs)
select *
from un1 order by data_set_date desc;
What if you append CSDFDS2 to the first CTE?
FROM [CSDFDS2].[FDS_RAM_MTBE].[dbo].[Fault_summ]
You sure you are connected to the database you think you are?
You sure this isn't an error from an underlying view which has become invalid?
精彩评论