How to Determine, how much recordsets returns Query?

Hi all!

Is there any chanse to determine in Transact-SQL, how much recordsets/rows already returned by currently executing query? I don't need count of rows affected by last statement (that @.@.ROWCOUNT returns), but ones, really returned to SQL-Client.

To understand, what I need it for, please see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1707794&SiteID=1#1715230

Solution found!

See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1716062&SiteID=1&mode=1#1716062

How to determine sp caller current database?

When executing a stored procedure that is defined in another database, as:

USE db1;

EXEC db2.dbo.sproc;

Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

If nothing else, you can use CONTEXT_INFO to retain that information.

To call the stored procedure you can do something like:

Code Snippet

USE db1;

declare @.binVar varbinary(128)
set @.binVar = convert(varbinary(128), 'db1')
set context_info @.binVar

EXEC db2.dbo.sproc;

and to fetch the information from within the stored procedure you can use something like:

Code Snippet

convert(varchar(128), context_info())

( This is assuming that db_name() is not working for you. )

sql

How to determine ROWCOUNT without executing the select statement

I'm selecting data from a large table in a paged manner by using the
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb

How to determine package's executing folder?

I should have written this down when I came across it -- but I recently saw an example of how to determine a package's currently-executing folder. Can anyone help?

Thanks!

- Mike

Can you elaborate on what you mean by "the current executing folder"?

-Jamie

|||

Jamie Thomson wrote:

Can you elaborate on what you mean by "the current executing folder"?

-Jamie

Thanks, Jamie -- sure, the folder where the DTSX resides as it executes.

Here's the situation -- the DBAs want to put all the packages I've prepared into a single folder.

The "main" package calls each of 42 "sub-packages", one after another. I'm planning to use the
Execute Package task for each of the 42, and set it to "File system" to reference each of the 42.

The Connection property of the Execute Package task wants the path to the DTSX file the Execute Package
task runs, and I'm not sure how to set this at runtime. Because all the packages will live in the same arbitrary
folder, I hoped I might be able to use an expression or something to set the Connection property properly.

Thanks for asking!

- Mike

|||

mike.groh wrote:

Jamie Thomson wrote:

Can you elaborate on what you mean by "the current executing folder"?

-Jamie

Thanks, Jamie -- sure, the folder where the DTSX resides as it executes.

Here's the situation -- the DBAs want to put all the packages I've prepared into a single folder.

The "main" package calls each of 42 "sub-packages", one after another. I'm planning to use the
Execute Package task for each of the 42, and set it to "File system" to reference each of the 42.

The Connection property of the Execute Package task wants the path to the DTSX file the Execute Package
task runs, and I'm not sure how to set this at runtime. Because all the packages will live in the same arbitrary
folder, I hoped I might be able to use an expression or something to set the Connection property properly.

Thanks for asking!

- Mike

Right. Well as far as I know theres no way for the package to discover where it "lives". The way I solve this is for each package to have a variable called RootFolder which gets set by an indirect configuration. I've talked about it on my blog quite a bit:

Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)

Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)

Indirect configurations gotcha
http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

-Jamie

|||

Thanks! I'll check it out in your blog.

- Mike