Change Increment Value for existing Identity Column

Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?

I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)

but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .

Thanks,
NataliaYou have to drop and recreate the table.sql

Change Identity value

I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:

> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:

>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>

change HH from 24 hrs to 12 hrs

I have a time value that I want to display in 12 hours instead of 24
hours(military time)
DATEPART(HH,@.ACTTIME)
How do I get 1 - 12 and instead of 0-23?Client side? Wouldn't it be nicer to let the end user choose how he/she
prefers to read the time?
Otherwise lookup the style parameter options on the CONVERT function.
David Portas
SQL Server MVP
--|||I hope the following helps you.
Declare @.ActTime DateTime
Set @.ActTime = '01/01/2005 11:15 PM'
Select Case
When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
ELSE DatePart(hh,@.ACTTIME) - 12
End
"LP" wrote:

> I have a time value that I want to display in 12 hours instead of 24
> hours(military time)
> DATEPART(HH,@.ACTTIME)
> How do I get 1 - 12 and instead of 0-23?
>|||It is called ISO-8601 and not "Military Time" and it is used in a ton
of ISO standards. Why do you wish to destroy the portability of your
data and violate standards?
The basic priniciple of a tiered architecture is that the applications
do the display work, not the database.|||No server side. It's for a static dimension table. I don't see the utility
in it but the client request pays the bills. I have most of it compete.
I'll include it tomorrow.
Thanks
"David Portas" wrote:

> Client side? Wouldn't it be nicer to let the end user choose how he/she
> prefers to read the time?
> Otherwise lookup the style parameter options on the CONVERT function.
> --
> David Portas
> SQL Server MVP
> --
>
>|||No destruction in mind. Simply fulfilling a client request for a static
dimension table. The utility of this field is a bit in question...but it
pays the bills.
Thanks
"--CELKO--" wrote:

> It is called ISO-8601 and not "Military Time" and it is used in a ton
> of ISO standards. Why do you wish to destroy the portability of your
> data and violate standards?
> The basic priniciple of a tiered architecture is that the applications
> do the display work, not the database.
>|||Thanks for getting back with me. I found another way around the issue and
I'll post it tomotrrow because it might be helpfull for others.
Thanks again.
LP
"satheeshks" wrote:
> I hope the following helps you.
> Declare @.ActTime DateTime
> Set @.ActTime = '01/01/2005 11:15 PM'
> Select Case
> When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
> ELSE DatePart(hh,@.ACTTIME) - 12
> End
> "LP" wrote:
>

Change gridline color at specific value

Has anyone written a conditional statement to change a gridline value at a
specific value?
=iif(?=90, "Red","Silver")
Any advise is apreciated.
Thanks,
Scott A. BakerSorry, setting the color of individual gridlines is not supported.
Are you trying to set a specific horizontal gridline (in a column chart or
line chart) or a vertical gridline (in a bar chart)?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
> Has anyone written a conditional statement to change a gridline value at
> a specific value?
> =iif(?=90, "Red","Silver")
> Any advise is apreciated.
> Thanks,
> Scott A. Baker
>|||I am attempting to set a horizontal gridline for a stacked column chart. I
noticed that where you set the color for grid there is also an expression
button. I assumed you would be able to change the color depending on the
value.
This becomes helpful in setting a static goal line. In my example
=iif(?=90, "Red","Silver") , the 90 represents the goal.
Thank you for your response.
Scott
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> Sorry, setting the color of individual gridlines is not supported.
> Are you trying to set a specific horizontal gridline (in a column chart or
> line chart) or a vertical gridline (in a bar chart)?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value at
>> a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>|||In that case, you could just add another data series and plot that series as
line. Set the data value expression to =90 and set the BorderColor property
accordingly.
Note: you don't use the gridlines to show your static goal line, but rather
you add a "fake" data series with a constant value to show the goal line.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>I am attempting to set a horizontal gridline for a stacked column chart. I
>noticed that where you set the color for grid there is also an expression
>button. I assumed you would be able to change the color depending on the
>value.
> This becomes helpful in setting a static goal line. In my example
> =iif(?=90, "Red","Silver") , the 90 represents the goal.
> Thank you for your response.
> Scott
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> Sorry, setting the color of individual gridlines is not supported.
>> Are you trying to set a specific horizontal gridline (in a column chart
>> or line chart) or a vertical gridline (in a bar chart)?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value at
>> a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>>
>|||Robert,
Again thank you for your prompt response. I have added a static line to the
charts that cover multiple time periods. However, I also show a period
summary that only has one x axis value. In this case, a static line does
not work because there is not more than one data point to connect to.
Any other suggestions?
Thanks,
Scott
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:usZ5QPS1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> In that case, you could just add another data series and plot that series
> as line. Set the data value expression to =90 and set the BorderColor
> property accordingly.
> Note: you don't use the gridlines to show your static goal line, but
> rather you add a "fake" data series with a constant value to show the goal
> line.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>>I am attempting to set a horizontal gridline for a stacked column chart.
>>I noticed that where you set the color for grid there is also an
>>expression button. I assumed you would be able to change the color
>>depending on the value.
>> This becomes helpful in setting a static goal line. In my example
>> =iif(?=90, "Red","Silver") , the 90 represents the goal.
>> Thank you for your response.
>> Scott
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> Sorry, setting the color of individual gridlines is not supported.
>> Are you trying to set a specific horizontal gridline (in a column chart
>> or line chart) or a vertical gridline (in a bar chart)?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value
>> at a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>>
>>
>|||If the chart has only one data point at all, then my suggestion won't work.
But here is another idea for the summary scenario with only one datapoint:
turn off margins on the x-axis and use a column chart to draw a "zone"
instead of a target line. Since the margins are turned off, it would like if
the chart had then only two zones and your actual datapoint summary value
either is in the "good" or the "bad" zone.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
news:up5Jr7W1FHA.2428@.tk2msftngp13.phx.gbl...
> Robert,
> Again thank you for your prompt response. I have added a static line to
> the charts that cover multiple time periods. However, I also show a
> period summary that only has one x axis value. In this case, a static
> line does not work because there is not more than one data point to
> connect to.
> Any other suggestions?
> Thanks,
> Scott
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:usZ5QPS1FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> In that case, you could just add another data series and plot that series
>> as line. Set the data value expression to =90 and set the BorderColor
>> property accordingly.
>> Note: you don't use the gridlines to show your static goal line, but
>> rather you add a "fake" data series with a constant value to show the
>> goal line.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>>I am attempting to set a horizontal gridline for a stacked column chart.
>>I noticed that where you set the color for grid there is also an
>>expression button. I assumed you would be able to change the color
>>depending on the value.
>> This becomes helpful in setting a static goal line. In my example
>> =iif(?=90, "Red","Silver") , the 90 represents the goal.
>> Thank you for your response.
>> Scott
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> Sorry, setting the color of individual gridlines is not supported.
>> Are you trying to set a specific horizontal gridline (in a column chart
>> or line chart) or a vertical gridline (in a bar chart)?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value
>> at a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>>
>>
>>
>|||Set Gridlines = null,Cellspacing = 1,BorderColor = 'Red' , and then you will
find the color of gridlines has been changed!
"Robert Bruckner [MSFT]" wrote:
> If the chart has only one data point at all, then my suggestion won't work.
> But here is another idea for the summary scenario with only one datapoint:
> turn off margins on the x-axis and use a column chart to draw a "zone"
> instead of a target line. Since the margins are turned off, it would like if
> the chart had then only two zones and your actual datapoint summary value
> either is in the "good" or the "bad" zone.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> news:up5Jr7W1FHA.2428@.tk2msftngp13.phx.gbl...
> > Robert,
> >
> > Again thank you for your prompt response. I have added a static line to
> > the charts that cover multiple time periods. However, I also show a
> > period summary that only has one x axis value. In this case, a static
> > line does not work because there is not more than one data point to
> > connect to.
> >
> > Any other suggestions?
> >
> > Thanks,
> >
> > Scott
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:usZ5QPS1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> >> In that case, you could just add another data series and plot that series
> >> as line. Set the data value expression to =90 and set the BorderColor
> >> property accordingly.
> >>
> >> Note: you don't use the gridlines to show your static goal line, but
> >> rather you add a "fake" data series with a constant value to show the
> >> goal line.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> >> news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> >>I am attempting to set a horizontal gridline for a stacked column chart.
> >>I noticed that where you set the color for grid there is also an
> >>expression button. I assumed you would be able to change the color
> >>depending on the value.
> >>
> >> This becomes helpful in setting a static goal line. In my example
> >> =iif(?=90, "Red","Silver") , the 90 represents the goal.
> >>
> >> Thank you for your response.
> >>
> >> Scott
> >>
> >>
> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> >> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> >> Sorry, setting the color of individual gridlines is not supported.
> >> Are you trying to set a specific horizontal gridline (in a column chart
> >> or line chart) or a vertical gridline (in a bar chart)?
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> >> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
> >> Has anyone written a conditional statement to change a gridline value
> >> at a specific value?
> >>
> >> =iif(?=90, "Red","Silver")
> >>
> >> Any advise is apreciated.
> >>
> >> Thanks,
> >>
> >> Scott A. Baker
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

change default value XACT_ABORT at instacle level

Hi,

How can I changed default value for XACT_ABORT at instacle level?

Thanks.

Faheem Ansari

You may use sp_configure to set up. Follow these steps:

exec sp_configure 'show advanced options',1

go

RECONFIGURE'

gp

exec sp_configure 'user options',16384

go

RECONFIGURE

go

Note :

1. 16384 is the option ID for XACT_ABORT (0x4000)

2. You may have already configured some value using sp_configure before, then please make sure you are adding them up. For example, you already set the user option to be 1024 (ANSI_NULL_DFLT_ON), then you need to use

exec sp_configure 'user options',17408

Hope this helps.

Ping

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Change default value

I need to change current default values from df_curr_user to df_login_user
for about 600 differrent fileds in different tables. Should I do it by using
sp_unbindefault and sp_bindefault for each column? How can I do it
programmatically?
Thanks in advance for any help!
PerayuAre you talking about changing the default constraint name?
AMB
"Perayu" wrote:

> I need to change current default values from df_curr_user to df_login_user
> for about 600 differrent fileds in different tables. Should I do it by usi
ng
> sp_unbindefault and sp_bindefault for each column? How can I do it
> programmatically?
> Thanks in advance for any help!
>
> Perayu
>
>|||Not only the default constraint name. I want to replace it to a new one.
Actually, I can't change the name or update current used df_curr_user
because it is bound to columns. So, I defined a new one as df_login_user and
try to replace it. But have no idea how to do it programmatically.
Thanks.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
> Are you talking about changing the default constraint name?
>
> AMB
> "Perayu" wrote:
>|||See if this helps.
use northwind
go
create default df_current_user as current_user
go
create default df_login_user as suser_sname()
go
create table t1 (
c1 nvarchar(256)
)
go
create table t2 (
c1 nvarchar(256)
)
go
create table t3 (
c1 nvarchar(256)
)
go
create table t4 (
c1 nvarchar(256)
)
go
exec sp_bindefault 'df_current_user', 't1.c1'
exec sp_bindefault 'df_current_user', 't2.c1'
exec sp_bindefault 'df_current_user', 't3.c1'
exec sp_bindefault 'df_current_user', 't4.c1'
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as %'
go
declare @.sql nvarchar(4000)
declare c cursor local fast_forward
for
select
'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as%'
open c
while 1 = 1
begin
fetch next from c into @.sql
if @.@.error != 0 or @.@.fetch_status != 0 break
exec sp_executesql @.sql
set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
exec sp_executesql @.sql
end
close c
deallocate c
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_login_user as %'
go
drop table t1, t2, t3, t4
go
drop default df_current_user, df_login_user
go
AMB
"Perayu" wrote:

> Not only the default constraint name. I want to replace it to a new one.
> Actually, I can't change the name or update current used df_curr_user
> because it is bound to columns. So, I defined a new one as df_login_user a
nd
> try to replace it. But have no idea how to do it programmatically.
> Thanks.
> Perayu
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
>
>|||It works like a charm!
Thank you so much.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E94A4876-0EF7-470B-8577-BF702B21B53F@.microsoft.com...
> See if this helps.
> use northwind
> go
> create default df_current_user as current_user
> go
> create default df_login_user as suser_sname()
> go
> create table t1 (
> c1 nvarchar(256)
> )
> go
> create table t2 (
> c1 nvarchar(256)
> )
> go
> create table t3 (
> c1 nvarchar(256)
> )
> go
> create table t4 (
> c1 nvarchar(256)
> )
> go
> exec sp_bindefault 'df_current_user', 't1.c1'
> exec sp_bindefault 'df_current_user', 't2.c1'
> exec sp_bindefault 'df_current_user', 't3.c1'
> exec sp_bindefault 'df_current_user', 't4.c1'
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as %'
> go
> declare @.sql nvarchar(4000)
> declare c cursor local fast_forward
> for
> select
> 'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as%'
> open c
> while 1 = 1
> begin
> fetch next from c into @.sql
> if @.@.error != 0 or @.@.fetch_status != 0 break
> exec sp_executesql @.sql
> set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
> exec sp_executesql @.sql
> end
> close c
> deallocate c
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_login_user as %'
> go
> drop table t1, t2, t3, t4
> go
> drop default df_current_user, df_login_user
> go
>
> AMB
> "Perayu" wrote:
>|||btw, does anyone know a 'clean' way to get the default value of a
default-bound column ?
for example:
create table t1(col varchar(10))
create default s as 'none'
sp_bindefault s, 't1.col'
I can find this value from information_schema.columns, like this:
select column_default
from information_schema.columns
where table_name = 't1'
and it returns:
create default s as 'none'
however, I believe extracting the string after the 'as' to get default
value isn't the best way. Can't imagine that sql server performs
inserts and calculates a default value on-the-flight
thanks,
Tam|||Sometime, the default value is not always a constant and must be done
on-the-flight. Like what I have is using SUSER_SNAME(), which will depends
on the login name.
Perayu
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125429897.170823.60630@.g43g2000cwa.googlegroups.com...
> btw, does anyone know a 'clean' way to get the default value of a
> default-bound column ?
> for example:
> create table t1(col varchar(10))
> create default s as 'none'
> sp_bindefault s, 't1.col'
> I can find this value from information_schema.columns, like this:
> select column_default
> from information_schema.columns
> where table_name = 't1'
> and it returns:
> create default s as 'none'
> however, I believe extracting the string after the 'as' to get default
> value isn't the best way. Can't imagine that sql server performs
> inserts and calculates a default value on-the-flight
> thanks,
> Tam
>|||Perayu - I agree, but there're times that default values are constant,
would it do on the flight anyway ?sql

Change default value

Hi,
How can i change default value in sql server through SQL script if i haven't
given default constraint name and using system default generated constraintg
name ?
I have 50 tables to change the default value of a particular field...pls
advice what to do...i don't want to open every table in design mode and
change the default value...pls suggest some script to do it.
With regards,
Gurmeet SinghThis scenario is exactly why you should name your constraint in the first
place...
If you want to automate this, you can use the system tables to get the
constraint name and use that name in your ALTER TABLE ... DROP CONSTRAINT
... statements. Note that SQL Server doesn't accept a variable for table or
constraint name, so you have do use dynamic SQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||Hi,
Refer books online for the below commands,
Alter table drop constraint <Const_name>
Alter table add constraint
Thanks
Hari
MCDBA
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||You can remove the default from a column using the following script:
-- remove the default
DECLARE @.constraint_name SYSNAME
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.name = '<table name>'
AND c_obj.xtype = 'D'
AND cols.[name] = '<column name>')
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
You can adjust this to use multiple tables and to add the new constraint as
well.
Jacco Schalkwijk
SQL Server MVP
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>

change default value

How do you change a default value for a column in tsql?
cheers
Chalie.drop existing default constraint using statement.
ALTER TABLE <table>
DROP CONSTRAINT <default constraint name>
Recreate the new default constraint using :
ALTER TABLE <table> ADD CONSTRAINT
<constraint name> DEFAULT 'x' FOR <column name>
-Vishal
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> How do you change a default value for a column in tsql?
> cheers
> Chalie.
>|||What about something like this
CREATE TABLE DefVals(col1 int CONSTRAINT def_col1 DEFAULT 1, col2 int)
GO
INSERT DefVals(col2) VALUES(1)
GO
SELECT * FROM DefVals
GO
ALTER TABLE DefVals DROP CONSTRAINT def_col1
GO
ALTER TABLE DefVals ADD CONSTRAINT def_col1 DEFAULT 4 FOR col1
GO
INSERT DefVals(col2) VALUES(1)
GO
SELECT * FROM DefVals
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Hello Charlie !
The Default Value is a constraint that you can change:
Sop you have to DROP and ADD the new constraint.
HTH, Jens Süßmeyer.|||Hi,
Thanks for this, but how do I find out what my constraint name is?
Charlie
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:uk9GvVibDHA.1580@.tk2msftngp13.phx.gbl...
> drop existing default constraint using statement.
> ALTER TABLE <table>
> DROP CONSTRAINT <default constraint name>
> Recreate the new default constraint using :
> ALTER TABLE <table> ADD CONSTRAINT
> <constraint name> DEFAULT 'x' FOR <column name>
>
> --
> -Vishal
> "charlie B" <Charlie.remove@.freeuk.com> wrote in message
> news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> > How do you change a default value for a column in tsql?
> >
> > cheers
> > Chalie.
> >
> >
>|||Try
Exec sp_helpconstraint 'TABLE NAME'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Charlie,
EXEC sp_helpconstraint <tablename>
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:qRI3b.453$b82.171440@.newsfep1-win.server.ntli.net...
> Hi,
> Thanks for this, but how do I find out what my constraint name is?
> Charlie
> "Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
> news:uk9GvVibDHA.1580@.tk2msftngp13.phx.gbl...
> > drop existing default constraint using statement.
> >
> > ALTER TABLE <table>
> > DROP CONSTRAINT <default constraint name>
> >
> > Recreate the new default constraint using :
> > ALTER TABLE <table> ADD CONSTRAINT
> > <constraint name> DEFAULT 'x' FOR <column name>
> >
> >
> > --
> > -Vishal
> >
> > "charlie B" <Charlie.remove@.freeuk.com> wrote in message
> > news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> > > How do you change a default value for a column in tsql?
> > >
> > > cheers
> > > Chalie.
> > >
> > >
> >
> >
>

Change default input parameter drop down list size

I am working on a report which uses a multi value input parameter. The value
set is depend on the other input parameter, so the size and value are
different when the value of the other parameter has changed. The problem is
sometime it works perfectly, but the other time when there are only two
value, the drop down list will show "Select All" and first item and gives me
a scroll bar. This is very annoying. Anyone know how to make the drop down
list to disply more desirous number of items. In addition, is there a way to
control the parameter text box length?I have the same problem but even a worse scenario. My list is filtered by
another and can end up with only one entry. While two entries at least lets
you scroll through the list ('cause the scroll buttons are shown), a list of
only one value doesn't even show the scroll buttons.
I also need a way to allow/force a list this short to be at least long
enough to show the scrollbars.
Jon

Change Date Format of Field value

What is the best way of converting datatime field value 29/03/2005 08:58:27 to 29/03/2005.

I only want to remove Time from date and I am using Sql Server 2000.

Thanks

Arvind

Look at the cast and convert functions

I use cast(convert(varchar(10), getdate(), 101) as datetime)

sql

Change Color of Textbox Depending on Value

He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?

Any Idea's ?|||

if your statement:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

You are evaluting the First Fields value "Literally" if you change it to

=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.

|||

Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!

=Iif(Fields!Days.Value is Null, "AliceBlue", "White")

|||

I tried this code for my returntime field: Do I need to do a data conversion?

=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.

Preview complete -- 0 errors, 1 warnings

|||

Humm,

I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.

Change Color of Textbox Depending on Value

I have the following:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") < 10.0, "Yellow", "Black")

Doesnt appear to be changing color to the numbers, actually its just putting the work black in all the cells in the report, the nubmer are % numbers, like 10.53% of 3.45%, and if its great than 10.00 % I want the textbox to show up yellow, if its >10% i want it to show up normal. Any help would be greatly appreciated.

Hi Duane,

You have the right idea, but the comparison may be not what you are looking for. I think a link to RS Expressions will help you out a good bit here. The link should explain better then I can how to use different expresssions.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

|||

Hi Duane! Are you putting your expression in the BackgroundColor property of the textbox. From what you described, it sounds like you are putting it in the value property.

Jennifer

|||

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

|||

I was placing it in the wrong place @. first, now I realized it, and am getting closer if you read my previous post.

Thanks

|||

Duane Haas wrote:

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

|||

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

|||

Duane,

Could it be summing this value for all rows in dataset and since the total value is greater than 10, all detail rows will be yellow? Maybe remove the sum function and only evaluate the value itself for each row.

T

|||

I tried that and than this is what I get:

h:\sqlreports\snaps\Report1.rdl The background color expression for the textbox ‘textbox2’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling.

h:\sqlreports\snaps\Report1.rdl The background color expression for the textbox ‘textbox2’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Not sure where to go from here, this is a matrix style report

|||

Sorry Duane, I have yet to build a matrix style report, so I have no more suggestions..

T

|||anybody have any ideas?

Change BackgroundColor for subtotals in Matrix

Hi,
I want to create a Matrix like this:
Group 1 Group 2 Value
A 1 2
2 3
Subtotal 5
B 1 8
2 10
Subtotal 18
...
I want the subtotals to have another BackgroundColor (grey) to differentiate
between the detail values and the subtotal value.
Please help !
ThomasIf you click on the little green triangle of the matrix heading cells, you
will notice that the VS properties windows shows properties that apply
specifically for subtotals. You can then set the background color on the
subtotal to grey.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
> Hi,
> I want to create a Matrix like this:
> Group 1 Group 2 Value
> A 1 2
> 2 3
> Subtotal 5
> B 1 8
> 2 10
> Subtotal 18
> ...
> I want the subtotals to have another BackgroundColor (grey) to
> differentiate
> between the detail values and the subtotal value.
> Please help !
> Thomas|||It´s that easy ! Thank you very much.
I used the Inscope-Function in the detail-cell to change colors and
Borderstyle for the subtotal, but this is much better.
Thomas
"Robert Bruckner [MSFT]" wrote:
> If you click on the little green triangle of the matrix heading cells, you
> will notice that the VS properties windows shows properties that apply
> specifically for subtotals. You can then set the background color on the
> subtotal to grey.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
> > Hi,
> > I want to create a Matrix like this:
> >
> > Group 1 Group 2 Value
> > A 1 2
> > 2 3
> > Subtotal 5
> > B 1 8
> > 2 10
> > Subtotal 18
> > ...
> >
> > I want the subtotals to have another BackgroundColor (grey) to
> > differentiate
> > between the detail values and the subtotal value.
> >
> > Please help !
> >
> > Thomas
>
>|||Using InScope() approach is useful if you the style should depend on the
cell's value. If you want formatting for subtotals in general, it is easier
to use the properties on the subtotal heading.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:7FB87177-A383-4356-A67A-6D5332B1AEBA@.microsoft.com...
> It´s that easy ! Thank you very much.
> I used the Inscope-Function in the detail-cell to change colors and
> Borderstyle for the subtotal, but this is much better.
> Thomas
> "Robert Bruckner [MSFT]" wrote:
>> If you click on the little green triangle of the matrix heading cells,
>> you
>> will notice that the VS properties windows shows properties that apply
>> specifically for subtotals. You can then set the background color on the
>> subtotal to grey.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
>> news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
>> > Hi,
>> > I want to create a Matrix like this:
>> >
>> > Group 1 Group 2 Value
>> > A 1 2
>> > 2 3
>> > Subtotal 5
>> > B 1 8
>> > 2 10
>> > Subtotal 18
>> > ...
>> >
>> > I want the subtotals to have another BackgroundColor (grey) to
>> > differentiate
>> > between the detail values and the subtotal value.
>> >
>> > Please help !
>> >
>> > Thomas
>>

Change attribute value in stored procedure

Can someone show me the syntax (if it is possible) to modify an attribute value in a stored proc? For example if I have an element that is being passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to change the 2 to something else (like a 3) depending on a condition? Or do I have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to change the value? Will someone
please let me know? Many Thanks!
There's no XML manipulation functionality built into SQL Server 2000 (other
than shredding the XML into a table, performing your update, and retrieving
it back out as XML again using a FOR XML query - definitely not an efficient
approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
better performing this kind of logic in the client application before
submitting it to the stored procedure (apply a style sheet or use an XML API
like the DOM or the XmlDocument in .NET).
The next release of SQL Server includes some extensions to XQuery that allow
you to modify data within an XML value, which is great if you can wait until
then, but probably of no practical use to you at the moment - sorry!
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
Can someone show me the syntax (if it is possible) to modify an attribute
value in a stored proc? For example if I have an element that is being
passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to
change the 2 to something else (like a 3) depending on a condition? Or do I
have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
change the value? Will someone please let me know? Many Thanks!
|||Thanks Graeme! That is exactly what I was looking for.
"Graeme Malcolm" wrote:

> There's no XML manipulation functionality built into SQL Server 2000 (other
> than shredding the XML into a table, performing your update, and retrieving
> it back out as XML again using a FOR XML query - definitely not an efficient
> approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
> better performing this kind of logic in the client application before
> submitting it to the stored procedure (apply a style sheet or use an XML API
> like the DOM or the XmlDocument in .NET).
> The next release of SQL Server includes some extensions to XQuery that allow
> you to modify data within an XML value, which is great if you can wait until
> then, but probably of no practical use to you at the moment - sorry!
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
> news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
> Can someone show me the syntax (if it is possible) to modify an attribute
> value in a stored proc? For example if I have an element that is being
> passed in as text like
> <CustomerName id="2" /> Is there SQLXML functionality that I can use to
> change the 2 to something else (like a 3) depending on a condition? Or do I
> have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
> change the value? Will someone please let me know? Many Thanks!
>
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?
You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?
|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Chang default file name when exporting

When exporting a report, the file name is defaulted to the name of the RDL
file for the report. Is there any way to change that to default to a value
in a textbox in the report?
StephanieOn Jul 25, 3:56 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> When exporting a report, the file name is defaulted to the name of the RDL
> file for the report. Is there any way to change that to default to a value
> in a textbox in the report?
> Stephanie
As far as I know, there is not. One way to do this is to create a
custom ASP.NET application that uses a report viewer control and
intercepts the export functionality of the control and changes the
name of the file to save. Sorry that I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECTNONEMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ONCOLUMNS, NONEMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ONCOLUMNSFROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ONCOLUMNSFROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ONCOLUMNSFROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ONCOLUMNSFROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!