QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?
Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

Query/Report for Owner of all SQL Jobs

Is there a query/report that I can run against all our SQL servers to see who the 'owner' of each job is?
If so, can this be run in SQL Server Enterprise Manager 8, SQL Server Management Studio or Hyena?Usual caveats re querying system tables (run at own risk etc):
SELECT J.name AS JobName
, L.name AS JobOwner
FROM msdb.dbo.sysjobs_view J
INNER JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid
HTH|||Thank you very much! I'm just a newbie DBA admin :)

Query Would Run For Ever - No Clue

Hello fellow DBAs

I have a strange situation here. I am executing a SQL query which runs for ever till it fills up all the available temp space.

The same query runs within 1 minute in another database on another server. That database is a development database but with same records (and data).

I tried the following:

UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG

Nothing helps... what should I do next?post the ddl + the query so we can see. Its common to have a rogue/running away query that would take the server down to its knee.

e.g.
select *
from master..syscolumns,master..syscolumns,master..sysc olumns,master..syscolumns

query with view locks database (was "Problem with Viiews")

Hi,
I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database.
Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.

Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.

Thanx in advance.

Regards,
SushmaMost of the time the ways views are implimented or used are not very efficient. They often result in slow queries because you are in effect querying a query and the result you are seeking is often more efficiently achieved by writing one well formed query. If the view was created for security purposes there are alternatives like restricting access to a table but granting access to a stored procedure that accesses a table.sql

query with time period spanning two days

I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and

'17:00:00' order by [DateTime]

However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and

'09:00:00' order by [DateTime]

I need a way to indicate that the start and end times span two days. Can anybody help with this?

I think I found the answer:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and ((convert(varchar, [DateTime],114) between '17:00:00' and

'23:59:59') or (convert(varchar, [DateTime],114) between '00:00:00' and

'09:00:00')) order by [DateTime]

|||

I'm not sure exactly what you're after, but you should investigate the DATEADD() or DATEDIFF() functions. They will handle the day differences for you.

CREATE TABLE #T1 (StartTime datetime, EndTime datetime)

INSERT INTO #T1

VALUES ('2000-01-01 8:00', '2000-01-01 8:05')

INSERT INTO #T1

VALUES ('2000-01-02 8:00', '2000-01-01 7:59')

SELECT DATEDIFF(hh, EndTime, StartTime)

FROM #T1

You may have to play with either of these functions if you want the full year, month, day, etc., but you just string them together to get that.

|||

I have numerous tables which contain data recorded over a 24hr period for several years. The tables each contain a column called [DateTime] (for historic reasons), as well as several other columns containing data recorded during those datetimes (in each record).

At times, it is necessary to examine data recorded during non-business hours (e.g., 5:00pm to 9:00am the following day). Hence, the start time (17:00:00 ) is greater than the end time (09:00:00) which falls on the following date (see code in posts above). The code in my second posting while a bit of a kludge seems to work. I'm not sure how using dateadd or datediff would help. But, thanks anyway.

|||I see. DATEADD() or DATEDIFF() will handle the day span problem for you, since they are aware (as shown in the example) that a day has passed between two hour or minute comparisons. You can use that logic to find and process the rows that have that condition.

Query window (sql2005) displays only 1000 rows

Hi Guys,

when I run a query , it only displays 1000 rows in the query window. Any one know how to change this setting?

thanks

Sonny

On the [Tools] menu, then [Option], then [Query Execution].

Check the SET ROWCOUNT value. If it is 1000, change to 0 for all rows.

|||

thanks Arnie, that way quick

Sonny

Query will not stop running

I have a query that suddenly will not resolve at all.
Whereas it used to run in < 10 seconds, now it will
continue running until it is cancelled with no results.
I used sql profiler on the query, and I let the query
run for 7 minutes, and while I got no results,
the query perfomed 112 million reads!
I have already dropped and recreated the stored procedure,
and I've cleared the procedure cache to no avail.
Any ideas?
Hi
Indexes? Check that the indexes are still there, re-build them.
Make sure that the query has not changed. An outer join would do something
like this. Even removing a join statement.
Regards
Mike
"Berry" wrote:

> I have a query that suddenly will not resolve at all.
> Whereas it used to run in < 10 seconds, now it will
> continue running until it is cancelled with no results.
> I used sql profiler on the query, and I let the query
> run for 7 minutes, and while I got no results,
> the query perfomed 112 million reads!
> I have already dropped and recreated the stored procedure,
> and I've cleared the procedure cache to no avail.
> Any ideas?
|||In news:42E0E3F8-B79C-4DDC-9ED7-F3AAFC8A9BF7@.microsoft.com,
Berry <Berry@.discussions.microsoft.com> said:
> I have a query that suddenly will not resolve at all.
> Whereas it used to run in < 10 seconds, now it will
> continue running until it is cancelled with no results.
> I used sql profiler on the query, and I let the query
> run for 7 minutes, and while I got no results,
> the query perfomed 112 million reads!
> I have already dropped and recreated the stored procedure,
> and I've cleared the procedure cache to no avail.
> Any ideas?
You start off by saying it's a query and finish with it as a stored
procedure. Assuming you mean the latter, do you have an loops that may not
ever reach their termination condition?
Steve
|||Berry wrote:

>I have a query that suddenly will not resolve at all.
>Whereas it used to run in < 10 seconds, now it will
>continue running until it is cancelled with no results.
>I used sql profiler on the query, and I let the query
>run for 7 minutes, and while I got no results,
>the query perfomed 112 million reads!
>I have already dropped and recreated the stored procedure,
>and I've cleared the procedure cache to no avail.
>Any ideas?
>
No, not without seeing some details. Can you possibly
post the query or stored procedure, as well as the CREATE TABLE
and CREATE INDEX statements for the tables it accesses? Here's
a guide that explains how to do that:
http://www.aspfaq.com/etiquette.asp?id=5006
Steve Kass
Drew University
|||Thanks for everyone's help, but my now-fixed issue was caused by indexes ,
or, i should say, the LACK of indexes.
"Steve" <steve@.somewhere.invalid.com> wrote in message
news:46rMd.974044$f47.166864@.news.easynews.com...
> In news:42E0E3F8-B79C-4DDC-9ED7-F3AAFC8A9BF7@.microsoft.com,
> Berry <Berry@.discussions.microsoft.com> said:
> You start off by saying it's a query and finish with it as a stored
> procedure. Assuming you mean the latter, do you have an loops that may
not
> ever reach their termination condition?
> --
> Steve
>

Query will not stop running

I have a query that suddenly will not resolve at all.
Whereas it used to run in < 10 seconds, now it will
continue running until it is cancelled with no results.
I used sql profiler on the query, and I let the query
run for 7 minutes, and while I got no results,
the query perfomed 112 million reads!
I have already dropped and recreated the stored procedure,
and I've cleared the procedure cache to no avail.
Any ideas?Hi
Indexes? Check that the indexes are still there, re-build them.
Make sure that the query has not changed. An outer join would do something
like this. Even removing a join statement.
Regards
Mike
"Berry" wrote:
> I have a query that suddenly will not resolve at all.
> Whereas it used to run in < 10 seconds, now it will
> continue running until it is cancelled with no results.
> I used sql profiler on the query, and I let the query
> run for 7 minutes, and while I got no results,
> the query perfomed 112 million reads!
> I have already dropped and recreated the stored procedure,
> and I've cleared the procedure cache to no avail.
> Any ideas?|||In news:42E0E3F8-B79C-4DDC-9ED7-F3AAFC8A9BF7@.microsoft.com,
Berry <Berry@.discussions.microsoft.com> said:
> I have a query that suddenly will not resolve at all.
> Whereas it used to run in < 10 seconds, now it will
> continue running until it is cancelled with no results.
> I used sql profiler on the query, and I let the query
> run for 7 minutes, and while I got no results,
> the query perfomed 112 million reads!
> I have already dropped and recreated the stored procedure,
> and I've cleared the procedure cache to no avail.
> Any ideas?
You start off by saying it's a query and finish with it as a stored
procedure. Assuming you mean the latter, do you have an loops that may not
ever reach their termination condition?
--
Steve|||Berry wrote:
>I have a query that suddenly will not resolve at all.
>Whereas it used to run in < 10 seconds, now it will
>continue running until it is cancelled with no results.
>I used sql profiler on the query, and I let the query
>run for 7 minutes, and while I got no results,
>the query perfomed 112 million reads!
>I have already dropped and recreated the stored procedure,
>and I've cleared the procedure cache to no avail.
>Any ideas?
>
No, not without seeing some details. Can you possibly
post the query or stored procedure, as well as the CREATE TABLE
and CREATE INDEX statements for the tables it accesses? Here's
a guide that explains how to do that:
http://www.aspfaq.com/etiquette.asp?id=5006
Steve Kass
Drew University|||Thanks for everyone's help, but my now-fixed issue was caused by indexes ,
or, i should say, the LACK of indexes.
"Steve" <steve@.somewhere.invalid.com> wrote in message
news:46rMd.974044$f47.166864@.news.easynews.com...
> In news:42E0E3F8-B79C-4DDC-9ED7-F3AAFC8A9BF7@.microsoft.com,
> Berry <Berry@.discussions.microsoft.com> said:
> > I have a query that suddenly will not resolve at all.
> > Whereas it used to run in < 10 seconds, now it will
> > continue running until it is cancelled with no results.
> > I used sql profiler on the query, and I let the query
> > run for 7 minutes, and while I got no results,
> > the query perfomed 112 million reads!
> > I have already dropped and recreated the stored procedure,
> > and I've cleared the procedure cache to no avail.
> > Any ideas?
> You start off by saying it's a query and finish with it as a stored
> procedure. Assuming you mean the latter, do you have an loops that may
not
> ever reach their termination condition?
> --
> Steve
>

Query will not stop running

I have a query that suddenly will not resolve at all.
Whereas it used to run in < 10 seconds, now it will
continue running until it is cancelled with no results.
I used sql profiler on the query, and I let the query
run for 7 minutes, and while I got no results,
the query perfomed 112 million reads!
I have already dropped and recreated the stored procedure,
and I've cleared the procedure cache to no avail.
Any ideas?Hi
Indexes? Check that the indexes are still there, re-build them.
Make sure that the query has not changed. An outer join would do something
like this. Even removing a join statement.
Regards
Mike
"Berry" wrote:

> I have a query that suddenly will not resolve at all.
> Whereas it used to run in < 10 seconds, now it will
> continue running until it is cancelled with no results.
> I used sql profiler on the query, and I let the query
> run for 7 minutes, and while I got no results,
> the query perfomed 112 million reads!
> I have already dropped and recreated the stored procedure,
> and I've cleared the procedure cache to no avail.
> Any ideas?|||In news:42E0E3F8-B79C-4DDC-9ED7-F3AAFC8A9BF7@.microsoft.com,
Berry <Berry@.discussions.microsoft.com> said:
> I have a query that suddenly will not resolve at all.
> Whereas it used to run in < 10 seconds, now it will
> continue running until it is cancelled with no results.
> I used sql profiler on the query, and I let the query
> run for 7 minutes, and while I got no results,
> the query perfomed 112 million reads!
> I have already dropped and recreated the stored procedure,
> and I've cleared the procedure cache to no avail.
> Any ideas?
You start off by saying it's a query and finish with it as a stored
procedure. Assuming you mean the latter, do you have an loops that may not
ever reach their termination condition?
Steve|||Berry wrote:

>I have a query that suddenly will not resolve at all.
>Whereas it used to run in < 10 seconds, now it will
>continue running until it is cancelled with no results.
>I used sql profiler on the query, and I let the query
>run for 7 minutes, and while I got no results,
>the query perfomed 112 million reads!
>I have already dropped and recreated the stored procedure,
>and I've cleared the procedure cache to no avail.
>Any ideas?
>
No, not without seeing some details. Can you possibly
post the query or stored procedure, as well as the CREATE TABLE
and CREATE INDEX statements for the tables it accesses? Here's
a guide that explains how to do that:
http://www.aspfaq.com/etiquette.asp?id=5006
Steve Kass
Drew University|||Thanks for everyone's help, but my now-fixed issue was caused by indexes ,
or, i should say, the LACK of indexes.
"Steve" <steve@.somewhere.invalid.com> wrote in message
news:46rMd.974044$f47.166864@.news.easynews.com...
> In news:42E0E3F8-B79C-4DDC-9ED7-F3AAFC8A9BF7@.microsoft.com,
> Berry <Berry@.discussions.microsoft.com> said:
> You start off by saying it's a query and finish with it as a stored
> procedure. Assuming you mean the latter, do you have an loops that may
not
> ever reach their termination condition?
> --
> Steve
>

Query which system table to answer this

I have a DB with 1 default defined: UW_Zerodefault
It simply puts a 0 into particular fields upon new record creation.
Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?
ThanksSqlSpec will give you this information, but I can't remember now what table it's hitting to get it. it may be as simple as querying sysdepends.

I'll take a look at the code tonight and post again.|||If I understand your query correctly (It's late and I'm tired!!) you can run exec sp_mshelpcolumns 'enter table name' and the col_dridefname column is the contraint name and the first text column is the default value.|||this give you all default usage by columns:

select
s.name as colname
,o1.name as tablename
,o2.name as defaultname
from
syscolumns s
inner join
sysobjects o1 on o1.id=s.id
inner join
sysobjects o2 on o2.id=s.cdefault

and this will give you default usage by udts:

exec sp_mshelptype @.typename=null, @.flags='uddt'

query w/ case help

Hey all, here is my problem: I want to run a query to grab all the 'next dates' from a column after the user inputs a date. .. and then repeat accordingly.

Here is some simplified example code: This works fine (but doesn't grab all rows, which varies, with the Date. It only grabs the first, hence the 'top 1')

select top 1 datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and purchord = '4112'
order by datewrk

Soo, then this grabs all the rows with the Date, but doesn't 'skip' correctly. If you pick the date right before a valid row, as in there are rows of data for Date 6/18/2004 and you pick 6/17/2004 it will bring up the next date fine. BUT if you pick 6/15/2004 it will not 'skip ahead'. Any ideas? Thanks

select datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and 1 = (case when Datewrk = (select min(Datewrk) from Hours where Datewrk is not null and Datewrk > '06/15/2004') then 1 else 0 end) and purchord = '4112'
order by datewrkDoes this work:SELECT datewrk
FROM hours
WHERE datewrk IS NOT NULL
AND datewrk > '06/15/2004'
AND purchord = '4112'
AND 1 = (case when Datewrk = (select min(Datewrk)
FROM Hours
WHERE Datewrk IS NOT NULL
AND purchord = '4112'
AND Datewrk > '06/15/2004') THEN 1 ELSE 0 END)
ORDER BY datewrk-PatP|||Yes, that works! THANK YOU.

Query using datetime datatype

hello,
so i have a table with column name Date with datetime as it's datatype. I'm
trying to run a select statement on it that will give me all rows where my
Date column has a datetime of 30 days or more. Can anyone help? I tried using
datediff but can't get it to work.
Thanks in advance!"FS" <FS@.discussions.microsoft.com> wrote in message
news:6A6874E0-38C5-4257-BA2A-4EEFF4D3A181@.microsoft.com...
> hello,
> so i have a table with column name Date with datetime as it's datatype.
> I'm
> trying to run a select statement on it that will give me all rows where my
> Date column has a datetime of 30 days or more. Can anyone help? I tried
> using
> datediff but can't get it to work.
> Thanks in advance!
WHERE dt <= DATEADD(DAY,-30,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Query user/worlstation access of database

I have a client who needs to be able to run a query that shows a list of all
workstations (IP address, logon etc) who are currently accessing a specified
SQL database with logon / last activity time (if possible).
Most of the users are using a web application to access the database, so how
would we identify which users are accessing the database through a web
server. There are some however who are using the fat client application to
access the same database.
Is this possible?see if this is what you want?
select * from sysprocesses
--
"Mark - HIS" wrote:

> I have a client who needs to be able to run a query that shows a list of a
ll
> workstations (IP address, logon etc) who are currently accessing a specifi
ed
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so h
ow
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>|||If using SQL Server 2000 (not sure about the 2005 equivalent) you can
view this info through the GUI: Under the "Management" node there
should be a node called "Current Activity" and under that a node called
"Process Info" & this is the one you want. You should click on this
node to get a display that contains, among other things, the following
info:
* The SQL Server login that is associated with a process
* The host that the process connected from (if access is via a web
server the entry for the host should be the name of the web server)
* Time logged in
* Time of last batch/command execution
* Command that was last executed (double click on a process for this
information)
In SQL Server 2000 you have the sp_who (you also have the undocumented
sp_who2 stored proc in SQL Server 2000, not sure if it's in SQL Server
2005) which will materialize a result set. In this result set, the
following may be of interest to you:
* loginname - The SQL Server login associated with a process
* hostname - The host the process connected from
In addition, sp_who2 provides a column (lastbatch) that gives the date
and time when the last command/batch is executed for a process.
Quick example calls:
EXEC sp_who
GO
EXEC sp_who2
GO
Hope that helps a bit|||This table is in the master database.
So use
select * from master.dbo.sysprocesses.|||Anyone connecting to the web server will show up as the network user that
the web page is running under. If you disable anonymous access and have the
users login with their network credentials, I think these MAY show up as the
network account in SQL Server, but I have not tried this myself, so I could
be wrong.
The IP address will always be the web server itself, since that is where the
connection is made.
The SQL Server user will show up as whatever login the web server is using
to connect to the database. If you are using the same connection string for
all users, then that is the ID that will show up.
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:48AAC7C6-FEA9-4090-9A85-DC502F55EE86@.microsoft.com...
> I have a client who needs to be able to run a query that shows a list of
all
> workstations (IP address, logon etc) who are currently accessing a
specified
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so
how
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>

Query two databases on same machine

Hi
I have an SQL 2000 Server, with a number of Databases on it,
I would like to run a query which extracts data from one database and
stores it in another database, the databases have a column which is
the same in each called "AccNumber".
How does one link two databases in an SQL 2000 query.
Any information is appreciated.
Thanks
KimboINSERT dbname1.dbo.table1 (AccNumber)
SELECT AccNumber FROM dbname2.dbo.table2
HTH. Ryan
"Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
> Hi
> I have an SQL 2000 Server, with a number of Databases on it,
> I would like to run a query which extracts data from one database and
> stores it in another database, the databases have a column which is
> the same in each called "AccNumber".
> How does one link two databases in an SQL 2000 query.
> Any information is appreciated.
> Thanks
> Kimbo|||Hi,
You need to use the FQN for the tables
[ServerName].[DatabaseName].[Owner].[tableName]
regards LaRa
"Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
> Hi
> I have an SQL 2000 Server, with a number of Databases on it,
> I would like to run a query which extracts data from one database and
> stores it in another database, the databases have a column which is
> the same in each called "AccNumber".
> How does one link two databases in an SQL 2000 query.
> Any information is appreciated.
> Thanks
> Kimbo|||Kimbo
INSERT INTO DataBase_One.dbo.Table
SELECT <> FROM Database_Two.dbo.Table
"Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
> Hi
> I have an SQL 2000 Server, with a number of Databases on it,
> I would like to run a query which extracts data from one database and
> stores it in another database, the databases have a column which is
> the same in each called "AccNumber".
> How does one link two databases in an SQL 2000 query.
> Any information is appreciated.
> Thanks
> Kimbo|||use dbname1
go
insert dbname2.owner.table2(field)
select field from table1
Regards,
"Ryan" wrote:

> INSERT dbname1.dbo.table1 (AccNumber)
> SELECT AccNumber FROM dbname2.dbo.table2
> --
> HTH. Ryan
>
> "Kimbo" <theozbuggaNOSPAM@.hotmail.com> wrote in message
> news:6etpr1lqu3ji8h4cr01p59i2pmah3flmf8@.
4ax.com...
>
>|||Look up "distributed partitioned views" in Books Online. Based on your post
I
see no need for physically moving data from one database into another.
ML
http://milambda.blogspot.com/

Query tuning

How do you guys go about compare the efficiency of two queries?
What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?
Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?
Correction, tips and suggestion of best practice will be appreciated.
ThanksJustin
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
Yes , sure , as well as looking at EXECUTION PLAN of the both queries
http://www.sql-server-performance.c...performance.asp
"Justin" <nospam@.nospam.com> wrote in message
news:Oe3YBjflGHA.4244@.TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>|||Justin wrote:
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the que
ry
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile (a
nd
> look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..

query tuning

I have a query which is taking 23 sec to run, if i create a temporary table
for subset of resultset of the query and rewrite it using the temporary
table , which is taking only 4 sec. I cant mention my real query, but i
outline it here.
Original Query outline:
select pacct from (select pacct, qacct from tableA
where pid = '123456' and date > @.date) a
where qacct in (select qacct from TableB where groupid = 'asdfa' )
Modified query outline:
select pacct, qacct into #tp from tableA
where pid = '123456' and date > @.date
select pacct from #tp
where qacct in (select qacct from TableB where groupid = 'asdfa' )
TableA has 12 million recs , Table B has half a million recs.
I used inner join too, there is no improvment. From this can anyone guess
what is wrong , with optimiser or query.
Thanks,
Subbu.
Try this instead:
select pacct from tableA
where pid = '123456'
and date > @.date
and exists
(select *
from TableB
where groupid = 'asdfa'
and TableB.qacct = TableA.qacct)
If that doesn't work, post DDL for your tables, including all constraints
and indexes.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have a query which is taking 23 sec to run, if i create a temporary
table
> for subset of resultset of the query and rewrite it using the temporary
> table , which is taking only 4 sec. I cant mention my real query, but i
> outline it here.
> Original Query outline:
> select pacct from (select pacct, qacct from tableA
> where pid = '123456' and date > @.date) a
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> Modified query outline:
> select pacct, qacct into #tp from tableA
> where pid = '123456' and date > @.date
> select pacct from #tp
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> TableA has 12 million recs , Table B has half a million recs.
> I used inner join too, there is no improvment. From this can anyone guess
> what is wrong , with optimiser or query.
> Thanks,
> Subbu.
>
>
>
|||It is taking more than 150 sec and still going i stopped it.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Try this instead:
>
> select pacct from tableA
> where pid = '123456'
> and date > @.date
> and exists
> (select *
> from TableB
> where groupid = 'asdfa'
> and TableB.qacct = TableA.qacct)
>
> If that doesn't work, post DDL for your tables, including all constraints
> and indexes.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> table
guess
>
|||What are your indexes on the two tables?
-Sue
On Wed, 17 Nov 2004 10:23:06 -0600, "Subbaiahd"
<subbaiahd@.hotmail.com> wrote:

>It is taking more than 150 sec and still going i stopped it.
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...
>guess
>

Query to View with Many Columns

Hi All,

I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?

Does anyone have idea how to join table that can reflect both left and right join?

Table A Table B

Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5

A Jan 5 xxx A Jan 1 yyy

B Feb 3 C Mar 4

B Mar 4 C Apr 3

C Apr 2 D May 2

E Mar 1

Result Table

Column1 Column2 Column3 Column4 Column 5

A Jan 6 (= 5+1) xxx yyyy

B Feb 3

B Mar 4

C Mar 4

C Apr 5 (= 2+3)

D May 2

E Mar 1

So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).

Thanks for the help.

The VIEW will first create a virtual table containing ALL of the columns, and then you will get a resultset of just the columns requested.

That could be very inefficient. Most likely, it would be better to query against the underlaying table for just the columns required. Or create a separate VIEW for this purpose.

|||

Thanks for the answer .

The problem is this query will be executed by Report Builder, so all of those columns will be needed as user might select any combination to include in the report, so it's quite difficult to provide only certain column by creating another view. I already try to separate the View into multiple entity in Report Builder, but when it comes to the aggregate, some of the calculation is calculated wrongly in the Report Builder, and some complexity with multiple currency/exchange rate araise, that force me to add more column inside the view .

Any idea or suggestion for my situation? Thanks

|||

You haven't provided enough detail for anyone to offer cogent suggestions.

However, one thing comes to mind. You might be able to use a Stored Procedure that Creates a custom VIEW (from input parameters), and then returns the resultset from that VIEW.

query to run to find SQL SP upgrade successful

I want to run a query in QA om each server to ensure that I have upgraded
all my SQL Servers to SP3a along with the MS03-031 SQL security patch
Thanks
Hassan wrote:
> I want to run a query in QA om each server to ensure that I have
> upgraded all my SQL Servers to SP3a along with the MS03-031 SQL
> security patch
> Thanks
From T-SQL just execute @.@.version (SP3 is version 760). Since that
hotfix does not modify the SQL Server version (as it can be applied to
both SQL 7 and 2000), you need to follow this article to see if the
patch is applied:
http://support.microsoft.com/?kbid=815495
David Gugick
Imceda Software
www.imceda.com
|||On Sat, 9 Apr 2005 20:57:37 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:

>I want to run a query in QA om each server to ensure that I have upgraded
>all my SQL Servers to SP3a along with the MS03-031 SQL security patch
>Thanks
SELECT ServerProperty('ProductLevel')
will give you a very succinct answer as to which service pack has been
applied.
Andrew Watt
MVP - InfoPath
|||Hi
MS03-031 is build 818.
SELECT SERVERPROPERTY('ProductVersion') will return for the minor version,
760 for SP3 and 818 for MS03-31
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e23LCVZPFHA.3076@.tk2msftngp13.phx.gbl...
> Hassan wrote:
> From T-SQL just execute @.@.version (SP3 is version 760). Since that hotfix
> does not modify the SQL Server version (as it can be applied to both SQL 7
> and 2000), you need to follow this article to see if the patch is applied:
> http://support.microsoft.com/?kbid=815495
> --
> David Gugick
> Imceda Software
> www.imceda.com