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
>
>