I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
...or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
Brian
Brian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>
|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query[vbcol=seagreen]
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
any
>
|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>
Showing posts with label persons. Show all posts
Showing posts with label persons. Show all posts
Wednesday, March 28, 2012
ORDER BY Question
I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
> >I have a table which contains a sNAME field - a persons name of "firstame
> >space
> > lastname".
> >
> > I have a query that delivers a recordset using "ORDER BY sName"
> >
> > Now, a client has asked to see the data sorted by "lastname". Is there
any
> > method using an SQL expression to deliver that recordset sorted by
> > lastname?
> >
> > ..or do I have to re-organize my data fields and data?
> >
> > I was hoping for a quick 'sql function' solution instead of changing the
> > database.
> >
> > Brian
> >
>|||Jerry,
It worked and it was quick too...thanks again
--
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>>I have a table which contains a sNAME field - a persons name of "firstame
>>space
>> lastname".
>> I have a query that delivers a recordset using "ORDER BY sName"
>> Now, a client has asked to see the data sorted by "lastname". Is there
>> any
>> method using an SQL expression to deliver that recordset sorted by
>> lastname?
>> ..or do I have to re-organize my data fields and data?
>> I was hoping for a quick 'sql function' solution instead of changing the
>> database.
>> Brian
>
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
> >I have a table which contains a sNAME field - a persons name of "firstame
> >space
> > lastname".
> >
> > I have a query that delivers a recordset using "ORDER BY sName"
> >
> > Now, a client has asked to see the data sorted by "lastname". Is there
any
> > method using an SQL expression to deliver that recordset sorted by
> > lastname?
> >
> > ..or do I have to re-organize my data fields and data?
> >
> > I was hoping for a quick 'sql function' solution instead of changing the
> > database.
> >
> > Brian
> >
>|||Jerry,
It worked and it was quick too...thanks again
--
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>>I have a table which contains a sNAME field - a persons name of "firstame
>>space
>> lastname".
>> I have a query that delivers a recordset using "ORDER BY sName"
>> Now, a client has asked to see the data sorted by "lastname". Is there
>> any
>> method using an SQL expression to deliver that recordset sorted by
>> lastname?
>> ..or do I have to re-organize my data fields and data?
>> I was hoping for a quick 'sql function' solution instead of changing the
>> database.
>> Brian
>
ORDER BY Question
I have a table which contains a sNAME field - a persons name of "firstame sp
ace
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
any[vbcol=seagreen]
>|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>sql
ace
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
any[vbcol=seagreen]
>|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>sql
Wednesday, March 7, 2012
Oracle linked server
I have several customers that are using oracle linked servers on MSSQL2k. Persons that have multisession rights on the oracle side seem to not have problems querying multiple tables but one of my customers only has the rights to have two sessions at any given time. This customer with the session limitation is trying to query multiple tables and it appears that each table query constitutes a session therefore after the second table is queried they exceed the quota and the job stalls.
I am currently using the microsoft oledb provided for each linked server. For the provider options I selected Nested Queries and AllowInProcess. I have tried the oracle provider, recieved errors, but these errors have not been resolved.
Using msaccess and odbc in the prior implementation worked for this customer. Does anyone have any suggestions or tech links that cover this scenario.
ThanksThis is the message at the end of the job\query.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
I am currently using the microsoft oledb provided for each linked server. For the provider options I selected Nested Queries and AllowInProcess. I have tried the oracle provider, recieved errors, but these errors have not been resolved.
Using msaccess and odbc in the prior implementation worked for this customer. Does anyone have any suggestions or tech links that cover this scenario.
ThanksThis is the message at the end of the job\query.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
Subscribe to:
Posts (Atom)