CASE statement with IN/OR

Hello,

I'm trying to write a query with case statement.

the condition is

when project_ref =393 then select qtn_ref in (7070000,7060000))
and when project_ref =391 and select q.qtn_ref=8700000

I need this condition in 'WHERE' statement.

I can use 2 SELECT queries using 'IF ELSE', but I woud like to find out if there's any way to use CASE so I can write 1 query.

Tring to do something like this but it doesn't work.

SELECT *

FROM table

WHERE qtn_ref = case when project_ref =393 then 7070000 or 7060000
when project_ref =391 then 8700000

Anyone can help, please?

Hi,

I just modified your Query. i dont have an SQL Installation so im not able to test the Query.

Plz Try this i hope i would solve your problem.

SELECT * FROM table
WHERE (qtn_ref = case when project_ref =393 then 7070000
when project_ref =391 then 8700000) OR
(qtn_ref = case when project_ref =393 then 7060000
when project_ref =391 then 8700000)

|||

SELECT*FROM yourTable

WHERE qtn_ref=(casewhen project_ref=393 then 7070000

when project_ref=391 then 8700000 end)OR

qtn_ref=(casewhen project_ref=393 then 7060000

when project_ref=391 then 8700000 end)

|||thanks guys. it works.|||Plese check one of them as answers!

CASE Statement

Hello All,

I have a condition for which I am trying to write a case statement as follows..

SELECT @.Segment_Field = 'Acct_Status_' + CASE @.Public_record_Type
WHEN NULL THEN ' '
WHEN 'BP' THEN 'BP' + ((CASE @.Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @.Acct_Status WHEN NULL THEN '' ELSE '2' END))
END

But this is not working.The condition is actually tht I have to set the value for the variable @.Segment_Field acc to the value of variables @.Bankruptcy_Type and @.Acct_Status. It should first check for @.Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @.Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2.

Can somebody please help..

Thanks

The problem is the null comparison. NULL <> NULL. You are trying to compare NULL = NULL, both being UNKNOWN values then it is unknown if they match. SQL Server comparisons are based on TRUE only, all others do not match.

declare @.value varchar(10)
--set @.value = 'Fred'
set @.value = NULL

select case @.value
when 'Fred' then 'It''s Fred'
when 'Barney' then 'It''s Barney'
when NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Just rewrite to a searched case expression:

declare @.value varchar(10)
--set @.value = 'Barney'
set @.value = NULL

select case when @.value = 'Fred' then 'It''s Fred'
when @.value = 'Barney' then 'It''s Barney'
when @.value is NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Note also that technically CASE is an expression, not a statement. Not that you used the CASE expression wrong, but it helps to remember that it evaluates to a scalar, not control of flow statements like in many other languages.

|||

Thanks Louis.

But my prob is that I have to check for value of two different variables and not one as you specified @.Value..I can definitley check for two different values of one variable but assigning value to a variable on basis of two different variables is what I am not able to do..

|||

Is this what you want? You can put as complex a condition in a WHEN clause as you need, even including subqueries...

SELECT @.Segment_Field = 'Acct_Status_' +

CASE when @.Public_record_Type is null then ''
when @.Public_record = 'BP' THEN 'BP' +
CASE when @.Bankruptcy_Type is null THEN ' ' ELSE '1' END +
CASE when @.Acct_Status is NOT NULL and @.Bankruptcy_Type is null THEN '2' ELSE '' END

CASE statement

I am trying to write a case statement that will look at a relationship between two fields. I have done this in the past but not one that needs to look at various options in one of the fields.

Example. I am comparing a location code to a code where an employee's check is mailed.

CASE
WHEN EjLocation ='AGACES'
THEN '3410'

But I need to also include 3415 and any check code that begins with 83 (i.e. 8315, 8350). How do I include this in the THEN statement?

Thanks!Could you post a small sample set and the result set you would like to see?|||Originally posted by Teddy
Could you post a small sample set and the result set you would like to see?

