Interesting Query Results

I was doing a code review and found code that was similar to the code
below. Bad style/practice aside, I was surprised that it ran without
error and did not give "expected" results. Expected meaning five
one-row ouptuts. This is using SQL Server 2000, can anyone tell me if
2005 will act the same or different?
Thanks
declare @.int int
set @.int =0
while @.int<5
begin
declare @.tab table (policy varchar(12), mynum int)
declare @.i int
set @.i=isnull(@.i,0)+1
insert into @.tab
values('test',@.int)
select @.i as I,* from @.tab
set @.int=@.int+1
ENDThe declarations for the table and int variables get "moved" outside the loo
p
by the compiler. I agree the output is not what I would expect either, but I
also think the output is better than what I would expect - i.e. SQL Server i
s
very "smart".
"dott@.accessGeneral.com" wrote:

> I was doing a code review and found code that was similar to the code
> below. Bad style/practice aside, I was surprised that it ran without
> error and did not give "expected" results. Expected meaning five
> one-row ouptuts. This is using SQL Server 2000, can anyone tell me if
> 2005 will act the same or different?
> Thanks
> declare @.int int
> set @.int =0
> while @.int<5
> begin
> declare @.tab table (policy varchar(12), mynum int)
> declare @.i int
> set @.i=isnull(@.i,0)+1
> insert into @.tab
> values('test',@.int)
> select @.i as I,* from @.tab
> set @.int=@.int+1
> END
>|||I think that "the output is better than what I would expect" is VERY
debatable. I would expect an error stating that @.tab and @.i already
exist, just as if I had
declare @.i int
declare @.i int
in code somewhere. But because there is no block level scope the @.tab
and @.i are not freed and redeclared, so by putting the declares inside
the loop should result in an error.|||On 26 Apr 2005 11:35:06 -0700, Otter wrote:

>I think that "the output is better than what I would expect" is VERY
>debatable. I would expect an error stating that @.tab and @.i already
>exist, just as if I had
>declare @.i int
>declare @.i int
>in code somewhere. But because there is no block level scope the @.tab
>and @.i are not freed and redeclared, so by putting the declares inside
>the loop should result in an error.
Hi Otter,
The reason for this is that the declare statements are checked at parse
time, not at execution time. Parsing is just one scan over the code, from
top to bottom, disregarding any flow-of-control statements.
That's why this will work:
IF 1 = 2
BEGIN
DECLARE @.i int
END
SET @.i = 1
SELECT @.i
And this won't
IF 1 = 2
BEGIN
SET @.i = 1
END
DECLARE @.i int
SELECT @.i
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||They don't exactly get moved outside, but the declarations
are not executed statements. They are definitions that define
the context for the rest of the batch when executed. You can
see that they don't really "move" by looking at
while 1=0 begin
select @.i
declare @.i int
end
go
while 1=0 begin
declare @.i int
end
select @.i
The documentation is not very clear on this, but the scope of
variable declarations in SQL Server is from the point of declaration
in the text of the code downward to the end of the batch (first GO).
The execution order of statements in the code is irrelevant. It's the
code as text that matters, as seen here:
goto a
select @.x
a: declare @.x int
The environment (i.e., the variable declarations and values) are not
passed into subprograms like stored procedures or into queries
called with EXEC (<string> ), unless passed explicitly through an
available mechanism, like stored procedure parameters.
The behavior in T-SQL is not too far from the behavior in most
programming languages, except that scope does not end with
the end of a block like BEGIN .. END, only with the end of a
batch.
if 1=1 begin
declare @.i int
set @.i = 1
end else begin
declare @.j int
set @.j = 1
end
select @.i, @.j
If I remember my C++, for example, the variable i is not
redeclared with each loop iteration below, and that is the same
behavior we see in T-SQL. But i is not in scope below the
loop, and that is not the same behavior as in T-SQL. In
T-SQL there is no option to initialize a variable at declaration
like I'm doing here. All in all, there's no good reason I can think
of to declare a T-SQL variable inside a loop unless it puts
the variable closer to its use and readers will not think it
has C-like block scope.
[untested]
x = 10;
while --x {
int i = 0;
i += x;
cout << i
}
Steve Kass
Drew University
KH wrote:
>The declarations for the table and int variables get "moved" outside the lo
op
>by the compiler. I agree the output is not what I would expect either, but
I
>also think the output is better than what I would expect - i.e. SQL Server
is
>very "smart".
>
>"dott@.accessGeneral.com" wrote:
>
>sql

Integration Services on a Multi-Instance SQL Server Cluster (Best Practice)

Hi
I have a 2-node active-passive SQL cluster (with SSIS manually
clustered in the SQL resource group). I want to convert to active-
active by installing a named instance on the second node. Am I correct
in saying that the new virtual server cannot have its own SSIS
instance but must instead utilise the SSIS instance (& the MSDB
database) of the first (original) virtual server (because there can
only be one instance of SSIS on a node) ?
Having said this, should I move the SSIS resources into (preferably) a
dedicated SSIS resource group or (alternatively) the cluster/quorum
group, moving the xml config file and the Packages folder to the
associated physical disk for that group ?
tia
brynjon
SSIS is typically set up as a local instance for each cluster node. Note
that each node must be installed independently. You can cluster the
resulting SSIS installations and refer to them a a cluster resource.
Here is Kirk's blog entry on how to do that:
http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"brynjon" <bryn.jones@.cheshire.gov.uk> wrote in message
news:1178014798.452341.88160@.p77g2000hsh.googlegro ups.com...
> Hi
> I have a 2-node active-passive SQL cluster (with SSIS manually
> clustered in the SQL resource group). I want to convert to active-
> active by installing a named instance on the second node. Am I correct
> in saying that the new virtual server cannot have its own SSIS
> instance but must instead utilise the SSIS instance (& the MSDB
> database) of the first (original) virtual server (because there can
> only be one instance of SSIS on a node) ?
> Having said this, should I move the SSIS resources into (preferably) a
> dedicated SSIS resource group or (alternatively) the cluster/quorum
> group, moving the xml config file and the Packages folder to the
> associated physical disk for that group ?
> tia
> brynjon
>

Integration services notification services

Does anybody know of a notification services using SSIS? Is it SendMail or otherwise? Is there a step-by-step practice on how to create one?Not sure I understand your question? Are you looking to trigger an SSIS package from Notification Services, or are you want to send a notification from the SSIS package?|||I'm hoping it's the later one - send the notification from the SSIS package.|||You can send an email by using the Send Mail Task in SSIS. Is that not what you are looking for?|||

That would possibly the same as DTS! Do you know of any step--by-step article on how to do this in SSIS?

Just out of curiosity, if I were to use SQL Server's Notification services, I'd be make use of SSRS(reporting services) as well, wouldn't I?

|||

If you search the forum for "Send Mail", you should find some links. Also, it's really not a very complicated task to use.

I'm not sure why you are tying Notifiation Services to Reporting Services (or Integration Services, for that matter). They are seperate products, and while they can interact with each other, they do it the same way any external application would interact. For example, SSIS can interact with Reporting Services directly by calling the web service to generate reports (just like any other external application).

|||The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.|||

onamika wrote:

The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.

Neither of which are related to SSIS though.

From SSIS look at using the Send Mail task, or using a Web Service to handle notifications.|||Frankly, I don't see any SendMail tasks example or how create an example. Maybe it's because I'm faily new to MSDN.|||

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=Send+Mail&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

The second result returned has a walkthrough of using a Send Mail Task inside a For..Each loop. I didn't go through the rest, but I'm sure there are more examples.

|||

onamika wrote:

Frankly, I don't see any SendMail tasks example or how create an example. Maybe it's because I'm faily new to MSDN.

BOL has some information on the Send Mail task inside SSIS.

http://msdn2.microsoft.com/en-us/library/ms142165.aspx|||

onamika wrote:

The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.

Perhaps the nomenclature is a problem here. Perhaps it should be "notification services" rather than (the product) "Notification Services".

I'd like to read that MSDN article because if that is what it says then it is lying. Analysis Services does not automatically use Reporting Services for anything. You can deliver reports on top of Analysis Services using Reporting Services - but that is something else entirely.

-Jamie

|||

onamika wrote:

Frankly, I don't see any SendMail tasks example or how create an example.

Its very very very very simple. Just try it - witha little perseverance you won't need any documentaiton.

onamika wrote:

Maybe it's because I'm faily new to MSDN.

Not sure what you mean by this. What has MSDN got to do with this? How can you be "new to MDSN"?

-Jamie

|||

I'd like to stick to the point - question about "SendMail" please!

Ok, bonus question for you. This is mostly text based mail I see. How can I make this email HTML based?

|||

onamika wrote:

I'd like to stick to the point - question about "SendMail" please!

Ok, bonus question for you. This is mostly text based mail I see. How can I make this email HTML based?

Drop the Send Mail task in the Control Flow tool box onto the work surface. Double click on it to configure it. From there, I think you should be able to figure out what to put where.

As far as HTML e-mail (WHY? WHY? WHY?), please search this forum for examples. This has been discussed before.

As an aside - HTML is VERY BAD design, in my opinion. HTML should be left to Web servers to display. If I had it my way (and many in the security community) e-mails would be limited to TEXT ONLY. Anyway......... That's my rant for today.