Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!
Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!
|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>
sql

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it
- fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates
that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it
- fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2M
SFTNGP03.phx.gbl...
>

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>>I need to change the collation for my database from SQL_Latin1_General_CP1_CI_ASto
>>SQL_Latin1_General_CP1_CI_AI
>> Is the statement "ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AI" enough or
>> should I change every existing fields (how to do it)?
>> Any ideas? Any precautions to take?
>> Thanks!
>

Changing authentication mode in TSQL?

Is there a TSQL statement that will allow you to change
the authentication mode from 'Windows Authentication'
to 'Mixed Mode'?
Thanks in advance!You can use the undocumented xp_regwrite/xp_instance_regwrite to change the
registry key that contols this (which is what EM does anyway) or use SQLDMO
but it needs a SQL Service restart to take effect e.g.
exec master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'LoginMode', N'REG_DWORD', 2
'Set security mode to mixed where oSQL is a SQLServer object
Set oSEC = oSQL.IntegratedSecurity
oSEC.SecurityMode = 2
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Fee" <william.fee@.compuware.com> wrote in message
news:132701c37266$f9ad2310$a601280a@.phx.gbl...
Is there a TSQL statement that will allow you to change
the authentication mode from 'Windows Authentication'
to 'Mixed Mode'?
Thanks in advance!

Changing a SQL Statement as it's passed to the server.

Wondering if this is possible...

If have a program (a standalone .Exe) that passed the following into our SQL Server.

SELECT l.LabelType, l.PalletLabelID, l.DelSchedID, l.AsnID, d.BasicPartNo, d.LatestPartNo, d.Description, d.PartColour, d.ContainerCode, d.PackFactor FROM Label l INNER JOIN DeliverySchedule d ON l.DelSchedID = d.DelSchedID WHERE l.SupplierID = 1 AND l.WarehouseID = 1 AND l.LabelID = ?

The statement is always the same, the only thing that differs is the LabelID field, which can be any number between 000000 and 999999.

The problem is however is that the statement is wrong! (very wrong), and I was wondering if it was possible to do any of the following:

Create something which could be classed as a "Select Trigger" (that is, I can create a script to determine what values are returned back).

Create something that constantly looks for that sentance been passed and then run my script to return the correct data.

I'm at a loss of ideas on how to achieve what I'm after.

The .Exe can NOT be changed.

Would really appreciate some feedback.

Define "wrong". You could possibly use views to make the data look like the query, if some data is wrong. There are a few things you can do performancewise if it is performance issue.

I don't think there is anything that you can do to capture the statement and change it in stream (unless you wrote your own drivers and used them instead of the OleDb or ODBC drivers, which is probably harder than just rewriting your EXE from scratch.)

>>The .Exe can NOT be changed.<<

Is it that large of a program? Is it a third party thing?

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?
Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?
Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:
>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:
>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes in Table

How we can stop alter statement of Table when the Table is referred by Stored Procedures and other Objects?

Quote:

Originally Posted by sajithamol

How we can stop alter statement of Table when the Table is referred by Stored Procedures and other Objects?

can you explain in detail

Change TSQl statement using case

Hello all, Thanks in advance to contributions.
I would like to be able to manipulate myTSQL in a stored procedure, based
on a value in a parameter, using the case statement.
I want to avoid Begin..End constructs
My code is such...
select field1,field2 etc etc
from Order o
inner join tablex oa on o.order_id = oa.order_id
-- etc etc p to 8 joins
where
-- some predicate
-- then the bit I want to case
case @.MyParameter
when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
when 'DE_MANAGER' then (and o.reason = 'T3')
end
So, what I am trying to do is change the overall selection predicate based
on the
value of the @.MyParameter. I am trying to avoid wrapping the whole code
block up into seperate Begin..End constructs if possible as it will make the
SP very big and ugly.
Amy I trying to break SLQ rules here?
Cheers
Scotchy
eg.Scotchy wrote:

> So, what I am trying to do is change the overall selection predicate
> based on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole
> code block up into seperate Begin..End constructs if possible as it
> will make the SP very big and ugly.
> Amy I trying to break SLQ rules here?
That is indeed not possible. You have the same problem as a couple of
threads above, check "Stored procedure using dynamic WHERE statement"
for the offered solutions.
HTH,
Stijn Verrept.|||yep, I was clutching a straws and hoping :-)
cheers
"Stijn Verrept" wrote:

> Scotchy wrote:
>
> That is indeed not possible. You have the same problem as a couple of
> threads above, check "Stored procedure using dynamic WHERE statement"
> for the offered solutions.
> --
> HTH,
> Stijn Verrept.
>|||declare @.parm int
declare @.sql nvarchar(400)
set @.parm = 1
set @.sql = 'select * from table1 where Type IS NOT NULL and '
if ( @.parm = 1 )
set @.sql = @.sql + 'type = ''t1'''
else if ( @.parm = 2 )
set @.sql = @.sql + 'type = ''t2'''
else
print 'error'
exec (@.sql)
William Stacey [MVP]
"Scotchy" <Scotchy@.discussions.microsoft.com> wrote in message
news:5CB3CA78-0973-4ED8-B308-7B3028CBB8F2@.microsoft.com...
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make
> the
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||-- Couple of things: do you need to CASE? Can't you do something like this
?
USE Northwind
GO
DECLARE @.EmployeeID INT
SET @.EmployeeID = 2
-- Normal query
SELECT *
FROM orders
WHERE EmployeeID = @.EmployeeID
-- You can use CASE in queries. Think of them like a function which returns
a value, so you have to put it on one side of an equals:
SELECT *
FROM orders
WHERE CustomerID =
CASE @.EmployeeID
WHEN 1 THEN 'ERNSH'
WHEN 2 THEN 'BLONP'
ELSE ''
END
-- Or multiple criteria. You could even nest your case statements.
SELECT *
FROM orders
WHERE CustomerID =
CASE
WHEN @.EmployeeID = 1 AND OrderDate < '19970101' THEN 'ERNSH'
WHEN @.EmployeeID = 2 AND OrderDate < '19970101' THEN 'BLONP'
ELSE ''
END
-- Let me know how you get on.
--
--
-- Damien
"Scotchy" wrote:

> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make t
he
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||On Tue, 20 Dec 2005 18:07:01 -0800, Scotchy wrote:

>Hello all, Thanks in advance to contributions.
>I would like to be able to manipulate myTSQL in a stored procedure, based
>on a value in a parameter, using the case statement.
>I want to avoid Begin..End constructs
>My code is such...
>select field1,field2 etc etc
>from Order o
>inner join tablex oa on o.order_id = oa.order_id
>-- etc etc p to 8 joins
>where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
>So, what I am trying to do is change the overall selection predicate based
>on the
>value of the @.MyParameter. I am trying to avoid wrapping the whole code
>block up into seperate Begin..End constructs if possible as it will make th
e
>SP very big and ugly.
>Amy I trying to break SLQ rules here?
Hi Scotchy,
CASE is an expression, not a statement. Check the examples in Books
Online to get a feeling for what you can accomplish with CASE. And never
think that CASE in SQL Server is similar to CASE in languages such as C.
For your situation, a combination of OR and AND works better than a
CASE:
WHERE ...
AND ( (@.MyParameter = 'IS_MANAGER' AND o.reason IN ('T1', 'T2') )
OR (@.MyParameter = 'DE_MANAGER' AND o.reason = 'T3' ))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Change the table structure

Hi...

I execute select statement on three tables and i get the following table:

ID

Value

Name

1

10

color

2

20

color

3

30

color

4

40

color

and, from this table i want to create the following table:

ID

color

1

10

2

20

3

30

4

40

mean, the value color become the header title of the column.

How can i do that?

thank you...

But you use a GridView or what to show the table ?

If you use a GridView you can set the Visible attribute of a BoundField at runtime.

|||

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

|||

may215:

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

Are you going to insert values in the second table, or its value will be same as the first table but columns' names are diffrents?

If you are not going to insert values in the second table, then I suggest to create a view that get its data from the first table.

1CREATE VIEW MyViewForTheSecondTable23AS45SELECT [ID], [VALUE]AS'Color'67FROM MyFirstTable8910

Now, you can do the SELECT on the view like:

1SELECT *FROM MyViewForTheSecondTable

The returned result will be same as in the first table but columns name will be ID and Color insted of ID and Value.

Good luck.

|||

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

|||

may215:

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....i

I cheked the example I wrote .. it is working fine.

Here is a complete senario that worked on my machine and it should work on your machine as well:

1-- create sample database2create database MyTestDB3go45-- use the sample database6use MyTestDB7go89-- create new table for testing10CREATE TABLE MyTable11(12[ID]INT,13[VALUE]CHAR(10)14)15GO1617-- insert two records in the testing table18INSERT INTO MyTableSELECT 4,'red'19INSERT INTO MyTableSELECT 5,'black'202122-- create a view23CREATE VIEW MyViewForTheSecondTable2425AS2627 SELECT [ID], [VALUE]AS'COLOR'2829FROM MyTable303132-- select data from the created view33SELECT *FROM MyViewForTheSecondTable

Please let me know if it did not work with you for any reason.

may215:

in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

First of all, please make sure those columns are exists in the table and they were typed correctly.

Then try this:

1SELECT prop.PhonePropertyValue, prop.PhonePropertyNameAS'color'23FROM dbo.TblPhoneType pt4INNERJOIN5 dbo.TblPropertyForPhoneType prop6ON pt.PhoneTypeID = prop.PhoneTypeID7INNERJOIN8 dbo.TblPhoneProperty pp9ON pp.PhonePropertyID = prop.PhonePropertyID10WHERE pp.PhonePropertyID ='34'11

Good luck.

Change the Column Heading on select statement

I have a problem I don't know if there's a way to solve it.

I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.

SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID

thanks.RE: I have a problem I don't know if there's a way to solve it.
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID thanks.

Q1 [Is it possible to have a result set heading e.g. , 'DRIVER NAME', conditionally appear as something else e.g. , 'HELPER NAME', depending on a passed parameter being either 'D' or 'H'?]
A1 Yes. Assign the passed parameter to a variable in the stored procedure; use a Case statement to evaluate and appropriatly use either 'DRIVER NAME' or 'HELPER NAME' in your result header column.

the logic [psudocode] amounts to something like:
Case @.vPassedParameter = 'H'
Then @.vColumnHeader = 'HELPER NAME'
Case @.vPassedParameter = 'D'
Then @.vColumnHeader = 'DRIVER NAME'

change small letter to capitals letters

I need to change over 300 records in a table field that have small letters to
capitals letters. Which sql command statement would change this?
Thanks in advance
Dave
Example:
update table1
set c1 = upper(c1)
AMB
"DCR" wrote:

> I need to change over 300 records in a table field that have small letters to
> capitals letters. Which sql command statement would change this?
> Thanks in advance
> Dave

change small letter to capitals letters

I need to change over 300 records in a table field that have small letters t
o
capitals letters. Which sql command statement would change this?
Thanks in advance
DaveExample:
update table1
set c1 = upper(c1)
AMB
"DCR" wrote:

> I need to change over 300 records in a table field that have small letters
to
> capitals letters. Which sql command statement would change this?
> Thanks in advance
> Dave

change small letter to capitals letters

I need to change over 300 records in a table field that have small letters to
capitals letters. Which sql command statement would change this?
Thanks in advance
DaveExample:
update table1
set c1 = upper(c1)
AMB
"DCR" wrote:
> I need to change over 300 records in a table field that have small letters to
> capitals letters. Which sql command statement would change this?
> Thanks in advance
> Dave

Change NULL values to default in SELECT statement

I have a stored procedure with a SELECT statement, that retrieves 1 row.

SELECT name FROM tblNames WHERE nameID = "1"

I want all the NULL values in that row to be change in some default values.

How do I do this?

You can use the IsNull Statement

SELECT isNull(name,'someDefaultValue') FROM tblNames WHERE nameID = "1"

|||

Perfect, perfect, this works great, so simple, but effective.

Now I can go celebrating Christmas.....thx.

|||

Next problem:

I call the stored procedure in the Data Access Layer via a typed dataset.

My stored procedure SELECTs 11 columns. The execute test in the stored procedure works perfect.

But when I preview the data in the DAL.xsd, I see 22 columns, with the first 11 columns having the right name, but no values and the second 11 columns having wrong names, but the right values.

What goes wrong?

|||

You will have to rename the derived columns in your select statement...

SELECT ISNULL(FirstName,'No Name') [FirstName], ISNULL(LastName,'No Last Name') [LastName]

It also sounds like you may be using SELECT *, remove the star so you don't end up with duplicate column names

change null row of roll up query

hi all

i m using rollup statement in my query. it gives me total field with null caption.

is it possible that i can change this null field with caption "total"

thanks & regards

ganesh

If I understand you question you could use following statement:

<your total field expression> as "Total"

Please, show your query

|||

Yes you can.. use the reference as the bellow query

Code Snippet

Declare @.data Table
(
Region Varchar(100),
Country Varchar(100),
Sales float
);

Insert Into @.data Values ('EMEA', 'France', 100);
Insert Into @.data Values ('EMEA', 'France', 200);
Insert Into @.data Values ('EMEA', 'Germany', 56);
Insert Into @.data Values ('EMEA', 'Germany', 12);
Insert Into @.data Values ('EMEA', 'UK', 1256);
Insert Into @.data Values ('EMEA', 'UK', 1212);
Insert Into @.data Values ('APJ', 'Japan', 130);
Insert Into @.data Values ('APJ', 'Japan', 32);
Insert Into @.data Values ('APJ', 'China', 256);
Insert Into @.data Values ('APJ', 'China', 212);
Insert Into @.data Values ('APJ', 'India', 23);
Insert Into @.data Values ('APJ', 'India', 232);
Insert Into @.data Values ('America', 'US', 23);
Insert Into @.data Values ('America', 'US', 23432);
Insert Into @.data Values ('America', 'Canada', 223256);
Insert Into @.data Values ('America', 'Canada', 2122);
Insert Into @.data Values ('America', 'Brazil', 23232);
Insert Into @.data Values ('America', 'Brazil', 223232);

Select
isnull(Case When Country is NULL Then Region + ' Total' Else Region End,'Grand Total') Region,
isnull(Country,'') Country,
Sum(Sales)
From
@.Data
Group By
Region,Country With Rollup