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.
Author |
Topic |
neelamsethi
Starting Member
1 Post |
Posted - 2007-10-19 : 13:29:45
|
I need to run a report using a linked server on SQL 2005. The report was running really slow so I tried doing an OPENQUERY which makes it a lot faster. However, I need to pass some parameters and not sure how to do it using OPENQUERY. Here is the query:SELECT RTRIM(client.ClientID) AS ClientID, client.name2 AS Client_Name, Project.Project, Project.StatutoryDueDate AS DueDate, Project.Extension1, Project.Extension2, Project.StartDate AS AsofDate, Project.PromiseDate AS CommitmentDate, Project.ReceivedDate AS InfoIn, Task.TaskID, Task.Empid, Task.ActualStartDate, Task.ActualFinishDate, Client.Partner, STAFF.PersonalTitle AS PIC, Client.Manager, MGR.PersonalTitle AS TIC, Client.Accountant, ACCT.PersonalTitle AS AIC, CASE WHEN Task.TaskID = 'PREP' THEN EMP.personaltitle END AS Prep_BY, CASE WHEN Task.TaskID = 'REV' THEN EMP.personaltitle END AS Rev_By, CASE WHEN Task.TaskID = 'PREP' THEN Task.ActualStartDate END AS Prep_Date, CASE WHEN Task.TaskID = 'REV' THEN Task.ActualStartDate END AS Rev_Date, CASE WHEN Task.TaskID = 'MAIL' THEN Task.ActualFinishDate END AS Mailed_Date, CASE WHEN Task.TaskID = 'TESNT' THEN Task.ActualFinishDate END AS TE_OUT, CASE WHEN Task.TaskID = 'TERCD' THEN Task.ActualFinishDate END AS TE_INFROM PROJECT, PROJCUS, TASK LEFT OUTER JOIN STAFF EMP ON TASK.EMPID = EMP.EMPID, CLIENT LEFT OUTER JOIN STAFF ON CLIENT.Partner = STAFF.EmpID LEFT OUTER JOIN STAFF MGR ON CLIENT.Manager = MGR.EmpID LEFT OUTER JOIN STAFF ACCT ON CLIENT.Accountant = ACCT.EMPIDWHERE CLIENT.ClientID = PROJECT.ClientID AND CLIENT.Engagement = PROJECT.Engagement AND PROJECT.Project = PROJCUS.Project AND PROJECT.Engagement = PROJCUS.Engagement AND PROJECT.ClientID = PROJCUS.ClientID AND CLIENT.ClientID = TASK.ClientID AND CLIENT.Engagement = TASK.Engagement AND PROJECT.Project = TASK.Project AND CLIENT.DroppedDate IS NULL AND ((PROJCUS.[~Custom35])='BT') AND (CLIENT.OfficeID in (@OfficeID)) AND (PROJECT.Project in (@Project)) |
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-19 : 23:57:47
|
Tried with dynamic query? |
 |
|
|
|
|
|
|