Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
Thanks
Hassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>
|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>
|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>

Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
ThanksHassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>

Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
ThanksHassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Id like to know when i have a query that returns say 12 million rows as
>> an example, is there an easy way to find out how much of data that is in
>> MB or GB ?
>> Thanks
>sql

Dynamic where in stored procedure help

Hi all,

I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample,
where field1= coalesce(@.parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?

Thanks,

JamesYou're on the right track. You'll find a great article at http://www.sommarskog.se/dyn-search.html.|||it is better then dynamic sql

you can use isnull function also|||I think it depends.

My understanding is that with dynamic WHERE clauses, SQL Server does not necessarily always store the most efficient execution plan; in the long run, this can hurt the performance of your query, especially if you have a ton of such dymanic elements in the where clause.

That said, I hate maintaining dynamic sql code.

ou might run some tests and see what you get performance-wise.|||But, on the OTHER hand, rebuilding an execution plan each time may be better for the particular combination of parameters than using the one excution plan that was built the first time for the query. E.g., if there are 10 parameters and the first time through, you enter values for all 10. An execution plan is built. Then, the normal user searches with one parameter most of the time. The original execution plan won't be ideal for that query.

It all depends. I went to a conference where some very good examples of both methods were displayed, profiled, etc. Very enlightening. Test and test some more for your particular case.|||I made some performance tests one day concerning the issue:

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

Dynamic WHERE & Dates

My dynamic sql correctly returns the top 10 dates with records if @.timeID
equals 1. I need the @.timeID equals 2 part to return records between
@.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
keep my example simple).
In the below section where @.timeID = 2, I'm getting a syntax error. Can
anyone help with the part below that tests IF @.timeID = 2 ? I just need some
quote help with that part of the where clause.
This runs on Northwind.
CODE ****************************************
***
declare @.SQL varchar(1000), @.typeID int, @.timeID int
declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
set @.typeID = 1
SET @.timeID = 1
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
set @.dtEndDate = @.dtMaxDate
SET @.SQL = 'SELECT Orders.OrderDate, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID
WHERE '
IF @.timeID = 1 /* Return last 10 days */
SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
@.dtMaxDate, 112) + ''''
IF @.timeID = 2 /* should Return records between start and end date */
PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
@.dtStartDate + ' AND '
+ CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
EXEC(@.SQL)Basically, I think your dynamic SQL should look like this when done:
=====
'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
@.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
CONVERT(CHAR(11), @.dtEndDate) + ''''
=====
From your example below, it looks like the CONVERT portion needs to be
within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
to the query with their values. Also, what date format are you expecting by
converting into CHAR(8)?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID
> WHERE '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott
I don't understand why would want to use dynamic sql but this is a fixed
code
-- PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + 'CONVERT (char(8), Orders.OrderDate, 112) >=''' +
convert(char(8),@.dtStartDate ,112)+ ''' AND '
+ 'CONVERT (char(8), Orders.OrderDate, 112) <='''
+convert(char(8),@.dtEndDate,112)+''''
BTW if IF @.timeID = 1, I got this error
Line 28: Incorrect syntax near '>'.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID
> WHERE '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott (sbailey@.mileslumber.com) writes:
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
Weren't you the guy with time constraints? Why then are you wasting the
time with building complete SQL strings, when using sp_executesql is so
much easier?
And why do you insist on not printing out the generated SQL? You could
at least include the generated SQL in your posts, so that it's easier for
people to spot the syntax errors.

> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
You have string concatanated with string, and then there is a comparison
operator in the middle of all this. That will obviously give a syntax
error.
And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
an WHERE clause. When you put a column in an expression, this prevents
any index from that column from being used. Thus, this can have a serious
impact on performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1. I'd really appreciate it if you would expand on your "avoid doing things
like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to pass dates
from ASP to my SPROCs as "11/24/2005". How or what syntax can I use on that
date format to avoid a performance hit?
2. As far as sp_executesql, I wanted to use that method, but the syntax is
more confusing that my method. If I good get some good simple examples, I'd
go that way.
3. My reason for going the way I am is if I hard coded every sql statement
with the matrix of variables I'm sending this SPROC, it would be 1,000 lines
or more. Normally they don't get this out of hand, but this one is for a
graph with lots of options.
4. I'll do better at printing the SQL from now on.
Thanks for any pointers on my date performance issue #1 above.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718610C29F6DYazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Weren't you the guy with time constraints? Why then are you wasting the
> time with building complete SQL strings, when using sp_executesql is so
> much easier?
> And why do you insist on not printing out the generated SQL? You could
> at least include the generated SQL in your posts, so that it's easier for
> people to spot the syntax errors.
>
> You have string concatanated with string, and then there is a comparison
> operator in the middle of all this. That will obviously give a syntax
> error.
> And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
> an WHERE clause. When you put a column in an expression, this prevents
> any index from that column from being used. Thus, this can have a serious
> impact on performance.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the efforts.
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:enDhqcL8FHA.1140@.tk2msftngp13.phx.gbl...
> Basically, I think your dynamic SQL should look like this when done:
> =====
> 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM
> Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
> @.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
> CONVERT(CHAR(11), @.dtEndDate) + ''''
> =====
> From your example below, it looks like the CONVERT portion needs to be
> within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
> to the query with their values. Also, what date format are you expecting
> by converting into CHAR(8)?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>|||Scott (sbailey@.mileslumber.com) writes:
> 1. I'd really appreciate it if you would expand on your "avoid doing
> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to
> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can
> I use on that date format to avoid a performance hit?
No, you don't have that pass dates that way from ASP. Even more you
shouldn't. That is not even a date as far as I am concerned.
The way to pass data from a client is to use parameters, and let the
the client interpret the date according to the regional setting. The is
then passed to SQL Server as a binary value, as dates are binary values
in SQL Server. (As they are in Windows, by the way.)
I don't do ASP, but in plain Visual Basic it looks something like:
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "dbo.some_sp"
cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
And obviously you are doing something like that already, as the date
variables in your example were declared as datetime, not as character.

