OpenQuery Max String Length - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

OpenQuery Max String Length

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 06:08:21

Hello All,

Upon Running this Open Query I keep on getting an error message.

Here is the script.


select * from openquery(LMYSQL, 
'select 
x.Sequence as "Sequence",
x.PrID as "Process ID",
x.process as "Process",
x.prdt as "Production Date",
x.shift as "Shift",
x.jo as "JO",
x.model as "Model",
x.bbc as "Batch Count",
x.float as "Float Glass",
x.smallbook as "Smallbook Count",
x.input as "Input",
x.output as "Output",
x.ng as "NG",
x.pss as "Pass"
from
(


select 
1 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "SCR") as "PrID",
"SCR" as "Process",
scr_pdt as "PRDT",
scr_shf as "Shift",
"NA" as "JO",
"NA" as "Model",
0 as "bbc",
0 as "Smallbook",
sum(scr_inp_ftg) as "Float",
sum(scr_inp_qty) as "Input",
sum(scr_out_qty) as "Output",
sum(scr_ng_qty) as "NG",
"1" as "pss"
from sum_scr_001 
group by  model, scr_shf, scr_pdt, pss

union all

select 
2 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "SCL") as "PrID",
"SCL" as "Process",
scl_pdt as "PRDT",
scl_shf as "Shift",
Scl_job as "JO",
scl_mdl as "Model",
count(distinct scl_mlb) as "bbc",
0 as "Smallbook",
0 as "Float",
sum(scl_inp_qty) as "Input",
sum(scl_out_qty) as "Output",
sum(scl_ng_qty) as "NG",
scl_pss as "Pss"
from sum_scl_002 
group by scl_mdl, scl_shf, scl_pdt, scl_pss

union all


select 
3 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "Set") as "PrID",
"SET" as "Process",
set_pdt as "PRDT",
set_shf as "Shift",
set_job as "JO",
set_mdl as "Model",
count(distinct set_mlb) as "bbc",
0 as "Smallbook",
0 as "Float",
sum(set_inp_qty) as "Input",
sum(set_out_qty) as "Output",
sum(set_ng_qty) as "NG",
set_pss as "Pss"
from sum_set_001 
where set_pss = 1
group by set_mdl, set_shf, set_pdt, set_pss

union all


select 
4 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "STK") as "PrID",
"STK" as "Process",
stk_pdt as "PRDT",
stk_shf as "Shift",
Stk_job as "JO",
stk_mdl as "Model",
count(distinct stk_bbn) as "bbc",
0 as "Smallbook",
0 as "Float", 
sum(stk_inp_qty) as "Input",
sum(stk_out_qty) as "Output",
sum(stk_ng_qty) as "NG",
"1" as "Pss"
from sum_stk_001
group by stk_mdl, stk_shf, stk_pdt, pss


union all

select 
5 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "CUT") as "PrID",
"CUT" as "Process",
cut_pdt as "PRDT",
cut_shf as "Shift",
cut_job as "JO",
cut_mdl as "Model",
count(distinct cut_bbn) as "bbc",
count(distinct cut_sbn) as "Smallbook",
0 as "Float",
sum(cut_inp_qty) as "Input",
sum(cut_out_qty) as "Output",
sum(cut_ng_qty) as "NG",
cut_pss as "Pss"
from mcgis_web.sum_cut_001
group by  cut_mdl, cut_shf ,cut_pdt, cut_pss

union all

select 
6 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "DI1") as "PrID",
"DI1" as "Process",
dim_pdt as "PRDT",
dim_shf as "Shift",
dim_job as "JO",
dim_mdl as "Model",
count(distinct dim_bbn) as "bbc",
count(distinct dim_sbn) as "Smallbook",
0 as "Float",
sum(dim_inp_qty) as "Input",
sum(dim_out_qty) as "Output",
sum(dim_ng_qty) as "NG",
dim_pss as "Pss"
from mcgis_web.sum_dim_001
group by  dim_mdl, dim_shf ,dim_pdt, dim_pss

union all


