I have a spreadsheet with 2 tabs, each with its own query via ODBC connection. On one query it works fine with parameters. On the other, when I have a date hard coded it runs fine. When I change the code to a ? so it will prompt for a parameter, that is when I get the errors:
[Microsoft][ODBC SQL Server Driver] Syntax Error or access violation
[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index
I am not sure why one works and why the other doesn't. Please help.
The code for the failing query is:
select t.cal_date
,a.agent_name
,a.Phone_Login
,t.dialed_nbr
from opscall.dbo.cc_gen_transfers_dialed t
inner join OpsDataD.dbo.resource_master rm
on rm.network_id = t.transf_empid
join opscall.dbo.cc_single_source_agent a
on rm.resource_id = a.resource_id
and t.cal_date = a.date
where t.cal_date between ? and ?
and rm.location_nbr in (22)
and rm.end_date > getdate()
group by t.cal_date
,a.agent_name
,a.Phone_Login
,t.dialed_nbr
order by 1,2,4
The code for the working query is:
select t.cal_date,101)
,a.agent_name
,a.Phone_Login
,a.in_calls_handled as inb_calls
,sum(t.transfers) as transfers
from opscall.dbo.tempe_agent_transfers t
inner join OpsDataD.dbo.resource_master rm
on rm.network_id = t.network_id
join opscall.dbo.cc_single_source_agent a
on rm.resource_id = a.resource_id
and t.cal_date = a.date
where t.cal_date between ? and ?
and rm.end_date > getdate()
group by t.cal_date
,a.agent_name
,a.Phone_Login
,a.in_calls_handled
[Microsoft][ODBC SQL Server Driver] Syntax Error or access violation
[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index
I am not sure why one works and why the other doesn't. Please help.
The code for the failing query is:
select t.cal_date
,a.agent_name
,a.Phone_Login
,t.dialed_nbr
from opscall.dbo.cc_gen_transfers_dialed t
inner join OpsDataD.dbo.resource_master rm
on rm.network_id = t.transf_empid
join opscall.dbo.cc_single_source_agent a
on rm.resource_id = a.resource_id
and t.cal_date = a.date
where t.cal_date between ? and ?
and rm.location_nbr in (22)
and rm.end_date > getdate()
group by t.cal_date
,a.agent_name
,a.Phone_Login
,t.dialed_nbr
order by 1,2,4
The code for the working query is:
select t.cal_date,101)
,a.agent_name
,a.Phone_Login
,a.in_calls_handled as inb_calls
,sum(t.transfers) as transfers
from opscall.dbo.tempe_agent_transfers t
inner join OpsDataD.dbo.resource_master rm
on rm.network_id = t.network_id
join opscall.dbo.cc_single_source_agent a
on rm.resource_id = a.resource_id
and t.cal_date = a.date
where t.cal_date between ? and ?
and rm.end_date > getdate()
group by t.cal_date
,a.agent_name
,a.Phone_Login
,a.in_calls_handled