Here is the query that I have started. Just need to work on the CASE statement.
My goal is to just show employees whose EjLocation doesn't match the PrbCheckSeqNo listed in the THEN statement.

IE
Name Location CheckCode
John Smith AGACES 8500

This person would show up because they don't equal 3410, 3415 or a code starting with 83##.

SELECT EpLastName, EpFirstName, EjLocation, PrbCheckSeqNo
FROM EJob, EBase, EPayrollBase
/* Check codes not matching facility code- excludes virtuals
Dev. T.Rucker 1/04 */
WHERE EpFlxideb = EjFlxideb AND Ebflxid = Prbflxideb AND PrbDateend is null AND EjDateEnd IS NULL AND Epflxideb = EeFlxideb AND
EeDateEnd IS NULL AND NOT PrbCheckSeqNo = '0000' AND
CASE
WHEN EjLocation ='AGACES'
THEN '3410'
WHEN EjLocation = 'BALCES'
THEN '2300'
ELSE 'O'
END <> PrbCheckSeqNo
ORDER BY EjLocation, PrbCheckSeqNo

Case sensitive query

I would like to execute query:
select Column1 from table
and I get an error message:
Invalid column name 'Column1'.
If I write query like this:
select column1 from table
than it works.
It looks that server is case sensitive for columns and tables. How can I
change this?
Otherwise I have to rewrite all aplication.
Regards,SYou'll have to do an ALTER DATABASE to change the collation on the DB.
However, you'll have to change the collation on all character columns to get
the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
I would like to execute query:
select Column1 from table
and I get an error message:
Invalid column name 'Column1'.
If I write query like this:
select column1 from table
than it works.
It looks that server is case sensitive for columns and tables. How can I
change this?
Otherwise I have to rewrite all aplication.
Regards,S|||I have already changed collation.
But I still have problems.
If I declare variable in my procedure, for example @.productID and than
somewhere in my procedure I use it, like: set @.productid=1
(not the same case for id), I get an error, because "id" should be "ID".
Any idea?
regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> You'll have to do an ALTER DATABASE to change the collation on the DB.
> However, you'll have to change the collation on all character columns to
> get
> the intended results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I would like to execute query:
> select Column1 from table
> and I get an error message:
> Invalid column name 'Column1'.
> If I write query like this:
> select column1 from table
> than it works.
> It looks that server is case sensitive for columns and tables. How can I
> change this?
> Otherwise I have to rewrite all aplication.
> Regards,S
>|||How exactly did you change the collation? What commands did you use?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
I have already changed collation.
But I still have problems.
If I declare variable in my procedure, for example @.productID and than
somewhere in my procedure I use it, like: set @.productid=1
(not the same case for id), I get an error, because "id" should be "ID".
Any idea?
regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> You'll have to do an ALTER DATABASE to change the collation on the DB.
> However, you'll have to change the collation on all character columns to
> get
> the intended results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I would like to execute query:
> select Column1 from table
> and I get an error message:
> Invalid column name 'Column1'.
> If I write query like this:
> select column1 from table
> than it works.
> It looks that server is case sensitive for columns and tables. How can I
> change this?
> Otherwise I have to rewrite all aplication.
> Regards,S
>|||I have changed collation:
ALTER DATABASE dbName COLLATE Slovenian_CI_AS
Queries now works:
select Column1 from table
or
select column1 from table
both works.
But if I declare variable in SP, like :
declare @.id int
and then set the value:
set @.ID=5
I get an error message, that I should declare variable @.ID.
This all is happening because Turkish has different letter for I or i.
But all my procedures are written case insensitive.
What should I do?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> How exactly did you change the collation? What commands did you use?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I have already changed collation.
> But I still have problems.
> If I declare variable in my procedure, for example @.productID and than
> somewhere in my procedure I use it, like: set @.productid=1
> (not the same case for id), I get an error, because "id" should be "ID".
> Any idea?
> regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
>
>|||Are you definitely inside the DB when you run the code or are you in master
or tempdb?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
I have changed collation:
ALTER DATABASE dbName COLLATE Slovenian_CI_AS
Queries now works:
select Column1 from table
or
select column1 from table
both works.
But if I declare variable in SP, like :
declare @.id int
and then set the value:
set @.ID=5
I get an error message, that I should declare variable @.ID.
This all is happening because Turkish has different letter for I or i.
But all my procedures are written case insensitive.
What should I do?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> How exactly did you change the collation? What commands did you use?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I have already changed collation.
> But I still have problems.
> If I declare variable in my procedure, for example @.productID and than
> somewhere in my procedure I use it, like: set @.productid=1
> (not the same case for id), I get an error, because "id" should be "ID".
> Any idea?
> regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
>
>|||I'm definitly inside the DB.
I go to database and click: create new stored procedure and then copy my
procedure from other server into this window and then check sintax failes
because of different letters.
Letter i is not the same as letter I on this server. On all other servers
everything works.
Any idea?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23ckyr$hXGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Are you definitely inside the DB when you run the code or are you in
> master
> or tempdb?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
> I have changed collation:
> ALTER DATABASE dbName COLLATE Slovenian_CI_AS
> Queries now works:
> select Column1 from table
> or
> select column1 from table
> both works.
> But if I declare variable in SP, like :
> declare @.id int
> and then set the value:
> set @.ID=5
> I get an error message, that I should declare variable @.ID.
> This all is happening because Turkish has different letter for I or i.
> But all my procedures are written case insensitive.
> What should I do?
> Regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||How about if you create the proc inside Query Analyzer? I don't trust
Enterprise Manager for most things.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:O8Z9iFkXGHA.196@.TK2MSFTNGP04.phx.gbl...
I'm definitly inside the DB.
I go to database and click: create new stored procedure and then copy my
procedure from other server into this window and then check sintax failes
because of different letters.
Letter i is not the same as letter I on this server. On all other servers
everything works.
Any idea?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23ckyr$hXGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Are you definitely inside the DB when you run the code or are you in
> master
> or tempdb?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
> I have changed collation:
> ALTER DATABASE dbName COLLATE Slovenian_CI_AS
> Queries now works:
> select Column1 from table
> or
> select column1 from table
> both works.
> But if I declare variable in SP, like :
> declare @.id int
> and then set the value:
> set @.ID=5
> I get an error message, that I should declare variable @.ID.
> This all is happening because Turkish has different letter for I or i.
> But all my procedures are written case insensitive.
> What should I do?
> Regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>

CASE returning different data types

Hi, folks...
I was trying to write a sql query which returns different data types, but it
returns every time, a smalldatetime format...
SELECT CASE WHEN Tipo = 'N' THEN Numerico
WHEN Tipo = 'L' THEN Logico
WHEN Tipo = 'P' THEN Percentual
WHEN Tipo = 'D' THEN Data
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
'Numerico' is an integer field
'L' is a bit field
'P' is a float field
'D' is a smalldatetime field.
Whatever is the datatype returning, the result is a smalldatetime field...
Any help would be apreciated.
Daniela.CASE is an expression -- and by definition every path of an expression must
return the same datatype (think of a function in a procedural language --
any function you define can only have a single return datatype). You might
consider, in this case, casting all return values to a string datatype.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Daniela Binatti" <Daniela Binatti@.discussions.microsoft.com> wrote in
message news:39E5A440-51BB-4DAF-A73B-8BDFD9E136F7@.microsoft.com...
> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||No, can't do that.. ALL Possible data values that could come out of a case
must be the same datatype. That's because the datatype is not associated
with the data item itself, but with the COLUMN of the resulting Resultset.
SQL has t oassigna a datatypes to the Column...
But not all s lost... You simply have t oCAST The data in each primitive
Table colum,n to the same datatype in the case statement
SELECT CASE WHEN Tipo = 'N' THEN Cast(Numerico As VarChar(20))
WHEN Tipo = 'L' And Logico = 1 THEN 'True'
WHEN Tipo = 'L' And Logico = 0 THEN 'False'
WHEN Tipo = 'P' THEN Cast(Percentual As VarChar(20))
WHEN Tipo = 'D' THEN Convert(VarChar(20), Data, 112)
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||If they are different types then how do you want to display them in a
single column? Maybe we could help you better if you explain more about
what you are trying to do.
David Portas
SQL Server MVP
--|||Thank you very much. I've sorted out the problem by converting every result
in a sql_variant field.
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>

CASE and INSERT statements

Hi,
I need to write a simple script which i am getting rather on how to
write. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather on how
> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.

Case

Being new to SQL Server and comming from an Oracle environment...
When I write queries for Oracle, I have to ensure that my queries are
written to be case sensitive. Now when I write those same queries in SQL
(using .Net) it does not appear to be case sensitive. Is this a database
setting or some setting in .Net which is causing case to be ignored?Hi,
You SQL Server collation must be set as CASE INSENSITIVE.. In this case
there is not difference betweeen Upper and
Lower case characters. If your collation is set to Case Sensitive then you
will have the difference between Upper and Lower case.
See the collation of the server using below command:-
select SERVERPROPERTY ( 'collation' )
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:D18CBCF9-EA51-4F72-A70D-61C86378C036@.microsoft.com...
> Being new to SQL Server and comming from an Oracle environment...
> When I write queries for Oracle, I have to ensure that my queries are
> written to be case sensitive. Now when I write those same queries in SQL
> (using .Net) it does not appear to be case sensitive. Is this a database
> setting or some setting in .Net which is causing case to be ignored?
>|||Hari Prasad wrote:
[vbcol=seagreen]
> Hi,
> You SQL Server collation must be set as CASE INSENSITIVE.. In this case
> there is not difference betweeen Upper and
> Lower case characters. If your collation is set to Case Sensitive then you
> will have the difference between Upper and Lower case.
> See the collation of the server using below command:-
> select SERVERPROPERTY ( 'collation' )
> Thanks
> Hari
> SQL Server MVP
>
> "Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
> news:D18CBCF9-EA51-4F72-A70D-61C86378C036@.microsoft.com...
It depends on collation of your SQL Server.
In your collation if you found CI then that collation is
case-insensitive and if it is CS then it is case-sensitive.
Try this
select * from ::fn_helpcollations()
where name = serverproperty('collation')
Regards
Amish Shah

Case

Being new to SQL Server and comming from an Oracle environment...
When I write queries for Oracle, I have to ensure that my queries are
written to be case sensitive. Now when I write those same queries in SQL
(using .Net) it does not appear to be case sensitive. Is this a database
setting or some setting in .Net which is causing case to be ignored?Hi,
You SQL Server collation must be set as CASE INSENSITIVE.. In this case
there is not difference betweeen Upper and
Lower case characters. If your collation is set to Case Sensitive then you
will have the difference between Upper and Lower case.
See the collation of the server using below command:-
select SERVERPROPERTY ( 'collation' )
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:D18CBCF9-EA51-4F72-A70D-61C86378C036@.microsoft.com...
> Being new to SQL Server and comming from an Oracle environment...
> When I write queries for Oracle, I have to ensure that my queries are
> written to be case sensitive. Now when I write those same queries in SQL
> (using .Net) it does not appear to be case sensitive. Is this a database
> setting or some setting in .Net which is causing case to be ignored?
>|||Hari Prasad wrote:
> Hi,
> You SQL Server collation must be set as CASE INSENSITIVE.. In this case
> there is not difference betweeen Upper and
> Lower case characters. If your collation is set to Case Sensitive then you
> will have the difference between Upper and Lower case.
> See the collation of the server using below command:-
> select SERVERPROPERTY ( 'collation' )
> Thanks
> Hari
> SQL Server MVP
>
> "Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
> news:D18CBCF9-EA51-4F72-A70D-61C86378C036@.microsoft.com...
> > Being new to SQL Server and comming from an Oracle environment...
> > When I write queries for Oracle, I have to ensure that my queries are
> > written to be case sensitive. Now when I write those same queries in SQL
> > (using .Net) it does not appear to be case sensitive. Is this a database
> > setting or some setting in .Net which is causing case to be ignored?
> >
It depends on collation of your SQL Server.
In your collation if you found CI then that collation is
case-insensitive and if it is CS then it is case-sensitive.
Try this
select * from ::fn_helpcollations()
where name = serverproperty('collation')
Regards
Amish Shah

Cascading delete

I have one master tables with 4 relational tables
The relationship between the tables is not cascading delete.
But I like to write one query that will delete record from the master table
as well as relational tables.
Let me know how can i do this.
Thanks in advanceThis newsgroup is for reporting services. Try one of the other SQL Server
newsgroups.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23%2355VDOtEHA.220@.TK2MSFTNGP15.phx.gbl...
> I have one master tables with 4 relational tables
> The relationship between the tables is not cascading delete.
> But I like to write one query that will delete record from the master
table
> as well as relational tables.
> Let me know how can i do this.
> Thanks in advance
>

Capturing WARNINGS to write to error log?

Hey y'all...

Anyone know how to capture SQL Warnings so I can write them to an error log? I can't seem to find any info on it in Books Online...

I can capture the errors just fine by using @.@.ERROR after a select, but what about warnings such as "Warning: Null value is eliminated by an aggregate or other SET operation."

Thanks!IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'NULL_VALUE'))
EXECUTE msdb.dbo.sp_delete_alert @.name = N'NULL_VALUE'
EXECUTE msdb.dbo.sp_add_alert @.name = N'NULL_VALUE', @.message_id = 8153, @.severity = 0, @.enabled = 1, @.delay_between_responses = 60, @.include_event_description_in = 4, @.category_name = N'[Uncategorized]'|||Warnings get returned as messages in the TDS stream, much like PRINT statements do. I don't think that they generate an internal event or change any spid state information, so I don't think that there is any way for a script/stored procedure to capture them.

I'm pretty sure that you can modify the client to capture warning messages and the text (after substitution) of error messages, but I can't think of any way to capture it on the server side.

-PatP|||Pat, do me a favor and replicate on your TEST system the following screens. I saved the actual error message screens, but before posting this also experimented with warnings that you called "like PRINT statements." So that there won't be any further confusion, anything that is stored in sysmessages can be logged to the errorlog of both SQL Server and Windows Application log, as long as the check box in screen #3 is checked.|||Will do, but it will be a couple of days before I have the opportunity. I'm traveling now, and when I get back to the orifice I'll be working on other issues for a couple of days. It will probably be 2004-07-29 before I really have a chance to try it.

-PatP

Can't write to SQL Table

I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
situation:
Microsoft SQL Server running Windows 2000 Server. Workstation connecting
via ODBC using SQL Driver.
We have a program on the workstation that pulls information from a single
table. There are over 800 tables in the database. For some reason the
application "scans" the entire database every time it pulls data from the
table we need. This takes a very long time. To try to combat this, we
created a restricted ID that only has access to the table we want. Now
reading from the table much faster.
However, now we can't write to the table. The table name is
"ups_work_table". When we try to write data to it, we get an error
"ups_work_table_trig_ins No data found for row".
So the admin person (as I understand it) made the ID part of a group that
has greater rights. The problem here is that again the ID has access to all
the tables and reading/writing is painfully slow. There has been talk of
groups & roles and how the group settings override the settings of the ID.
So restricting the access of the ID is defeated by giving it greater rights.
My question is: How can we restrict the ID to access a single table and
give that ID read, write, insert, & delete permissions for that table?
Thanks.
Hi
The error "ups_work_table_trig_ins No data found for row" looks like an
error getting raised by some code in the "ups_work_table" insert trigger.
Have a look at that trigger. Possibly, a lookup is beign done on some other
data, and because a specific user is expected by the code, it fails.
Regards
Mike
"Terry Olsen" wrote:

