Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juicyapple
Posting Yak Master
176 Posts |
 Posted - 2011-03-16 : 05:11:19
|
| Hi,I have a select statement (run in MSSQL2K8) query data remotely using link server (MSSQL2K). It returns error and the message as below. Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Msg 107, Level 16, State 2, Line 1The column prefix 'Tbl1005' does not match with a table name or alias name used in the query.Msg 107, Level 16, State 2, Line 1The column prefix 'Tbl1003' does not match with a table name or alias name used in the query.Msg 107, Level 16, State 2, Line 1The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.The query works fine in MSSQL2K5 which is before I migrated it to SQL2K8.And funny thing happens if I change all the left outer join to inner join, the error gone. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-03-16 : 07:19:33
|
| Can you post the code?MadhivananFailing to plan is Planning to fail |
 |
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2011-03-16 : 20:57:09
|
quote:
Originally posted by madhivanan
Can you post the code?MadhivananFailing to plan is Planning to fail
Here is the code.SELECT DISTINCT a.Record_No, c.Param_value as Machine_ID, f.Param_Value AS Prod_Type, 22, d.charttype_id, d.Charttype_Name , o.USL, o.UCL, o.Target, o.LCL, o.LSL, d.Decimal_Points, d.Display_Seq, ISNULL(d.UOM,'NA') as UOM from [Server1\Inst1].[PCDB].[dbo].Machine_Test_Header a WITH (NOLOCK) inner join [Server1\Inst1].[PCDB].[dbo].Machine_Test_Detail b WITH (NOLOCK) on a.Record_No = b.Record_No inner join [Server1\Inst1].[PCDB].[dbo].Machine_TestCollection_Header c WITH (NOLOCK) on a.Record_No = c.Record_No and c.param_name ='Machine_id' inner join [Server1\Inst1].[PCDB].[dbo].Machine_Charttype d WITH (NOLOCK) on b.Charttype_Id = d.Charttype_Id AND c.Param_Value = d.Machine_Id and d.testtype_id = '22' inner join tbl_ref_Machine_new_series_filter e WITH (NOLOCK) on d.Machine_id = e.Machine_id and d.testtype_id = e.testtype_id and d.charttype_id = e.charttype_id left outer join [Server1\Inst1].[PCDB].[dbo].Machine_Test_ControlLimits o WITH (NOLOCK) ON o.Record_No = a.Record_No AND e.ChartType_ID = o.Charttype_Id inner join [Server1\Inst1].[PCDB].[dbo].Machine_TestCollection_Header f WITH (NOLOCK) on a.Record_No = f.Record_No and f.param_name ='prod_type' inner join [Server1\Inst1].[PCDB].[dbo].Machine_TestCollection_Header g WITH (NOLOCK) on a.Record_No = g.Record_No and g.param_name ='date_tm' inner join [Server1\Inst1].[PCDB].[dbo].Machine_TestCollection_Header h WITH (NOLOCK) on a.Record_No = h.Record_No and h.param_name = 'flag' left outer join tbl_Calendar z WITH (NOLOCK) on convert(varchar(10), cast(g.param_value as datetime), 112) between convert(varchar(10),z.frdate,112) and convert(varchar(10),z.todate,112) where convert(varchar(20),cast(g.param_value as datetime),112) = '20101018' and h.Param_value ='PROD' and b.series_value <>0 |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
|
|
|
|
|
|
|
Error with Left Outer Join