backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980sThere isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:
> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
--
David G.|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:
> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backup
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
> > Hello:
> >
> > Could someone please share with me the exact syntax to use within Query
> > Analyzer for backing up a single table, rather than a whole database?
> >
> > Thanks!
> >
> > childofthe1980s

What is Query Analyzer's replacement in SQL Server 2005?

Both Query Analyzer and Enterprise Manager are gone from SQL Server 2005. In their place is a single tool, SQL Server Management Studio. This tool has most of the features of its predecessors, but with an upgraded UI and a lot of improved functionality. I think that most DBAs will find it to be a good upgrade. If you'd like more information on some of the new features with this tool, read my article on SQL Server Management Studio client tool enhancements.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1146801,00.htmlPAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
Andrew J. Kelly SQL MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> guys,
> what is the difference between AWE and PAE?
|||for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:

> PAE allows the OS to use more than 4GB of memory. AWE allows the
> application to use more than 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>
>
|||AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...[vbcol=seagreen]
> for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> "Andrew J. Kelly" wrote:
|||in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:

> AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
> want SQL Server to utilize > 4GB memory, you need both settings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>
>
|||boot.ini should have something like this:
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
Enterprise" /fastdetect /pae /3gb
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.)
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager.
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example):
exec sp_configure 'awe enabled', 1
reconfigure
go
Then you have to restart the SQL instance as the AWE setting only takes
affect on server startup. Also, when SQL Server is using AWE memory, it
cannot use dynamic memory management. It *will not swap pages out of
memory *if another app requests memory and the OS doesn't have enough to
satisfy the request (unlike the SQL dynamic memory manager). So you
should specify a "max server memory" amount with sp_configure. For
example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
remaining 1GB to the OS & other apps, you would do this in QA:
exec sp_configure "max server memory", 5120
reconfigure
go
All this is documented in SQL BOL:
http://msdn.microsoft.com/library/de...onfig_3stg.asp
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
AWE & memory management.
HTH
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
rupart wrote:
[vbcol=seagreen]
>in that case...in a server with 5G of ram
>should i put the /AWE /PAE swith in the same line in the boot.ini?
>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>how do i check it has 5G? Also, is there any significant on it? The
>performance shd be better i suppose
>Thank you
>"Tibor Karaszi" wrote:
>
|||does /3g means the system will allocate 3g for system and the rest for
sql(that is after enabling thru AWE, rite?)?
yeah, good link...thank you
"Mike Hodgson" wrote:

> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just check
> the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only takes
> affect on server startup. Also, when SQL Server is using AWE memory, it
> cannot use dynamic memory management. It *will not swap pages out of
> memory *if another app requests memory and the OS doesn't have enough to
> satisfy the request (unlike the SQL dynamic memory manager). So you
> should specify a "max server memory" amount with sp_configure. For
> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
> remaining 1GB to the OS & other apps, you would do this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...onfig_3stg.asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
> AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>
|||The other way around. 3 GB for the application and 1 GB for the system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:4944B057-1DC7-4638-BB88-9040FDCC9B9F@.microsoft.com...[vbcol=seagreen]
> does /3g means the system will allocate 3g for system and the rest for
> sql(that is after enabling thru AWE, rite?)?
> yeah, good link...thank you
> "Mike Hodgson" wrote:
|||Oops - slight typo in my "max server memory" statement. To set a max
server memory of 4GB you would run:
exec sp_configure "max server memory", 4096
reconfigure
go
The 5120 figure I included in my previous post would try to set it at
5GB (not 4GB).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just
> check the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only
> takes affect on server startup. Also, when SQL Server is using AWE
> memory, it cannot use dynamic memory management. It *will not swap
> pages out of memory *if another app requests memory and the OS doesn't
> have enough to satisfy the request (unlike the SQL dynamic memory
> manager). So you should specify a "max server memory" amount with
> sp_configure. For example, on your 5GB box, if you wanted to allocate
> 4GB to SQL and the remaining 1GB to the OS & other apps, you would do
> this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...nfig_3stg..asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff
> on AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:

Avoiding WHERE IN

