Link two tables in UPDATE - ERR:single-row subquery returns more than one row

1 UPDATE PM_ASSIGNMENTS
2 SET PM_ASSIGNMENTS.JOB_TYPE_CODE = (SELECT PM_TASK_DETAILS.JOB_TYPE_CODE
3 FROM PM_ASSIGNMENTS, PM_TASK_DETAILS
4 WHERE ((PM_ASSIGNMENTS.TASK_NUMBER) = (PM_TASK_DETAILS.TASK_NUMBER))
5* AND (PM_ASSIGNMENTS.TASK_NUMBER = '1000'))
SQL> /
SET PM_ASSIGNMENTS.JOB_TYPE_CODE = (SELECT PM_TASK_DETAILS.JOB_TYPE_CODE
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one rowthe sub-query is returning more than one row for the given condition.

either group-by them, or give all user-key conditions.

regards,
Raghu.sql

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default...b;en-us;280106
http://support.microsoft.com/default...b;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
sql

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
http://support.microsoft.com/default.aspx?scid=kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error mess
age
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

Linekd Server and DateTime Problem

,
I runs this querys from QA in one session to my local
server
// tb1 in local server
Select * from TB1
where cdatetime <= '30/01/2003'
runs ok
// tb1 in linked server
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '30/01/2003'
fails with
conversion from data char to l DateTime out of Range
// tb1 in linked server
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '01/30/2003'
fails with
conversion from data char to l DateTime out of Range
Two servers have the same language and dateformat dmy
If I open one connection in QA to the linked server, the
query
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '30/01/2003'
runs ok.
I've tested with many diferents formats mmddyyyy,
yyyymmdd, and so on.
Any Ideas ?amosquera
Try use 'yyyymmdd' Format
"amosquera" <alvarosusana@.vodafone.es> wrote in message
news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
> ,
> I runs this querys from QA in one session to my local
> server
> // tb1 in local server
> Select * from TB1
> where cdatetime <= '30/01/2003'
> runs ok
> // tb1 in linked server
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '30/01/2003'
> fails with
> conversion from data char to l DateTime out of Range
> // tb1 in linked server
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '01/30/2003'
> fails with
> conversion from data char to l DateTime out of Range
>
> Two servers have the same language and dateformat dmy
> If I open one connection in QA to the linked server, the
> query
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '30/01/2003'
> runs ok.
> I've tested with many diferents formats mmddyyyy,
> yyyymmdd, and so on.
> Any Ideas ?
>|||In the original message I've said
I've tested with many diferents formats mmddyyyy,yyyymmdd,
and so on.
and the query fails.
>--Original Message--
>amosquera
>Try use 'yyyymmdd' Format
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> ,
>> I runs this querys from QA in one session to my local
>> server
>> // tb1 in local server
>> Select * from TB1
>> where cdatetime <= '30/01/2003'
>> runs ok
>> // tb1 in linked server
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '30/01/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>> // tb1 in linked server
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>
>> Two servers have the same language and dateformat dmy
>> If I open one connection in QA to the linked server, the
>> query
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '30/01/2003'
>> runs ok.
>> I've tested with many diferents formats mmddyyyy,
>> yyyymmdd, and so on.
>> Any Ideas ?
>
>.
>|||Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '01/30/2003'
> fails with
> conversion from data char to l DateTime out of Range
>
This one fails because SQL Server cannot recognize 30 as a month .
Have you changed it to '30/01/2003'?
One more thought: Have you checked regimal settings on the linked server?
I mean an year 2003 or 03 .
"amosquera" <alvarosusana@.vodafone.es> wrote in message
news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
> In the original message I've said
> I've tested with many diferents formats mmddyyyy,yyyymmdd,
> and so on.
> and the query fails.
> >--Original Message--
> >amosquera
> >Try use 'yyyymmdd' Format
> >
> >
> >
> >"amosquera" <alvarosusana@.vodafone.es> wrote in message
> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
> >> ,
> >>
> >> I runs this querys from QA in one session to my local
> >> server
> >>
> >> // tb1 in local server
> >> Select * from TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> runs ok
> >>
> >> // tb1 in linked server
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> fails with
> >> conversion from data char to l DateTime out of Range
> >>
> >> // tb1 in linked server
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '01/30/2003'
> >>
> >> fails with
> >> conversion from data char to l DateTime out of Range
> >>
> >>
> >> Two servers have the same language and dateformat dmy
> >>
> >> If I open one connection in QA to the linked server, the
> >> query
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> runs ok.
> >>
> >> I've tested with many diferents formats mmddyyyy,
> >> yyyymmdd, and so on.
> >>
> >> Any Ideas ?
> >>
> >
> >
> >.
> >|||Hi,
Well I think that I've tested all possible formats of
datetime and the query fails.
>--Original Message--
> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>This one fails because SQL Server cannot recognize 30 as
a month .
>Have you changed it to '30/01/2003'?
>One more thought: Have you checked regimal settings on
the linked server?
>I mean an year 2003 or 03 .
>
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat dmy
>> >>
>> >> If I open one connection in QA to the linked server,
the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The linked server has the same settings options that the
main server.
dd/mm/yyyy
>--Original Message--
>Hi,
>Well I think that I've tested all possible formats of
>datetime and the query fails.
>>--Original Message--
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>This one fails because SQL Server cannot recognize 30 as
>a month .
>>Have you changed it to '30/01/2003'?
>>One more thought: Have you checked regimal settings on
>the linked server?
>>I mean an year 2003 or 03 .
>>
>>
>>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
>mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in
message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my
local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat
dmy
>> >>
>> >> If I open one connection in QA to the linked
server,
>the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||I've had a new test:
Select * from LinkedServer.DB.dbo.TB1
where convert(char(10),cdatetime,103) <= '01/30/2003'
and it runs ok but the execution plan show that the query
uses a table scan rather than a index seek on cdatetime
column.
Thanks.
>--Original Message--
>The linked server has the same settings options that the
>main server.
>dd/mm/yyyy
>>--Original Message--
>>Hi,
>>Well I think that I've tested all possible formats of
>>datetime and the query fails.
>>--Original Message--
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>This one fails because SQL Server cannot recognize 30
as
>>a month .
>>Have you changed it to '30/01/2003'?
>>One more thought: Have you checked regimal settings on
>>the linked server?
>>I mean an year 2003 or 03 .
>>
>>
>>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
>>mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in
>message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my
>local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat
>dmy
>> >>
>> >> If I open one connection in QA to the linked
>server,
>>the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>>
>>.
>>.
>.
>|||Hi Uri,
Thanks in advantaje
I know this, only to remark that although the query runs,
the execution plan is not valid for me.
Any more ideas about the original question ?
>--Original Message--
>The CONVERT dictate to optimyzer use a INDEX SCAN
insetad of INDEX SEEK
>
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:0a4d01c36bb3$36968ce0$a401280a@.phx.gbl...
>> I've had a new test:
>> Select * from LinkedServer.DB.dbo.TB1
>> where convert(char(10),cdatetime,103) <= '01/30/2003'
>> and it runs ok but the execution plan show that the
query
>> uses a table scan rather than a index seek on cdatetime
>> column.
>> Thanks.
>>
>> >--Original Message--
>> >The linked server has the same settings options that
the
>> >main server.
>> >dd/mm/yyyy
>> >>--Original Message--
>> >>Hi,
>> >>
>> >>Well I think that I've tested all possible formats of
>> >>datetime and the query fails.
>> >>
>> >>--Original Message--
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >>This one fails because SQL Server cannot recognize 30
>> as
>> >>a month .
>> >>Have you changed it to '30/01/2003'?
>> >>One more thought: Have you checked regimal settings
on
>> >>the linked server?
>> >>I mean an year 2003 or 03 .
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>"amosquera" <alvarosusana@.vodafone.es> wrote in
message
>> >>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> >> In the original message I've said
>> >>
>> >> I've tested with many diferents formats
>> >>mmddyyyy,yyyymmdd,
>> >> and so on.
>> >>
>> >> and the query fails.
>> >>
>> >> >--Original Message--
>> >> >amosquera
>> >> >Try use 'yyyymmdd' Format
>> >> >
>> >> >
>> >> >
>> >> >"amosquera" <alvarosusana@.vodafone.es> wrote in
>> >message
>> >> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> >> ,
>> >> >>
>> >> >> I runs this querys from QA in one session to my
>> >local
>> >> >> server
>> >> >>
>> >> >> // tb1 in local server
>> >> >> Select * from TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> runs ok
>> >> >>
>> >> >> // tb1 in linked server
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> fails with
>> >> >> conversion from data char to l DateTime out of
>> Range
>> >> >>
>> >> >> // tb1 in linked server
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '01/30/2003'
>> >> >>
>> >> >> fails with
>> >> >> conversion from data char to l DateTime out of
>> Range
>> >> >>
>> >> >>
>> >> >> Two servers have the same language and
dateformat
>> >dmy
>> >> >>
>> >> >> If I open one connection in QA to the linked
>> >server,
>> >>the
>> >> >> query
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> runs ok.
>> >> >>
>> >> >> I've tested with many diferents formats
mmddyyyy,
>> >> >> yyyymmdd, and so on.
>> >> >>
>> >> >> Any Ideas ?
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >>
>> >>
>> >>.
>> >>
>> >>.
>> >>
>> >.
>> >
>
>.
>

Line Numbers in SQL

Is there a function I can put into my select clause that tell me what the
line number of the row is?
i.e. in Oracle you can do something like (Been a year since I've used
Oracle)
select rownum, Field1, Field2
from blah
Any help is appreciated. Thanks.No, you could do something with a temp table and IDENTITY. I believe Yukon
has the rownumber function. What do you need it for?
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uTDWhna8DHA.1052@.TK2MSFTNGP12.phx.gbl...
> Is there a function I can put into my select clause that tell me what the
> line number of the row is?
> i.e. in Oracle you can do something like (Been a year since I've used
> Oracle)
> select rownum, Field1, Field2
> from blah
> Any help is appreciated. Thanks.
>|||It's the only way I can think of to alternate row colors in reporting
services...
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:%233NMuic8DHA.1548@.tk2msftngp13.phx.gbl...
> No, you could do something with a temp table and IDENTITY. I believe Yukon
> has the rownumber function. What do you need it for?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uTDWhna8DHA.1052@.TK2MSFTNGP12.phx.gbl...
the
>|||you should have a look at the subject "row style in matrix" in
microsoft.public.sqlserver.reportingsvcs
Jean-Philippe.
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> a crit dans le
message de news:eDztD6e8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> It's the only way I can think of to alternate row colors in reporting
> services...
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:%233NMuic8DHA.1548@.tk2msftngp13.phx.gbl...
Yukon
message
> the
>|||I don't seem to have a message with that title and the newsgroup is not in
Google groups yet...
"Jean Philippe Vankemmel" <vankemjp@.ifrance.com> wrote in message
news:%23zh0zZG9DHA.452@.TK2MSFTNGP11.phx.gbl...
> you should have a look at the subject "row style in matrix" in
> microsoft.public.sqlserver.reportingsvcs
> Jean-Philippe.
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> a crit dans le
> message de news:eDztD6e8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Yukon
> message
what
used
>|||See here - http://www.microsoft.com/sql/commun...ups/default.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:%23k0kO%23K9DHA.4060@.tk2msftngp13.phx.gbl...
> I don't seem to have a message with that title and the newsgroup is not in
> Google groups yet...
> "Jean Philippe Vankemmel" <vankemjp@.ifrance.com> wrote in message
> news:%23zh0zZG9DHA.452@.TK2MSFTNGP11.phx.gbl...
le
> what
> used
>|||Nothing there, either..
Doesn't come up in search.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eKr4rHL9DHA.400@.tk2msftngp13.phx.gbl...
> See here - http://www.microsoft.com/sql/commun...ups/default.asp
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:%23k0kO%23K9DHA.4060@.tk2msftngp13.phx.gbl...
in
> le
reporting
believe
>|||Nothing there? I just went to it, it's toward the bottom of the page, search
for "reporting services"
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uVp1YYL9DHA.2712@.tk2msftngp13.phx.gbl...
> Nothing there, either..
> Doesn't come up in search.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:eKr4rHL9DHA.400@.tk2msftngp13.phx.gbl...
message
not
> in
dans
> reporting
> believe
in
me
I've
>|||you should have a look at the subject "row style in matrix" in
That is what I'm looking up in that newsgroup. I can find the reporting
services newsgroup.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:%23yp%23tcL9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Nothing there? I just went to it, it's toward the bottom of the page,
search
> for "reporting services"
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uVp1YYL9DHA.2712@.tk2msftngp13.phx.gbl...
http://www.microsoft.com/sql/commun...ups/default.asp
> message
> not
> dans
wrote
> in
tell
> me
> I've
>|||Ah, OK.
I use NNTP and searched and found it, this is the excerpt: (See below)
You will need to add an expression the outer most row grouping on the
matrix.
The attached sample report demonstrates how to do this.
Key Areas:
* Matrix Properties Dialog : Groups Tab : Country Group has an expression
on it
that controls the number of rows:
=System.Math.Ceiling(RowNumber(Nothing)/5)
* Place a PageBreakAtEnd on the Matrix Properties Dialog : Groups Tab :
Country Group.
NOTE: There is a bug on this dialog in RTM version. The checkboxes to
set
PageBreakAt[Start | End] are missing. You will need to hand edit the RDL
to get this
to work. Before proceeding backup your RDL. The code snippet shows where
the
PageBreakAtEnd tag was added to the RDL:
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd> <<<<<< Tag added
here.
</Grouping>
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Avi" <anonymous@.discussions.microsoft.com> wrote in message
news:B9A6926F-2CBD-4C40-9471-49422C3EA39E@.microsoft.com...
> Hi
> Is there any way by which I can specify the number of rows to be displayed
on one page in matrix?
> Suppose I want only 10 rows to be displyed on one page irrespective of
collapsed or expanded state of the groups.
> How do I achieve this?
> -Avi
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:OeKWKlO9DHA.3704@.tk2msftngp13.phx.gbl...
> you should have a look at the subject "row style in matrix" in
> That is what I'm looking up in that newsgroup. I can find the reporting
> services newsgroup.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:%23yp%23tcL9DHA.548@.TK2MSFTNGP11.phx.gbl...
> search
message
> http://www.microsoft.com/sql/commun...ups/default.asp
is
crit
> wrote
> tell
since
>sql

Line Number in Transact SQL

Hi,

How do you generate line numbers in Transact SQL?

Example:

In Sybase, I could use this...
select number(*), customer_name from customer_table

number(*) customer_name
----------------
1 Customer Name 1
2 Customer Name 2
3 Customer Name 3

Thanks,
GeoffOriginally posted by geoff_sy
Hi,

How do you generate line numbers in Transact SQL?

Example:

In Sybase, I could use this...
select number(*), customer_name from customer_table

number(*) customer_name
----------------
1 Customer Name 1
2 Customer Name 2
3 Customer Name 3

Thanks,
Geoff

select number=identity(int,1,1), fak.* into #temp

from (select top 100 percent customer_name from customer_table order by 1) fak

select * from #temp order by 1

drop table #temp|||I don't understant why you suggested a nested query it could more simple I think eg:

select number=identity(int,1,1), customer_name into #temp
from customer_table order by 1 -- (or any other column)

Originally posted by harshal_in
select number=identity(int,1,1), fak.* into #temp

from (select top 100 percent customer_name from customer_table order by 1) fak

select * from #temp order by 1

drop table #tempsql

line break

hi how do i add line break in a select statement
i want
select 'name' + @.name + 'line break here'
+ 'age' + @.ageSELECT 'name' + CHAR(13) + CHAR(10) + 'age';
"ichor" <ichor@.hotmail.com> wrote in message
news:eabgZ4nMGHA.1288@.TK2MSFTNGP09.phx.gbl...
> hi how do i add line break in a select statement
> i want
> select 'name' + @.name + 'line break here'
> + 'age' + @.age
>
>

Limiting the Records in SELECT Statement

Hi..

As everyone knows that there's a keyword in MySQL that is used to limit the records in SELECT statement as

SELECT * FROM Table LIMIT 40,10

means that select from record 40 to onward 10 records.

In SQL Server it is a long way with nested select ... can someone provide an alternate or we have to use the Nested SELECT?

Thanks

Yes

U can specify

select top n * from tablename

similarly u can also specify top n percentage in a select statement.

Check for Keyword top in sql Help

|||

Nested select (technically called a derived table) is the best way to go in SQL. Here are a few articles that cover the subject.

http://www.sqlmag.com/Article/ArticleID/43922/sql_server_43922.html

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

limiting the range of select result

Hi,

i'm searching how to tell to mysql server to limit
the range of a result select.
I would like to have the equivalent of the LIMIT clause in MYSQL.

I know there is TOP but it is not sufficient because it returns
only the first n lines.
I would like to tell sqlserver to begin at a certain offset.

Any idea ?

Thxyou can use a derived table

select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

Will get the second 10 recs.

Limiting SQL results

I have a query that simply needs to determine if data exists in a table for
a given key.
Initially I used COUNT:
Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
The table has about 500,000 of rows so this takes too long.
Then I used TOP to speed things up. But TOP causes a timeout if key X isn't
in the table.
Does anyone have any suggestions on how to simply determine if key X has
records without counting every record?
Does anyone have a clue why TOP goes into never-never land if the table has
a lot of rows?
ThanksTry
if exists (Select 1 from TABLEWITHTONSODATA Where Key = 'X')
begin
...
end
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||Use EXISTS:
...
EXISTS
(SELECT *
FROM tablewithtonsodata
WHERE key = 'X')
If it's still too slow then consider indexing the column "key".
--
David Portas
--
Please reply only to the newsgroup
--|||You could do...
IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
SELECT 1
Or I believe this would perform equally well:
SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
COUNT(*) has to actually go and count all of the rows. TOP has to return
based on ordering of some sort. EXISTS just needs to find a single row that
matches.
I assume that Key is indexed?
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||All great suggestions...will try. I am still wondering if anyone else has
had problems with TOP in large tables.
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||In article <ueHi#GtqDHA.1632@.TK2MSFTNGP10.phx.gbl>, amachanic@.air-
worldwide.nospamallowed.com said...
> You could do...
> IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> SELECT 1
> Or I believe this would perform equally well:
> SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> COUNT(*) has to actually go and count all of the rows. TOP has to return
> based on ordering of some sort. EXISTS just needs to find a single row that
> matches.
Top doesn't have to have an order. I would think you would want to go
with:
IF EXISTS (SELECT TOP 1 'X' FROM TableWithTonsOfData With (NoLock) Where
Key = 'X')
-- Do your thing
The NoLock should help depending on usage.|||"Brad" <me@.privacy.net> wrote in message
news:MPG.1a1f0ba92c81d36f98b995@.news...
> Top doesn't have to have an order. I would think you would want to go
> with:
An unordered TOP is meaningless. TOP 1 what?|||In article <OuROemvqDHA.3732@.tk2msftngp13.phx.gbl>, amachanic@.air-
worldwide.nospamallowed.com said...
> "Brad" <me@.privacy.net> wrote in message
> news:MPG.1a1f0ba92c81d36f98b995@.news...
> > Top doesn't have to have an order. I would think you would want to go
> > with:
> An unordered TOP is meaningless. TOP 1 what?
It doesn't matter in his case because he is just checking existence. Top
1 is perfect for that because it tells the server to find one record in
whatever order is easiest and then stop.
I use top 1 * or top 10 * all of the time to just get a sample record(s).|||Brad wrote:
> In article <ueHi#GtqDHA.1632@.TK2MSFTNGP10.phx.gbl>, amachanic@.air-
> worldwide.nospamallowed.com said...
> > You could do...
> >
> > IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> > SELECT 1
> >
> > Or I believe this would perform equally well:
> >
> > SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> >
> > COUNT(*) has to actually go and count all of the rows. TOP has to return
> > based on ordering of some sort. EXISTS just needs to find a single row that
> > matches.
> Top doesn't have to have an order. I would think you would want to go
> with:
> IF EXISTS (SELECT TOP 1 'X' FROM TableWithTonsOfData With (NoLock) Where
> Key = 'X')
> -- Do your thing
> The NoLock should help depending on usage.
Using EXISTS(SELECT TOP 1) will not be faster than EXIST(SELECT *), but
equally fast. This is because both can be satisfied with exactly the
same algorithm.
For that reason I would suggest you use the ANSI-SQL standard, and avoid
the SQL-Server proprietary TOP keyword.
Gert-Jan

limiting query result based on AS clause

can some point me to a sql statement that would perfrom the equivalent of th
e
following statement?
select Desc, (select count (*) from CART_Items where
fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
from CART_ItemCategories
where itemcount >0
typical ouput
Desc itemcount
Cat 1 0
Cat 2 2
Cat 3 1
Cat 4 0
i would simply like to limit my results to itemcount values greater than zer
o.SELECT
ic.Desc,
COUNT(ci.fkItemCategoryID)
FROM
Cart_Items ci
INNER JOIN
Cart_ItemCategories ic
ON
ci.fkItemCategoryID = ic.pkItemCategoryID
GROUP BY
ic.Desc
HAVING
COUNT(ci.fkItemCategoryID) > 0;
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
> can some point me to a sql statement that would perfrom the equivalent of
> the
> following statement?
> select Desc, (select count (*) from CART_Items where
> fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
> from CART_ItemCategories
> where itemcount >0
>
> typical ouput
> Desc itemcount
> Cat 1 0
> Cat 2 2
> Cat 3 1
> Cat 4 0
> i would simply like to limit my results to itemcount values greater than
> zero.|||thanks a million
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT
> ic.Desc,
> COUNT(ci.fkItemCategoryID)
> FROM
> Cart_Items ci
> INNER JOIN
> Cart_ItemCategories ic
> ON
> ci.fkItemCategoryID = ic.pkItemCategoryID
> GROUP BY
> ic.Desc
> HAVING
> COUNT(ci.fkItemCategoryID) > 0;
>
>
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
>
>

Limitations in a query !

Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

I've read at some documentation that there is a limitation for Columns
in a table and i.e. we can have only 1024 columns per table, is this
true?
Plz help me !!

Thanx in advance.

Kind Regards,
HarryOn 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
>on "IN" clause in select query.
>for example :
>"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
>('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.

Quote:

Originally Posted by

>I've read at some documentation that there is a limitation for Columns
>in a table and i.e. we can have only 1024 columns per table, is this
>true?

Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.

Quote:

Originally Posted by

>Plz help me !!
>
>Thanx in advance.
>
>Kind Regards,
>Harry

Roy Harvey
Beacon Falls, CT|||Thanx alot Roy. :)

Roy Harvey wrote:

Quote:

Originally Posted by

On 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:
>

Quote:

Originally Posted by

Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

>
There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.
>

Quote:

Originally Posted by

I've read at some documentation that there is a limitation for Columns
in a table and i.e. we can have only 1024 columns per table, is this
true?

>
Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.
>

Quote:

Originally Posted by

Plz help me !!

Thanx in advance.

Kind Regards,
Harry

>
Roy Harvey
Beacon Falls, CT

|||Harsha, See this post

http://groups.google.com/group/comp...2da17b97723fe15
harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

Thanx alot Roy. :)
>
Roy Harvey wrote:

Quote:

Originally Posted by

On 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
>on "IN" clause in select query.
>for example :
>"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
>('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.

Quote:

Originally Posted by

>I've read at some documentation that there is a limitation for Columns
>in a table and i.e. we can have only 1024 columns per table, is this
>true?

Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.

Quote:

Originally Posted by

>Plz help me !!
>
>Thanx in advance.
>
>Kind Regards,
>Harry

Roy Harvey
Beacon Falls, CT

|||(harsha.21aug@.gmail.com) writes:

Quote:

Originally Posted by

I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

To add to Roy's response, be aware of that when the list grows very
long (1000 elements) compile times on SQL 2000 can be absymal. SQL 2005
appears to do better in this regard.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Limit total # of records used in a report

For performance purposes, is it possible to limit the # of records
returned for use in a report? I know you can do SELECT TOP 1000, but in
instances where the report contains several subselects, you have to
perform that function with every SELECT statement. This also has the
potential of making maintenance hectic.
Is there a way to globally limit the rendered rows?
Thanks!
MikeHi Mike,
There is no direct properties of records per page.FOr controlling the
records per page you need to specify the height and accordingly number of
recrds are set to the report.
Thanks
Jasvinder
"Bassist695" wrote:
> For performance purposes, is it possible to limit the # of records
> returned for use in a report? I know you can do SELECT TOP 1000, but in
> instances where the report contains several subselects, you have to
> perform that function with every SELECT statement. This also has the
> potential of making maintenance hectic.
> Is there a way to globally limit the rendered rows?
> Thanks!
> Mike
>

limit the select to single row

Hello,
How can I limit the select statement to 1 single row ?
How can I limit the select statement to specific no. of rows ?
Thanks :)SELECT TOP 1
FROM ...
ORDER BY ...
SELECT TOP 10
FROM ...
ORDER BY ...
Note that without ORDER BY, which rows that are returned is undefined.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eitan M" <no_spam_please@.nospam_please.com> wrote in message
news:%23lJnZbwoFHA.860@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I limit the select statement to 1 single row ?
> How can I limit the select statement to specific no. of rows ?
> Thanks :)
>|||The obvious answer to your first question is to use a WHERE clause that
references a unique key.
In the second case you'll probably want to take a look at the SELECT
TOP n feature or use SET ROWCOUNT.
With TOP and ROWCOUNT be aware that unless you specify a unique key in
the ORDER BY clause or use the TOP WITH TIES modifier you may get
undefined results. That ought to be common sense but too many times I
see people omit to specify a well-defined criteria for a top n subset.
David Portas
SQL Server MVP
--

Limit the range to select on dateparameter

Hello out there,

I try to force my users to select a date not more than 6 months in the past. How can I limit the range of dateselection in the reportparameter? Any ideas?

Thank you very much!

Kind regards

dollmaker

Up

|||

I'm assuming that you have FromDate and ToDate Report parameters, which have values from a query.

Try doing this :

Set the Available values for the ToDate parameter as Non-Queried and put an expression in the LABEL and VALUES columns, evaluating the ToDate Parameter against the FromDate parameter with the DateDiff function, and then allowing only the values within 6 months.

I don't know if this is the exact scenario you're facing, but this is how I would do for my application.

Let me know how it goes. All the best!

Limit the range to select on dateparameter

Hello out there,

I try to force my users to select a date not more than 6 months in the past. How can I limit the range of dateselection in the reportparameter? Any ideas?

Thank you very much!

Kind regards

dollmaker

Up

|||

I'm assuming that you have FromDate and ToDate Report parameters, which have values from a query.

Try doing this :

Set the Available values for the ToDate parameter as Non-Queried and put an expression in the LABEL and VALUES columns, evaluating the ToDate Parameter against the FromDate parameter with the DateDiff function, and then allowing only the values within 6 months.

I don't know if this is the exact scenario you're facing, but this is how I would do for my application.

Let me know how it goes. All the best!

Limit the number of records returned in Stored procedure.

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.

Query

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

Select the top 100 records:

SELECT TOP 100 *

Select the top 10% of the total number of records:

SELECT TOP 10 percent *

|||

Or if you want to retrieve a limited number of records, but not necessarily the top X records, you could use the row_number() function. For example, to retrieve records 250-299 you could try

 
1SELECT*
2FROM3(
4SELECT *, row = row_number()OVER (ORDER BY id)
6FROM yourtable
7) a
8WHERE rowBETWEEN 250AND 299
 

LIMIT rows with an offset

I have a query in MySQL which needs to be translated to an equivalent in MSSQL Server. The query uses a LIMIT clause with an offset.

select * from test LIMIT 10,5

(meaning from the 10th row containing 5 rows in the result set)

How can we port the same logic to SQL Server ? I know TOP can retrieve top n rows but how can we specify the offset ?

Any help is appreciated !Any such offsets or paging is meaningless without an ORDER BY clause.|||Blindman is quite correct, without an ORDER BY clause this is dangerous in any SQL dialect.

SQL-2000 doesn't have a good way to implement the functionality of MySQL's LIMIT clause. Until a new version of SQL provides a better solution, the best answer I can give you is that your code needs to provide the paging functionality.

The best way that I know to provide paging functionality is to use ORDER BY with a "rowset key" within your data and the TOP N syntax to limit the number of rows returned by a SELECT statement. Keep track of the rowset key of the last row returned, and use that as the seed for the next iteration (page) of your SELECT.

-PatP|||Appreciate your help ! Thanks !|||It must be paging day

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx