Get Table name from another query result

I have a table as like below:

Table AuditLog(
Reference varchar(10) [primary key],
TableName varchar(10),
ModifiedColumn varchar(10),
ChangeData varchar(max),
TableReference varchar(10)
)

I am trying to achieve following query:

select *,
    (select accountNumber from [AuditLog.TableName] where Reference = AuditLog.TableReference ) 
    from AuditLog

AuditLog table column 'TableName' contain table name of that database. I need to load accountnumber from that table base on TableReference.

What's the right way to have table name populated dynamically?

SSIS Execute SQL Task Stored Procedure returning empty result sets

I have a simple stored procedure that returns 1 row with 3 columns. I am trying to create an SSIS package that reuses the values in these columns later. If I run a simple select query everything is happy but when I run the stored procedure I get the following error:

[Execute SQL Task] Error: Executing the query "rs_UpdateMemberExtract" failed with the following error: "Unable to populate result columns for single row result type. The query returned an empty result set.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've set up an ado.net connection and the proc runs correctly in SSMS, I have the task set up like follows:

ConnectionType: ADO.NET Connection: ServerName.connectionName SQLSourceType: Direct Input SQLstatment: Name of Proc (rs_UpdateMemberExtract) IsQueryStoredProcedure: True

I have each parameter mapped to a variable and the direction set to Output

I have the name of each column that should be returned in the result set and mapped to the associated variable.

The task works if I set the ResultSet to 'None',

Any ideas What I've missed?

Thanks

Second maximum value by using dense rank function

i have table like

name marks raja 88 ravi 88 karthik 99 praveen 99 vijay 70

in that table i assumee the rank of the table is '

name   marks rank 
raja    88    1
ravi    88    2
karthik  99   3
praveen  99   4
vijay    70   5

iget that by using dense rank function

select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D order by marks desc

but what i need is from that bove thabe i need to get second lowest rank i mena the output i want is "praveen 99 4"

i tried to get thesecond lowest rank but i cant get the all the columns ,i tried thse query

select max(a.ranks) as b from (
select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D ) as a where a.ranks not in
(

select max(a.ranks) as b from (
select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D ) as a )

Query to get report name, reports first and last run time and date, how often it was run and method of delivery (email or locations)

I am new to sql server.

I have been trying to write a query to get report name, reports first and last run time and date, scheduled by ,how often it was run and method of delivery (email or locations) for SQL Server 2005.

Please help me with this.

How to check what type of lock an UPDATE statement uses?

How can I determine if an update statement puts a table lock, page lock, or row lock on a table?

dateadd() is not working as desired to find the date of 7 days from now sql-server

I am trying to find the date of 7 days from a specific date. To do this I tried

Select @Fromdate = DATEADD(day,-7, max(last_update)), @todate= max(last_update)
FROM vwABC

But it is not working and when I tried the following every thing works fine.

SELECT @Fromdate = '20150601', @ToDate = MAX(last_update)
FROM vwABC

I cant understand, whats the problem. Please help.

SQL Server - cumulative sum on overlapping data - getting date that sum reaches a given value

In our company, our clients perform various activities that we log in different tables - Interview attendance, Course Attendance, and other general activities. I have a database view that unions data from all of these tables giving us the ActivityView that looks like this. As you can see some activities overlap - for example while attending an interview, a client may have been performing a CV update activity.

+----------------------+---------------+---------------------+-------------------+
| activity_client_id   | activity_type | activity_start_date | activity_end_date |
+----------------------+---------------+---------------------+-------------------+
|                  112 | Interview     | 2015-06-01 09:00    | 2015-06-01 11:00  |
|                  112 | CV updating   | 2015-06-01 09:30    | 2015-06-01 11:30  |
|                  112 | Course        | 2015-06-02 09:00    | 2015-06-02 16:00  |
|                  112 | Interview     | 2015-06-03 09:00    | 2015-06-03 10:00  |
+----------------------+---------------+---------------------+-------------------+

Each client has a "Sign Up Date", recorded on the client table, which is when they joined our programme. Here it is for our sample client:

+-----------+---------------------+
| client_id | client_sign_up_date |
+-----------+---------------------+
|       112 | 2015-05-20          |
+-----------+---------------------+

I need to create a report that will show the following columns:

+-----------+---------------------+--------------------------------------------+
| client_id | client_sign_up_date | date_client_completed_5_hours_of_activity |
+-----------+---------------------+--------------------------------------------+

We need this report in order to see how effective our programme is. An important aim of the programme is that we get every client to complete at least 5 hours of activity as quickly as possible. So this report will tell us how long from sign up does it take each client to achieve this figure.

What makes this even trickier is that when we calculate 5 hours of total activity, we must discount overlapping activities:

In the sample data above the client attended an interview between 09:00 and 11:00.
On the same day they also performed CV updating activity from 09:30 to 11:30. For our calculation, this would give them total activity for the day of 2.5 hours (150 minutes) - we would only count 30 minutes of the CV updating as the Interview overlaps it up to 11:00.

So the report for our sample client would give the following result:

+-----------+---------------------+--------------------------------------------+
| client_id | client_sign_up_date | date_client_completed_5_hours_of_activity |
+-----------+---------------------+--------------------------------------------+
|       112 | 2015-05-20          | 2015-06-02                                 |
+-----------+---------------------+--------------------------------------------+

So my question is how can I create the report using a select statement ? I can work out how to do this by writing a stored procedure that will loop through the view and write the result to a report table. But I would much prefer to avoid a stored procedure and have a select statement that will give me the report on the fly.

I am using SQL Server 2005.