select 
7 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FLT") as "PrID",
"FLT" as "Process",
flt_pdt as "PRDT",
flt_shf as "Shift",
flt_job as "JO",
flt_mdl as "Model",
count(distinct flt_bbn) as "bbc",
count(distinct flt_sbn) as "Smallbook",
0 as "Float",
sum(flt_inp_qty) as "Input",
sum(flt_out_qty) as "Output",
sum(flt_ng_qty) as "NG",
flt_pss as "Pss"
from mcgis_web.sum_flt_001
group by  flt_mdl, flt_shf ,flt_pdt, flt_pss

union all


select 
8 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FRM") as "PrID",
"FRM" as "Process",
frm_pdt as "PRDT",
frm_shf as "Shift",
frm_job as "JO",
frm_mdl as "Model",
count(distinct frm_bbn) as "bbc",
count(distinct frm_sbn) as "Smallbook",
0 as "Float",
sum(frm_inp_qty) as "Input",
sum(frm_out_qty) as "Output",
sum(frm_ng_qty) as "NG",
frm_pss as "Pss"
from mcgis_web.sum_frm_001
group by  frm_mdl, frm_shf ,frm_pdt, frm_pss


union all

select 
9 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "DI2") as "PrID",
"DI2" as "Process",
dim_pdt as "PRDT",
dim_shf as "Shift",
dim_job as "JO",
dim_mdl as "Model",
count(distinct dim_bbn) as "bbc",
count(distinct dim_sbn) as "Smallbook",
0 as "Float",
sum(dim_inp_qty) as "Input",
sum(dim_out_qty) as "Output",
sum(dim_ng_qty) as "NG",
dim_pss as "Pss"
from mcgis_web.sum_dim_002
group by  dim_mdl, dim_shf ,dim_pdt, dim_pss


union all

select 
10 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "CHM") as "PrID",
"CHM" as "Process",
chm_pdt as "PRDT",
chm_shf as "Shift",
chm_job as "JO",
chm_mdl as "Model",
count(distinct chm_bch) as "bbc",
count(distinct chm_sbn) as "Smallbook",
0 as "Float",
sum(chm_inp_qty) as "Input",
sum(chm_out_qty) as "Output",
sum(chm_ng_qty) as "NG",
chm_pss as "Pss"
from mcgis_web.sum_chm_001
group by  chm_mdl, chm_shf ,chm_pdt, chm_pss


union all


select 
11 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "HWT") as "PrID",
"HTW" as "Process",
htw_pdt as "PRDT",
htw_shf as "Shift",
htw_job as "JO",
htw_mdl as "Model",
count(distinct htw_bch) as "bbc",
count(distinct htw_sbn) as "Smallbook",
0 as "Float",
sum(htw_inp_qty) as "Input",
sum(htw_out_qty) as "Output",
sum(htw_ng_qty) as "NG",
htw_pss as "Pss"
from mcgis_web.sum_htw_001
group by  htw_mdl, htw_shf ,htw_pdt, htw_pss


union all


select 
12 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "SEP") as "PrID",
"SEP" as "Process",
sep_pdt as "PRDT",
sep_shf as "Shift",
sep_job as "JO",
sep_mdl as "Model",
count(distinct sep_bch) as "bbc",
count(distinct sep_sbn) as "Smallbook",
0 as "Float",
sum(sep_inp_qty) as "Input",
sum(sep_out_qty) as "Output",
sum(sep_ng_qty) as "NG",
sep_pss as "Pss"
from mcgis_web.sum_sep_001
group by  sep_mdl, sep_shf ,sep_pdt, sep_pss


union all


select 
13 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "PCL") as "PrID",
"PCL" as "Process",
pcl_pdt as "PRDT",
pcl_shf as "Shift",
pcl_job as "JO",
pcl_mdl as "Model",
count(distinct pcl_bch) as "bbc",
count(distinct pcl_sbn) as "Smallbook",
0 as "Float",
sum(pcl_inp_qty) as "Input",
sum(pcl_out_qty) as "Output",
sum(pcl_ng_qty) as "NG",
pcl_pss as "Pss"
from mcgis_web.sum_pcl_001
group by  pcl_mdl, pcl_shf ,pcl_pdt, pcl_pss


union all


select 
14 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "PHL") as "PrID",
"PHL" as "Process",
phl_pdt as "PRDT",
phl_shf as "Shift",
phl_job as "JO",
phl_mdl as "Model",
count(distinct phl_bch) as "bbc",
count(distinct phl_sbn) as "Smallbook",
0 as "Float",
sum(phl_inp_qty) as "Input",
sum(phl_out_qty) as "Output",
sum(phl_ng_qty) as "NG",
phl_pss as "Pss"
from mcgis_web.sum_phl_001
group by  phl_mdl, phl_shf ,phl_pdt, phl_pss



union all


select 
15 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "DTF") as "PrID",
"DTF" as "Process",
dtf_pdt as "PRDT",
dtf_shf as "Shift",
dtf_job as "JO",
dtf_mdl as "Model",
count(distinct dtf_bch) as "bbc",
count(distinct dtf_sbn) as "Smallbook",
0 as "Float",
sum(dtf_inp_qty) as "Input",
sum(dtf_out_qty) as "Output",
sum(dtf_ng_qty) as "NG",
dtf_pss as "Pss"
from mcgis_web.sum_dtf_001
group by  dtf_mdl, dtf_shf ,dtf_pdt, dtf_pss


union all


select 
16 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "CHS") as "PrID",
"CS" as "Process",
chs_pdt as "PRDT",
chs_shf as "Shift",
chs_job as "JO",
chs_mdl as "Model",
count(distinct chs_bch) as "bbc",
count(distinct chs_sbn) as "Smallbook",
0 as "Float",
sum(chs_inp_qty) as "Input",
sum(chs_out_qty) as "Output",
sum(chs_ng_qty) as "NG",
chs_pss as "Pss"
from mcgis_web.sum_chs_001
group by  chs_mdl, chs_shf ,chs_pdt, chs_pss


union all



select 
17 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "WET") as "PrID",
"WTF" as "Process",
wet_pdt as "PRDT",
wet_shf as "Shift",
wet_job as "JO",
wet_mdl as "Model",
count(distinct wet_bch) as "bbc",
count(distinct wet_sbn) as "Smallbook",
0 as "Float",
sum(wet_inp_qty) as "Input",
sum(wet_out_qty) as "Output",
sum(wet_ng_qty) as "NG",
wet_pss as "Pss"
from mcgis_web.sum_wet_001
group by  wet_mdl, wet_shf ,wet_pdt, wet_pss


union all


select 
18 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FCL") as "PrID",
"FCL" as "Process",
fcl_pdt as "PRDT",
fcl_shf as "Shift",
fcl_job as "JO",
fcl_mdl as "Model",
count(distinct fcl_bch) as "bbc",
count(distinct fcl_sbn) as "Smallbook",
0 as "Float",
sum(fcl_inp_qty) as "Input",
sum(fcl_out_qty) as "Output",
sum(fcl_ng_qty) as "NG",
fcl_pss as "Pss"
from mcgis_web.sum_fcl_001
group by  fcl_mdl, fcl_shf ,fcl_pdt, fcl_pss


union all


select 
19 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FVI") as "PrID",
"FVI" as "Process",
fvi_pdt as "PRDT",
fvi_shf as "Shift",
fvi_job as "JO",
fvi_mdl as "Model",
count(distinct fvi_bch) as "bbc",
count(distinct fvi_sbn) as "Smallbook",
0 as "Float",
sum(fvi_inp_qty) as "Input",
sum(fvi_out_qty) as "Output",
sum(fvi_ng_qty) as "NG",
fvi_pss as "Pss"
from mcgis_web.sum_fvi_001
group by  fvi_mdl, fvi_shf ,fvi_pdt, fvi_pss) x;')

It show this Error Message

quote:

The character string that starts with 'select blah blah blah
is too long. Maximum length is 8000

I have tried to run this one


declare @Script varchar(max)




set @Script = '  select * from openquery(LMYSQL, 
''select 
x.Sequence as "Sequence",
x.PrID as "Process ID",
x.process as "Process",
x.prdt as "Production Date",
x.shift as "Shift",
x.jo as "JO",
x.model as "Model",
x.bbc as "Batch Count",
x.float as "Float Glass",
x.smallbook as "Smallbook Count",
x.input as "Input",
x.output as "Output",
x.ng as "NG",
x.pss as "Pass"
from
(


select 
1 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "SCR") as "PrID",
"SCR" as "Process",
scr_pdt as "PRDT",
scr_shf as "Shift",
"NA" as "JO",
"NA" as "Model",
0 as "bbc",
0 as "Smallbook",
sum(scr_inp_ftg) as "Float",
sum(scr_inp_qty) as "Input",
sum(scr_out_qty) as "Output",
sum(scr_ng_qty) as "NG",
"1" as "pss"
from sum_scr_001 
group by  model, scr_shf, scr_pdt, pss