This query seems to run particularly slowly, is there a way to rewrite
it without using IN?
id fruit_name
14 Apple
34 Apple
37 Banana
56 Banana
66 Banana
78 Pear
103 Orange
select * from my_table
where id in (
select max(id)
from my_table
group by fruit_name )
Result
==================
id fruit_name
34 Apple
66 Banana
78 Pear
103 Orange
Cheers!SELECT A.* FROM MyTable A
INNER JOIN
(SELECT max(id) as Id FROM MyTable) B
ON A.id = B.Id
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<davidol@.hushmail.com> wrote in message
news:1109567370.806709.130280@.o13g2000cwo.googlegroups.com...
> This query seems to run particularly slowly, is there a way to rewrite
> it without using IN?
> id fruit_name
> 14 Apple
> 34 Apple
> 37 Banana
> 56 Banana
> 66 Banana
> 78 Pear
> 103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
> Result
> ==================
> id fruit_name
> 34 Apple
> 66 Banana
> 78 Pear
> 103 Orange
>
> Cheers!
>|||An index on (fruit_name) or perhaps (fruit_name, id) or (fruit_name, id
desc)
will help, I think.
You can also try some alternative was of writing this.
select fruit_name, max(id)
from my_table
group by fruit_name
select * from my_table
where not exists (
select * from my_table as mtcopy
where mtcopy.fruit_name = my_table.fruit_name
and mtcopy.id > my_table.id
)
select fruit_name, id
from my_table
where id = (
select top 1 id
from my_table as mtcopy
where mtcopy.fruit_name = my_table.fruit_name
order by id desc
)
Steve Kass
Drew University
davidol@.hushmail.com wrote:

>This query seems to run particularly slowly, is there a way to rewrite
>it without using IN?
>id fruit_name
>14 Apple
>34 Apple
>37 Banana
>56 Banana
>66 Banana
>78 Pear
>103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
>Result
>==================
>id fruit_name
>34 Apple
>66 Banana
>78 Pear
>103 Orange
>
>Cheers!
>
>|||SELECT MAX(id), fruit_name
FROM my_table
GROUP BY fruit_name
However, this probably won't do much to speed up the query. Can you give
actual DDL, including any indexes? (See http://www.aspfaq.com/5006
On 2/28/05 12:09 AM, in article
1109567370.806709.130280@.o13g2000cwo.googlegroups.com,
"davidol@.hushmail.com" <davidol@.hushmail.com> wrote:

> This query seems to run particularly slowly, is there a way to rewrite
> it without using IN?
> id fruit_name
> 14 Apple
> 34 Apple
> 37 Banana
> 56 Banana
> 66 Banana
> 78 Pear
> 103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
> Result
> ==================
> id fruit_name
> 34 Apple
> 66 Banana
> 78 Pear
> 103 Orange
>
> Cheers!
>

Avoiding time-outs

The C++ application calls the database to look up property data. One
troublesome query is a function that returns a table, finding data which
is assembled from four or five tables through a view that has a join,
and then updating the resulting @.table from some other tables. There
are several queries inside the function, which are selected according
to which parameters are supplied (house #, street, zip, or perhaps parcel
number, or house #, street, town, city,...etc.). If a lot of parameters
are provided, and the property is not in the database, then several queries
may be attempted -- it keeps going until it runs out of queries or finds
something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
some failure cases, depending on the distribution of data. (~100 mil
properties in the DB) Some queires operate on the assumption the input data
is slightly faulty, and take relatively a long time, e.g., if WHERE
ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
all this is going on the application may decide the DB is never going to
return, and time out; it also seems more likely to throw an exception the
longer it has to wait. Is there a way to cause the DB function to fail if
it takes more than a certain amount of time? I could also recast it as
a procedure, and check the time consumed after every query, and abandon
the search if a certain amount of time has elapsed.

Thanks in advance,
Jim Geissmanjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

You don't give any information about your version of MSSQL, and the
client library you're using, but you may be able to set a suitable
timeout period on the client side. Alternatively, look at the "query
governor cost limit Option" in Books Online - this terminates queries
that run for more than a given number of seconds.

Simon|||jim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

See "remote query timeout Option" in the help text. However, relying
on this may cause inconsistent bahaviour.

This design pattern can also lead to heavy load on your database.

As a suggestion, have two separate sets of queries, one that assumes
good data (should be much quicker which you want to use most times?)
and one that may have incorrect data (will be slower, but not used
very often). In you screen have a checkbox to indicate what search
option to use. Alternatively perform better validation on the data
before submitting the form.|||That sounds interesting. I'll look into it.

> You don't give any information about your version of MSSQL, and the
> client library you're using, but you may be able to set a suitable
> timeout period on the client side. Alternatively, look at the "query
> governor cost limit Option" in Books Online - this terminates queries
> that run for more than a given number of seconds.
> Simon

Avoiding Temporary Tables

Hello All,
I have a question about how I might avoid using a temporary table in a sql
query. In a query I am writing I am trying to extract data from a table o
n
an existing database via a set of identifier codes (These queries are writte
n
from the Matlab environment and the list of identifiers are easily accesible
in matlab).
The query I am currently using looks something like this...
select t.var1, t.code, t.var2
from pinf t
join
( SELECT distinct s.pcode as code
FROM prc s
WHERE s.code in (74156, 54471) ) -- identifiers 74156, 54471
as prctable on prctable.code = t.code
and '12/06/2005' >= t.dt1 and t.d2 <= '12/06/2005'
with the identifier being 74156, 54471. The problem is there is not a
convenient table with all of the identifiers in it. So the solution above
where I draw from an existing table is not sufficient.
I believe that I can create a temporary table with these identifiers in it,
but the performance of the query slows markedly.
Is there some other way to extract the data with these identifier from the
table pinf without creating a temporary table?
Any help would be greatly appreciated.
Cheers,
Lanny>> ... with the identifier being 74156, 54471.
Is this a set of two identifier values or a single comma separated
identifier value with two numbers in them?
I am not familiar with the matlab environment, but how exactly do you
extract the identifiers to the #temp table? The general alternatives ( which
in many cases may not be sufficient ) are using a view or even a base table
which can have all the required identifiers.
Anith|||It is a set of 2 distinct identifier, and conceptually I would want to look
for many more than 2 (perhaps on the order of thousands) of these distinct
identifiers.
I extract the identifiers via another query that I know functions properly
and will give me the desired identifier. I looked into using a base table
or a view, and did not believe they provided the functionality I needed.
Essentially, I have some list of numbers and I want to extract that list, bu
t
all of the elements of this list do not exist on any distinct table in the
database.
Thanks so much for the help.
"Anith Sen" wrote:

> Is this a set of two identifier values or a single comma separated
> identifier value with two numbers in them?
>
> I am not familiar with the matlab environment, but how exactly do you
> extract the identifiers to the #temp table? The general alternatives ( whi
ch
> in many cases may not be sufficient ) are using a view or even a base tabl
e
> which can have all the required identifiers.
> --
> Anith
>
>|||On Wed, 7 Dec 2005 10:18:03 -0800, Lanny wrote:

>It is a set of 2 distinct identifier, and conceptually I would want to look
>for many more than 2 (perhaps on the order of thousands) of these distinct
>identifiers.
>I extract the identifiers via another query that I know functions properly
>and will give me the desired identifier. I looked into using a base table
>or a view, and did not believe they provided the functionality I needed.
>Essentially, I have some list of numbers and I want to extract that list, b
ut
>all of the elements of this list do not exist on any distinct table in the
>database.
>Thanks so much for the help.
Hi Lanny,
I'm not sure if I understand your requirements completely, but based on
what I do understand, you might find what you need on Erlands site:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

avoid Timeout expired?