> I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
> situation:
> Microsoft SQL Server running Windows 2000 Server. Workstation connecting
> via ODBC using SQL Driver.
> We have a program on the workstation that pulls information from a single
> table. There are over 800 tables in the database. For some reason the
> application "scans" the entire database every time it pulls data from the
> table we need. This takes a very long time. To try to combat this, we
> created a restricted ID that only has access to the table we want. Now
> reading from the table much faster.
> However, now we can't write to the table. The table name is
> "ups_work_table". When we try to write data to it, we get an error
> "ups_work_table_trig_ins No data found for row".
> So the admin person (as I understand it) made the ID part of a group that
> has greater rights. The problem here is that again the ID has access to all
> the tables and reading/writing is painfully slow. There has been talk of
> groups & roles and how the group settings override the settings of the ID.
> So restricting the access of the ID is defeated by giving it greater rights.
> My question is: How can we restrict the ID to access a single table and
> give that ID read, write, insert, & delete permissions for that table?
> Thanks.
>
>

Can't write to SQL Table

I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
situation:
Microsoft SQL Server running Windows 2000 Server. Workstation connecting
via ODBC using SQL Driver.
We have a program on the workstation that pulls information from a single
table. There are over 800 tables in the database. For some reason the
application "scans" the entire database every time it pulls data from the
table we need. This takes a very long time. To try to combat this, we
created a restricted ID that only has access to the table we want. Now
reading from the table much faster.
However, now we can't write to the table. The table name is
"ups_work_table". When we try to write data to it, we get an error
"ups_work_table_trig_ins No data found for row".
So the admin person (as I understand it) made the ID part of a group that
has greater rights. The problem here is that again the ID has access to all
the tables and reading/writing is painfully slow. There has been talk of
groups & roles and how the group settings override the settings of the ID.
So restricting the access of the ID is defeated by giving it greater rights.
My question is: How can we restrict the ID to access a single table and
give that ID read, write, insert, & delete permissions for that table?
Thanks.Hi
The error "ups_work_table_trig_ins No data found for row" looks like an
error getting raised by some code in the "ups_work_table" insert trigger.
Have a look at that trigger. Possibly, a lookup is beign done on some other
data, and because a specific user is expected by the code, it fails.
Regards
Mike
"Terry Olsen" wrote:

> I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
> situation:
> Microsoft SQL Server running Windows 2000 Server. Workstation connecting
> via ODBC using SQL Driver.
> We have a program on the workstation that pulls information from a single
> table. There are over 800 tables in the database. For some reason the
> application "scans" the entire database every time it pulls data from the
> table we need. This takes a very long time. To try to combat this, we
> created a restricted ID that only has access to the table we want. Now
> reading from the table much faster.
> However, now we can't write to the table. The table name is
> "ups_work_table". When we try to write data to it, we get an error
> "ups_work_table_trig_ins No data found for row".
> So the admin person (as I understand it) made the ID part of a group that
> has greater rights. The problem here is that again the ID has access to a
ll
> the tables and reading/writing is painfully slow. There has been talk of
> groups & roles and how the group settings override the settings of the ID.
> So restricting the access of the ID is defeated by giving it greater right
s.
> My question is: How can we restrict the ID to access a single table and
> give that ID read, write, insert, & delete permissions for that table?
> Thanks.
>
>

Cant write longer string in the record

I have a field which was varchar before but I changed it to text.

But i can't write in it enough text as i wish. This field is important becouse it holds SQL senences which i parse latter in my application and than i execute it.

it says <Long Text> and i can't enter any more characters.You got more than 8000 bytes of data?

Look up READTEXT WRITETEXT.

Welcome to a whole new world of hurt.

should've stayed with varchar(8000)|||Each "statement" should easily fit into an 8000-character field. Add a table that would organize the statements into batches, then restructure your original table by adding FK from your BatchMaster, and a Statement Sequence field (what statement comes first, second, etc. in each batch) Ability to read/write text fields is good, but when there is no need...there is no need ;)