How can I request row #3 in a dataset?

I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, ElevationFROM [PointTable]WHERE (RouteID = 5)

But I cannot (or do not know how to) add a clause that says

AND (Num = 3)

So can someone show me how to request a specific row?

It's really easy, and the trick to it will serve you in good stead in many other situations.

When you issue a select statement, you are selecting "from" something. That something might be a table, or it might be a view.

But what IS a view? It's a query that's given a name, an alias if you will. When a view is used, the actual query statement it represents is substituted for the view's name in the sql statement as part of the parsing process.

How does that knowledge help?

Because you don't have to bother to give a view a name, you can just fill in a query for yourself. Just pop in inside a set of parentheses and supply an alias

So:

SELECT *

FROM

(

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, Elevation
FROM [PointTable]
WHERE (RouteID = 5)

) AS temporary_result_set

WHERE temporary_result_set.Num = 3

|||WOW! Thank you David, that did the trick. You're explaination just doubled my understanding on how T-SQL works and got my mind thinking of other things to try.

How can i move logins after a Mirror failover

I've search a lot of articles trying to find a script which not also re
create SQL logins from one server to another but that also makes really
automatically a SQL 2005 Mirror failover (map sql login to database and
set permissions)
Does anybody know how to do this? I am looking for a script.
Hi
The logins should be created on the mirror server before you invoke
failover. This is easier if you use trusted connections and have your users
in a windows group (as only the group needs to be added).
See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
http://support.microsoft.com/kb/246133/ for a script.
John
"jocamp3@.gmail.com" wrote:

> I've search a lot of articles trying to find a script which not also re
> create SQL logins from one server to another but that also makes really
> automatically a SQL 2005 Mirror failover (map sql login to database and
> set permissions)
> Does anybody know how to do this? I am looking for a script.
>
|||Hello John:
Thanks for reply.
This does not work. Yes .. i can create a login in the Mirror Server
but i can not map that login name to the desired database, because
simply it does not exists or it's in recovery state. So ... when i make
the failover, app. does not have the required credentials to log in,
because the SQL login is there but with no map to the Mirrored
database.
Got my point?
John Bell wrote:[vbcol=seagreen]
> Hi
> The logins should be created on the mirror server before you invoke
> failover. This is easier if you use trusted connections and have your users
> in a windows group (as only the group needs to be added).
> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> http://support.microsoft.com/kb/246133/ for a script.
> John
> "jocamp3@.gmail.com" wrote:
|||Hello Tibor:
Maybe i'm doin' something wrong ... or i can not explain myself well.
...Could you please provide me the steps order or the whole process?
Mirror 1st? Script first? etc ... i will try to recreate everything
again.
Tibor Karaszi wrote:[vbcol=seagreen]
> But the mapping exists *inside* the database, so it is already there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegr oups.com...
|||Hi
sp_help_revlogin and how to use it is described in the second link I posted
http://support.microsoft.com/kb/246133/
John
"jocamp3@.gmail.com" wrote:

> Hello Tibor:
> Maybe i'm doin' something wrong ... or i can not explain myself well.
> ...Could you please provide me the steps order or the whole process?
> Mirror 1st? Script first? etc ... i will try to recreate everything
> again.
> Tibor Karaszi wrote:
>
|||Ok ... got it! ... and i will post this because there are a lot of
persons with the same problem. I can not believe nobody knows about
this ...
Some times, a step by step explanation helps much more than just point
to a simple script or link ...
1. On the server which will be Principal, create the Login (which the
app. will use to connect) at Server level WITH NO MAPPING to the
database. Put master database at default db instead.
2. Use SSID and execute the required package to copy Logins from one
server to another. If package turns green, it works. You should also be
able to see the Login in the second server.
3. Now, on 1st server again, set database mapping for SQL login. Set
the required permissions to database also (data reader, data writer,
etc)
3. Restore database on 2nd server (future mirror) in recovery mode.
4. Establish the Mirror
5. Test the failover: check the 2nd server (now the Principal) and you
should be able to see the SQL login. Change default database from
master to the desired database (now you can, because you're in the
principal)
6. Make another manual failover. Since now ... SQL server login its
already there and mapping exist with the required permissions also.
Jose Campo
John Bell wrote:[vbcol=seagreen]
> Hi
> sp_help_revlogin and how to use it is described in the second link I posted
> http://support.microsoft.com/kb/246133/
> John
> "jocamp3@.gmail.com" wrote:

how can i monitor full text queries?

i've got a sharepoint database configured by a sys admin.
how can i use profiler to see if there are any full text queries
happening?
just look for the "contains" clause or something else?
I take it you mean Sharepoint Team Services, that requires you to monitor
Microsoft Search, for SPS monitor Search, Queries and Query rate.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ch" <ch@.dontemailme.com> wrote in message
news:43CE51F7.9F6C5653@.dontemailme.com...
> i've got a sharepoint database configured by a sys admin.
> how can i use profiler to see if there are any full text queries
> happening?
> just look for the "contains" clause or something else?
|||monitor the object Microsoft Search and the counters queries and query rate.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ch" <ch@.dontemailme.com> wrote in message
news:43CE51F7.9F6C5653@.dontemailme.com...
> i've got a sharepoint database configured by a sys admin.
> how can i use profiler to see if there are any full text queries
> happening?
> just look for the "contains" clause or something else?
|||i need to see the actual queries and whether or not they're returning
anything and not just the counters available in perfmon.
Hilary Cotter wrote:[vbcol=seagreen]
> monitor the object Microsoft Search and the counters queries and query rate.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ch" <ch@.dontemailme.com> wrote in message
> news:43CE51F7.9F6C5653@.dontemailme.com...
|||you need to use profiler for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ch" <ch@.dontemailme.com> wrote in message
news:43CE6B6E.8BC997C5@.dontemailme.com...[vbcol=seagreen]
>i need to see the actual queries and whether or not they're returning
> anything and not just the counters available in perfmon.
>
> Hilary Cotter wrote:
sql

How can I increase the maximum number of parallel connections when exporting to Oracle via

Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>

How can I increase the maximum number of parallel connections when exporting to Oracle via

Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>