SQL server connection: 2012

Tuesday, March 20, 2012

Ansi

What is ANSI SQL and how do you spell ANSI?ANSI = american national standards institute

ANSI is spelled I-S-O|||Right, I would also suggest to rename this forum to "ISO SQL". ANSI is "just" a national body that participates in the standardization process. SQL comes from ISO and ANSI merely copies it into a national standard.|||in that case, let me cast my vote for "Standard SQL"

which should eliminate any confusion, eh|||I always find it amusing when dealing with users around the world that ANSI is recognized worldwide, and has only one commonly used expanded form (American National Standards Institute). While there are a few companies that have kludged names to allow them to usurp the ANSI acronym, they are oddball efforts which don't seem to confuse newcomers to the technology field.

ISO on the other hand is recognized much less frequently, with much less certainty, and looking up the full name from the ISO acronym can be very confusing, especially for Asians.

I can always explain what I mean by the term ISO, and have grown accustomed to simply providing the URL with my first reference in order to avoid confusion. I've never, ever needed to explain what I meant when I used the ANSI acronym.

I think that we ought to call it "Joe's SQL", in reference to Mr. Celko and his efforts to entice, cajole, or bludgeon everyone onto a single playing field! ;)

On a slightly more serious note, I like "Standard SQL" as a description, but I'm pretty sure that there is already a United States copyright fight on the phrase. I think that Oracle has first claim on the copyright, but that claim was disputed by Microsoft, IBM, and possibly other companies too. I haven't paid much attention to this, but I remember that the phrase was disputed so that it fell out of general use even by the marketing types.

I don't really care what we call the forum, but I like the idea of a standards based forum for discussing SQL that is both aware of how the different engines implement SQL, but remains as agnostic as is practical in regard to those implementations.

-PatP|||All this time i've been spelling it "antsy".|||What about Straight-up SQL?|||We only had a flipping vote on this in private a week ago. Perhaps we need some Standards for renaming forums too.

Since we are all getting a say I'd like to expand SQL to Structured Query Language - some people pronounce it ESS QUEUE ELLLL and others SEQUEL. It is bound to cause confusion, especially if reading out loud.|||excellent idea, i want to change my vote to Structured Query Language too

:)|||excellent idea, i want to change my vote to Structured Query Language too

:)Blimey - talk about a floating voter - no wonder there are 736 main stream political parties in Canada ;)|||haha check out Brazil...|||haha check out Brazil...

Sure, you buying?

Just watch out from becoming a hostage|||no way man|||Sure, you buying?

Just watch out from becoming a hostage

Hostages in Brazil will only become Americans who don't want to have the exactly same rules applied to them as the US applies to all foreigners, i.e. having their picture and finger prints taken at the port of entrance. ;)|||Hostages in Brazil will only become Americans who don't want to have the exactly same rules applied to them as the US applies to all foreigners, i.e. having their picture and finger prints taken at the port of entrance. ;)

Yeah right

talk about trying to stop a flood

Anoying bug...

Hello,
I have a report which used AS as a source. I have a report parameter and a
dataset with the following query:
="with
Member [Measures].[BGBNLUniqueName] as
'[Product].[Concern].currentmember.UniqueName'
member [Measures].[BGBNLDisplayName] as 'IIF(
[Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
Business Groups"", [Product].[Concern].Currentmember.Name)'
set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6920]} ' "
& IIF( Parameters!Product_BG.Label = "All Business Groups",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
")' ") & "
select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] } on
columns,
{ [ChosenProducts] } on rows
from [EDWH_SMRT_VIRT_SALESOUT]"
I have tested this in the mdx sample application and in order to work the
Parameter has to be enclosed by double quotes. When I do that I always get
the errormessage from AS that the value from the parameter is not enclosed
with double quotes.
Any Ideas? How can I escape the double quote?
Regards,
QI think the third part of the expression should be this:
& IIF( Parameters!Product_BG.Label = "All Business Groups", "Set
[ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = """ & Parameters!Product_BG.Label &
""")' ") & "
Note: I have added "" around your parameter string concatenation.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:8DBC307C-6823-46CE-9835-31EAF552B941@.microsoft.com...
> Hello,
> I have a report which used AS as a source. I have a report parameter and a
> dataset with the following query:
> ="with
> Member [Measures].[BGBNLUniqueName] as
> '[Product].[Concern].currentmember.UniqueName'
> member [Measures].[BGBNLDisplayName] as 'IIF(
> [Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
> Business Groups"", [Product].[Concern].Currentmember.Name)'
> set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
> [Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6920]} ' "
> & IIF( Parameters!Product_BG.Label = "All Business Groups",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
> ")' ") & "
> select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] }
> on
> columns,
> { [ChosenProducts] } on rows
> from [EDWH_SMRT_VIRT_SALESOUT]"
>
> I have tested this in the mdx sample application and in order to work the
> Parameter has to be enclosed by double quotes. When I do that I always get
> the errormessage from AS that the value from the parameter is not enclosed
> with double quotes.
> Any Ideas? How can I escape the double quote?
> Regards,
> Q|||Hi Robert,
I have tried this but still get the error.
Q
"Robert Bruckner [MSFT]" wrote:
> I think the third part of the expression should be this:
> & IIF( Parameters!Product_BG.Label = "All Business Groups", "Set
> [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = """ & Parameters!Product_BG.Label &
> """)' ") & "
> Note: I have added "" around your parameter string concatenation.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:8DBC307C-6823-46CE-9835-31EAF552B941@.microsoft.com...
> > Hello,
> >
> > I have a report which used AS as a source. I have a report parameter and a
> > dataset with the following query:
> >
> > ="with
> > Member [Measures].[BGBNLUniqueName] as
> > '[Product].[Concern].currentmember.UniqueName'
> > member [Measures].[BGBNLDisplayName] as 'IIF(
> > [Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
> > Business Groups"", [Product].[Concern].Currentmember.Name)'
> >
> > set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
> > [Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6920]} ' "
> >
> > & IIF( Parameters!Product_BG.Label = "All Business Groups",
> > "Set [ChosenProducts] as ' Filter( [Products],
> > [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> > "Set [ChosenProducts] as ' Filter( [Products],
> > [Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
> > ")' ") & "
> >
> > select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] }
> > on
> > columns,
> > { [ChosenProducts] } on rows
> >
> > from [EDWH_SMRT_VIRT_SALESOUT]"
> >
> >
> > I have tested this in the mdx sample application and in order to work the
> > Parameter has to be enclosed by double quotes. When I do that I always get
> > the errormessage from AS that the value from the parameter is not enclosed
> > with double quotes.
> >
> > Any Ideas? How can I escape the double quote?
> >
> > Regards,
> > Q
>
>

another xp_smtp_sendmail question

A snippet from
http://support.microsoft.com/default...b;en-us;263556
says:
How To Set Up SQL Mail With An Internet Mail Server
WARNING: Unless you can guarantee 100% connectivity to your Internet mail
server without the need to reauthenticate your mail logon, SQL Mail can
hang, forcing you to stop and restart SQL Server. Use of an Internet mail
server is not a recommended solution but may be the only alternative in some
cases.
The procedure for setting up SQL Mail with an Internet mail server
(POP3/SMTP server) is similar to that for setting up SQL Mail with an
Exchange Server:
I was wondering if anyone has experienced similar downfalls to using
xp_smtp_sendmail if the SMTP box is unavailable for a while?
TIA, ChrisR
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
Nope.
Keith
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/default...b;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>
|||The problem with sp_sendmail has to do with using Outlook as a Service.
Outlook will try and pop up a dialog box. Since there is normally no UI,
this fails and hangs the MAPI session. The only way to recover is to drop
both ends of the MAPI session with a SQL server restart.
Xp_smtp_sendmail simply times out and returns an error code to the procedure
caller.
In short, the same problem does not and cannot occur. This is the number
one reason I use xp_smtp_sendmail.
Geoff N. Hiten
Microsoft SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/default...b;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>
|||Another way to use CDO: CDO.Message
with sp_OA...
If you want I can post example.
Regards,
SkyWalker.
"ChrisR" wrote:

> A snippet from
> http://support.microsoft.com/default...b;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in some
> cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>
>
|||Im good now, thanks.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:1B4578C6-776D-4D26-9B1C-1D3DD9629CC1@.microsoft.com...[vbcol=seagreen]
> Another way to use CDO: CDO.Message
> with sp_OA...
> If you want I can post example.
> Regards,
> SkyWalker.
> "ChrisR" wrote:
|||Thanks to all, Im alot more comfortable with this now.
ChrisR
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/default...b;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>

another xp_smtp_sendmail question

A snippet from
http://support.microsoft.com/defaul...kb;en-us;263556
says:
How To Set Up SQL Mail With An Internet Mail Server
WARNING: Unless you can guarantee 100% connectivity to your Internet mail
server without the need to reauthenticate your mail logon, SQL Mail can
hang, forcing you to stop and restart SQL Server. Use of an Internet mail
server is not a recommended solution but may be the only alternative in some
cases.
The procedure for setting up SQL Mail with an Internet mail server
(POP3/SMTP server) is similar to that for setting up SQL Mail with an
Exchange Server:
I was wondering if anyone has experienced similar downfalls to using
xp_smtp_sendmail if the SMTP box is unavailable for a while?
TIA, ChrisR> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
Nope.
Keith
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/defaul...kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>|||The problem with sp_sendmail has to do with using Outlook as a Service.
Outlook will try and pop up a dialog box. Since there is normally no UI,
this fails and hangs the MAPI session. The only way to recover is to drop
both ends of the MAPI session with a SQL server restart.
Xp_smtp_sendmail simply times out and returns an error code to the procedure
caller.
In short, the same problem does not and cannot occur. This is the number
one reason I use xp_smtp_sendmail.
Geoff N. Hiten
Microsoft SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/defaul...kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>|||Another way to use CDO: CDO.Message
with sp_OA...
If you want I can post example.
Regards,
SkyWalker.
"ChrisR" wrote:

> A snippet from
> http://support.microsoft.com/defaul...kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in so
me
> cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>
>|||Im good now, thanks.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:1B4578C6-776D-4D26-9B1C-1D3DD9629CC1@.microsoft.com...[vbcol=seagreen]
> Another way to use CDO: CDO.Message
> with sp_OA...
> If you want I can post example.
> Regards,
> SkyWalker.
> "ChrisR" wrote:
>|||Thanks to all, Im alot more comfortable with this now.
ChrisR
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/defaul...kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>

another xp_smtp_sendmail question

A snippet from
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
says:
How To Set Up SQL Mail With An Internet Mail Server
WARNING: Unless you can guarantee 100% connectivity to your Internet mail
server without the need to reauthenticate your mail logon, SQL Mail can
hang, forcing you to stop and restart SQL Server. Use of an Internet mail
server is not a recommended solution but may be the only alternative in some
cases.
The procedure for setting up SQL Mail with an Internet mail server
(POP3/SMTP server) is similar to that for setting up SQL Mail with an
Exchange Server:
I was wondering if anyone has experienced similar downfalls to using
xp_smtp_sendmail if the SMTP box is unavailable for a while?
TIA, ChrisR> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
Nope.
Keith
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>|||The problem with sp_sendmail has to do with using Outlook as a Service.
Outlook will try and pop up a dialog box. Since there is normally no UI,
this fails and hangs the MAPI session. The only way to recover is to drop
both ends of the MAPI session with a SQL server restart.
Xp_smtp_sendmail simply times out and returns an error code to the procedure
caller.
In short, the same problem does not and cannot occur. This is the number
one reason I use xp_smtp_sendmail.
Geoff N. Hiten
Microsoft SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>|||Another way to use CDO: CDO.Message
with sp_OA...
If you want I can post example.
Regards,
SkyWalker.
"ChrisR" wrote:
> A snippet from
> http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in some
> cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>
>|||Im good now, thanks.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:1B4578C6-776D-4D26-9B1C-1D3DD9629CC1@.microsoft.com...
> Another way to use CDO: CDO.Message
> with sp_OA...
> If you want I can post example.
> Regards,
> SkyWalker.
> "ChrisR" wrote:
>> A snippet from
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
>> says:
>> How To Set Up SQL Mail With An Internet Mail Server
>> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
>> server without the need to reauthenticate your mail logon, SQL Mail can
>> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
>> server is not a recommended solution but may be the only alternative in
>> some
>> cases.
>> The procedure for setting up SQL Mail with an Internet mail server
>> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
>> Exchange Server:
>>
>> I was wondering if anyone has experienced similar downfalls to using
>> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>>
>> TIA, ChrisR
>>
>>|||Thanks to all, Im alot more comfortable with this now.
ChrisR
"ChrisR" <noemail@.bla.com> wrote in message
news:O%23iSi$VYFHA.4036@.tk2msftngp13.phx.gbl...
>A snippet from
>http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
> says:
> How To Set Up SQL Mail With An Internet Mail Server
> WARNING: Unless you can guarantee 100% connectivity to your Internet mail
> server without the need to reauthenticate your mail logon, SQL Mail can
> hang, forcing you to stop and restart SQL Server. Use of an Internet mail
> server is not a recommended solution but may be the only alternative in
> some cases.
> The procedure for setting up SQL Mail with an Internet mail server
> (POP3/SMTP server) is similar to that for setting up SQL Mail with an
> Exchange Server:
>
> I was wondering if anyone has experienced similar downfalls to using
> xp_smtp_sendmail if the SMTP box is unavailable for a while?
>
> TIA, ChrisR
>
>sql

Another WTF for MS - Row Count Error

I try to get a Row Count from data flow - and get Error:
[Row Count [724]] Error: The variable "User::COUNT_NEWIMGS" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

I tried setting COUNT_NEWIMGS on Package Level, on Data Flow level (Int32 datatype)
i tried specifying variable as "COUNT_NEWIMGS", "User::COUNT_NEWIMGS", "@.[COUNT_NEWIMGS]" - still the same error

And then when I'm ready to crash my keyboard (again) - I found on another forum that Row Count doesnt even update that variable until the dft is complete. WTF!!?!!

TheViewMaster wrote:
I try to get a Row Count from data flow - and get Error:
[Row Count [724]] Error: The variable "User::COUNT_NEWIMGS" specified by VariableName property is not a valid variable. Need a valid variable name to write to.
I tried setting COUNT_NEWIMGS on Package Level, on Data Flow level (Int32 datatype)
i tried specifying variable as "COUNT_NEWIMGS", "User::COUNT_NEWIMGS", "@.[COUNT_NEWIMGS]" - still the same error

Its case-sensitive. Could that be the problem?

TheViewMaster wrote:
And then when I'm ready to crash my keyboard (again) - I found on another forum that Row Count doesnt even update that variable until the dft is complete. WTF!!?!!

Well of course it doesn't. If you want to know how many rows are in a data path you have to wait until all the rows have gone through the data path before you know. How can you possibly have a complaint about that?

-Jamie

|||Please be sure that you don't have a trailing space when you type in the variable name...|||

Jamie Thomson wrote:
Well of course it doesn't. If you want to know how many rows are in a data path you have to wait until all the rows have gone through the data path before you know. How can you possibly have a complaint about that?
-Jamie

IDK - How the data flow was suppose to work that it takes in List of Images to Unzip - it checks in DB if those are required & if they are newer version. Then it uses Script component (Xceed) to unzip. Since - when you run it in BIDS - you can see no of records - I wanted to show status of progress e.g. 250 of 2500 images unzipped - 10% complete.

To my surprise - even when using zip component the most optimal way (creating an array of imagenames to unzip) - doing it in data flow was MUCH SLOWER than in Script task which unzipped images one-by-one.
Following is approximate time codes to unzip 767 images to a network share:
Data Flow - unzip 1-by-1: 2:55
Data Flow - array of imgs: 1:02
Custom Script task 1by1: 0:40

SO - thanks guys for help - but I'm not sold on data flow anymore as I have serious doubts about it's performance/usability (you can't even do a task like Update in reasonable time) - which leads me to use more SQL and application code & rely less on SSIS.

BTW - How many MS employees did it take to create the SSIS?|||

Jamie Thomson wrote:

TheViewMaster wrote:
Jamie Thomson wrote:
Well of course it doesn't. If you want to know how many rows are in a data path you have to wait until all the rows have gone through the data path before you know. How can you possibly have a complaint about that?
-Jamie

IDK - How the data flow was suppose to work that it takes in List of Images to Unzip - it checks in DB if those are required & if they are newer version. Then it uses Script component (Xceed) to unzip. Since - when you run it in BIDS - you can see no of records - I wanted to show status of progress e.g. 250 of 2500 images unzipped - 10% complete.
To my surprise - even when using zip component the most optimal way (creating an array of imagenames to unzip) - doing it in data flow was MUCH SLOWER than in Script task which unzipped images one-by-one.
Following is approximate time codes to unzip 767 images to a network share:
Data Flow - unzip 1-by-1: 2:55
Data Flow - array of imgs: 1:02
Custom Script task 1by1: 0:40
SO - thanks guys for help - but I'm not sold on data flow anymore as I have serious doubts about it's performance/usability (you can't even do a task like Update in reasonable time) - which leads me to use more SQL and application code & rely less on SSIS.
BTW - How many MS employees did it take to create the SSIS?

Huh? You're trying to unzip files within the data-flow? Why would you try and do that?

The data-flow is for moving data. Unzipping files is all about preparing data and no way should that be done in the pipeline. I'm worried that there's some documentation out there somewhere that leads you to think that you SHOULD be doing this. If there is, please point me to it and we'll go about getting it changed.

Why do you continually try and blame anyone but yourself for your own misunderstanding? I'm not trying to blame you for anything, if you misunderstand something then that's ok, let's try and put it right, but there are right and wrong ways of achieving things and in this instance you are trying to achieve something in the wrong way. You can't blame Microsoft for that.

And quite frankly I'm also sick to death of your sarcastic pithy little put-downs of Microsoft and the SSIS team as well. If you have gripes then address them in the right way but veiled insults to people that you've never met when you're hidden behind the anonymity of a computer screen isn't going to endear me to you and therefore I'm less likely to help you out (which I believe I have done quite a bit). Criticism is welcome if its constructive and valid. If its neither of those things, please don't bother.

There's a case in point right here on this thread. I see that you have marked Phil's original answer as correct which means that this was your mistake. Hardly warrants a title of "Another WTF for MS" does it?

You're right, you can't do updates from the pipeline particularly quickly. This is largely inherent in the very nature of an UPDATE and no ETL tool in the world will treat it any differently. With SSIS you have a perfectly valid and performant workaround - stage it to a transient table and issue a set-based update.

-Jamie

|||My bad - sorry I got overly frustrated.

Thanks for all the help - appreciate it

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentid
try:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||To determine which is most efficient, you need to look at the execution plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||You are absolutely correct. I meant to include the LEFT on the join.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> from
> to
> exist
>
|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

another way to write this query?

To determine which is most efficient, =you need to look at the execution plans, examine the I/O, and execution =times.
Here is an alternative:
SELECT StudentID =FROM Students s JOIN CourseStudent c ON s.StudentID =3D =c.StudentIDWHERE c.StudentID IS NULL
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Dodo Lurker" =wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...> =Hi> > I have the following query> > select studentid =from students where studentid not in (select studentid from> coursestudent)> > is there some way I could write this =with using the 'not in'?> > student is a table of students. coursestudent is a join table of courses to> students. I'm trying> to find those students who are not registered for a course. While the> above query works,> I'd =like to find a more efficient query.> > create table =student> (studentid int identity primary key,> name varchar(50),> address1varchar(50),> address2 varchar(50),> city varchar(50),> state varchar(4),> zip varchar(11))> => create table coursestudent> (courseid int> studentid int)> > notes:> - non clustered index on courseid =and non clustered index on studentid exist> - there is a foreign key =relationship from coursestudent's studentid to> student studentid> => > >

--=_NextPart_000_0247_01C6E0CB.6186E090--|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
>> Hi
>> I have the following query
>> select studentid from students where studentid not in (select studentid
> from
>> coursestudent)
>> is there some way I could write this with using the 'not in'?
>> student is a table of students. coursestudent is a join table of courses
> to
>> students. I'm trying
>> to find those students who are not registered for a course. While the
>> above query works,
>> I'd like to find a more efficient query.
>> create table student
>> (studentid int identity primary key,
>> name varchar(50),
>> address1varchar(50),
>> address2 varchar(50),
>> city varchar(50),
>> state varchar(4),
>> zip varchar(11))
>> create table coursestudent
>> (courseid int
>> studentid int)
>> notes:
>> - non clustered index on courseid and non clustered index on studentid
> exist
>> - there is a foreign key relationship from coursestudent's studentid to
>> student studentid
>>
>>
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentidtry:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||To determine which is most efficient, you need to look at the execution plan
s, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.co
mcast.com...
> Hi
>
> I have the following query
>
> select studentid from students where studentid not in (select studentid fr
om
> coursestudent)
>
> is there some way I could write this with using the 'not in'?
>
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
>
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
>
> create table coursestudent
> (courseid int
> studentid int)
>
> notes:
> - non clustered index on courseid and non clustered index on studentid exi
st
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
>
>|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> from
> to
> exist
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

Another Way To Write a Stored Procedure

In the stored procedure below, there are 9 different if conditions being
checked. I was wondering if there is a more efficient way (or not) to write
this procedure, without having to write the same select criteria each time.
The only thing different is the where clause. Any suggestions are greatly
appreciated.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC ListParticipantsByRole(
@.testRoleEnum int,
@.errorID int = 0 OUTPUT)
AS
SET NOCOUNT ON
-- List Originators
IF (@.testRoleEnum = 1)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleOriginator = 1
ORDER BY P.DisplayName
-- List Validators
IF (@.testRoleEnum = 2)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidator = 1
ORDER BY P.DisplayName
-- List Screeners
IF (@.testRoleEnum = 3)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleScreener = 1
ORDER BY P.DisplayName
-- List SMEs
IF (@.testRoleEnum = 4)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleSME = 1
ORDER BY P.DisplayName
-- List Validation OPRs
IF (@.testRoleEnum = 5)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidationOPR = 1
ORDER BY P.DisplayName
-- List Validation Authorities
IF (@.testRoleEnum = 6)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidationAuthority = 1
ORDER BY P.DisplayName
-- List Officials
IF (@.testRoleEnum = 7)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleOfficial = 1
ORDER BY P.DisplayName
-- List Test Directors
IF (@.testRoleEnum = 8)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleTestDirector = 1
ORDER BY P.DisplayName
-- List Survey Participants
IF (@.testRoleEnum = 9)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleSurveyParticipant = 1
ORDER BY P.DisplayName
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE 1 = CASE @.testRoleEnum
when 1 then T.TestRoleOriginator
when 2 then T.TestRoleValidator
...
end
ORDER BY P.DisplayName
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to
> write
> this procedure, without having to write the same select criteria each
> time.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Mike,
A redesign of TestParticipants is the best solution. Instead of
having a bunch of separate columns TestRoleThis, TestRoleThat,
TestRoleOther, ... you should have, depending on whether
a participant can have more than one role, either a column TestRole,
with possible values 'Validator', 'Screener', etc., or a separate table
TestParticipantRoles that links the participants with their roles.
Then your stored procedure becomes a single simple query like
this (case where there is a linking table):
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN TestParticipantRoles R ON T.PersonnelID = R.PersonnelId
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE R.RoleID = @.testRoleEnum
ORDER BY P.DisplayName
Short of that, you can still now do
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE CASE @.testRoleEnum
WHEN 1 THEN T.TestRoleOriginator
WHEN 2 THEN T.TestRoleValidator
..
END = 1
ORDER BY P.DisplayName
This latter solution may turn out to have poorer performance, however.
Steve Kass
Drew University
Mike Collins wrote:

>In the stored procedure below, there are 9 different if conditions being
>checked. I was wondering if there is a more efficient way (or not) to write
>this procedure, without having to write the same select criteria each time
.
>The only thing different is the where clause. Any suggestions are greatly
>appreciated.
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
>AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>|||Well, a more efficient design would store the numeric coefficient in a
single column, instead of having 9 different columns. It's hard to tell
from your requirements exactly what solution you need (e.g. can someone be a
screener and a roleSME?). But this is certainly not the best approach.
When you add or remove a role type, you have to change table structure and
all the code that references it. Blecch.
If a personnel member can only be in one role, then this design makes more
sense, IMHO:
CREATE TABLE dbo.TestRoles
(
TestRoleEnum INT PRIMARY KEY,
Description VARCHAR(32) UNIQUE
)
SET NOCOUNT ON;
INSERT dbo.TestRoles(Description) SELECT 'Originator';
...
CREATE TABLE dbo.TestParticipants
(
Personnelid INT /* FOREIGN KEY... */,
TestRoleEnum INT
)
Now your stored procedure can say:
ALTER PROCEDURE dbo.ListParticipantsByRole
@.testRoleEnum int,
@.errorID int = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT
P.DisplayName,
T.Personnelid
FROM
dbo.TestParticipants t
INNER JOIN
Common.dbo.vwPersonnelInfo p
ON
t.Personnelid = P.personellid
WHERE
t.TestRoleEnum = @.testRoleEnum
ORDER BY
P.DisplayName;
END
GO
And now you can add and remove test roles as you please, without having to
change any of the database structure. And as a bonus, your code becomes a
lot easier to read.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to
> write
> this procedure, without having to write the same select criteria each
> time.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||try this.. and see if it works faster...
writing for 2 ofthem.. u can add the rest..
Hope this helps.
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE @.testRoleEnum = 1 and T.TestRoleOriginator = 1
ORDER BY P.DisplayName
union all
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE @.testRoleEnum = 2 and T.TestRoleValidator = 1
ORDER BY P.DisplayName|||Thank you all for the quick and great responses. I see now better ways of
designing my tables and how to better write my stored procedure. In this
scenario, a person can have more than one role.
"Mike Collins" wrote:

> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to writ
e
> this procedure, without having to write the same select criteria each tim
e.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||If you are planning to use a case statement, I would suggest going for a
union all
like I mentioned since it helps in better use of indexes, if any. If you
have not indexed the columns in the where clause, then go for the case. But
,
if you are redesining the table, thats the best way.|||That's a fairly straightforward approach. And here I was going to suggest
dynamic SQL.
Of course, the various suggestions to change the table structure are much
better in the long run.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u9$RbXPeGHA.564@.TK2MSFTNGP02.phx.gbl...
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE 1 = CASE @.testRoleEnum
> when 1 then T.TestRoleOriginator
> when 2 then T.TestRoleValidator
> ...
> end
> ORDER BY P.DisplayName
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
greatly
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O9EDeiPeGHA.3556@.TK2MSFTNGP02.phx.gbl...
> That's a fairly straightforward approach. And here I was going to suggest
> dynamic SQL.
> Of course, the various suggestions to change the table structure are much
> better in the long run.
I'm getting lazy. :-)|||Thanks for the additional input. Unfortunately it is not my decision to
redesign the database and I might have to use the case statements. I am
definitely going to give my two cents about redesigning the database.
"Omnibuzz" wrote:

> If you are planning to use a case statement, I would suggest going for a
> union all
> like I mentioned since it helps in better use of indexes, if any. If you
> have not indexed the columns in the where clause, then go for the case. Bu
t ,
> if you are redesining the table, thats the best way.sql

Another way than using a Cursor

Hi all,
I have the following example Table & Data...
Create Table Test
(
Cust_Code varchar(10),
Acc_Deal_Type Varchar(3),
Account_No Varchar(30),
AccountTypes Varchar(50)
)
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345678')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345679')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','CCL','12345680')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345681')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345682')
I need to concatanate the distinct values of Acc_Deal_Types Column for
each Cust_Code and set the AccountTypes column with the Result.
So, in this example, the grouped results would be two rows with the
Cust_Code & AccountTypes Column selected:
1st Row
Cust_Code = 123456
AccountType = MMC,CCL
2nd Row
Cust_Code = 123457
AccountType = MMC
How can I create the AccountTypes column without using a cursor? The
Table in question has around 7000 rows.
Thanks
Barrytry
select distinct Acc_Deal_Type + Cust_Code as NewType into Test# from Test
update Test
set Acc_Deal_Type = NewType
-- select Cust_Code, Acc_Deal_Type, Account_No, AccountTypes , NewType
from Test a, Test# b
where NewType = Acc_Deal_Type + Cust_Code
increase the zize of the Acc_Deal_Type though|||Hi, Barry
See: http://www.aspfaq.com/show.asp?id=2529
Razvan

Tuesday, March 20, 2012

Ansi

Anoying bug...

another xp_smtp_sendmail question

another xp_smtp_sendmail question

another xp_smtp_sendmail question

Another WTF for MS - Row Count Error

another way to write this query?

another way to write this query?

another way to write this query?

Another Way To Write a Stored Procedure

Another way than using a Cursor

SQL server connection

Blog Archive

About Me