Showing posts with label asking. Show all posts
Showing posts with label asking. Show all posts

Tuesday, March 27, 2012

get @@trancount for all users/ connections URGENT

Hi all,

Is there any way to get the @.@.trancount for a connection from outside the connection?

The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.

So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)

So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.

Thanks in advance,

Gert-Jan

In 2005, you can use this query to see that information.

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

|||The DB is on 2K, do you have this script for 2K?|||

Yeah, it is in sysprocesses. The column is open_tran

select spid, open_tran

from master..sysprocesses

|||

Louis,

Thanks for the very quick response, i'll get back to you if you saved the day.

Regards Gert-Jan

|||

Hi Gert-Jan van der Kamp,

> So another theory is that a certain sproc began a transaction, but never finished because of an error.

> (We had some strange timeouts as well, so this is quite plausible.)

I think you are looking for open transactions. It that case, the "select" statement provided by Louis (hope we can have your new book about DMVs and DMFs soon), will not help you much because that session will not have a match in sys.dm_exec_requests. You can find an example in BOL, under the topic about "sys.dm_exec_sessions".

This is from BOL.

SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );

Forgot to mention that in SS 2000 your choice is to use "dbcc opentran".AMB

sql

Wednesday, March 21, 2012

Generating report using batch process

Hi all,

I have tried asking the same question in other forums. All i get is links Sad

Please help me. I have the following requirement:

I have the following tables:
Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

I want to generate reports that will tell me the revenue generated for each theater in each location in a state. I want to run a batch process which will loop through the 3 tables and will passing the location and state id as parameters one by one. I want each report to be generated as a pdf and stored in a location. How do I do this?

Thanks.

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50JTongue Tiedqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

The change that you will make to the standard script is in the Define a query to retrieve subscriber data section. You will just add to the standard query a join to your three tables and use the query results as parameters to the report.

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

The reason that you are getting links is that people are willing take the time to give assistance and pointers but not specific solutions. The problem you describe is an interesting one and one that will I will now probably try and cover in my blog in the near future. When I do, I will post the link here and you should be able to get code that is closer to a complete solution.

Larry Smithmier

|||

dear larry,

thanks for the link.

vidkshi

Sunday, February 19, 2012

Generate A Report Via Login

To all you experts out there, I have one hurdle I need help on.
I've created a SQL report which has 3 parameters asking for a from_date, a
to_date & an employee. The report then returns all projects, tasks,
yadda-yadda, for the employee, within the date range from a time tracking
database. Very usefull come performance review time.
My intention is to make this report available on Sharepoint via a link (or
Report Manager), where whoever logs into sharepoint (or Report Manager) can
access this report for themselves and no one else, meaning they click on the
report and get prompted for the date range parameters but not the employee
parameter. The report should recongize who is accessing the report by virtue
of their login & find the corresponding employee information in the database
& display the report for that employee.
Should this functionality be implemented in the SQL report or should it be
implemented within Sharepoint? Based on that question, how can this
functionality be implemented?
Thank you very much for the help.I assume you want to have the manager get all of his employees. You would
need a table somewhere with this relationship. Then use the global variable
User!UserID. This is the user running the report. Your query parameter does
not have to be mapped to a report parameter. Click on the ... for the
dataset, parameters tab, right side of list select expression which takes
you to the expression builder. Note that User!UserID returns the user with
the domain i.e. domain\userid
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> To all you experts out there, I have one hurdle I need help on.
> I've created a SQL report which has 3 parameters asking for a from_date, a
> to_date & an employee. The report then returns all projects, tasks,
> yadda-yadda, for the employee, within the date range from a time tracking
> database. Very usefull come performance review time.
> My intention is to make this report available on Sharepoint via a link (or
> Report Manager), where whoever logs into sharepoint (or Report Manager)
> can
> access this report for themselves and no one else, meaning they click on
> the
> report and get prompted for the date range parameters but not the employee
> parameter. The report should recongize who is accessing the report by
> virtue
> of their login & find the corresponding employee information in the
> database
> & display the report for that employee.
> Should this functionality be implemented in the SQL report or should it be
> implemented within Sharepoint? Based on that question, how can this
> functionality be implemented?
> Thank you very much for the help.|||A manager can get to his employees (individually) as the report exists (with
a report parameter for the emp_name).
I completely forgot about the userid. Good news is I can match up the userid
to the substring of a field already being returned (emp_name), but I don't
quite follow how to set it up as a query parameter. Do I create a new dataset
or create this query parameter on the dataset for my existing result set
details(where emp_name exists)? Can you give me a hint on what the syntax
would look like?
Thanks
"Bruce L-C [MVP]" wrote:
> I assume you want to have the manager get all of his employees. You would
> need a table somewhere with this relationship. Then use the global variable
> User!UserID. This is the user running the report. Your query parameter does
> not have to be mapped to a report parameter. Click on the ... for the
> dataset, parameters tab, right side of list select expression which takes
> you to the expression builder. Note that User!UserID returns the user with
> the domain i.e. domain\userid
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> > To all you experts out there, I have one hurdle I need help on.
> >
> > I've created a SQL report which has 3 parameters asking for a from_date, a
> > to_date & an employee. The report then returns all projects, tasks,
> > yadda-yadda, for the employee, within the date range from a time tracking
> > database. Very usefull come performance review time.
> >
> > My intention is to make this report available on Sharepoint via a link (or
> > Report Manager), where whoever logs into sharepoint (or Report Manager)
> > can
> > access this report for themselves and no one else, meaning they click on
> > the
> > report and get prompted for the date range parameters but not the employee
> > parameter. The report should recongize who is accessing the report by
> > virtue
> > of their login & find the corresponding employee information in the
> > database
> > & display the report for that employee.
> >
> > Should this functionality be implemented in the SQL report or should it be
> > implemented within Sharepoint? Based on that question, how can this
> > functionality be implemented?
> >
> > Thank you very much for the help.
>
>|||Thanks Bruce...Got It. I created the UserID parameter as @.EmployeeName &
refered to that parameter in the data set as;
WHERE pe_date between @.FromDate AND @.ToDate
AND ('DOMAIN\' + Substring(descr,3,25)) = @.EmployeeName
OR ('DOMAIN\' + Substring(descr,1,1) + Substring(descr,3,25)) = @.EmployeeName
and it works just great. Thanks for shaking the cobwebs loose.
JD
"Bruce L-C [MVP]" wrote:
> I assume you want to have the manager get all of his employees. You would
> need a table somewhere with this relationship. Then use the global variable
> User!UserID. This is the user running the report. Your query parameter does
> not have to be mapped to a report parameter. Click on the ... for the
> dataset, parameters tab, right side of list select expression which takes
> you to the expression builder. Note that User!UserID returns the user with
> the domain i.e. domain\userid
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> > To all you experts out there, I have one hurdle I need help on.
> >
> > I've created a SQL report which has 3 parameters asking for a from_date, a
> > to_date & an employee. The report then returns all projects, tasks,
> > yadda-yadda, for the employee, within the date range from a time tracking
> > database. Very usefull come performance review time.
> >
> > My intention is to make this report available on Sharepoint via a link (or
> > Report Manager), where whoever logs into sharepoint (or Report Manager)
> > can
> > access this report for themselves and no one else, meaning they click on
> > the
> > report and get prompted for the date range parameters but not the employee
> > parameter. The report should recongize who is accessing the report by
> > virtue
> > of their login & find the corresponding employee information in the
> > database
> > & display the report for that employee.
> >
> > Should this functionality be implemented in the SQL report or should it be
> > implemented within Sharepoint? Based on that question, how can this
> > functionality be implemented?
> >
> > Thank you very much for the help.
>
>