Hi,
When I execute the "Delete" statement in Query for large database, it
prompts the error
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I know there is setting to configurate it, but I couldn't find out
which one. can anyone tell me please? thanks
--
GinolaThis is a client side configuration issue (ODBC really...)
from query analyzer... you can control this from tools - options -
connections - query time out
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403c13f7.8256406@.msnews.microsoft.com...
> Hi,
> When I execute the "Delete" statement in Query for large database, it
> prompts the error
> "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> I know there is setting to configurate it, but I couldn't find out
> which one. can anyone tell me please? thanks
>
> --
> Ginola|||Thanks, I found it. my setting is 0 under QueryTime out, it should
mean unlimited, right ' But I do get the error message
"[Microsoft][ODBC SQL Server Driver]Timeout expired "
when deleting a lot of record under EM !! Any idea?
>This is a client side configuration issue (ODBC really...)
>from query analyzer... you can control this from tools - options -
>connections - query time out
--
Ginola|||Hi,
"0 is Unlimited".
Try to delete the records from Query Analyzer using Delete statement. Here
delete statement will be faster if you have indexes on columns used in where
clause of delete statement.
Thanks
Hari
MCDBA
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> >This is a client side configuration issue (ODBC really...)
> >
> >from query analyzer... you can control this from tools - options -
> >connections - query time out
> --
> Ginola|||Hi,
I am really really new in SQL server and I haven't had much experience
in using Query Analyzer. Most of my experience is from MS-Access
If I write the following, will it work ?
Use DBName
go
Delete * from [TableName]
go
Why we don't use delete under EM instead ?
On Wed, 25 Feb 2004 13:04:58 +0530, "Hari" <hari_prasad_k@.hotmail.com>
wrote:
>Hi,
>"0 is Unlimited".
>Try to delete the records from Query Analyzer using Delete statement. Here
>delete statement will be faster if you have indexes on columns used in where
>clause of delete statement.
>Thanks
>Hari
>MCDBA
>
>"Ginola" <ginola@.mailcity.om> wrote in message
>news:403d30ed.15670093@.msnews.microsoft.com...
>> Thanks, I found it. my setting is 0 under QueryTime out, it should
>> mean unlimited, right ' But I do get the error message
>> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
>> when deleting a lot of record under EM !! Any idea?
>>
>> >This is a client side configuration issue (ODBC really...)
>> >
>> >from query analyzer... you can control this from tools - options -
>> >connections - query time out
>> --
>> Ginola
>
Ginola|||Enterprise Manager does not look at the timeout settings used by QA. There
is no way to change the default timeout setting for EM...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> >This is a client side configuration issue (ODBC really...)
> >
> >from query analyzer... you can control this from tools - options -
> >connections - query time out
> --
> Ginola

avoid Timeout expired?

Hi,
When I execute the "Delete" statement in Query for large database, it
prompts the error
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I know there is setting to configurate it, but I couldn't find out
which one. can anyone tell me please? thanks
GinolaThis is a client side configuration issue (ODBC really...)
from query analyzer... you can control this from tools - options -
connections - query time out
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403c13f7.8256406@.msnews.microsoft.com...
> Hi,
> When I execute the "Delete" statement in Query for large database, it
> prompts the error
> "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> I know there is setting to configurate it, but I couldn't find out
> which one. can anyone tell me please? thanks
>
> --
> Ginola|||Thanks, I found it. my setting is 0 under QueryTime out, it should
mean unlimited, right ' But I do get the error message
"[Microsoft][ODBC SQL Server Driver]Timeout expired "
when deleting a lot of record under EM !! Any idea?

>This is a client side configuration issue (ODBC really...)
>from query analyzer... you can control this from tools - options -
>connections - query time out
Ginola|||Hi,
"0 is Unlimited".
Try to delete the records from Query Analyzer using Delete statement. Here
delete statement will be faster if you have indexes on columns used in where
clause of delete statement.
Thanks
Hari
MCDBA
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> --
> Ginola|||Hi,
I am really really new in SQL server and I haven't had much experience
in using Query Analyzer. Most of my experience is from MS-Access
If I write the following, will it work ?
Use DBName
go
Delete * from [TableName]
go
Why we don't use delete under EM instead ?
On Wed, 25 Feb 2004 13:04:58 +0530, "Hari" <hari_prasad_k@.hotmail.com>
wrote:

>Hi,
>"0 is Unlimited".
>Try to delete the records from Query Analyzer using Delete statement. Here
>delete statement will be faster if you have indexes on columns used in wher
e
>clause of delete statement.
>Thanks
>Hari
>MCDBA
>
>"Ginola" <ginola@.mailcity.om> wrote in message
>news:403d30ed.15670093@.msnews.microsoft.com...
>
Ginola|||Enterprise Manager does not look at the timeout settings used by QA. There
is no way to change the default timeout setting for EM...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> --
> Ginola|||Try a Truncate table <tablename>. It's the fastest way to delete all records
from a table.

avoid reusing query plan..

Hi,

I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?

Thanks,

S.How about...

DBCC FREEPROCCACHE|||Does it also clear the Query cache ??|||Originally posted by Enigma
Does it also clear the Query cache ??

Not sure, never had the need..

Remarks
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.|||Thanks guys,

Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
Thanks anyway,

S.|||Look up Buffer manage object in BOL...

Haven't seen a way to do what you're looking for...

but hey ...1 second ain't bad....

1 mississippi...

done|||Originally posted by dbadelphes
Thanks guys,

Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
Thanks anyway,

S.

Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.|||That's what I was looking for.
Thanks a lot...(go Leafs go.. :) )

S.

Originally posted by homer37
Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.

AVOID merge joins

Hi,
Does anyone know of a way to AVOID merge joins?
I have a complex query that works fine on one system, but not on another.
After inspection, I found that the difference lies in different query plans generated by the query optimizer. On the failing system, a merge join is used, but since the total size of the columns is more than 8k, I get the error:
Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8571, which is greater than the allowable maximum of 8094.
With query optimizer hints I can favour an optimization, but is there also a way to AVOID one?
(It should be a query optimizer hint on the complete SELECT, not on a specific table: the merge join is used on an intermediate step of the query, not on an identified table.)
Many thanksIn the mean time, I have found that I can use OPTION(ROBUST PLAN) in the select. Does anyone know the syntax when I would want to use it in a view?

Thanks,
Jan|||Basically it seems an issue that you have is not with type of join but with row size. When you use ROBUST PLAN option optimizer will try to use the max possible row size at the expense of actual query performance.
I would recommend to review query strategy to avoid of using huge row sizes.

But in a mean time you may try to use join type hint to prevent of using MERGE JOIN or use OPTION with particular JOIN type.

Regards.

Avoid interrupting of BEGIN-END?

Hello,
I am searching for a way to get data moved from table to table. Meanwhile, data is being added to the table.
The batch query I thought of was the following:

BEGIN
SELECT (Column1, Column2) INTO Table2 (Column1, Column2)
FROM Table1;
TRUNCATE Table1;
(process data from Table2)
TRUNCATE Table2;
END

Problem is, it musn't be interrupted.
But data is being inserted continuously by another process, so the data from that process need to be copied to a cache table of sql server, or something like that.
Another thought of mine was letting that process insert it's data via a synonym (in this example called "Synonym1"). The application making use of Table 2 can either make use of Table1, that doesn't matter.
The batch query would be like the following:

BEGIN
ALTER Synonym1=Table1_Temp;
(process data from Table1);
TRUNCATE Table1;
ALTER Synonym1=Table1;
SELECT (Column1, Column2) INTO Table1 (Column1, Column2)
FROM Table1_Temp;
TRUNCATE Table1_Temp;
END

But the ALTER function does not exist for the SYNONYM function in SQL Server.
It needs to be done with drop and create.
Problem is, in that split second the other program wich is adding data to the database, will make an error because it cannot find that synonym.
Does anybody know how to make such processes uninterruptible?
Thanks in advance

Can you modify the structure of the tables in question or are they set?

If you can change them then I would add a processing field to table1 which defaults to Null (or possibly 0 if bit). The first step is to set that flag for all the records in table1. Then all your other operations (copying and deleting) use that in a where clause to only affect those records. Any records added after you start will have that value at the default and therefore not be affected (until the process runs the next time.

The only problem I can see is if this process runs twice with overlap - and the old version would have that problem as well. In that case what you could do is use a unique ID for each run and place that rather than simply a flag value in to the processing field. The most obvious is a GUID but as those are 16-bytes there is probably a better solution (particularly if this is as active a table as you imply).

A problem with transferring between tables as you show in your sample is if you try to use automatic identity columns as these will probably not be unique between the tables.

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that select query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?
Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user US
ER created in the server, if I try to make a sql query (select * from tablen
ame) the sql panel writes "select * from USER.tablename" and this causes a e
rror when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

AVG using ROW_NUMBER

I'm using SQL Server 2005, sp 2. My query is below. What I want to see for the results is the average of all of partition 1, the average of partition 2, etc.Does anybody know how I can get this?

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

Results:

Partition This Year Date Code Location Code

1 .85 20070101 1

2 .58 20070509 1

1 .52 20070808 2

2 .54 20070905 2

3 .26 20070104 3

3 .26 20070905 3

Is this what you're looking for?

Code Snippet

select

Partition

,(sum(Thisyear)/count(*)) 'ThisYearAvg'

from

(

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

) a

group by

Partition

|||

The average of what?

select

...,

avg(Score) over(partition by Shop.Location_Code) as avg_score

from

....

AMB

|||

Yes Anthony, That's exactly what I was looking for. Thank you so much for your help!

Lindsay

avg of most current 50 only

I have a query that returns the averages for a selected group of records.
select AVG(h.stkhstClose), h.stkhstcsisym
from stkhst h
JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
and u.unvmemUnvID = 29001
group by h.stkhstcsisym
order by h.stkhstcsisym
this works and returns 99 averages. However, I need it to average only the
last 50 records of each group based on the date. I'm looking for a clean way
to do this without using a temporary table or cursor. and ideas would be
appreciated
thanks
kesJust a guess. See my signature for a more precise answer.
SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
<whatever> ORDER BY datecolumn DESC) x
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> I have a query that returns the averages for a selected group of records.
> select AVG(h.stkhstClose), h.stkhstcsisym
> from stkhst h
> JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
> and u.unvmemUnvID = 29001
> group by h.stkhstcsisym
> order by h.stkhstcsisym
> this works and returns 99 averages. However, I need it to average only the
> last 50 records of each group based on the date. I'm looking for a clean
way
> to do this without using a temporary table or cursor. and ideas would be
> appreciated
> thanks
> kes|||Thank You Aaron (you seem to answer a lot of my postings and your suggestion
s
have always proven helpful)
this will get the average for one group but how about the rest? Would a
where stkhstDate IN (select top 50 stkhstdate from stkhst where stkhstid =
xx order by stkhstdate DESC)
thank you
kes
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||>> this will get the average for one group but how about the rest?
Did you read Aaron's post? To repeat:
See his signature for a more precise answer.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Anith|||ok, fair enough.
stkhst:
CREATE TABLE [stkhst] (
[stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
[stkhstCsiSym] [int] NULL ,
[stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstDate] [int] NULL ,
[stkhstOpen] [decimal](9, 4) NULL ,
[stkhstHi] [decimal](9, 4) NULL ,
[stkhstLow] [decimal](9, 4) NULL ,
[stkhstClose] [decimal](9, 4) NULL ,
[stkhstVol] [int] NULL ,
[stkhstDiv] [int] NULL ,
[stkhstX] [int] NULL ,
[stkhstO] [int] NULL ,
[stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
[stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstCPosL] [int] NULL ,
[stkhstCPosH] [int] NULL ,
[stkHstCCol] [int] NULL
) ON [PRIMARY]
GO
unvmem:
CREATE TABLE [unvmem] (
[unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
[unvmemCsiId] [int] NOT NULL ,
[unvmemUnvID] [int] NOT NULL ,
[unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
) ON [PRIMARY]
GO
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||noted, posted
thanks
kes
"Anith Sen" wrote:

> Did you read Aaron's post? To repeat:
> See his signature for a more precise answer.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> --
> Anith
>
>|||What about sample data and desired results?
The point is that we're not going to drive to Wichita or Kansas or wherever
you are to see what data is in your table and try to figure out what result
you want from that data. And we're certainly not going to spend our
afternoon inventing fictitious but possibly unrealistic data to populate
your empty table, then spend time developing a solution against that, only
to find out all the "buts" that come with the assumptions we made. Please
supply sample data in the form of INSERT statements, and the resultset you
want based on that data.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO|||Kurt,
Your tables seem to have no primary keys which is a critical design flaw.
Generally for such problems, others cannot test the solutions without sample
data. You have not provided that either. Also as a side note, if your scheme
allows, you may want to look closely at your naming convention as well.
Here is another attempt with guesswork:
SELECT AVG( stkhstClose ), stkhstcsisym
FROM ( SELECT TOP 50 h.stkhstClose, h.stkhstcsisym
FROM stkhst h
INNER JOIN unvmem u
ON h.stkhstcsisym = u.unvmemCsiId
WHERE u.unvmemUnvID = 29001
ORDER BY h.stkhstcsisym ) D ( stkhstClose, stkhstcsisym )
GROUP BY stkhstcsisym ;
Anith|||Kurt,
You want to extract and average the last 50 records for each group...
Just add a where clause that restricts the query to operate only on those
records which have 50 or less "partners" (in the same group) after them...
Select AVG(h.stkhstClose), h.stkhstcsisym
From stkhst h
Where (Select Count(*) From stkhst
Where stkhstcsisym = h.stkhstcsisym
And DateColumn >= h.DateColumn) <= 50
Select * From
"Kurt Schroeder" wrote:
> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO
> "Aaron [SQL Server MVP]" wrote:
>|||My apologies, I did not mean to imply that I needed more than advise. Please
understand that I do not feel it appropriate to ask for more than just that.
I feel it would be unfair to you or anyone else to do my work for me.
Aaron, your first posting to my question gave me what I needed to search for
the answer. My real query is much more complex, but this part of it was
simple enough to post for advice.
Again I wish to thank you for your help.
Humbly yours
kes
"Aaron [SQL Server MVP]" wrote:

> What about sample data and desired results?
> The point is that we're not going to drive to Wichita or Kansas or whereve
r
> you are to see what data is in your table and try to figure out what resul
t
> you want from that data. And we're certainly not going to spend our
> afternoon inventing fictitious but possibly unrealistic data to populate
> your empty table, then spend time developing a solution against that, only
> to find out all the "buts" that come with the assumptions we made. Please
> supply sample data in the form of INSERT statements, and the resultset you
> want based on that data.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
>>I need to average the resulting averages from the query below
>>This query gets the averages for each TowerNumber.
>>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
>> AS AvgSupplyCalciumHardness
>>FROM tblTowers
>>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND
>>(SystemID
>>= @.SystemID) AND
>> (LocationID = @.LocationID)
>>GROUP BY LocationID, SystemID, TowerNumber
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
Thanks
On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.
|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>
|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.
|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemI
D
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.
4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work
.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

available values and free text

Hi,
I have a parameter that get his available values from a query.

I want that user will have the option to select from the values list OR to type in his own value.

Is there a way to do that?

Thanks,YOu will have to use two parameters and compose your query to accept either the one or the other value.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hello Jens,

I was afraid of that :(
I was sure at first that there is a simple solution other then that.

Now it mean that I need to check both parameters and if the user fill them both I need to decide which one to choose and so on...

Any way, It seems that its the only way.

Thanks for your replay,
Roy.

available values and free text

Hi,
I have a parameter that get his available values from a query.

I want that user will have the option to select from the values list OR to type in his own value.

Is there a way to do that?

Thanks,

Hello my friend,

I am assuming that your rdl report is working and the user can select from a drop down list and you just want extra functionality to allow the user to choose their own, which may not be on the list. A way to do this would be to use 2 other parameters. One will be the user's chosen value and the other will be a flag to say whether to use the selected drop down value or what the user entered

SELECT * FROM MyTable WHERE (MyID = @.DropDownListValue AND @.UsingDropDownList = 1)

OR (MyID = @.UserValue AND @.UsingDropDownList = 0)

Kind regards

Scotty

|||

Hi Scotty,

Thank for your replay!

I also thought about useing two parameters but it looked like a really poor idea.

After seeing your answer I got the feeling That Its the only way :(

The problem is that I already have to much parameters and helf the screen is with parameters textBox (since it organized them in two column and I can't do any thing about that),

Thanks for your help,

Roy.

|||

Hello again my friend,

You do not need to use the default parameter screen that the report generates. When you submit the report after choosing your parameters, observe the url it generates. It concatenates your parameters on to the URL. Instead of using the default report screen, you can make your own user-friendly web page where they choose the parameters and then just generate the URL based on their choices and redirect them.

Kind regards

Scotty