> 2. As far as sp_executesql, I wanted to use that method, but the syntax
> is more confusing that my method. If I good get some good simple
> examples, I'd go that way.
SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
@.params = N'@.par1 int, @.par2 datetime'
EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
How is this more confusing that trying to get quotes and date formats
correct, something you appear to have big problems with.
As for writing dynamic searches, have you looked at my web site where
I have a longer article on the topic, with both static and dynamic methods?
http://www.sommarskog.se/dyn-search.html. There are also tips for people
that use EXEC().
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The part of sp_executesql that gets confusing is the N' syntax. Does N' mean
it's a text type?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||on your site in search_orders_1, for example, how does SQL know what
@.xorderid is when you only declare @.orderid at the beginning of the SPROC? I
see @.xorderid listed in the @.paramlist after you use it as a WHERE criteria,
but how does SQL know what @.xorderid is?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
It mean it is Unicode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:ut24UCY8FHA.3952@.TK2MSFTNGP12.phx.g
bl...
> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9718E396131E5Yazorman@.127.0.0.1...
>

dynamic table in report

hi,

i have a dataset which returns me a dynamic set of data ie. number of columns can be different....say 14 columns for some dates...and just 12 for some other...(pivot table is being used in the backend)...how can i present this in the table...also my column names are best suited to be the column headers in the table.....is it possible to create a table at runtime...?

Are your field names and the max # of fields returned in the dataset fixed? If so, you can define a table with N (N=max# of fields) columns (one field per column). Then set the visibility property on the table column to be based on the IsMissing property on the field http://msdn2.microsoft.com/en-us/library/ms157274.aspx|||it was..but the client wants it dynamic...columns r the active schemes..they can vary with time...though i am getting the number of schemes and their names..... its like...select activeschemes where date between report start date and end date ........pivot it and get other details..and return the dataset....|||i got it, made the columns into rows and used matrix in report....|||Just create a dataset and Mark visiability of column by =IIf(Fields!Collection.IsMissing=True,True,False) so ur report becomes dynamic.Column which is not there will gets hidden

dynamic table in report

hi,

i have a dataset which returns me a dynamic set of data ie. number of columns can be different....say 14 columns for some dates...and just 12 for some other...(pivot table is being used in the backend)...how can i present this in the table...also my column names are best suited to be the column headers in the table.....is it possible to create a table at runtime...?

Are your field names and the max # of fields returned in the dataset fixed? If so, you can define a table with N (N=max# of fields) columns (one field per column). Then set the visibility property on the table column to be based on the IsMissing property on the field http://msdn2.microsoft.com/en-us/library/ms157274.aspx|||it was..but the client wants it dynamic...columns r the active schemes..they can vary with time...though i am getting the number of schemes and their names..... its like...select activeschemes where date between report start date and end date ........pivot it and get other details..and return the dataset....|||i got it, made the columns into rows and used matrix in report....|||Just create a dataset and Mark visiability of column by =IIf(Fields!Collection.IsMissing=True,True,False) so ur report becomes dynamic.Column which is not there will gets hidden

dynamic table columns