union all

select 
2 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "SCL") as "PrID",
"SCL" as "Process",
scl_pdt as "PRDT",
scl_shf as "Shift",
Scl_job as "JO",
scl_mdl as "Model",
count(distinct scl_mlb) as "bbc",
0 as "Smallbook",
0 as "Float",
sum(scl_inp_qty) as "Input",
sum(scl_out_qty) as "Output",
sum(scl_ng_qty) as "NG",
scl_pss as "Pss"
from sum_scl_002 
group by scl_mdl, scl_shf, scl_pdt, scl_pss

union all


select 
3 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "Set") as "PrID",
"SET" as "Process",
set_pdt as "PRDT",
set_shf as "Shift",
set_job as "JO",
set_mdl as "Model",
count(distinct set_mlb) as "bbc",
0 as "Smallbook",
0 as "Float",
sum(set_inp_qty) as "Input",
sum(set_out_qty) as "Output",
sum(set_ng_qty) as "NG",
set_pss as "Pss"
from sum_set_001 
where set_pss = 1
group by set_mdl, set_shf, set_pdt, set_pss

union all


select 
4 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "STK") as "PrID",
"STK" as "Process",
stk_pdt as "PRDT",
stk_shf as "Shift",
Stk_job as "JO",
stk_mdl as "Model",
count(distinct stk_bbn) as "bbc",
0 as "Smallbook",
0 as "Float", 
sum(stk_inp_qty) as "Input",
sum(stk_out_qty) as "Output",
sum(stk_ng_qty) as "NG",
"1" as "Pss"
from sum_stk_001
group by stk_mdl, stk_shf, stk_pdt, pss


union all

select 
5 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "CUT") as "PrID",
"CUT" as "Process",
cut_pdt as "PRDT",
cut_shf as "Shift",
cut_job as "JO",
cut_mdl as "Model",
count(distinct cut_bbn) as "bbc",
count(distinct cut_sbn) as "Smallbook",
0 as "Float",
sum(cut_inp_qty) as "Input",
sum(cut_out_qty) as "Output",
sum(cut_ng_qty) as "NG",
cut_pss as "Pss"
from mcgis_web.sum_cut_001
group by  cut_mdl, cut_shf ,cut_pdt, cut_pss

union all

select 
6 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "DI1") as "PrID",
"DI1" as "Process",
dim_pdt as "PRDT",
dim_shf as "Shift",
dim_job as "JO",
dim_mdl as "Model",
count(distinct dim_bbn) as "bbc",
count(distinct dim_sbn) as "Smallbook",
0 as "Float",
sum(dim_inp_qty) as "Input",
sum(dim_out_qty) as "Output",
sum(dim_ng_qty) as "NG",
dim_pss as "Pss"
from mcgis_web.sum_dim_001
group by  dim_mdl, dim_shf ,dim_pdt, dim_pss

union all


select 
7 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FLT") as "PrID",
"FLT" as "Process",
flt_pdt as "PRDT",
flt_shf as "Shift",
flt_job as "JO",
flt_mdl as "Model",
count(distinct flt_bbn) as "bbc",
count(distinct flt_sbn) as "Smallbook",
0 as "Float",
sum(flt_inp_qty) as "Input",
sum(flt_out_qty) as "Output",
sum(flt_ng_qty) as "NG",
flt_pss as "Pss"
from mcgis_web.sum_flt_001
group by  flt_mdl, flt_shf ,flt_pdt, flt_pss

union all


select 
8 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FRM") as "PrID",
"FRM" as "Process",
frm_pdt as "PRDT",
frm_shf as "Shift",
frm_job as "JO",
frm_mdl as "Model",
count(distinct frm_bbn) as "bbc",
count(distinct frm_sbn) as "Smallbook",
0 as "Float",
sum(frm_inp_qty) as "Input",
sum(frm_out_qty) as "Output",
sum(frm_ng_qty) as "NG",
frm_pss as "Pss"
from mcgis_web.sum_frm_001
group by  frm_mdl, frm_shf ,frm_pdt, frm_pss


union all

select 
9 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "DI2") as "PrID",
"DI2" as "Process",
dim_pdt as "PRDT",
dim_shf as "Shift",
dim_job as "JO",
dim_mdl as "Model",
count(distinct dim_bbn) as "bbc",
count(distinct dim_sbn) as "Smallbook",
0 as "Float",
sum(dim_inp_qty) as "Input",
sum(dim_out_qty) as "Output",
sum(dim_ng_qty) as "NG",
dim_pss as "Pss"
from mcgis_web.sum_dim_002
group by  dim_mdl, dim_shf ,dim_pdt, dim_pss


union all

select 
10 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "CHM") as "PrID",
"CHM" as "Process",
chm_pdt as "PRDT",
chm_shf as "Shift",
chm_job as "JO",
chm_mdl as "Model",
count(distinct chm_bch) as "bbc",
count(distinct chm_sbn) as "Smallbook",
0 as "Float",
sum(chm_inp_qty) as "Input",
sum(chm_out_qty) as "Output",
sum(chm_ng_qty) as "NG",
chm_pss as "Pss"
from mcgis_web.sum_chm_001
group by  chm_mdl, chm_shf ,chm_pdt, chm_pss


union all


select 
11 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "HWT") as "PrID",
"HTW" as "Process",
htw_pdt as "PRDT",
htw_shf as "Shift",
htw_job as "JO",
htw_mdl as "Model",
count(distinct htw_bch) as "bbc",
count(distinct htw_sbn) as "Smallbook",
0 as "Float",
sum(htw_inp_qty) as "Input",
sum(htw_out_qty) as "Output",
sum(htw_ng_qty) as "NG",
htw_pss as "Pss"
from mcgis_web.sum_htw_001
group by  htw_mdl, htw_shf ,htw_pdt, htw_pss


union all


select 
12 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "SEP") as "PrID",
"SEP" as "Process",
sep_pdt as "PRDT",
sep_shf as "Shift",
sep_job as "JO",
sep_mdl as "Model",
count(distinct sep_bch) as "bbc",
count(distinct sep_sbn) as "Smallbook",
0 as "Float",
sum(sep_inp_qty) as "Input",
sum(sep_out_qty) as "Output",
sum(sep_ng_qty) as "NG",
sep_pss as "Pss"
from mcgis_web.sum_sep_001
group by  sep_mdl, sep_shf ,sep_pdt, sep_pss


union all


select 
13 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "PCL") as "PrID",
"PCL" as "Process",
pcl_pdt as "PRDT",
pcl_shf as "Shift",
pcl_job as "JO",
pcl_mdl as "Model",
count(distinct pcl_bch) as "bbc",
count(distinct pcl_sbn) as "Smallbook",
0 as "Float",
sum(pcl_inp_qty) as "Input",
sum(pcl_out_qty) as "Output",
sum(pcl_ng_qty) as "NG",
pcl_pss as "Pss"
from mcgis_web.sum_pcl_001
group by  pcl_mdl, pcl_shf ,pcl_pdt, pcl_pss


union all


select 
14 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "PHL") as "PrID",
"PHL" as "Process",
phl_pdt as "PRDT",
phl_shf as "Shift",
phl_job as "JO",
phl_mdl as "Model",
count(distinct phl_bch) as "bbc",
count(distinct phl_sbn) as "Smallbook",
0 as "Float",
sum(phl_inp_qty) as "Input",
sum(phl_out_qty) as "Output",
sum(phl_ng_qty) as "NG",
phl_pss as "Pss"
from mcgis_web.sum_phl_001
group by  phl_mdl, phl_shf ,phl_pdt, phl_pss



union all


