Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string
building, and
it took me a while to figure it out. I think I can appreciate *why* it's do
ing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf
else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16),
@.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the r
esultant
expression, and determining that it can be coerced to a NCHAR(2). However,
somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR
-- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4)
(as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything wor
ks as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16)
, @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrL
fs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatyp
e
> only, and the datatype it returns is determined by looking at the datatype
s
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or
a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will b
e
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> string building, and
> doing this, but
> @.CrLf else N'' end +
> resultant
> However, somewhere
> NCHAR -- sort of
> NCHAR(4) (as per the
> works as
>

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string building, and
it took me a while to figure it out. I think I can appreciate *why* it's doing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the resultant
expression, and determining that it can be coerced to a NCHAR(2). However, somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR -- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4) (as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything works as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrLfs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatype
> only, and the datatype it returns is determined by looking at the datatypes
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I had a situation where I was getting some extraneous spaces in some
> string building, and
> > it took me a while to figure it out. I think I can appreciate *why* it's
> doing this, but
> > I wish it weren't.
> >
> > Consider the following:
> >
> >
> > declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> > declare @.String nvarchar(255) select @.String = N''
> >
> > select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
> @.CrLf else N'' end +
> > N'Test'
> > print N'"' + @.String + N'"'
> > print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
> >
> >
> > The output is:
> >
> >
> > " Test"
> > 0x20002000200020005400650073007400
> >
> >
> > It feels like, for some reason, the CASE is evaluating both "sides" of the
> resultant
> > expression, and determining that it can be coerced to a NCHAR(2).
> However, somewhere
> > along the lines, it gets confused and is introducing 2 bytes for every
> NCHAR -- sort of
> > like accidentally making it a CHAR(4) that then gets promoted to a
> NCHAR(4) (as per the
> > NULL bytes in every other character).
> >
> > If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
> works as
> > expected.
> >
> > It's very odd, and certainly smells like a bug to me.
> >
> > Thoughts?
> >
> >
>

Interesting Behavior of Service Broker Queues

Hello All:

I've been experimenting with the new SQL Server Service Broker, and I think I've discovered some interesting behavior. Service Broker relies on "Queues" to store messages that need to be processed. Service Broker operates by sending a message from one Queue (the INITIATOR Quque) to another Queue (the TARGET Queue). A Queue can have an "Activation Stored Procedure" associated with it. This procedure is what actually processes the messages in the Queue.

The first behavior I obeserved related to the setting of a Queue's RETENTION parameter. The RETENTION parameter indicates whether or not the Queue will retain a copy of the messages it receives. By default, the parameter's value is "OFF" (meaning it will not retain messages). In the Activation Stored Procedure of my TARGET Queue, I used "sp_send_dbmail" to send an e-mail message. I wanted to capture the "conversation_handle" (a uniqueidentifier that identifies a particular message) and include it in the body of the e-mail. I was unable to capture it, because the Queue's RETENTION parameter was "OFF". When I tried to capture the conversation_handle from the INITIATOR queue (whose RETENTION parameter was "ON"), I was successful. The moral of the story is you apparently need to have RETENTION = "ON" if you need to capture information from a Queue.

The second behavior I observed relates to the setting of a Queue's MAX_QUEUE_READERS setting. This setting allows you to automatically invoke multiple instances of the Activation Stored Procedure. As messages come into the Queue, the Queue creates an additional instance of the Activation Stored Procedure up to the number you specified in the MAX_QUEUE_READERS setting. This allows parallel processing of messages in the Queue. There is also a programming technique called a "RECEIVE LOOP" which is used for processing messages. In the RECEIVE LOOP, you have a parameter called WAITFOR which tells the Queue whether it should stay on constant alert for new messages or whether it should time out after a specified number of seconds.

If you have the Queue wait indefinitely (by not specifying a TIMEOUT value in the WAITFOR statement) and you have invoked multiple copies of the Activation Stored Procedure, the procedure will remain in memory indefinitely. Therefore, if you make a change to the code of the Activation Stored Procedure, the change will NOT be reflected in the Activation Stored Procedure until you change the STATUS of the Queue. I had changed my procedure so that it would not send an e-mail, but the e-mails kept coming. The e-mails did not stop until I executed an ALTER QUEUE statement. I ran "ALTER QUEUE queue_name WITH STATUS = OFF;" and then I ran "ALTER QUEUE queue_name WITH STATUS = ON;" After that, the changes were reflected in the procedure.

Be aware of this behavior as you design your Queues.

"The moral of the story is you apparently need to have RETENTION = ON if you need to capture information from a Queue."

I wrong about this. The reason I wasn't capturing the information the first time around was because I didn't alter the queue to reset the STATUS. I have since been able to turn off RETENTION while having the ability to capture information from the queue.

|||

Ken, some necessary corrections:

- conversation_handle identifies conversations endpoints, not messages. Initiator and target have distinct conversation_handles. Conversation_id is identifies conversations and is shared by initiator and target. A message is unique by conversation_handle AND message sequence number.

- RETENTION has nothing to do with the capability to 'capture' conversation handles. To obtain the the conversation handle on the initiator side one must get it from the output of BEGIN DIALOG. To obtain the conversation handle on the target side one must put the conversation_handle into the RECEIVE projection list. RETENTION is totally unrelated to this, it is intended for helping application implement compensation logic on failed dialogs. RETENTION keeps a copy of sent and received messages for the duration of the conversation. These messages are visible for the SELECT statement, but not for the RECEIVE statement.

- any stored proc that has an infinite loop, once launched, will stay in memory until killed (explicitly or implicitly) or until server shutdown. This is not specific to activation, is general SQL Server behavior. The only difference is that when this happens by invocation from a user connection it is usually killed implicitly, by that connection being closed or by the batch being aborted. Turning the queue OFF will cause the infinite WAITFOR(RECEIVE...) in the procedure to error and thus exit the RECEIVE loop. One could just as easily KILL the procedure (lookup the SPID in sys.dm_broker_activated_tasks). Needless to say, activated procedures with infinite loops are a very bad practice. It leads to unnecessary consumtion of server resources (threads at the very least) when there are not messages to receive.

- altering the code of an stored proc does not affect the exisiting running instances of that stored proc. This behavior is general in SQL Server, not particular to activated stored procs. Only new invocations of the stored proc will detect the change.

I hope this clarifies some of the behavior you observed.
HTH,

~ Remus

|||

Thanks for the clarifications. I wasn't thinking of the RECEIVE LOOP as an infinite loop, since you don't see the timer on the Query Editor window keep running, but it obviously is one. I also wasn't clear about the circumstances under which a stored procedure stays in memory, so thanks for that information as well.

Now I want to show you a code sample to clarify what I am doing. Is this what you mean by "put the conversation_handle into the RECEIVE projection list"?

<code>

USE ServiceBrokerTest

GO

/*

DROP PROCEDURE usp_FTPService

*/

ALTER PROCEDURE usp_FTPService

AS

BEGIN

DECLARE @.conversationHandle UNIQUEIDENTIFIER

DECLARE @.messageBody NVARCHAR(MAX)

DECLARE @.messageTypeName SYSNAME

DECLARE @.eMailBody NVARCHAR(MAX)

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

WAITFOR (

RECEIVE @.messageTypeName=message_type_name,

@.conversationHandle=conversation_handle,

@.messageBody=message_body

FROM [FTPQueue])

-- If a message was received, process it, else skip

IF (@.@.rowcount <= 0)

BREAK;

-- Process ObjectFTPStartRequest message

IF @.messageTypeName = N'ObjectFTPStartRequest'

BEGIN

SET @.eMailBody = 'FTP job for Object conversation ' + CAST(@.conversationHandle AS NVARCHAR(MAX)) + ' completed'

EXEC msdb.dbo.sp_send_dbmail @.profile_name = 'Ken',

@.recipients = 'username@.domain.com',

@.subject = 'FTP Service Response (ASP)',

@.body = @.eMailBody;

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [LookupRaceFTPStartResponse]

(N'<ObjectFTPStartResponse>

FTP job for Object completed

</ObjectFTPStartResponse>')

END CONVERSATION @.conversationHandle

END

COMMIT

END

COMMIT

END

</code>

If that's what you meant, then that's what I was doing. The reason I was having a problem was solely due to the fact that the first version of the procedure was still running in memory.

|||

To put the conversation_handle into the projection list it simply means to have the conversation_handle in the list of columns you RECEIVE. You are doing this fine.

The code you posted needs two changes:
- the WAITFOR(RECEIVE ...) must have a timeout. Typically, the value should be a few seconds. The ideea is that the procedure should linger a bit, in hope a new message arrives and it can be processed. Too short interval and the procedure is gonna miss new messages, exiting and being re-launched, which is a somewhat expensive operation. Too long and your procedure is going to consume server resources w/o doing anything.
- You must add a TOP(1) clause to the RECEIVE. Otherwise you will receive multiple messages, but only process the last one.

HTH,

~ Remus

|||

I don't think it's proper to say the TIMEOUT is required since the T-SQL language does not require a TIMEOUT with the WAITFOR clause. If it were required I would get a syntax or runtime error upon execution of the code.

As for whether it's a good idea to use a TIMEOUT, here's what Roger Wolter (Microsoft's Group Program Manager for SQL Server Service Broker) says in his book "The Rational Guide to SQL Server 2005 Service Broker" (Page 56):

"A WAITFOR with no timeout means the RECEIVE will wait for a message no matter how long it takes. Waiting for a message to appear on the queue is generally more efficient than polling the queue by using the RECEIVE command periodically. The main exception to this would be a low priority queue that is only polled for messages periodically when higher priority queues are empty."

As for the use of the TOP clause, I quote Roger again (again on Page 56):

"Retreiving all the messages available utilizes fewer server resources than multiple RECEIVE commands, so you should avoid the TOP clause whenever possible. The main reason to use the TOP clause is in stored procedures where receiving one message at a time into TSQL variables simplifies programming, or when there's a chance that a RECEIVE command will retrieve more messages than you want to process in a single transaction."

|||

Indeed, the TIMEOUT is not syntactically required. But no TIMEOUT in activated stored proc loop practicaly turns it into an infinite loop and you'll run into the problems you encountered.

Assignment in RECEIVE projection list (e.g. @.conversation_handle = conversation_hanlde) requires a TOP(1). It is true that using the TOP clause is less efficient, but otherwise the procedure gets significantly more complex. You'd have to RECEIVE INTO @.tableVariable, open a cursor over the table variable and scan this cursor. That is what the "simplifies programming" part of he's quote reffers to. You either use a simple T-SQL program (i.e. @.variables), but pay the penalty of the TOP clause, or you use a more complex T-SQL program (i.e. @.tablevariable and cursor). Roger himself mentions this, see the two examples of RECEIVE on page 57.
Note that if 99% of your RECEIVEs would return only one message anyway (as is often the case), then the TOP is not expensive at all.

See the Receive Loop example on page 63, you'll see that Roger uses both a TOP(1) clause and a 5s TIMEOUT.

HTH,
~ Remus

Interesting Behavior of Alter Table

We've found something that seems odd for which we'd like an explanation.
We have a table defined as follows:
create table tester
(col1 int, col2 char(2), col3 int)
Our script executes something like this:
begin
alter table tester add id int not null constraint df_test default 0
alter table tester add id2 int identity
update tester
set id = id2
alter table tester drop column id2
end
The first time we run the script above, the following error occurs:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'id2'.
All subsequent times in the same database, the code works as expected. Even
after the table is dropped and recreated, or we just drop the columns we
added and rerun it. Even after restarting query analyzer.
If I change the table name used to "tester2" and rerun the script, the error
is back. If I then drop that table and rerun the script, the errors
disappear.
Any ideas? We have worked around it by putting the schema altering
statements in their own batch, which is easy enough. But the behavior sure
seems erratic.
Thanks!
Steven Bras
Tessitura Network, Inc.have you tried it with GO in between the various sub steps , so you can send
each one as a transaction.?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"StevenBr" <sbras@.community.nospam> wrote in message
news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> We've found something that seems odd for which we'd like an explanation.
> We have a table defined as follows:
> create table tester
> (col1 int, col2 char(2), col3 int)
> Our script executes something like this:
> begin
> alter table tester add id int not null constraint df_test default 0
> alter table tester add id2 int identity
> update tester
> set id = id2
> alter table tester drop column id2
> end
> The first time we run the script above, the following error occurs:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id2'.
> All subsequent times in the same database, the code works as expected.
Even
> after the table is dropped and recreated, or we just drop the columns we
> added and rerun it. Even after restarting query analyzer.
> If I change the table name used to "tester2" and rerun the script, the
error
> is back. If I then drop that table and rerun the script, the errors
> disappear.
> Any ideas? We have worked around it by putting the schema altering
> statements in their own batch, which is easy enough. But the behavior sure
> seems erratic.
> Thanks!
> Steven Bras
> Tessitura Network, Inc.|||When I run this in SQL server management studio I get the errors
everytime. After the first unsuccessful run has the table schema been
altered?|||I forgot to mention, as well as using GO , test it without the begin / end
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"StevenBr" <sbras@.community.nospam> wrote in message
news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> We've found something that seems odd for which we'd like an explanation.
> We have a table defined as follows:
> create table tester
> (col1 int, col2 char(2), col3 int)
> Our script executes something like this:
> begin
> alter table tester add id int not null constraint df_test default 0
> alter table tester add id2 int identity
> update tester
> set id = id2
> alter table tester drop column id2
> end
> The first time we run the script above, the following error occurs:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id2'.
> All subsequent times in the same database, the code works as expected.
Even
> after the table is dropped and recreated, or we just drop the columns we
> added and rerun it. Even after restarting query analyzer.
> If I change the table name used to "tester2" and rerun the script, the
error
> is back. If I then drop that table and rerun the script, the errors
> disappear.
> Any ideas? We have worked around it by putting the schema altering
> statements in their own batch, which is easy enough. But the behavior sure
> seems erratic.
> Thanks!
> Steven Bras
> Tessitura Network, Inc.|||Yes, that's essentially our workaround. Thanks.
--
Steven Bras
Tessitura Network, Inc.
"Jack Vamvas" wrote:

> have you tried it with GO in between the various sub steps , so you can se
nd
> each one as a transaction.?
>
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "StevenBr" <sbras@.community.nospam> wrote in message
> news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> Even
> error
>
>|||Hmm. We're using Query Analyzer against SQL 2k. And no, the alterations have
not been made.
This is very strange; to go back and check what you asked, I reran the same
script with a table name I've used before, and it worked without error.
Tried the same thing on a brand new table, and got the errors again.
Thanks!
--
Steven Bras
Tessitura Network, Inc.
"Will" wrote:

> When I run this in SQL server management studio I get the errors
> everytime. After the first unsuccessful run has the table schema been
> altered?
>|||Same problem, and we need those because it's in an IF block in the productio
n
script. I can leave that out and still repro the problem.
--
Steven Bras
Tessitura Network, Inc.
"Jack Vamvas" wrote:

> I forgot to mention, as well as using GO , test it without the begin / end
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "StevenBr" <sbras@.community.nospam> wrote in message
> news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> Even
> error
>
>|||This is what I have learned to do as well. I think that semicolons
following each statement also works, although I have not tested it in every
situation.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:9f2dneo6CqO3jv_ZRVnyiw@.bt.com...
> have you tried it with GO in between the various sub steps , so you can
send
> each one as a transaction.?
>
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "StevenBr" <sbras@.community.nospam> wrote in message
> news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> Even
> error
sure
>|||This is because how SQL Server parses the queries. When the parses sees the
text, the ALTER TABLE
haven't been executed yet. So this is why the parses throws the errors (as i
t tries to validate that
the columns exists).
Execute them in separate batches. Although the big question is why you creat
e columns at run-time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"StevenBr" <sbras@.community.nospam> wrote in message
news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> We've found something that seems odd for which we'd like an explanation.
> We have a table defined as follows:
> create table tester
> (col1 int, col2 char(2), col3 int)
> Our script executes something like this:
> begin
> alter table tester add id int not null constraint df_test default 0
> alter table tester add id2 int identity
> update tester
> set id = id2
> alter table tester drop column id2
> end
> The first time we run the script above, the following error occurs:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id2'.
> All subsequent times in the same database, the code works as expected. Eve
n
> after the table is dropped and recreated, or we just drop the columns we
> added and rerun it. Even after restarting query analyzer.
> If I change the table name used to "tester2" and rerun the script, the err
or
> is back. If I then drop that table and rerun the script, the errors
> disappear.
> Any ideas? We have worked around it by putting the schema altering
> statements in their own batch, which is easy enough. But the behavior sure
> seems erratic.
> Thanks!
> Steven Bras
> Tessitura Network, Inc.|||I don't see it working when doing your steps:
create table tester
(col1 int, col2 char(2), col3 int)
go
--run the statement and get the error
begin
alter table tester add id int not null constraint df_test
default 0
alter table tester add id2 int identity
update tester
set id = id2
alter table tester drop column id2
end
go
--Step 1 run each statement manually
alter table tester add id int not null constraint df_test default 0
go
alter table tester add id2 int identity
go
update tester
set id = id2
go
alter table tester drop column id2
go
--Step 2 manually remove the default and ID
alter table tester drop constraint df_test
alter table tester drop column id
go
--Step 3 - rerun the code and the error appears for me
begin
alter table tester add id int not null constraint df_test
default 0
alter table tester add id2 int identity
update tester
set id = id2
alter table tester drop column id2
end
go
drop table tester
go

interactivity in RS

Hi there!
I have a question about the interactive behavior in the reports.
Is it possible to add interactive sorting capability for the report.
By this I mean that a user can click on the column header of the
report and the report will be sorted in the appropriate way.
Another question is about drilldown reports.
Lets consider the situation when, we a have a group
and no data in that group. The group column ( e.g. Employee
name ) was selected as the field on which some other columns depend.
We have a drilldown and a "+" sign. So the question comes if there is no
data in the group, how we can disable "+" sign or not show it?
TIA
--
Vadym StetsyakVadym Stetsyak wrote:
> Hi there!
> I have a question about the interactive behavior in the reports.
> Is it possible to add interactive sorting capability for the report.
> By this I mean that a user can click on the column header of the
> report and the report will be sorted in the appropriate way.
> Another question is about drilldown reports.
> Lets consider the situation when, we a have a group
> and no data in that group. The group column ( e.g. Employee
> name ) was selected as the field on which some other columns depend.
> We have a drilldown and a "+" sign. So the question comes if there is
> no data in the group, how we can disable "+" sign or not show it?
> TIA
> --
> Vadym Stetsyak
There is a slightly grubby hack that will allow you to (sort of) sort
on the column headers. I want to add this to the sorting/grouping
article at
http://highlyobscure.net/howto/sorttablewithparameters/sorttable.htm
but haven't had time. Basically you add a rectangle to the column
header with 2 more textboxes in it, to link to ascending and descending
sorted reports. Note that in SP1 you can't put little images of arrows
in the textboxes because RS errors out if you do; you have to use text.
If you're familiar with parameter-based sorting (and it sounds like you
are) you can skip the article above and go straight to
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6
a347b53-5594-40f9-861d-876beabeda16 , a great sample.
As to the second question, I dunno. Let us know if you figure it out
8-).
hth
Highly Obscure