I have a sproc that returns sales amount for each month in a given range of
dates. The date range is over the last 8 years, and will grow automatically
as time goes by.
I really do not want to create a table in RS with over 120 columns of data
and then set each of them to display/hide based on the parameters passed.
Matrix table doesnt seem to work for me as I dont want to un-pivot my data
just so it can pivot it back out.
Is there some solution to this problem? I have 5 base columns that will show
regardless of what yeear/month is passed, and usually anywhere from 12 to 48
result columns. is there a way for the table to recognize how many columns
are in the result set and create enough columns automagically?
Thanks!!On Dec 6, 4:03 pm, Carl Henthorn
<CarlHenth...@.discussions.microsoft.com> wrote:
> I have a sproc that returns sales amount for each month in a given range of
> dates. The date range is over the last 8 years, and will grow automatically
> as time goes by.
> I really do not want to create a table in RS with over 120 columns of data
> and then set each of them to display/hide based on the parameters passed.
> Matrix table doesnt seem to work for me as I dont want to un-pivot my data
> just so it can pivot it back out.
> Is there some solution to this problem? I have 5 base columns that will show
> regardless of what yeear/month is passed, and usually anywhere from 12 to 48
> result columns. is there a way for the table to recognize how many columns
> are in the result set and create enough columns automagically?
> Thanks!!
I guess I'm failing to see why you have to return all of the data
unaggregated to RS. Why not just limit the amount of months by
passing the correct parameters to your stored procedure? Then you
could just let the Matrix do its job and dynamically create the
columns.
If you absolutely have to do it this way, go to the Matrix Properties,
Filter tab, and add some log here to determine which of the Months
(that Group) you want to see. This will have the same effect as
setting the Visible on the columns. It will not speed up the data
fetch portion of your report.
If you could provide a little more detail as to what you are actually
trying to do, it would be helpful. What kind of groupings do you have
in the rows, since Months are your columns? Are you manually creating
multiple Rows, each with a different field in your dataset? There are
many ways to get to the same outcome.
-- Scott

Dynamic SQL help

I have a SP that returns a different dataset based on different id(parameter).
So that I can NOT get fields in the Report Builder.
Is there a way to have a "generic" Report that displays whatever it gets
from the SP.
In a simple "table" format.
If not, could you, please, get me some suggestions how to address this
problem.
One idea that I have in mind is to create Report Definitions dynamically...
I'd rather not to go there.. but..
Thank you very much!Can you UNPIVOT in the SPROC and then use a matrix in the report?
that would make the datatable always 3 columns... one for the row number,
one for the column name, one for the data.
I am just tossing this out there because I have not tried it.
"GoldenTamarin" <GoldenTamarin@.discussions.microsoft.com> wrote in message
news:298CC26A-E105-425B-B647-FD12EDDBD48A@.microsoft.com...
>I have a SP that returns a different dataset based on different
>id(parameter).
> So that I can NOT get fields in the Report Builder.
> Is there a way to have a "generic" Report that displays whatever it gets
> from the SP.
> In a simple "table" format.
> If not, could you, please, get me some suggestions how to address this
> problem.
> One idea that I have in mind is to create Report Definitions
> dynamically...
> I'd rather not to go there.. but..
> Thank you very much!

Dynamic SQL delete query problem

Hi all--Given a query that returns ~557K rows on SQL Server 2005 SP1:

SELECT distinct(a.[Import_Date]),b.[Import_Date],
a.[OS_USERNAME],b.[OS_USERNAME],a.[USERNAME],b.[USERNAME],
a.[TIMESTAMP],b.[TIMESTAMP]
FROM [DBA_AUDIT_SESSION] a, [DBA_AUDIT_SESSION] b
where a.[OS_USERNAME]=b.[OS_USERNAME]
and a.[USERNAME]=b.[USERNAME]
and a.[TIMESTAMP]=b.[TIMESTAMP]
and a.[Import_Date]<b.[Import_Date]

On a table that has the following definition:

TABLE [dbo].[DBA_AUDIT_SESSION](
[Import_Date] [datetime] NULL,
[INSTANCE_NAME] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOST_NAME] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OS_USERNAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERHOST] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TERMINAL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIMESTAMP] [datetime] NULL,
[ACTION_NAME] [varchar](27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOGOFF_TIME] [datetime] NULL,
[LOGOFF_LREAD] [float] NULL,
[LOGOFF_PREAD] [float] NULL,
[LOGOFF_LWRITE] [float] NULL,
[LOGOFF_DLOCK] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSIONID] [float] NULL,
[RETURNCODE] [float] NULL,
[CLIENT_ID] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSION_CPU] [float] NULL

I would like to delete duplicate data from this table and keep only the data with the latest Import_Date. I came up with the following delete statement:

delete FROM [DBA_AUDIT_SESSION]
FROM [DBA_AUDIT_SESSION] a,
inner join [DBA_AUDIT_SESSION] b
on a.[TIMESTAMP]=b.[TIMESTAMP]
where a.[Import_Date]<b.[Import_Date];

The command parses successfully, but I get the following runtime error:
'Table DBA_AUDIT_SESSION is ambiguous.'

Does anyone have suggestions on how to fix this delete statement?

Use the TSQL update/delete statement syntax sparingly. The TSQL specific syntax can produce unexpected results if used incorrectly or performance problems also. You can use the standard DELETE statement syntax like:

-- assumes that import_date is unique per timestamp

delete [DBA_AUDIT_SESSION]

where Import_Date < (select max(a.Import_Date) from [DBA_AUDIT_SESSION] as a

where a.TimeStamp = [DBA_AUDIT_SESSION].TimeStamp)

|||This seems to do the trick, and meets all the criteria for cleanup needed. Thanks!