select 
15 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "DTF") as "PrID",
"DTF" as "Process",
dtf_pdt as "PRDT",
dtf_shf as "Shift",
dtf_job as "JO",
dtf_mdl as "Model",
count(distinct dtf_bch) as "bbc",
count(distinct dtf_sbn) as "Smallbook",
0 as "Float",
sum(dtf_inp_qty) as "Input",
sum(dtf_out_qty) as "Output",
sum(dtf_ng_qty) as "NG",
dtf_pss as "Pss"
from mcgis_web.sum_dtf_001
group by  dtf_mdl, dtf_shf ,dtf_pdt, dtf_pss


union all


select 
16 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "CHS") as "PrID",
"CS" as "Process",
chs_pdt as "PRDT",
chs_shf as "Shift",
chs_job as "JO",
chs_mdl as "Model",
count(distinct chs_bch) as "bbc",
count(distinct chs_sbn) as "Smallbook",
0 as "Float",
sum(chs_inp_qty) as "Input",
sum(chs_out_qty) as "Output",
sum(chs_ng_qty) as "NG",
chs_pss as "Pss"
from mcgis_web.sum_chs_001
group by  chs_mdl, chs_shf ,chs_pdt, chs_pss


union all



select 
17 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "WET") as "PrID",
"WTF" as "Process",
wet_pdt as "PRDT",
wet_shf as "Shift",
wet_job as "JO",
wet_mdl as "Model",
count(distinct wet_bch) as "bbc",
count(distinct wet_sbn) as "Smallbook",
0 as "Float",
sum(wet_inp_qty) as "Input",
sum(wet_out_qty) as "Output",
sum(wet_ng_qty) as "NG",
wet_pss as "Pss"
from mcgis_web.sum_wet_001
group by  wet_mdl, wet_shf ,wet_pdt, wet_pss


union all


select 
18 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FCL") as "PrID",
"FCL" as "Process",
fcl_pdt as "PRDT",
fcl_shf as "Shift",
fcl_job as "JO",
fcl_mdl as "Model",
count(distinct fcl_bch) as "bbc",
count(distinct fcl_sbn) as "Smallbook",
0 as "Float",
sum(fcl_inp_qty) as "Input",
sum(fcl_out_qty) as "Output",
sum(fcl_ng_qty) as "NG",
fcl_pss as "Pss"
from mcgis_web.sum_fcl_001
group by  fcl_mdl, fcl_shf ,fcl_pdt, fcl_pss


union all


select 
19 as "Sequence",
(select ppr_oid from mcgis_test1.m_ppr_001 where ppr_sht_nme = "FVI") as "PrID",
"FVI" as "Process",
fvi_pdt as "PRDT",
fvi_shf as "Shift",
fvi_job as "JO",
fvi_mdl as "Model",
count(distinct fvi_bch) as "bbc",
count(distinct fvi_sbn) as "Smallbook",
0 as "Float",
sum(fvi_inp_qty) as "Input",
sum(fvi_out_qty) as "Output",
sum(fvi_ng_qty) as "NG",
fvi_pss as "Pss"
from mcgis_web.sum_fvi_001
group by  fvi_mdl, fvi_shf ,fvi_pdt, fvi_pss) x;'')'

select @script

But instead of running the query it only show the
query as is.

How can I fix this?

Hope you can give me some advice.

Thank you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-14 : 06:54:28

The 8000 character limit is a documented limitation of OPENQUERY. http://msdn.microsoft.com/en-us/library/ms188427.aspx

If you do want to execute the script you have saved, instead of the last "select @script" you should use "exec(@script)" or "sp_executesql @script". However, that is not going to help you in this case - dynamic SQL has the same limitation, and even if that didn't OPENQUERY would still display the error.

Couple of possibilities that I can think of are:

a) If you have access to the remote server, create a view that matches the query and then select from that view.

b) Since your query is a bunch of UNION ALL's, break it up into pieces that are shorter than 8000 characters, store the intermediate results in a temp table, and do a final select from the temp table.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 06:56:28

Probably your string is longer than 8000 characters.

To run your string
exec (@script)
But not sure if that will work anyway.

But instead of things like
"HTW" as "Process",
htw_pdt as "PRDT",
htw_shf as "Shift",
htw_job as "JO",
htw_mdl as "Model",

Why not have an identifier for each of the union all clauses and assign those literals locally - better than assigning them on the remote server and transferring the data.

I would also consider executing each of the queries individually and inserting into a temp table to accumulate.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources