Case sensitivity

Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the inpu
t
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
ThanksHi,
see the following code.. use this logic in your function
DECLARE @.c CHAR
DECLARE @.d CHAR
SET @.c = 'a'
SET @.d = 'B'
DECLARE @.a VARCHAR(126)
SET @.a = ''
SET @.a = @.a + @.c + @.d
PRINT @.a
I hope that this will help you
Regards
Sivakumar
"RioDD" wrote:

> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Check out COLLATE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RioDD" <RioDD@.discussions.microsoft.com> wrote in message
news:D1E3AE60-365A-4B64-8408-E7C118D9F84C@.microsoft.com...
Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the
input
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
Thanks|||Sorry it didn't help me. The problem is when I use
if @.c='a'
it returns true for both 'a' and 'A'
"Subramaniam Sivakumar" wrote:
> Hi,
> see the following code.. use this logic in your function
> DECLARE @.c CHAR
> DECLARE @.d CHAR
> SET @.c = 'a'
> SET @.d = 'B'
> DECLARE @.a VARCHAR(126)
> SET @.a = ''
> SET @.a = @.a + @.c + @.d
> PRINT @.a
> I hope that this will help you
> Regards
> Sivakumar
> "RioDD" wrote:
>|||try this
IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
"RioDD" wrote:

> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Thanks, this helped me
"Subramaniam Sivakumar" wrote:
> try this
> IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
>
> "RioDD" wrote:
>|||This will only work if the string is less than 64 bits. You really should
look up the various collations in books online, that is the correct way to d
o
this.|||Hi,
no... you can use varbinary upto 8000.
"Scott Simons" wrote:

> This will only work if the string is less than 64 bits. You really should
> look up the various collations in books online, that is the correct way to
do
> this.

Case sensitive problem

Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BBYou simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause can
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
--
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
--
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:
>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
--
Hugo Kornelis, SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||Thanks for these quick replies!!! I believe it works ;)
You are the best!

Case sensitive AS server and VBA functions

I use VBA function, e.g. ABS, in an MDX statment, and try to deploy the project to a case-sensitive AS 2005 server -- got and error message "An unexpected exception occured". No such problem when server isn't case sensitive.

Any one came across this problem and has any idea how to overcome this problem?

Thanks.

Try to contact Customer support and report your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||How to connect them? I tried the feedback site, but it's seems no body in MS read it.|||

Try going through http://support.microsoft.com/oas/default.aspx?gprid=2855 .

Navigate to the edition of SQL server you are running.

Which VBA function are you trying to use? Have you tried to spell them with all capital letters?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I tried to use ABS() and VAL(). I tried to spell it ABS, abs, Abs even AbS, aBs, etc. Simply doesn't work. Microsoft doen't care, and customer support costs money. Why should I pay for their Bugs? Hello, MS, somebody at home?

|||

If you willing to share your design, I would be happy to take a look.

Feel free to contact me by removing the "noreply.online." part of my display email.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

CASE Query

Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL.
For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?? Query:

SELECT CASE PositionType
WHEN PositionType '1' THEN 'BUY'
WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY ContractFor starters, there's no need to repeat PositionType in the WHEN lines. You've specified it in the CASE line. Secondly, if the 1 or -1 is a numeric value, they shouldn't be surrounded by quotes. Third, the "}" is wrong. Fourth, no END.|||Hi,

You can re-edit the CASE statement as
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],

SELECT
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],
CAST(TradePrice as float(20,8) ) AS [Price],
Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike,
expiringdate,
comment,
SUBSTRING(contract+CONVERT(varchar,expiringdate),1 ,20) AS [FEEDCODE]
FROM TS_Positions
WHERE
(Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY Contract

Eralper
http://www.kodyaz.com|||Thanks Eralper! That worked but i decided to do it this way.

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S]...

Is there anyway where i can put on there to NOT show the NONE for the else? So i would only show the B(1) and S(-1).

Also on my query i would like to add 2 new columns at the end for example:

select col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets

Soo i should put that at the end of my query so it will look like this correct?

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE], col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY PositionType

2 new columns for my query would be Portfolio and Markets at the end..
Right now the columns without the config has:
B/S comment Price Volume Kind Strike Expiringdate Feedcode

New query would include 2 columns
B/S comment Price Volume Kind Strike Expiringdate Feedcode Portfolio Markets

CASE function result with result expression values (for IN keyword)

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE
GROUP.GROUP_ID = 2
AND DEPT.DEPT_ID = 'D'
AND WORK_TYPE_ID IN
(
CASE DEPT_ID
WHEN 'D' THEN 'A','B','C' <- ERROR
WHEN 'F' THEN 'C','D
ELSE 'A','B','C','D'
END
)

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'WHERE'.

which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

AND

(

(CASE DEPT_ID = 'D' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID = 'F' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID != 'D' AND CASE DEPT_ID != 'F' AND

WORK_TYPE_ID IN ('A','B','C'))

)

Though this could lead to bad performance :-(

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks for the reply . It works, and I agree with you that it could lead to performance degradation.

However, if the number of records involved are filtered and limited to, say under 1000 rows, it would still be managable ? Just a feeling, I know it is hard to quantify the expense of a query by just the row count alone.

Kenny

Case Function on a Cell

Hi all,
have a Matrix-based report that tracks project's %complete by period id for a
particular year, as follows:
- 2005
---
01 02 03 04 05 06 07 08 09 10 11 12
project1 10% 12% 18% 25% 38% 52%
project2 13% 29% 68% 89% 100%
project3 25% 68% 100%
:
:
:
I need to know how to change the header grop of Period_id "J F M A M J J A S
O N D" instead of "01 02 03 04 05 06 07 08 09 10 11 12" inside a statement
in the report, since i can not control the sql statement that feeds the
report.
Thank you in advance
Tony
--
Message posted via http://www.sqlmonster.comHave a look at my reply for "Proper syntax of CASE()" etc - in other words
write a little VB .net function in the Report's code box, then call the
funtion to return a string value
"Antony Altobelli via SQLMonster.com" wrote:
> Hi all,
> have a Matrix-based report that tracks project's %complete by period id for a
> particular year, as follows:
> - 2005
> ---
> 01 02 03 04 05 06 07 08 09 10 11 12
> project1 10% 12% 18% 25% 38% 52%
> project2 13% 29% 68% 89% 100%
> project3 25% 68% 100%
> :
> :
> :
> I need to know how to change the header grop of Period_id "J F M A M J J A S
> O N D" instead of "01 02 03 04 05 06 07 08 09 10 11 12" inside a statement
> in the report, since i can not control the sql statement that feeds the
> report.
> Thank you in advance
> Tony
> --
> Message posted via http://www.sqlmonster.com
>

Case function in T-SQL

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @.dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:

datediff(dd,@.startdate,@.today) = @.dDay

or convert(varchar(12), @.startDate, 101) between convert(varchar(12), @.today, 101) and convert(varchar(12), dateadd(dd,@.dDay, @.today), 101)

It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help

Any suggestion is welcome. thanks

I think I got confused.. can you explain a bit more..?

|||

i too am confused by what you want but just keep in mind that if you include a function your where clause you will likely disallow the use of indexes on your date field, if there is one

|||

 
SELECT *FROM tblWHERE StartDate>=DATEADD(DAY,DATEDIFF(DAY, 0, @.today), 0)AND StartDate<CASEWHEN @.dday='more'THEN'9999-12-31'ELSEDATEADD(DAY,DATEDIFF(DAY, 0, @.today), @.dday+1)END
 

|||

I mean this; I have a textbox with a dropdownlist control to search my databasein which the user can select either "Today", "7 days", "14 days", and "More". If a user select either of the list item except "More", i think i can easily use the"between" and"and" to get the recordsets or usedatediff function to get the recordsets, but what of if the user select"more" which is not bounded but means "from that day and on", how can one control this by using the same T-SQL statement to return the recordset from the database. I am not ad-hoc method to access my datastore but stored procedure.

So i am looking at usingcase function to make the decision and return the recordsets. Is it possible or is there any simpler way?.

Please Help. Urgent

|||

If I understand you correctly, I would use a second stored procedure for the "More" case. IOW,

select @.SomeDate = datediff(....)

then

select ...... from..... where DateField < @.SomeDate

If the user selects a specific number of days, select proc1, else select proc2

Another option is to use a big if statement with 2 selects (to handle either case), but that won't optimize as well

|||

If that in the case, I would change the listitem in the dropdown with the "more" text to have an extremely large value, like say 100000 (250ish years). Then you don't have to have anything complicated on the SQL side. That way you can still have "more" displayed in the listbox, but when it actually goes to send it to the SqlDatasource, it will send 100000 instead of the word more.

CASE function

Hi,
I have a date column where the application users puposely enter a date way
in the future as part of their business rule. For instance, entering the yea
r
2033 if the given value for this date column is unknown.
I need to programmatically retrieve this date and represent those
out-of-whack dates to show as the current date + 10 days.
I created the following SELECT stmt. using the CASE function:
SELECT ....
CASE post_datetime
WHEN post_datetime > getdate()+365 THEN getdate()+10
...
However, SQL QA returns an error (Incorrect syntax near '>') when I try to
execute this stmt. What am I doing wrong here. Please help. Thanks.
Regards,
- Rob.Rob wrote:
> Hi,
> I have a date column where the application users puposely enter a date way
> in the future as part of their business rule. For instance, entering the y
ear
> 2033 if the given value for this date column is unknown.
> I need to programmatically retrieve this date and represent those
> out-of-whack dates to show as the current date + 10 days.
> I created the following SELECT stmt. using the CASE function:
> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
> ...
> However, SQL QA returns an error (Incorrect syntax near '>') when I try to
> execute this stmt. What am I doing wrong here. Please help. Thanks.
> Regards,
> - Rob.
Try this instead:
CASE WHEN post_datetime > GETDATE() + 365 THEN GETDATE() + 10 ELSE
post_datetime END|||> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
There are two general forms of the CASE expression (it is not a function).
You can either say
CASE [expression] WHEN [value] THEN [value] END
or
CASE WHEN [expression][operator][value] THEN [value] END
You combined the two in a way I don't recall ever seeing (and as you have
found out, the syntax is invalid). You need the latter, because you are
testing a more complex expression than simple equality.
Try:
SELECT
CASE WHEN post_datetime > getdate()+365 THEN getdate()+10 ENDsql

Case / Switch function help

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.Use the CASE statement in SQL Server.|||Can I do this in the view design mode or do I need to make a user defined function?
Any sample code would be really appreciated.|||CASE WHEN [CategoryName] = 'Beverage' THEN 'Drink'
WHEN [CategoryName] = 'Cheese' THEN 'Dairy'
ELSE 'Unknown'
END AS DerivedColumnName

Carriage Returns in Data

I have a ntext field of data. I was trying to use the REPLACE function to
change the carriage returns to spaces, but have not any luck.
Can anyone make any suggestions?
Thank you,
JLFlemingYou don't need the text within <> -- it is just an example to show that
things are working as expected.
Here is an example:
create table #foo (col1 varchar(20))
insert into #foo values ('test')
insert into #foo values ('test
more')
select col1 from #foo
select REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>')
from #foo
Keith
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||You will have write a procedure that loops 8000 character chunks of the data
doing the replace.
Thomas
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||I missed the ntext bit the first time I read your post.
Keith
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMF47mHYFHA.4036@.tk2msftngp13.phx.gbl...
> You don't need the text within <> -- it is just an example to show that
> things are working as expected.
> Here is an example:
> create table #foo (col1 varchar(20))
> insert into #foo values ('test')
> insert into #foo values ('test
> more')
> select col1 from #foo
> select
> REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>') from
> #foo
>
> --
> Keith
>
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>|||The best way to do this is outside of SQL Server. Text data is a beast in
SQL Server 2000 and earlier to deal with in SQL. The chunking idea given by
Thomas is feasible, but you have to be careful about your search value
crossing the chunk boundry.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming

Carriage return within column alias

Is there a way to insert a carriage return or line feed in the middle of a column alias within a select statement? I tried using the CHAR function for the ascii value of the carriage return, but SQL Server wouldn't allow it inside the alias name for the column.
Any ideas?What are you trying to achieve with the end result? Is the result to be used in html or something? If so, you can use html tags in the header.|||The database query will be used in an ASP script run from a web site. The problem was that there were so many columns that I couldn't fit them on one page landscape for printing. If I can put some of the column headings (which are declared as aliases in my SQL query) on two lines as opposed to one long heading line, it will save page space.|||The simple solution, then is to put the HTML tag in the alias.. ie

SELECT col1 as 'COLUMN <BR> ONE'
bla bla bla

then when the column header is rendered by the asp, if it is set up correctly, it will put the break in. I believe, however, that there are ways to do this in HTML w/o the need of putting it in the column name.

Hope this helps.

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
>

Can't use parameter with DateAdd Function?

Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.

DATEADD(hh, @.hours, @.startTime)

It works if I have it set such as DATEADD(hh, 8, @.startTime), but I need that parameter there for what I need.

I get this error:

Error Source: System.Data
Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.

Any ideas?

Hello,

Try this:

=DateAdd("h", CDbl(@.hours), @.startTime)

Hope this helps.

Jarret

|||Just tried using this with no luck in the following expression. My goal is to use this with a report parameter that sets the value of @.period to 1, 7, 30, 90, or 365.

SELECT *
FROM dbo.v_call
WHERE (dbo.v_call.entry_date >= DATEADD(d, CDbl(@.period), GETDATE()))

When running the query I get the following error; string was not recognized as a valid date time.

I try changing my query to

SELECT *
FROM dbo.v_call
WHERE (dbo.v_call.entry_date >= DATEADD(d, @.period, GETDATE()))

and get this error:

Failed to convert parameter value from decimal to a datetime.
|||

Try using:

Code Snippet

="SELECT * FROM dbo.v_call

WHERE (dbo.v_call.entry_date >= DATEADD(d," &

CSTR(@.period) &

", GETDATE())) "

>L<

|||

For what it is worth, I have used the following with no problems

@.Trend is an integer parameter of 30, 90, 180, 365 or 720.

Code Snippet

authopen_idx >= dateadd(d,@.Trend*-1, getdate())