Solved

SQL Query fails on SSRS 2005

Posted on 2012-03-30
20
564 Views
Last Modified: 2012-04-06
I posted this question (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27655535.html) today regarding using a like in a join for SQL. The solution provided I was able to use - and get it to work in SQL 2005.

However, when I put it into SSRS 2005 I get errors and it isn't happy when ever I posted the code for the union.

The code (section) that works looks like:
left outer join [ServerName].HPD_HelpDesk HPD
on HPD.Summary like '%' + caption + '%'

Whenever I add this to my SSRS query it barfs.

The error looks like:
Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.

Additional Information:
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
0
Comment
Question by:edrz01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
20 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788128
post full query. we need to find which column is generating this error

Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
0
 

Author Comment

by:edrz01
ID: 37788262
Had to attach as a pdf....
SSRS-Query.pdf
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788354
can you post as text! the pdf one looks like it is image... i cannot copy paste and analyze in my tools...
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:edrz01
ID: 37788359
I wish I could but I can't. It came from another system and all I could do is scan it and put it as a pdf.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788379
but that join does not make any sense? are you trying to use some columns from HPD.summary after adding that breaking code? also what is caption datatype? what is hpd.summary datatype?
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788384
ok, fine... what are the datatypes for

HPD.Summary
caption

in this part:

HPD.Summary like '%' + caption + '%'
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788390
when you do this, does it break?

on convert(varchar(max),HPD.Summary) like '%' + convert(varchar(max),caption) + '%'
0
 

Author Comment

by:edrz01
ID: 37788394
HainKurt, I am trying to convert the PDF to text with an online OCR reader. I hope to post it in a few once I clean it up.

As for your last post the join is supposed to look in the 'summary' field in the HPD_HelpDesk table for any occurance of the value from the Caption field.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788395
also looks like you need to add another condition here

left outer join [ServerName].HPD_HelpDesk HPD
on HPD.someCol=sometable.somecol and HPD.Summary like '%' + caption + '%'

without that condition, that code does not make any sense to me...
0
 

Author Comment

by:edrz01
ID: 37788411
Not sure how to answer that. As I said it works ok in my SQL Studio when I uncomment the test values.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788413
are you trying to do something like this?

with 
a as (select 1 id, 'a' caption),
b as (select 'a bb cc d' summary union select 'aa bbbb cccc')
select * from a left outer join b on b.summary like '%'+a.caption+'%'

id	caption	summary
1	a	a bb cc d
1	a	aa bbbb cccc

Open in new window

0
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 37788436
also try this:

left outer join [ServerName].HPD_HelpDesk HPD
on charindex(caption, HPD.Summary)>0
0
 

Author Comment

by:edrz01
ID: 37788445
Sorry, that last one really confused me.

Let me give you an example....

The main part of the query looks at the Events table and figures out all events that occured between the @StartDate and the @EndDate.

Sample:
Caption, EventTime, OutageInMinutes, Uptime, NumMinPeriod, CountOfAssets.....
------------------------------------------------------------------------------------------------------------
ABC1234, 0212-03-11 04:19:34, 168, 99.996, 43200, 71 ......
------------------------------------------------------------------------------------------------------------

I then want it to look at the HPD_HelpDesk Summary field to see if the Caption name shows up 'anywhere' in the Sumamry field (hence the like).

The Summary field might contain something like:
"The ABC1234 system is not working. Please open ticket...."
0
 

Author Comment

by:edrz01
ID: 37788454
Trying your "also try this:

left outer join [ServerName].HPD_HelpDesk HPD
on charindex(caption, HPD.Summary)>0
" post now....
0
 

Author Comment

by:edrz01
ID: 37788459
... looks like it is working in SQL Studio.

I know need to test in SSRS.....
0
 

Author Comment

by:edrz01
ID: 37788498
Nope, same thing. I soon as I paste it in and try to preview it craps out - same error.

I even deleted any commented out stuff (at top) to make sure it wasn't that.
0
 

Author Comment

by:edrz01
ID: 37788589
HainKurt,

Doing some more testing I think the problem might be deeper than I thought and the new join might not be the problem.

While it works in SQL Studio and works in SSRS without the new code section when I go the Preview and deploy it I get an error when I try to just run the code in the Data mode.

I will have to research this further but will go ahead and give you the points as I think I can use the code idea you posted.

left outer join [ServerName].HPD_HelpDesk HPD
on charindex(caption, HPD.Summary)>0
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788712
does this make sense to you?

I converted the SP to use Table Variables instead of temp tables and it works now.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37788739
but still i do not get what column do you want from this hpd table

left outer join [ServerName].HPD_HelpDesk HPD
on charindex(caption, HPD.Summary)>0

will you add some columns into your select part from hpd table? if yes, try

left outer join (select summary from [ServerName].HPD_HelpDesk) HPD
on charindex(caption, HPD.Summary)>0

maybe there are some columns in this table which creates problems...
0
 

Author Closing Comment

by:edrz01
ID: 37816721
Sorry, accepted the wrong solution
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question