[Microsoft][ODBC Driver Manager] Data source name too long"

[Vikas038]

Hi Team,

I am facing strange situation with alteryx designer. For one of the script, which is running fine in athena, is throwing error "[Microsoft][ODBC Driver Manager] Data source name too long" for me.

Few points for your understanding are:-

* I used to refer other scripts earlier and didn`t get this error before

* for each table i add complete information about data base object for reference like awsdatacatalog.customer_intelligence.TableName

Please help me to resolve this that where is the issue. I am attaching screen shot for your reference.

Thanks,

[AbhilashR]

Hi @Vikas038, pardon me if I am asking some pedestrian questions. What happens if you just type TableName in your FROM statement as opposed to explicitly having awsdatacatalog.customer_intelligence in the table name? If you still get an error using just the TableName, can you cut down your SQL to use only one of the many Select statements and see if that helps you troubleshoot?

[Vikas038]

Hi Abhilash,

As i said i worked without any issue with some other sample scripts. But giving this error when i enter below script. Although, below script with hard coded dates is also running fine in athena.

Script for your reference is :-

select username as UserName,first_name as FirstName,contractid as ContractID,last_name as LastName,email as Email,phone as Phone,city as City
,state as State,country as Country,Region
,Companyname,Companytype,MM, Sum(Views) Views from

(select distinct upper(userid) as userid,username,first_name,contractid,last_name,email,phone,city,state,country,Region
,accountname as Companyname,accounttype as Companytype from awsdatacatalog.customer_intelligence.view_crdb_mdc_contract) A

left outer join

(select userid,MM,sum(Views) as Views from
(select upper(userid) as userid,date_format(eventtime,'%Y-%c') as MM,count(eventtime) as Views
from awsdatacatalog.customer_intelligence.fact_wt_mdc_contentgroupviewevent a
where contentgroup ='ATAG_MFRA'
and eventtime between date'@Startdate'
and Case When Date'@Enddate' < Date'2019-08-05' Then Date'@Enddate' Else Date'2019-08-05' End
GROUP BY upper(userid),date_format(eventtime,'%Y-%c')
union all
select upper(userid) as userid,date_format(eventtime,'%Y-%c') as MM,count(eventtime) as Views
from awsdatacatalog.customer_intelligence.fact_wt_mdc_referringsourceevent a

where (a.ReferringSource in ('MFRA~PreSetMedians','MFRA~EntitySearch','Export~ATAG_MFRA~Financials-AsAdjusted'
, 'ATAG_MFRA~Financials~View~AsReported', 'MFRAHS~MFRA~Financials~Help','MFRA~RelatedDocuments','ClearSearch~MFRA')
or A.ReferringSource like '%~MFRA~Financials~RunQuery~EntitySearch'
or A.ReferringSource like '%~MFRA~Financials~RunQuery~QRATEQuery'
or A.ReferringSource like 'MFRA%EntitySearch%'
or A.ReferringSource like 'MFRA%PreSetMedians%')
and eventtime between date'@Startdate'
and Case When Date'@Enddate' < Date'2019-08-05' Then Date'@Enddate' Else Date'2019-08-05' End
GROUP BY upper(userid),date_format(eventtime,'%Y-%c')
union all
select upper(login_id) as userid,date_format(date_time,'%Y-%c') MM,count(a.clickstream_key) as Views
from awsdatacatalog.customer_intelligence.fact_adobe_mdc_pageload a
inner join dim_date b
on a.date_id = b.date_id
and b.date_time between Case When Date'@Startdate' > Date'2019-08-06'
Then Date'@Startdate' Else Date'2019-08-06' end and date'@Enddate'
where page_url='https://www.moodys.com/moodysmfra#/'
GROUP BY upper(login_id),date_format(date_time,'%Y-%c')) A
group by userid,MM) B
on upper(A.userid)=upper(B.userid)
where A.contractid='@Contract'
group by username,first_name,contractid,last_name,email,phone,city
,state,country,Region
,Companyname,Companytype,MM

[Vikas038 · Accepted solution]

Please close this ticket, Abhilash.

I tracked solution for this issue. Although, it was strange to get mentioned error message because of it.

For your reference, below are steps that i followed to crack it:-

1. Tested each sub part of complete code

2. found one of sub code which was causing the error

3. In that code, value 'MFRA|Txt|C-Left|||%|MFRA%' was the culprit. Also, after removing pipelines from this value I was able to validate code in alteryx without any issue.

I hope this will be helpful for you as well to provide inputs in similar kind of queries.

Thanks,

Vikas