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 with variables using varchar workaround

Author  Topic 

koenslootmans
Starting Member

1 Post

Posted - 2013-04-05 : 08:39:36
Hello,

I have an openquery which is being passed variables in a procedure
It compiles fine but When i run it i get the following error:

Msg 245, Level 16, State 1, Procedure GET_SLOWMOVERSINFO, Line 24
Conversion failed when converting the nvarchar value 'select count(1) as nrofreference

here is the procedure...

Create PROCEDURE [dbo].[GET_SLOWMOVERSINFO]
@PickLocation nvarchar(30),
@StorageLocationWarehouse nvarchar(30),
@StorageLocationAisle nvarchar(30),
@PickBinType int


AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
insert into dbo.ProcedureLog values(SYSDATETIME(),'GET_SLOWMOVERSINFO','started',' ');

delete from dbo.KPI_Inventory_Health where datediff(DAY,dateofloading, SYSDATETIME()) = 0 and PickBinType = @PickBinType
and PickLocation = @PickLocation and @StorageLocationAisle = StorageLocationAisle and @StorageLocationWarehouse = StorageLocationWarehouse;



Declare @TSQL nvarchar(4000);
set @TSQL = 'select count(1) as nrofreferences, sqdept, Product_Type, Product_Status, sum(qtystocked) as qtystocked, sum(boxesstored) as boxesstocked, sum(valuestocked) as salesvaluestocked
from (select
sqdept||''-''||sqstyl||''-''||sqcolr||''-''||sqsize as a_reference,
sqdept,
rmdesc,
MAS.RMFRCT,
sum(bstk.sqqtys) as qtystocked,
sum(bstk.sqqtys*rmsspr) as valuestocked,
sum(bstk.sqqtys)/rmpunu as boxesstored,
qty_out,
max_box_out,
avg_box_out,
boxes_out,
nof_weeks_picked as nbr_times_picked,
rmpunu,
rmsspr,
last_act_yy,
last_act_ww,
lastactivedate,
case
when (max_box_out <= 2 and max_box_out >1 and sysdate - rmfrct >28 ) then ''Slow mover''
when (max_box_out <= 1 and sysdate - rmfrct >28) then ''Very slow mover''
else ''Normal'' end as Product_type,
case
when (sysdate - rmfrct >28 and sysdate - lastactivedate > 28 ) then ''Sleeping''
when (sysdate - rmfrct >140 and sysdate - lastactivedate > 140 ) then ''Death''
When (sysdate - rmfrct <= 28) then ''NEW''
else ''Normal''
end as Product_status,
sqdept as dept
from
stkbstk bstk,
stkmas mas,
pick_aisle_loc ais,
(select qrrdept,qrrstyl, qrrsize,qrrcolr, max(lastactivedate) as lastactivedate from
(select qrdept as qrrdept, qrstyl as qrrstyl, qrsize as qrrsize, qrcolr as qrrcolr, max(qrcreated_dt) as lastactivedate from stkpick
group by qrdept, qrstyl,qrsize,qrcolr
union select qrdept as qrrdept, qrstyl as qrrstyl, qrsize as qrrsize, qrcolr as qrrcolr, max(qrcreated_dt) as lastactivedate from stkpick_hist
group by qrdept, qrstyl,qrsize,qrcolr
)
group by qrrdept,qrrstyl,qrrsize,qrrcolr
)
,
(
select

max(crea_yy) as last_act_yy,
max(crea_ww) as last_act_ww,
mas.rmdesc as description,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(quantityout) as qty_out,
sum(quantityout/rmpunu) as boxes_out ,
max(quantityout/rmpunu) as max_box_out,
avg(quantityout/rmpunu) as avg_box_out,
count(1) as nof_weeks_picked
from
(
select

crea_yy,
crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(quantityOut) as quantityOUT
from
(
select

to_char(qrcreated_dt,''yy'') as crea_yy,
to_char(qrcreated_dt,''ww'') as crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(qrrqty) as quantityOUT
from
Disport.stkpick pik,
disport.pick_aisle_loc ais
where
pik.qraisl = ais.AISLE and
aisle_com '''+ @PickLocation +''' and
qrcreated_dt > sysdate - 140
group by
to_char(qrcreated_dt,''yy''),
to_char(qrcreated_dt,''ww''),
qrdept,
qrstyl,
qrcolr,
qrsize
union
select
to_char(qrcreated_dt,''yy'') as crea_yy,
to_char(qrcreated_dt,''ww'') as crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(qrrqty) as quantityOUT
from
Disport.stkpick_hist hist,
Disport.pick_aisle_loc ais
where
qraisl = ais.AISLE and
aisle_com '''+@PickLocation+''' and
qrcreated_dt > sysdate - 140
group by
to_char(qrcreated_dt,''yy''),
to_char(qrcreated_dt,''ww''),
qrdept,
qrstyl,
qrcolr,
qrsize
)
group by
crea_yy,
crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize
) p1,
stkmas mas
where
qrdept = rmdept and
qrstyl = rmstyl and
qrcolr = rmcolr and rmcomp = 1 and

qrsize = rmsize
group by
qrdept,
qrstyl,
qrcolr,
qrsize,
rmpunu,
rmdesc
) p2
where
qrdept(+) = sqdept and
qrstyl(+) = sqstyl and
qrcolr(+) = sqcolr and
qrsize(+) = sqsize and
qrrstyl = qrstyl and qrrsize = qrsize and qrrcolr = qrcolr and qrrdept = qrdept and
sqdept = rmdept and
sqstyl = rmstyl and
sqcolr = rmcolr and
sqsize = rmsize and
rmcomp = 1 and
sqaisl = ais.AISLE and
aisle_com '''+@StorageLocationWarehouse+''' and Sqaisle '''+@StorageLocationAisle+'''
and SQBTYP = '''+@PickBinType+'''
group by
sqdept,
sqstyl,
sqcolr,
sqsize,
qty_out,
boxes_out,
last_act_yy,
last_act_ww,
rmpunu,

rmfrct,

rmsspr,lastactivedate,
rmdesc,
avg_box_out,
max_box_out,
nof_weeks_picked)
group by sqdept, product_type,product_status'




set @TSQL = N'insert into dbo.KPI_Inventory_Health select SYSDATETIME() as dateofloading,
'''+@PickBinType+''' as PickBinType,
'''+@PickLocation+''' as PickLocation,
'''+@StorageLocationWarehouse+''' as StorageLocationWarehouse,
'''+@StorageLocationAisle+''' as StorageLocationAisle,
NROFREFERENCES,SQDEPT, PRODUCT_TYPE , PRODUCT_STATUS, qtystocked,boxesstocked,salesvaluestocked from openquery(Disport,''' + REPLACE(@TSQL, '''', '''''') + ''')'

print @TSQL;
exec (@TSQL);

insert into dbo.ProcedureLog values(SYSDATETIME(),'GET_SLOWMOVERSINFO','completed','ALL');



END
commit

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-05 : 11:44:21
Change:
and SQBTYP = '''+@PickBinType+'''

To:
and SQBTYP = '''+ CAST(@PickBinType AS VARCHAR(20)) +'''

Alwasy try to avoid impicit conversions.
Go to Top of Page
   

- Advertisement -