try there reverse then
Do you have a sql editor? TOAD or SQL Server Management Studio, or something like that?
You should be able to test the query there to see if there are error in the query. Find the temp guid replace it and run the query.
LEFT OUTER JOIN EpicTest10.Erp.TranGLC T5 ON
T3.Company = T5.Company
and T5.Key1 = cast(T3.PONum as varchar)
and T5.Key2 = cast(T3.POLine as varchar)
and T5.Key3 = cast(T3.PORelNum as varchar)
AND T5.RelatedToFile = ‘PORel’"
I am not quite sure how you mean. I use SQL Server Management Studio.
I created a query like this that works:
select T5.GLAccount, * from erp.PORel T3
LEFT OUTER JOIN EpicorTest10.Erp.TranGLC T5 ON
T3.Company = T5.Company
and T5.Key1 = cast(T3.PONum as varchar)
and T5.Key2 = cast(T3.POLine as varchar)
and T5.Key3 = cast(T3.PORelNum as varchar)
AND T5.RelatedToFile = 'PORel'
Since you mentioned GUID, I don’t think I am doing what you asked.
Could you be a bit more specific, please?
Your help is definitely appreciated.
Thanks,
Shawn
I am attaching what I mean.
You need to write the query in the SSRSTest (or what you call the report database), not from the Epicor Database. If you have issues let me know. We can connect offline.
Hey @knash ,
This worked on my test set-up once I tracked down that one of the fields was excluded. I fixed and it worked fine on the report.
I tried to recreate on my production but it blew up.
I did the test on productions SSRS server and it is giving me
'Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Epicor10.Erp.TranGLC’.
Epicor10 is our Production database name.
The big difference is the the test environment is contained on one server and Production is split across several servers.
The SSRS server is different from the SQL server.
Is my problem that I need to somehow to get it to point to the Epicor10 database on my EpicSQL server?
How would I do that?
I appreciate the help, I think I am almost there.
Thanks,
Shawn
you will need to change the
LEFT OUTER JOIN EpicorTest10.Erp.TranGLC T5
to
LEFT OUTER JOIN EpicorProd10.Erp.TranGLC T5
Try this in SQL Server Management Studio on the report server. You might get a permissions error, which you will then have to resolve on the DB side.
You might need to add the servername as well as the database name in your call.
I did the change to the production database name to Epicor10 and it was then that I got the error mentioned in my last post.
If I add the servername ‘EpicSQL’ to it I get:
Msg 7202, Level 11, State 2, Line 1
Could not find server ‘EpicSQL’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
My complete query on the EPICSSRS server is:
select * from
[dbo].[PORel_17682f6ecb1d4415949b65222f553af8] T3
left outer join EpicSQL.Epicor10.Erp.TranGLC T5 on
T3.Company = T5.Company AND
T5.key1 = cast(T3.PONum as varchar) and
T5.key2 = cast(T3.POLine as varchar) and
T5.key3 = cast(T3.PORElNUm as varchar) AND
T5.RelatedToFile = 'PORel'
Check out this link
https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server
OK @knash,
Latest in my saga…
I added the Linked Server EpicSQL and ran the test SQL I posted above and it worked fine from the EpicSSRS server.
I then modified my report the same way I did the test database report. I run the report and the subreport errors out.
Is there something specific on the Production report that would be different from the test report that worked?
Is there some type of link I need to create elsewhere to get it to work for the report?
Thanks,
Shawn
You can run the SQL on the production SSRS database, but not through the Report Builder?? I am just showing you some google searches. This is not something I do everyday.
something like this?
Thanks . The issue has been resolved. I modified the Linked Server configuration RPC and RPC Out as true which was false initially.
I set those to True and tried again, still no go. Not sure why it works from the SSRS server but not the Epicor Client print preview report.
I am copying the exact SQL query that works from SSRS into the dataset of the report, with no modifications.
Blows my mind.
If anything else occurs to you or anyone else who has had this issue, please let me know. I really appreciate it.
Thanks,
Shawn
It works in test though…
Yep, where test has both SQL and SSRS installed in the same server…
Does it won on the Prod SSRS Server in Studio?
If so what is the SQL? Please post the sql you are using in the reports as well.
Thanks,
It does not work in Studio.
SQL working from SSRS server:
select T5.GLaccount, * from
[dbo].[PORel_a67ffbdd75e940c4968bfc6a81a86e84] T3
left outer join EpicSQL.Epicor10.Erp.TranGLC T5 on
T3.Company = T5.Company AND
T5.key1 = cast(T3.PONum as varchar) and
T5.key2 = cast(T3.POLine as varchar) and
T5.key3 = cast(T3.PORElNUm as varchar) AND
T5.RelatedToFile = ‘PORel’
Dataset Expression (select trimmed for brevity):
=“SELECT T5.GLAccount,T3.TranType
FROM POHeader_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PONum = T2.PONUM
LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.PONUM = T3.PONum AND T2.POLine = T3.POLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID
LEFT OUTER JOIN EpicSQL.Epicor10.Erp.TranGLC T5 on
T3.Company = T5.Company AND
T5.key1 = cast(T3.PONum as varchar) and
T5.key2 = cast(T3.POLine as varchar) and
T5.key3 = cast(T3.PORelNum as varchar) AND
T5.RelatedToFile = ‘PORel’"
If there is anything else you need to see, let me know.
Have you tried to create a view in the SSRS Database? Have the view be something like
Select * from EpicSQL.Epicor10.Erp.TranGLC
Then rewrite the query to reference the local view. I am guessing it is a datasource issue within SSRS. Hard for me to say as we have both databases on the same machine.
Hey @knash,
You can stop. I got it working.
I had to dig in the logs and keep trying different things but I finally narrowed it down to the Linked Server’s login to the EpicSQL server.
I had to change the Security section of it to ‘Be made using this security context’ and they find the user and pass to the EpicSQL local login account. Using any other user and logging didn’t seem to work.
After that, I ran the test and poked through the logs. When I realized the logs for the current run were next to non-existent, I looked and the PDF and realized it worked!
I can’t thank you enough for your help and patience @knash. If not for you, I would still be trying to figure out how to get the string and integers to agree with each other, lol.
You might need to add a New data Source in SSRS
http://YOURSSRSSERVER/Reports/Pages/Folder.aspx?ViewMode=Detail
fill in the information. and then add the datasource to the SSRS report in the report builder.
Great!!! Let us know what you did.
I did in my previous reply, I decided to edit it to keep the information in the same area.
Sounds good. make sure to mark the post complete as well. Then folks will know not to try and send you workarounds like me. 
