CASE statement

Hello,
I created a view in SQL Server Express 2005 that contained CASE END
statements.
I use this view in a .NET 2005 project, which works fine on my machine.
When I deploy my project to a machine that is running SQL Server 200, I get
the following error:
The Query Designer does not support the CASE SQL contruct.
Any way around this'
TIA,
Amber"amber" <amber@.discussions.microsoft.com> wrote in message
news:C5E9214E-628F-4CC3-8CC4-4DD7A5347999@.microsoft.com...
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I
> get
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
If you could include the CASE statement that you used, and any other
relevant DDL we could be more helpful.
Rick Sawtell|||Which SQL 200x? :) 2000 or 2005?
The way around this: don't use the Query Designer. Script it and run the
code in Query Analyzer.
amber wrote:
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I ge
t
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>|||"amber" wrote:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
Yes. Learn to write SQL instead of using the Query Designer. The designer is
a crutch that will do you know favours in the long run. If you want to use
anything more than very basic stuff then you must avoid it.
David Portas
SQL Server MVP
--

Carriage returns

I'm migrating from Crystal. When I inserted address fields which contained
carriage returns, Crystal seemed to recognise them automatically, putting
the second line beneath the first line, etc. RS doesn't do this - obviously
I have to do something more explicit. Cam anybody help?
ThanksFirst: You need SP1. Second: I've only had success with carriage returns
when there physically was a CRLF pair. With the HTML output, the <BR> tags
are not rendered unless said CRLF pair exist.
--
TIM ELLISON
"Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
> I'm migrating from Crystal. When I inserted address fields which contained
> carriage returns, Crystal seemed to recognise them automatically, putting
> the second line beneath the first line, etc. RS doesn't do this -
obviously
> I have to do something more explicit. Cam anybody help?
> Thanks
>|||Seem to have got this sorted with an expression:
=replace((Fields!AD_ADDRESS.Value),chr(13),vbcrlf)
Thanks for the help anyway, will get SP1 installed pronto...
"Tim" <TimmyDotNet@.direcway.com> wrote in message
news:eINaxi24EHA.1976@.TK2MSFTNGP09.phx.gbl...
> First: You need SP1. Second: I've only had success with carriage returns
> when there physically was a CRLF pair. With the HTML output, the <BR>
> tags
> are not rendered unless said CRLF pair exist.
> --
> TIM ELLISON
> "Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
> news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
>> I'm migrating from Crystal. When I inserted address fields which
>> contained
>> carriage returns, Crystal seemed to recognise them automatically, putting
>> the second line beneath the first line, etc. RS doesn't do this -
> obviously
>> I have to do something more explicit. Cam anybody help?
>> Thanks
>>
>

can't use the index tuning wizard wioth a function??

Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.
Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert
|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>

can't use the index tuning wizard wioth a function??

Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>

can't use the index tuning wizard wioth a function??

Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID = A.ID
what can I do?
thanks.
Jerome.Jéjé wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jéjé wrote:
>> Hi,
>> I receive this error when I try to execute the index tuning wizard:
>> "There are no events in the workload. Either the trace
>> file contained no SQL batch or RPC events or the SQL
>> script contained no SQL queries."
>> I have tried from the query analyzer and from a workload trace file,
>> in the 2 cases I receive the error.
>> My query contain a join to a custom function which return a simple
>> list. if I remove the function, then the index tuning works fine.
>> my query:
>> select * from table1 inner join dbo.MyFunction(@.Param) A on
>> table1.ID = A.ID
>> what can I do?
>> thanks.
>> Jerome.
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>