Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Thursday, March 29, 2012

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Tuesday, March 27, 2012

CASE SQL Construct

I am using the following code to construct an SQL 7.0 View Column

"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"

and it works great!!

However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"

The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."

It will not let me save, so I'm not sure what to do from here now??You need to write your query in Query Analyzer, not the Query Designer, which is limited in the types of query logic it can represent in its GUI interface.
No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...|||You are right, I shouldn't be using Query Designer.... here's my problem.

My client is in another city, and I am logging onto their system remotely to copy some code I've written using a VPN they set up for me on my lap top...

The only software I have on my lap top is designer ... which is lame I know.

I have Visual Studio on my primary system...... but no simple way to put this code on their system - ANY thoughts?|||Why don't you use osql?|||No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...

You taking midol today?|||You taking midol today?
Just my usual grace and elegance.|||Just rename the file to remove the .txt extension|||I opened the file and I get a black screen that looks like an old DOS prompt, which is asking for a password. No matter what I enter it doesn't accept, and a blank simply closes the window....... I'm confused..... what's the password - my windos password or something specific that I have yet to learn??|||at a command window type 'osql.exe /?' it will give you a quick help message on how to use osql. Once you are in to the server, you can execute t-sql commands from there. Look it up in BOL if you need more help.

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Sunday, March 25, 2012

Case Sensitive vs Insensitive

After all the pain I've been going through with code pages and collation, I was asked how, when sql server does it's joins and predicate searches, how does it actual (internals now) know the an "A" = "a" in an insensitive search?

I didn't have the answer.

Damn, Now I really have to pick up Kelans book.I've seen tricks suchs as uppercasing or lowercasing both prior the compare. I think formatting routines such as these can be very quick because they only have lookup's to do.|||Well, the questions stems from an insensetive server

You don't need to use CASE functions in this case

"A" = "a"
"A" = "A"
"a" = "A", and
"a" = "a"

Internally each has it's on ASCII represenation.

How does an insesitive ("the big jerk") server resolve this?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?

No, they don't know, and because DB2 OS/390 is case sensitive, he didn't understand...and because I've got sql boxes built six ways to sunday, it's been bugging me...and that's how it arose...

But now it's bugging me as well...|||helluva question - 1st i'd smack the guy who asked.

it's just like asking how does SQL server know 1 != 2?
not a technically valid question, IMHO, but thats way the cookie crumbles sometimes, i reckon.

my understanding is that collation is a 3 legged stool
the sort order + the code page + the character set or "dictionary".

if the sort order is binary, my personal favorite,
its pretty simple: the bit pattern mapped to 'a' is not the same as 'A'. what ends up writing to disk is simply as different between 'a' and 'A' as it is between '1' and '2'. it knows 'a' is not 'A' because they are completely differnt patterns stored on the disk.

i dont think i've ever totally understood how non-binary case-sensitive sort orders know the difference - but i do know it involves 'interrogating' the character set dictionary at a very very low level to get the info it needs - which is why it is a noticably slower way to store/join/compare your data.

both binary and dictionary sorts 'technically' use bit patterns, but sometimes a particular character set stored in non-binary can have funky characters and stuff that make it not "always" work.

in either scenario, depending on the level of the person asking the question - i'd think the bit pattern explanation is close enough for gov't work, after the back-hand, of course.|||I think ascii comparisons are from the old days. If case-insensitive is set, I would think it'll look at the codepage and have the codepage decide which character is its uppercase or lowercase equivalent.|||helluva question - 1st i'd smack the guy who asked.

Now do you seriously think I'd entertain this question from someone who didn't SIGN my check?

Anyway, it'a valid question, and from what ya'll (no I'm not from the south) have mentioned, it makes a lot of sense, and was thinking in those terms...

Still gotta google some details...

Thanks|||LOL - i know what you mean.
Gotta make that mortgage payment.|||The problem is that the actual process isn't that simple. There is bitmap that shows which charcters are mapped, and a hash of arrays that allows each unicode character set to "remap" characters as needed. Each array contains the hash signature, a value for comparison, and a value for sorting. Most characters don't have lookup entries in the hash, so they use the actual binary value.

Aren't you glad that you asked?

-PatP|||hey pat -
does the 'array' you refer to only store the unicode, and special non-unicode characters for languages that use them (tilde, etc)? or all characters?

sounds as though the premise that the code page has all the info in it and SQL services communicate with its contents for sorting and comparison rules is the basic idea for dictionary sorts - or it uses the binary value directly for binary sorts.

is that your take, at a high level?|||The hash is the actual lookup mechanism. The array that is returned from the hash contains values that are used for comparisons and sorting.

You can think of it something like this: When processing a character, it gets expanded to 16 bits if it wasn't already, then that 16 bit value is used as a lookup into the bitmask to see if this character gets special processing (most don't). If it needs processing, a hash lookup is done which returns the character itself, the value to use for comparison purposes, and a value to use for sorting purposes. If the character didn't need processing, it is copied into all three values in an empty array.

-PatP|||thanks!
'splains a little better why binary sorts are mo'better, IMO.
they need no translation and/or re-sorting.

of course, some users expect charater ordering to work like it did for them in 3rd grade, but, eh, NMFP. the database is faster, that IS my problem.

i'm starting to think i'm an evil dba...

:confused:|||Once you get past the surface...it's all about the internals and undersatanding how things work...

Thanks again Pat...

(He has a signed copy of Kelans book no doubt)|||quite true.
there's so much to learn i'll be busy the rest of my life.

might be useful for this thread and/or compiling a detailed answer for your bossman:

master..syscharsets is where SQL server goes to get the binary definitions for character sets and sort orders.

peace.|||(He has a signed copy of Kelans book no doubt)That I do! If you mean Kalen's Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=UM7Mwdc9jn&isbn=0735609985&itm=1).

-PatP

Tuesday, March 20, 2012

CASE in WHERE clause?

I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:

select name, address, city etc
from customer
where if users want to see customers of a certain type then type = @.myInParameterForType
and if users want to see customers from a certain area then area = @.myInParameterForArea

is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!Yes, you may use CASE in where|||one easy (but not very performant) way is to build your "where" clause in your application (ie:web form ) and send it to your sp:
------------------------
CREATE PROCEDURE mySP

(
@.WhereClause varchar(4000)
)

AS
Declare @.SQL varchar(6000)

begin

set @.SQL = 'SELECT field1, field2 ... from myTable where ' + @.WhereClause + ' ORDER BY someID'

end

EXEC (@.SQL)

RETURN

GO
------------------------

when you have many optional parameter it is very conveniant

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

Monday, March 19, 2012

case and group by problem

Hi I have this code below it gives me the right results but there are load o
f
null values. Is there a way to group by on a case statement to elimiinate
these nulls to smarten up ther report
select ,c.name ,c.custno,c.salesno, case catno
when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
from ius_cust c
inner join ius_detail d
on d.custno=c.custno
inner join ius_prods p
on p.prod=d.prod
where invdate > '2005-01-01'
group by c.name,c.custno,c.salesno,p.catno
thanks for any help
Sammychange the case catno
when 'L' then sum(qtyshp* netprice) end as
to case catno
when 'L' then sum(qtyshp* netprice) else 0 end as
put else 0 in between
http://sqlservercode.blogspot.com/
Sammy wrote:
> Hi I have this code below it gives me the right results but there are load
of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||Sammy
Can you show us your table's structure?
create table #test
(
col1 int,
col2 int,
col3 char(1)
)
insert into #test values (1,10,'h')
insert into #test values (1,40,'h')
insert into #test values (1,20,'s')
insert into #test values (2,20,'h')
insert into #test values (2,10,'h')
insert into #test values (2,850,'a')
select col1,sum(case when col3='h' then col2 end),
sum(case when col3='h' then col2 end)
from #test
group by col1
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:CE89E103-FD29-4B44-A12B-D10C7D836A13@.microsoft.com...
> Hi I have this code below it gives me the right results but there are load
> of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||The nulls are from empty result sets, so try try this to get zero
amounts
SUM (CASE cat_no WHEN 'n'
THEN (qty_shp* net_price)
ELSE 0.00 END) AS cat-N
Do not put the alias names in single quotes since that is proprietary
syntax that will screw up other tools and portability.|||On Wed, 21 Dec 2005 08:23:03 -0800, Sammy wrote:

>Hi I have this code below it gives me the right results but there are load
of
>null values. Is there a way to group by on a case statement to elimiinate
>these nulls to smarten up ther report
>select ,c.name ,c.custno,c.salesno, case catno
>when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
>when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
>when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
>when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
>from ius_cust c
>inner join ius_detail d
>on d.custno=c.custno
>inner join ius_prods p
>on p.prod=d.prod
>where invdate > '2005-01-01'
>group by c.name,c.custno,c.salesno,p.catno
Hi Sammy,
Try if this works:
SELECT c.name, c.custno, c.salesno,
SUM(CASE catno WHEN 'n' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-N",
SUM(CASE catno WHEN 'L' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-L",
SUM(CASE catno WHEN 'G' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-G",
SUM(CASE catno WHEN 'I' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-I"
FROM ius_cust AS c
INNER JOIN ius_detail AS d
ON d.custno = c.custno
INNER JOIN ius_prods AS p
ON p.prod = d.prod
WHERE invdate > '20050101' -- Note the recommended format
GROUP BY c.name, c.custno, c.salesno, p.catno
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 21 Dec 2005 08:33:07 -0800, --CELKO-- wrote:

>The nulls are from empty result sets, so try try this to get zero
>amounts
> SUM (CASE cat_no WHEN 'n'
> THEN (qty_shp* net_price)
> ELSE 0.00 END) AS cat-N
>Do not put the alias names in single quotes since that is proprietary
>syntax that will screw up other tools and portability.
Hi Joe,
The alternative you recommend is portable - it results in an error on
ALL platforms. ;->
Try enclosing it in double quotes: ... AS "cat-N"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading path problem

Hello,
The following SQL code produces the famous multiple cascading paths problem.
How should I design the tables to have the below functionality, but keep the
cascading paths? A Doc doesn't necessarily have to be related to a Folder,
but must be related to a Cust.
Changing ON UPDATE to NO ACTION would solve it partly, but it just doesn't
feel right.
Thanks for any help!
cheers,
Jonah
CREATE TABLE Cust (
usr_name varchar(20) NOT NULL,
usr_pwd varchar(40) NOT NULL,
customer_name nvarchar(50) NOT NULL,
created_date datetime default getdate() NOT NULL,
change_date datetime default getdate() NOT NULL,
deactivate_date datetime default getdate() NULL
) ON [PRIMARY]
GO
ALTER TABLE Cust ADD CONSTRAINT
PK_Cust PRIMARY KEY CLUSTERED
(
usr_name
) ON [PRIMARY]
GO
CREATE TABLE Folders (
folder_id int NOT NULL ,
folder_name nvarchar(20) NOT NULL ,
folder_description nvarchar(150) NULL ,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
PK_Folders PRIMARY KEY CLUSTERED
(
folder_id
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
FK_Folders_Cust FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
GO
CREATE TABLE Docs (
doc_id int NOT NULL,
header nvarchar(255) not null,
created_date datetime default getdate() NOT NULL,
updated_date datetime default getdate() NOT NULL,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
PK_Docs PRIMARY KEY CLUSTERED
(
doc_id
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
FK_Cust_Docs FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
ON DELETE NO ACTION
GO
CREATE TABLE DocsInFolders
(
doc_id int NOT NULL,
folder_id int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
PK_DocsInFolders PRIMARY KEY CLUSTERED
(
doc_id,
folder_id
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Folders FOREIGN KEY
(
folder_id
) REFERENCES Folders
(
folder_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Docs FOREIGN KEY
(
doc_id
) REFERENCES Docs
(
doc_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GOYou say 'A Doc *doesn't necessarily have to be* related to a Folder,
but *must be* related to a Cust.'
Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
relationship in procedures. You make it sound as if the importance of the
Cust<--Docs relationship supercedes the importance of the Folders<--Docs
relationship.
Vital relationships should not be cascading.
ML|||Correct. The Cust<--Docs relationship is more importance, but that's why I
have only ON UPDATE CASCADE (to make possible usr_name changes up to date)
and not ON DELETE because I don't want Custs having Docs deleted by mistake.
An SP takes care of that.
So what you are saying is that I should change the ON CHANGE to NO ACTION as
well?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:601F4976-E669-4865-9D92-FBAF9A16CA8F@.microsoft.com...
> You say 'A Doc *doesn't necessarily have to be* related to a Folder,
> but *must be* related to a Cust.'
> Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
> relationship in procedures. You make it sound as if the importance of the
> Cust<--Docs relationship supercedes the importance of the Folders<--Docs
> relationship.
> Vital relationships should not be cascading.
>
> ML|||If usr_name can be changed then using it as a primary key (and/or referencin
g
it from a foreign key table) is really bad practice. Either disallow usr_nam
e
changes or use a better kandidate key.
I wouldn't allow cascades for this one.
ML|||OK. So if I disallow cascades for usr_name, you would consider the design to
be correct?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:90F300A3-7104-4924-A594-E28AC05D645D@.microsoft.com...
> If usr_name can be changed then using it as a primary key (and/or
> referencing
> it from a foreign key table) is really bad practice. Either disallow
> usr_name
> changes or use a better kandidate key.
> I wouldn't allow cascades for this one.
>
> ML|||As far as I can see, the design is fine. I would, however, do something abou
t
the Folders and Docs entities. Right now you allow a single document to exis
t
in more than one folder, which can lead to problems. The same goes for
folders - you should focus on preventing circular references.
Oh, and if any given document cannot exist in more than one folder, then the
DocsInFolders table is obsolete. You could simply add a nullable folder_id
foreign key to the Docs table (nullable since you've mentioned that a
document need not exist in any folder).
I hope you started on paper. :) And in case you haven't, maybe you'll do it
next time.
ML|||My design question applied to the cascading paths, not the business rules
themselves. One Doc may actually exist in several Folders.
- One Cust may have zero or more Folders
- One Cust may have zero or more Docs not connected to a Folder
- One Folder may have zero or more Docs related
Thus, my question was only related to if there's a better way of designing
the relations and tables to avoid circular references, which now occurs.
FYI, I didn't start on paper. I use Visio.
Thank you,
Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:F981A87A-893F-4940-9EBD-566C385CF1CB@.microsoft.com...
> As far as I can see, the design is fine. I would, however, do something
> about
> the Folders and Docs entities. Right now you allow a single document to
> exist
> in more than one folder, which can lead to problems. The same goes for
> folders - you should focus on preventing circular references.
> Oh, and if any given document cannot exist in more than one folder, then
> the
> DocsInFolders table is obsolete. You could simply add a nullable folder_id
> foreign key to the Docs table (nullable since you've mentioned that a
> document need not exist in any folder).
> I hope you started on paper. :) And in case you haven't, maybe you'll do
> it
> next time.
>
> ML|||No pun intended.
You are right - there is a better way to avoid circular references. You
might find more answers studying trees and hierarchies. Consider this model:
ItemInstance : ItemID : BelongsToInstance : ItemType
ItemInstance is unique.
ItemID can be either cust_id, folder_id or doc_id.
BelongsToInstance is a foreign key referencing ItemInstance.
ItemType designates whether ItemID is customer, folder or document.
Valid relationships are:
1) Customer/Folder/Document
2) Customer/Document
3) Folder/Folder (<-- not sure about this one, but seems logical, however:
parent folder_id must should be equal to child folder_id).
A customer can only exist as a root element (BelongsToInstance is null).
A Document can only exist as a leaf element (its ItemInstance is never
referenced in a BelongsToInstance).
The above constraints could be reinforced through the use of indexed views.
ItemInstance and BelongsToInstance prevent circular references while still
allowing all possible relationships between the three entities.
ML|||Thank you for your detailed answer.
I do have a copy of a trees and hierarchies book which I could take a closer
look into (Joe Celko's Trees and Hierarchies in SQL for Smarties). Maybe I
can find some more answers and examples there.
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:D5B9F5D8-4790-4088-BF18-14DE5119891D@.microsoft.com...
> No pun intended.
> You are right - there is a better way to avoid circular references. You
> might find more answers studying trees and hierarchies. Consider this
> model:
> ItemInstance : ItemID : BelongsToInstance : ItemType
> ItemInstance is unique.
> ItemID can be either cust_id, folder_id or doc_id.
> BelongsToInstance is a foreign key referencing ItemInstance.
> ItemType designates whether ItemID is customer, folder or document.
> Valid relationships are:
> 1) Customer/Folder/Document
> 2) Customer/Document
> 3) Folder/Folder (<-- not sure about this one, but seems logical, however:
> parent folder_id must should be equal to child folder_id).
> A customer can only exist as a root element (BelongsToInstance is null).
> A Document can only exist as a leaf element (its ItemInstance is never
> referenced in a BelongsToInstance).
> The above constraints could be reinforced through the use of indexed
> views.
> ItemInstance and BelongsToInstance prevent circular references while still
> allowing all possible relationships between the three entities.
>
> ML|||What about using a nested sets model for the hierarchy? I am not a big
fanof misxed node trees, but this case is pretty easy:
1) If it is the root node, it is a Customer
2) If it is a leaf node, it is a document
3) other it is a folder
CREATE TABLE DocumentHierarchy
(customer_id INTEGER NOT NULL
REFERENCES Customers
ON UPDATE CASCADE
ON DELETE CASCADE,
folder_id INTEGER -- null means no folder
REFERENCES Folders (folder_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
document_id INTEGER -- null means no document
REFERENCES Documents(doc_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
lft INTEGER NOT NULL CHECK (lft >0),
rgt INTEGER NOT NULL CHECK (rgt >lft),
PRIMARY KEY (customer_id, lft, rgt));
untested. You can also get a copy of TREES & HIERARCHIES IN SQL for
more ideas.

Thursday, March 8, 2012

Cascade 2 strings in stored procedure for MS SQL

What is the code to cascade two strings

SET @.string1 = 'aa'
SET @.string2 = 'bb'

How can I get a string 'aabb' in stored procedure in SQL SERVER 2000?

thanksAs I understand ur question u can show such

Print @.string1 + @.string2

CAS

I'm having a difficult time wrapping my head around Code Access Security.
We have just installed Sharepoint Portal Server 2003. I have a web part that
uses the obc.net dataprovider that is causing me a security error when I try
to upload the web part.
I'm just not understanding what I need to change on the server to allow this
code to run. Since very few people will be able to add web parts does it
make sense just to take the security down to a lower level? This is on our
corporate intranet. If so, can someone explain to me how to lower the
security for the whole site?
Thanks,
JasonSorry - wrong newsgroup
"Jason MacKenzie" <jmackenzie_nospamallowed@.formet.com> wrote in message
news:uunGCpJpEHA.896@.TK2MSFTNGP12.phx.gbl...
> I'm having a difficult time wrapping my head around Code Access Security.
> We have just installed Sharepoint Portal Server 2003. I have a web part
> that uses the obc.net dataprovider that is causing me a security error
> when I try to upload the web part.
> I'm just not understanding what I need to change on the server to allow
> this code to run. Since very few people will be able to add web parts
> does it make sense just to take the security down to a lower level? This
> is on our corporate intranet. If so, can someone explain to me how to
> lower the security for the whole site?
> Thanks,
> Jason
>

Wednesday, March 7, 2012

carriage return in label

Hi, any way to code a linefeed/carriage return in a label? I need it for
labels in a pie graph (description on line one, value on line 2 of pie graph
series description).
Any suggestions welcome!
Thanks,
JohnJust use an expression similar to this for the datapoint label expression:
=Fields!Description.Value & vbcrlf & Sum(Fields!X.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:69F5F15A-D307-49FE-B705-77CB5CE4C7C7@.microsoft.com...
> Hi, any way to code a linefeed/carriage return in a label? I need it for
> labels in a pie graph (description on line one, value on line 2 of pie
> graph
> series description).
> Any suggestions welcome!
> Thanks,
> John
>|||You can also try using System.Environment.NewLine as vbcrlf may not
always be interpreted correctly depending on the report format (PDF,
Excel, HTML,...) you use.
Q
Robert Bruckner [MSFT] wrote:
> Just use an expression similar to this for the datapoint label expression:
> =Fields!Description.Value & vbcrlf & Sum(Fields!X.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "John" <John@.discussions.microsoft.com> wrote in message
> news:69F5F15A-D307-49FE-B705-77CB5CE4C7C7@.microsoft.com...
> > Hi, any way to code a linefeed/carriage return in a label? I need it for
> > labels in a pie graph (description on line one, value on line 2 of pie
> > graph
> > series description).
> >
> > Any suggestions welcome!
> >
> > Thanks,
> > John
> >|||Works! Thx!
"baseLogiK" wrote:
> You can also try using System.Environment.NewLine as vbcrlf may not
> always be interpreted correctly depending on the report format (PDF,
> Excel, HTML,...) you use.
> Q
> Robert Bruckner [MSFT] wrote:
> > Just use an expression similar to this for the datapoint label expression:
> > =Fields!Description.Value & vbcrlf & Sum(Fields!X.Value)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "John" <John@.discussions.microsoft.com> wrote in message
> > news:69F5F15A-D307-49FE-B705-77CB5CE4C7C7@.microsoft.com...
> > > Hi, any way to code a linefeed/carriage return in a label? I need it for
> > > labels in a pie graph (description on line one, value on line 2 of pie
> > > graph
> > > series description).
> > >
> > > Any suggestions welcome!
> > >
> > > Thanks,
> > > John
> > >
>

Caputre T-SQL Error Message in Stroed Proc

I need to capture the exact error message whenever the error occur while exectuing the SQL query. I am able to get the error code and error message from sysmessages table but error message contain some input values like '%ls'. I need to get the exact mess
age.
Any help would be appricated.
Thanks in Advanced
Not possible: http://vyaskn.tripod.com/programming_faq.htm#q7
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jitendra" <Jitendra@.discussions.microsoft.com> wrote in message
news:16CBD76A-C566-407E-A554-945809530259@.microsoft.com...
> I need to capture the exact error message whenever the error occur while
exectuing the SQL query. I am able to get the error code and error message
from sysmessages table but error message contain some input values like
'%ls'. I need to get the exact message.
> Any help would be appricated.
> Thanks in Advanced
|||I dont know if there's a way where the stored proc woudl run to completion
if an error occurs. It would ideally exit and print the message. So unless
you want to say, direct your results (inc error message) to a txt file?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Caputre T-SQL Error Message in Stroed Proc

I need to capture the exact error message whenever the error occur while exe
ctuing the SQL query. I am able to get the error code and error message from
sysmessages table but error message contain some input values like '%ls'. I
need to get the exact mess
age.
Any help would be appricated.
Thanks in AdvancedNot possible: http://vyaskn.tripod.com/programming_faq.htm#q7
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jitendra" <Jitendra@.discussions.microsoft.com> wrote in message
news:16CBD76A-C566-407E-A554-945809530259@.microsoft.com...
> I need to capture the exact error message whenever the error occur while
exectuing the SQL query. I am able to get the error code and error message
from sysmessages table but error message contain some input values like
'%ls'. I need to get the exact message.
> Any help would be appricated.
> Thanks in Advanced|||I dont know if there's a way where the stored proc woudl run to completion
if an error occurs. It would ideally exit and print the message. So unless
you want to say, direct your results (inc error message) to a txt file?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Saturday, February 25, 2012

Capturing SQL Data via Triggers

Good afternoon!

The code below only captures previous recorded profile information but not the profile information currently being saved.

How can I correct this issue?

TRIGGER INFORMATION:

USE heat601
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trk_profile' AND type = 'TR')
DROP TRIGGER trk_profile
GO
CREATE TRIGGER trk_profile
ON profile
FOR INSERT
AS
DECLARE @.vTracker varchar(10), @.vTrackername varchar (25), @.vAuditDate varchar(10), @.vCustId varchar(10)

SELECT @.vTracker = profile.Tracker, @.vTrackername = calllog.RbyFullName, @.vAuditDate = profile.DTLMod, @.vCustId = profile.CustId
From profile JOIN calllog On profile.custid = calllog.custid

Insert Into AuditTrail Values(@.vTracker,@.vTrackername,@.vAuditDate,@.vCusti d)

EXEC master..xp_sendmail 'tward@.caremark.com',
'HEAT CMS customer profile is currently being manipulated.'
GODo U use the Temporary Inserted & Deleted Tables ?

Capturing SQL code that generates error

I have an application that imports application data based on the Windows Installer .MSI schema into a SQL database. If it possible that the data in the tables exceeds the size allowed in our database. When that happens, an error message is generated. This
is fine as the user has the option to 'Ignore.'
The problem is that I cannot tell what application is being imported when the error message is produced. Is there a method to capturing the 'parent' SQL insert statements so that I can get the application name?
Thanks
Have you considered using Profiler? Let us know if you need help with using
Profiler to capture the statement and the error.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DanetteB" <DanetteB@.discussions.microsoft.com> wrote in message
news:E5E14CFC-8F1C-49F5-AEE5-40C14448FC86@.microsoft.com...
I have an application that imports application data based on the Windows
Installer .MSI schema into a SQL database. If it possible that the data in
the tables exceeds the size allowed in our database. When that happens, an
error message is generated. This is fine as the user has the option to
'Ignore.'
The problem is that I cannot tell what application is being imported when
the error message is produced. Is there a method to capturing the 'parent'
SQL insert statements so that I can get the application name?
Thanks
|||Vyas -
Thanks for the reply. I have tried the Profiler, but I am not getting the data I need. I'm probably not capturing the correct information. Any suggestions would be much appreciated.
"Narayana Vyas Kondreddi" wrote:

> Have you considered using Profiler? Let us know if you need help with using
> Profiler to capture the statement and the error.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
|||Vyas -
I got it - thank you for the Profiler suggestion. I went back into the tool and added the TSQL events.
DanetteB
"DanetteB" wrote:

> Vyas -
> Thanks for the reply. I have tried the Profiler, but I am not getting the data I need. I'm probably not capturing the correct information. Any suggestions would be much appreciated.
>
> "Narayana Vyas Kondreddi" wrote:
>

Capturing SQL code that generates error

I have an application that imports application data based on the Windows Ins
taller .MSI schema into a SQL database. If it possible that the data in the
tables exceeds the size allowed in our database. When that happens, an error
message is generated. This
is fine as the user has the option to 'Ignore.'
The problem is that I cannot tell what application is being imported when th
e error message is produced. Is there a method to capturing the 'parent' SQL
insert statements so that I can get the application name?
ThanksHave you considered using Profiler? Let us know if you need help with using
Profiler to capture the statement and the error.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DanetteB" <DanetteB@.discussions.microsoft.com> wrote in message
news:E5E14CFC-8F1C-49F5-AEE5-40C14448FC86@.microsoft.com...
I have an application that imports application data based on the Windows
Installer .MSI schema into a SQL database. If it possible that the data in
the tables exceeds the size allowed in our database. When that happens, an
error message is generated. This is fine as the user has the option to
'Ignore.'
The problem is that I cannot tell what application is being imported when
the error message is produced. Is there a method to capturing the 'parent'
SQL insert statements so that I can get the application name?
Thanks|||Vyas -
Thanks for the reply. I have tried the Profiler, but I am not getting the da
ta I need. I'm probably not capturing the correct information. Any suggestio
ns would be much appreciated.
"Narayana Vyas Kondreddi" wrote:

> Have you considered using Profiler? Let us know if you need help with usin
g
> Profiler to capture the statement and the error.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>|||Vyas -
I got it - thank you for the Profiler suggestion. I went back into the tool
and added the TSQL events.
DanetteB
"DanetteB" wrote:

> Vyas -
> Thanks for the reply. I have tried the Profiler, but I am not getting the
data I need. I'm probably not capturing the correct information. Any suggest
ions would be much appreciated.
>
> "Narayana Vyas Kondreddi" wrote:
>
>

Capturing operations and tSQL executed under a user account

All,
I am looking for a way to capture all of the SQL code executed against a
database by a specific user.
Currently, a SQL 2005 database is being maintained by a third party that
connects remotely and does occasional "maintenance." The client would like
to bring this operation in house and is looking for a way to capture these
activities so that they can be documented.
The accuracy of this is essential so we are looking for a way to do a direct
capture.
Suggestions?
Ryan Hanisco
MCSE, MCTS: SQL 2005, Project+
Chicago, IL
Remember: Marking helpful answers helps everyone find the info they need
quickly.
On Jun 7, 4:06 pm, Ryan Hanisco
<RyanHani...@.discussions.microsoft.com> wrote:
> All,
> I am looking for a way to capture all of the SQL code executed against a
> database by a specific user.
> Currently, a SQL 2005 database is being maintained by a third party that
> connects remotely and does occasional "maintenance." The client would like
> to bring this operation in house and is looking for a way to capture these
> activities so that they can be documented.
> The accuracy of this is essential so we are looking for a way to do a direct
> capture.
> Suggestions?
The easy way is to use Profiler. (That's what they called it in SQL
2000, it's undoubtedly there in SQL 2005 but possibly under a new
name.)
If you have to do it the hard way, look at the sp_trace stored
procedure.
|||Ryan Hanisco (RyanHanisco@.discussions.microsoft.com) writes:
> I am looking for a way to capture all of the SQL code executed against a
> database by a specific user.
> Currently, a SQL 2005 database is being maintained by a third party that
> connects remotely and does occasional "maintenance." The client would
> like to bring this operation in house and is looking for a way to
> capture these activities so that they can be documented.
> The accuracy of this is essential so we are looking for a way to do a
> direct capture.
This is precisely what SQL Profiler is good for. Or a server side trace,
if you want to reduce the load.
Although, this appears to me a somewhat funny thing to document procedures.
It sounds more like eavesdropping to me.n
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx