Have SQL Report IP Of Query-er

Sep 17, 2007

I am trying to come up with a way to save the IP of the last computer that ran an update in our servers. I don't want to have to re-write the .net code, so I am wondering if there is a way, using functions available in SQL 2000 that I can get the IP of the machine running the update.

I know that SQL server 'knows' this information because it can log it in it's log files, but I don't know how to get at it from a stored procedure.

Thanks.

View 1 Replies


ADVERTISEMENT

SQL Query Help -for Ad Hoc Report

Nov 18, 2005

Need your help to write a SQL query:

My mgr wants to know
- # of times a particular kind of service (Service ID - AAA) was done on a particular kind of program (Program ID - SP) in 2005
- what is the Service percentage? For example, service was done 40% of the time
- Average cost of the service for those programs that had it

There are more than 100 tables linked together.

Please help..

View 3 Replies View Related

Report Query

Jul 27, 2005

I am trying to create a query which should return 4 columnsFacility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicantsI would be glad to even have just 3 columns since, the Total can be computedin the display table (ColdFusion interface)Facility | NoOfActiveApplicants | NoOfArchivedApplicantsSo far I have the following query, which returns just 2 rows with bothNoOfActiveApplicants & NoOfArchivedApplicants under the same column.Am stuck here, any help is apprecialted. Thanks in advance!selectNoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end), Facility = case when c.facility is null then c.JobDBFacilityName else c.facility endfrom tblapplicant a, tblJobDB b, tblfacilities cwhere a.jobid = b.jobidand b.facility = c.facilityidgroup by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lityunion allselectNoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end), Facility = case when c.facility is null then c.JobDBFacilityName else c.facility endfrom tblArchiveapplicant a, tblArchiveJob b, tblfacilities cwhere a.JobVacancyNumber = b.JobVacancyNumberand b.facility = c.facilityidgroup by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lityorder by facility

View 2 Replies View Related

Query For A/R Aging Report

Aug 20, 2001

How can I write a query to output the balances of unpaid invoices into aging columns? I have invoices, lineitems, and payments tables. I have a query that will join the 3 tables to determine which invoices are unpaid. I need to write an aging report in Access 2000 with the unpaid invoices listed with the balance under the proper heading (ie:<30, 30-60, 60+). Any ideas on how to do that? I can write a query to pull one date segment at a time but I can't get them all into one query with different headings to base a report on. My customer had it working in Access 2000 but he upgraded to SQL Server 7 with an Access 2000 Project as a front end and we can't get it to work. It's driving me crazy! TIA!!!

View 2 Replies View Related

Perf Report Query

Sep 22, 2005

CREATE TABLE [Perf] ([TransId] INTEGER,[FileNo] VARCHAR(80),[TimeInSeconds] INTEGER,[FileSizeMB] INTEGER,[FileName] VARCHAR(255),[StartDate] datetime)Ok!! Here's the Problem. I am working on a perf stats report. FileType is First 9 chars of the field FileName. I need to compare a similar filename from this month to last month or before ordered by file size. Maybe this is a very simple query but currrently my mind refuses to work. Seeking F1.

View 3 Replies View Related

Query Report By HOUR

Sep 11, 2014

I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far.

AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh,[datetime]) = 1430 AND DATEPART(hh [datetime]) = 2230

View 13 Replies View Related

Query/Report Help Needed

Jul 23, 2005

Hello all,I need to do the following task repeatedly. I have been doing itmanually until now and I am thinking if there is any way I couldautomate the whole process.Here is the task:For a selected group of tables, I need to create an Excel file with oneworksheet per table. The worksheet contains information about thetable.Here is an Example of Person table with fields PersonId, LastName,FirstName ...Column Name -> PersonId LastName FirstName ...Type -> char char char ...Length -> 6 50 50Data -> P00001 Smith John .... P00002 Smith Joan ...

View 1 Replies View Related

How To Query From Report Model

Jan 31, 2008

Hi, it's my first time for using report model as the datasource of reporting service. But I don't know how to build the query string to get data from the report model.
Can anyone help me?

Thanks

View 3 Replies View Related

Report Builder Query

Feb 15, 2007

Can we view the query that is fired on the Report builder to debug as i am finding some error in data...

View 3 Replies View Related

From Query - Report Parameter

Nov 28, 2007

What needs to be done to allow a user to input data in a "From query report parameter"?

The "From query" option was used to show all possible selections the user can do but what if the user would now like to select for partial key...i.e.: like '%tss%'

I would like to avoid the Multi-Value parameter option.

NOTE: The report is done through Microsoft Visual Studio / RDL.

Thanks,

View 2 Replies View Related

Query/Report For 'Owner' Of All SQL Jobs

Nov 3, 2006

Is there a query/report that I can run against all our SQL servers to see who the 'owner' of each job is?

If so, can this be run in SQL Server Enterprise Manager 8, SQL Server Management Studio or Hyena?

View 2 Replies View Related

Query To Create View / Report?

Apr 27, 2012

I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.

ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communicatio

[code]....

Query in DESIGN View

SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication

[code]....

Results from QUERY in DESIGN VIEW

TYPE DATECOMPIDCOMMIDSEQ
AWAY2011-02-24 00:00:00.00051747531
RETURN2011-03-31 00:00:00.00051747542
AWAY2011-03-28 00:00:00.00064740681
RETURN2011-04-30 00:00:00.00064752972

[code]....

RESULTS FROM QUERY in SELECT TOP 11

TypeDateCompIDCommIDSeqAwayDaysReturnDate
AWAY2011-06-20 00:00:00.00016977564182011-06-28 00:00:00.000
AWAY2011-12-23 00:00:00.00015059050441122012-04-13 00:00:00.000
AWAY2011-10-09 00:00:00.0003534839461162011-10-25 00:00:00.000
AWAY2012-01-27 00:00:00.0003983890363942012-04-30 00:00:00.000
AWAY2012-03-09 00:00:00.0004064900615202012-03-29 00:00:00.000

[code]....

QUERY in DESIGN VIEW

SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN

[Code] .....

QUERY RESULTS IN SELECT

csTranscsTranDateCompanyIDComm_IDNameCommIDSeq
CS12009-07-27 00:00:00.0001176147Harrison Bailey 761471
CS32007-08-27 00:00:00.0002673777Dorothy Wheeler 737771
CS42011-12-02 00:00:00.0002685087Dorothy Wheeler 850872
CS52012-01-01 00:00:00.0002685446Dorothy Wheeler 854463

[Code] ....

View 12 Replies View Related

Query To Produce A Report In Apex

Jan 24, 2014

I am trying to add to a query to produce a report in Apex. Currently I have this

SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,

[Code] ....

I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?

View 3 Replies View Related

No Query-results Shown In My Report

Oct 17, 2007

hi,

i have a problem with a report. if i try it with vs2005 and the preview it works.
if i call the report from my web-application i get no error-message, but i only get my
headlines and no query-results.

i use reportingservices 2005, vs2005 and .net 1.1
my datasource is an olap-cube.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ReportExecution.ExecutionInfo ei = repExecution.LoadReport(ReportPath, historyID);



repExecution.SetExecutionParameters(convertToReportExecutionParameter(Parameters), "de-AT");

