Moving report from Access to Reporting Services

I created 2 stand alone queries and another that combines/joins the 2 for a
cumulative repoirt in MS Access. I need to recreate the combined report in
Reporting Services but do no know how:
Query 1 MON _is Down Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Query 2 MON_is Up Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Combined Query
SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
AHD_net_res.nr_prim_search_key
FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
[MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
Thank you,
KarenI wanted to add the query in Reporting Services I have created:
SELECT DISTINCT
AHD.call_req.ref_num, MIN(DISTINCT
AHD.act_log.time_stamp) AS MINOftime_stamp, MIN(DISTINCT
AHD.act_log.time_stamp) AS [Time Stamp Down],
DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Time Down], DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)) AS [Time Up], MAX(DISTINCT act_log_1.time_stamp)
AS [Time Stamp Up], MAX(DISTINCT act_log_1.time_stamp)
AS MaxOftime_stamp, AHD.net_res.nr_prim_search_key
FROM AHD.net_res RIGHT OUTER JOIN
AHD.call_req ON AHD.net_res.id =AHD.call_req.affected_rc LEFT OUTER JOIN
AHD.act_log act_log_1 ON AHD.call_req.persid =act_log_1.call_req_id LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid =AHD.act_log.call_req_id
WHERE (AHD.act_log.action_desc LIKE '%to ''MON-System Down%') AND
(act_log_1.action_desc LIKE '%to ''MON-System Up%')
GROUP BY AHD.call_req.ref_num, DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)), AHD.net_res.nr_prim_search_key
ORDER BY AHD.net_res.nr_prim_search_key, AHD.call_req.ref_num
--The query works up to a point. I changed "First" to "Min" and "Max". I
am trying to weed out multiple instances of time_stamp when action_desc
inlcludes Mon is Up or Mon is Down. In a perfect world the user would enter
each only once but you know how that goes. So, I want the earliest time_stamp
when action_desc includes "to Mon-System Down" and Max time_stamp when
action_desc includes "to Mon-System Up".
Thanks in advance for any help with this.
Karen
"Moving rpts from Access to Rptg Services" wrote:
> I created 2 stand alone queries and another that combines/joins the 2 for a
> cumulative repoirt in MS Access. I need to recreate the combined report in
> Reporting Services but do no know how:
> Query 1 MON _is Down Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Query 2 MON_is Up Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Combined Query
> SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
> Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
> AHD_net_res.nr_prim_search_key
> FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
> [MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
> JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
> JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
> Thank you,
> Karen

moving logins and password to new server

Hi Freinds,
SQL2000
I am using sp_help_revlogin to create the script for account to recreate on
new server.
It is ok, but when I am trying to add a server role or add database access
to new users on new servre , it is asking me the password for verification.
But on the old server it is not like this, no password asked for adding a
new server role.
Any idea?
Thanks
PatHOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Patrick" wrote:

> Hi Freinds,
> SQL2000
> I am using sp_help_revlogin to create the script for account to recreate o
n
> new server.
> It is ok, but when I am trying to add a server role or add database access
> to new users on new servre , it is asking me the password for verification
.
> But on the old server it is not like this, no password asked for adding a
> new server role.
> Any idea?
> Thanks
> Pat
>
>|||Are you attempting to make these changes using Enterprise Manager ? If
so, see "FIX: You are prompted for password confirmation after you
change a standard SQL Server login"
at http://support.microsoft.com/default.aspx/kb/826161
From MS page - Sympton:
After you install MS03-031: Cumulative Security Patch for SQL Server,
when you make changes to a standard SQL Server login by using Enterprise
Manager, you are prompted for a password, even if you did not change the
password. If you did not change the password, you cannot successfully
close the dialog box, regardless of the entry that you use.
*** Sent via Developersdex http://www.examnotes.net ***