result = repExecution.Render(Format, devInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

do you have any idea?
thank you

harald

View 1 Replies View Related

Writing Query In Report Builder

Oct 15, 2007



Hi,

I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.

Thanks,
S Suresh

View 3 Replies View Related

Report Builder Query Timeout?

Dec 29, 2005

Is their a way when creating a Report Builder model to set a query timeout?  Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
 
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance.  This has nothing to do with a report being published to a report server and setting a reports execution time limit.

View 6 Replies View Related

SSRS Report Query Problem

Apr 9, 2008

Hi,

I am newbie and i am working on SSRS to create some reports.

I have a reprot where i am displaying ProviderNos in the first column and based on provider i need to get some other information from another database and display it in the same line with Provider.

Below is my report format.

ProviderNo Speciality
1111111 A
2222222 B
3333333 C

The problem is, PrivderNos are from different database so i am getting it in separate Dataset. And Speciality is in Different Database so i am getting it in separate dataset. But i want both of them to be displayed in same table on report.

Below is the query for ProviderNos:
Select Prv_No from server1.database1.dbo.Providers where MemberNo='xxxxxx'

Below is the quer for getting Speciality:
Select Speciality from server2.database2.dbo.Prv_SP where prv_no IN (@prvno)

If i am assigning SpecialityDS to the table, it displays correct values for each n every provider. But i dont know how do i make it in such a way that it displays the result as above format.

View 3 Replies View Related

Displaying DataSet Query In Report

Oct 16, 2007

Is there a way to create an expression that references the DataSets / Queries? I'd like to be able to create an expandable text box that shows the raw sql query used to generate the report.

View 4 Replies View Related

Report Distribution Based On SQL Query In RDL

Apr 6, 2007



I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager:






Mgr ID
Manager Name

1
John Q Manager

2
Jane Q Manager

3
Jack O Lantern



I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution.



Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?

View 1 Replies View Related

Slow Report From Simple Query

Apr 2, 2007

Hi all, I've been building a set of traffic based reports on our website and I've run into a strange problem.

The reports are pretty basic, and up till now I've been really impressed with RS overall.

Recently I've added a StartDate and EndDate and since then the performance has gone from ~10 secs to ~10 minutes.

I've taken a really simple query from my reports. Running this query in Management Studio on the same data returns in less than a second. When its run from a test report with nothing else in it it takes ~1 minute. Even stranger when I run the same query with the same values for parameters inside of RS in the data view it takes less than 1 second. ARG!


SELECT COUNT(DISTINCT SessionID) as Occurences
FROM WebAppSummary
JOIN WebAppLocalizations
ON WebAppSummary.ClientIp = WebAppLocalizations.ClientIp
where FirstTime BETWEEN @StartDate AND @EndDate


The last line that was just added is this part:
where FirstTime BETWEEN @StartDate AND @EndDate

So whats going on here? Is this a really poorly performing query that management studio is optimizing but RS isnt? Is RS messing up the databind and getting a bunch of DSs instead of just one?

View 1 Replies View Related

Change Query Of Report In Reportviewer

May 8, 2007

Hi,



I have a reporting services report that i show in an asp.net page by a reportviewer control.

Is there a property that i can set in my asp.net page, to change the query for changing the data in my report.

The number and names of the columns will be the same.



I should work with parameters, but the query changes more than just a few where-clauses. So i think working with parameters looks impossible.





Thanks,

Dennis











View 3 Replies View Related

Query For Outstanding Invoice Amount Report

Apr 3, 2008

Hi all - struggling a bit with this one... I have made an accounts application and need to make a report called "Aged Debtors". It lists all of the clients with outstanding invoices and breaks them down into periods:

current (this month)
period 1 (last month)
period 2 (2 months ago)
period 4 (invoices raised 3 months ago or more)

My tables are as follows (simplified)

Clients
clientid int
clientname varchar(100)

invoices
invoiceid int
clientid int
invoicedate datetime
cinvoiceamount decimal(19,2)
paid int (1 = paid, 0 = unpaid)
paidamount decimal(19,2) (how much of this invoice has been paid)

I need the following columns in the report

clientname invoiceid current period1 period2 period3+
test client 1 £100 - - -
2 £50 - - -
3 - £100 - -
test client2

I can format all the report using a matrix in SSRS so just need the stored proc to get me going - any help greatly appreciated!
Thanks guys,
Stephen.

View 5 Replies View Related

Query With Transpose - Create A Report Out Of Database

Nov 3, 2014

I need to create a report out of our database.

Simplified example:

Table 1:

LINK_ID Item
123456 Item1
123457 Item2
123458 Item3
123459 Item4

Table 2

Link_IDPROP_IDProperty
1234561Property_1
1234562Property_2
1234563Property_3
1234564Property_4

[Code] ....

When I Join this 2 tables i get:

LINK_IDItemPROP_IDProperty
123456Item11Property_1
123456Item12Property_2
123456Item13Property_3
123456Item14Property_4

[Code] ....

But I need:

LINK_IDItemPROP_ID1PROP_ID2PROP_ID3PROP_ID4
123456Item1Property_1Property_2Property_3Property_4
123457Item2Property_1Property_2Property_3Property_4
123458Item3Property_1Property_2Property_3Property_4
123459Item4Property_1Property_2Property_3Property_4

View 3 Replies View Related

Unable To See A Named Query In The Report Model

Dec 5, 2006

Hi guys,

I added a Named Query on my existing data source view and I recreate the report model, however, I am unable to see the Named Query on the Report Model, I can see only the tables.

Does anybody has any idea?

Sincerely,

View 1 Replies View Related

Please Give Me Your Suggestions On Report Query - Very Urgent

Oct 25, 2006

I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.

"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"

ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.

Thanks,

Malar











View 6 Replies View Related

Wrting A Query For A Report With Different Time Periods

Jan 10, 2008



There are three tables Loans, CDs and Account.

I want to create a report based on above three tables
It looks like as follows;
















Program Summary

Yesterday/Current
MTD
Previous Month
YTD




Amount
Number
Amount
Number
Amount
Number
Amount
Number

Portfolio












Loans Commenced

$
#
$
#
$
#
$
#


Loans Closed

$
#
$
#
$
#
$
#


CDs Commenced

$
#
$
#
$
#
$
#


CDs Closed

$
#
$
#
$
#
$
#















Loan table - Amount, State
CD - Amount, State
Account - CommencedOnDate(date time), closeondate(date time)

Yesterday - Loans/CDs started yesterday(calander date..not last 24 hours)
MTD - " " First of this month to yesterday
Previous Month - " " during previous month
YTD - " " Loans/CDs started from 1 of this year to yesterday

Loan and cd states are Commenced and Closed

Based on this I have to create a report. But this time format seems to be really complex.
Can anyone help me to write the query pls?

View 7 Replies View Related

Report On Access Database Using Parameters In Query

Nov 16, 2007

Hi all,

I am trying to create a report against an Access 2003 database using the provider as follows: "Provider=Microsoft.Jet.OLEDB.4.0;"

Simple reports with no parameters are fine (e.g. using a text query of 'Select * from BERs'

But how do I pass in a report parameter to limit the results coming back ? I have only used stored procs in the past which work fine but how to achieved this using Access has me stumped.

An example query I need would be 'Select * from BERs WHERE Year = [Year?]'

Anyone got any pointers ?

Thanks in advance

Steve

View 7 Replies View Related

Transaction Report By Weekly----Plz Help To Write Query

Nov 30, 2007



Hi Guys,
I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.






W1
W2
W3
W4
W5
W6

OKC
79
38
50
76
35
47

NFL
0
0
45
43
33
28

LA

5

12

10

0

0

10

Total
79
38
95
119
68
75


Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.













W37
W38
W39
W40
W41
W42
W43
W44
W45

OKC
80









OKC

38








OKC


95







OKC



119







OKC




68





OKC





75




OKC






74



OKC







70


OKC








59

OKC










OKC










LA










And i wrote query like this,

select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count
from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut
on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl
on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm
on rl.bm_code=bm.bm_code inner join dbo.Market m
on bm.market_id=m.market_id
where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market)
group by m.Market_name,ut.creation_date
order by m.Market_name desc

Could you please some one help me to get this correctly.



Thanks in advance


Thanks
San

View 2 Replies View Related

Query Parameters Not Recognized By Report Designer

Feb 17, 2007

No matter how I try to enter an automatic query parameter it just isn't recognized as such. I get missing expression errors from the SQL syntax check and no report variables are generated. There must be something incredibly simple that I am missing....

The most recent query string I've entered is

SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = @Facility

Thanks for anyone who can help..

View 2 Replies View Related

Named Query Not Appearing In Report Builder

Aug 20, 2007

I'm having a heck of a time with named queries. At this point I just want a proof of concept in my DSV. I have a bunch of tables added to the DSV, and I would like to add a named query to it so the user can pick from the name query in report builder. What has to be done to make it appear in the report builder?

View 1 Replies View Related

This Feature Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Report

Jun 16, 2006

SQL server 2005 express reporting problem.

error message:

This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service

I got this error message when I try to connect to database hosted in another PC running SQL server 2000.

Is it true that SQlL server Express can only use Local Database Engine to host the database?



View 5 Replies View Related

Sales Report - Previous Year Stats In Same Query?

Aug 6, 2004

Hello,

I am creating a sales report that needs to display Sales statistics for a selected month grouped by Material. Also, it needs to display the same stats for the selected month of the previous year. Finally, it needs to display Year To Date Statistics for the current year and previous year up to and including the selected month.

Currently, I am using 3 queries to do this. The first one gets the statistics the current month grouped by material. The others sprocs get their corresponding information by me passing in the material number and the month and doing the appropriate sum. So, essentially, for a single line of the report, 3 queries are being done. The problem arises in that sometimes there may be as many as 300 materils displayed in a given month, which amounts to alot of db activity and a long delay loading the report.

Is there anyway to get this information in one swoop using some fancy aggregation?

Thanks,
Jake

View 14 Replies View Related

SQL Server 2012 :: Write A Query To Generate A Report?

Mar 25, 2014

I want to write a query to generate a report. I have a date column which will be holding all the business dates. No dates of Saturday and Sunday are allowed. What I am looking for is, I want to get the result of every 5th business day of each month. A month could start with any day. I just want only the 5th business day.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved