Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scripts/article.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||Like most people you need proper database change control
then these issues would disappear. It seems that people
are all to quick to think that their processes are OK
until the inevitable happens and they are in the _hit!
Smart people use DB Ghost.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL
Server
>--Original Message--
>MS SQL Server 2000 does not allow columns with Default
value constraints to
>be dropped. We are looking for an easy way to drop such
columns. The problem
>occurs because the DEFAULT clause actually creates a
constraint on the
>table. This name of this constraint is chosen by SQL
Server and will differ
>from database to database.
>The seemingly obvious solution:
>exec sp_unbindefault 'table.column'
>does not work but gives us the following error:
>Server: Msg 15049, Level 11, State 1, Procedure
sp_unbindefault, Line 98
>Cannot unbind from 'table.column'. Use ALTER TABLE DROP
CONSTRAINT
>OK, ALTER TABLE DROP CONSTRAINT would certainly work,
but we don't know the
>name of the contraint! Makes it kind of hard for us to
script this to work
>for multiple databases.
>The only working solution we have seems very cumbersome.
(I guess we could
>convert it to a stored procedure.)
>DECLARE @.STR VARCHAR(100)
>SET @.STR = (
>SELECT NAME
>FROM SYSOBJECTS SO
>JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
>WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
>AND SO.XTYPE = 'D' AND SC.COLID =>(SELECT COLID FROM SYSCOLUMNS
>WHERE ID = OBJECT_ID('<table>')
>AND NAME = '<column>')
>)
>SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR
EXEC (@.STR)
>The other solution would be to stop using the DEFAULT
clause on the column
>definition, but rather to create a named constraint. But
completely
>eschewing the DEFAULT clause will drive the complexity
of our database
>definition scripts way up. We use DEFAULT values rather
heavily and I don't
>want to have to specify an ALTER TABLE ADD CONSTRAINT
for every column with
>a default value! I don't like this option.
>There's got to be a better way (I hope). I can't believe
it has to be this
>difficult to drop a column simply because it has a
default value specified
>for it. Is there an easy way to deal with this?
>Thanks for your help!
>Joe Geretz
>
>.
>sql

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe Geretz
Check this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scrip...le.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>
|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scri...cle.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)
did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear examnotes <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>sql

Easy Way Determine Database File Size Another

What is an easy way to determine the numeric value for database file size,
this database exist on another server using T-SQL?
Thank You,If you have a linked server, you can try
EXEC linked_server.database_name.dbo.sp_helpfile
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D329E712-EB1F-4418-BCD1-EA93FCB95704@.microsoft.com...
> What is an easy way to determine the numeric value for database file size,
> this database exist on another server using T-SQL?
> Thank You,
>

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy question - changing Visibility of textbox

I'm trying to affect the visibility of a text box based upon a field value
returned in my dataset. The field is a summed field, and I simply do NOT
want to show the field if it is 0, less than zero, or NULL.
What should my expression for the "Visbility" property look like?
TIA,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.comiif(Parameters!Fieldname.Value <= 0 OR Parameters!Fieldname.Value IS Nothing, True, False)
"G" wrote:
> I'm trying to affect the visibility of a text box based upon a field value
> returned in my dataset. The field is a summed field, and I simply do NOT
> want to show the field if it is 0, less than zero, or NULL.
> What should my expression for the "Visbility" property look like?
> TIA,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>
>|||IS Nothing
that is what was tripping me up, thanks comet.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"comet61" <comet61@.discussions.microsoft.com> wrote in message
news:CFF1A78C-9DE1-475E-9BB9-5D4D6F4EAE04@.microsoft.com...
> iif(Parameters!Fieldname.Value <= 0 OR Parameters!Fieldname.Value IS
Nothing, True, False)
> "G" wrote:
> > I'm trying to affect the visibility of a text box based upon a field
value
> > returned in my dataset. The field is a summed field, and I simply do NOT
> > want to show the field if it is 0, less than zero, or NULL.
> >
> > What should my expression for the "Visbility" property look like?
> >
> > TIA,
> >
> > --
> > Brian Grant
> > Senior Programmer
> > SI International
> > www.si-intl.com
> >
> >
> >

Easy question

How do i format a date which is 9/1/2004 as Sept 04

I have tried giving

Format(Fields!GrowthDates.Value,"Y")

but i just get Y and not dates.

Thanks

Regards,

Karen

= Month(Fields!GrowthDates.Value) & " " & Year(Fields!GrowthDates.Value)

Easy question, easy answer. You want to put this in the format expression.

|||

Date.Now.ToString("MMM yy")

|||

Greg,

When i use that expression for 08/01/2004 its give 08 2004 and not Aug 2004 ,,, how can i get it to show Aug 2004

|||

I've never been able to get this function to work, but you would use MonthName. It always returns strange data for me. Maybe someone else can clear this up...

= MonthName(Month(Fields!GrowthDates.Value)) & "-" & Year(Fields!GrowthDates.Value)

Instead of September, I get SepAe0ber 2006

For October, I get OcAober

November - Nove0ber

Strange, huh?

|||

Greg i got it to work

=MonthName(Month(Fields!GrowthDates.Value),true)& " " & Year(Fields!GrowthDates.Value)

True = Abbreviate.

so if my data 08/01/2004 its gonna display Aug 2004

Hope this helps

Regards,

Karen

|||

Why dont you try ="MMM yyyy" in format expression.

Priyank

|||

I tried that in the report but on my X axis instead of the date i get MMM yyy which is of new use.

|||

I get bad abbreviations with that. Some are good but some are still strange...

August - AuA.D.

October OcA

It's almost as if the definitions for the months on my installation of reporting services are wrong.

|||

Priyank Pandey wrote:

Why dont you try ="MMM yyyy" in format expression.

Priyank

That did work, note the case. Thanks!

|||

Its showing up correct on mine....

Regards

Karen

|||

Can you pls mark it as ans.

Thanks!

Easy One

Hi,
I need to format part of an expression as money. The expression is ="Total
Aged Balance : " & SUM(Fields!Invoice_Total.Value).
How do I do that?
Thanks
JerryOk...got it...FORMATCURRENCY.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23aV%23sWuvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I need to format part of an expression as money. The expression is
> ="Total Aged Balance : " & SUM(Fields!Invoice_Total.Value).
> How do I do that?
> Thanks
> Jerry
>

Easy Newbie reporting services question

I'd like to learn more about what I can put in expressions.
For example: Fields!<FieldName>.Value.Length
Is very useful. I found it on this user group but could not find it in
the documentation (online books). The expression painter does not seem
to be much help.
Where can I find more information on what I can put into expressions?
Thanks in advance :)Hi,
Search for "Using Expressions in Reporting Services" and
"Expression Examples in Reporting Services" this on gives you how to use it
with examples.
Hope you might have seen this. but this is very useful. Moreover you can get
the help from "Edit Expression" form. click on any function. you can get the
help.
Amarnath
"mzwilli" wrote:
> I'd like to learn more about what I can put in expressions.
> For example: Fields!<FieldName>.Value.Length
> Is very useful. I found it on this user group but could not find it in
> the documentation (online books). The expression painter does not seem
> to be much help.
> Where can I find more information on what I can put into expressions?
> Thanks in advance :)
>

Easy Determine Free Disk Space Another Disk Drive

What is easy way to return a value for the free disk space on another server
?
Please help me with this problem.
Thank You,Why would you want SQL Server to do this? I would use VBScript's
FileSystemObject or WMI and populate SQL Server with the information, maybe
on five minute intervals. When you run the query from SQL Server, the data
is up to five minutes old, but the user doesn't wait and there are no file
share / server access permissions issues.
A
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:4DB5CF71-938E-4E43-ACE2-73392DB4590B@.microsoft.com...
> What is easy way to return a value for the free disk space on another
> server?
> Please help me with this problem.
> Thank You,
>
>
>sql

Easiest way to get a value in af file into a variable ?

I get a file with some key information delivered to an ftp destination each day along with some files containing rawdata.

The file is a csv file containing some short description of what is being delivered.

Numrows;pulltime;sourceinfo

25302524;25-01-2006;dssrv34

So the file has columndescription and 1 row with some information.

My question is, what is the easiest way to get those 3 informations into 3 variables ?

How about a Flat File Adapter into a script component in which you can load the values into the variables.

-Jamie

|||

Or you could do this: http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

(I forgot I'd done this before)

-Jamie

|||Personally, I would create a script task that takes in a filespec variable and writes to three variables. Like so.

Dim strFileSpec as String = cstr(Dts.Variables("MyFileSpec").Value)

Dim sr As System.IO.StreamReader

Dim strVals() As String

' Check if file exists

If System.IO.File.Exists(strFileSpec) Then

' Open the stream reader

sr = New System.IO.StreamReader(strFileSpec)

' Check if not at end of stream

' Split the first line at the semicolons and put in string array

If Not sr.EndOfStream Then

strVals = sr.ReadLine.Split(";")

End If

' Close the stream reader

sr.Close

' Check if there are three variables,

' If so right to output variables

If strVals.GetLength = 3 Then

Dts.Variables("MyVar1").Value = strVals(0)

Dts.Variables("MyVar2").Value = strVals(1)

Dts.Variables("MyVar3").Value = strVals(2)

End If

End If

Larry Pope

Each member in my attribute has the same value as All (when used with a particular measure)

This only seems to occur in one area, and unfortunately (or fortunately, meaning it's not a bug) I cannot get anything in adventure works to display the problem.

In my cube, if I look at [Customer].[Location Tree].[All], but also say where Customer.[Organization Type].&[School], the sales value of [all] is presenting itself like the organization type slicing wasn't even used.

ie:

(the total sales from this query is correct in my mind)

select [sales] on 0,
Customer.Territory.AllMembers on 1
from cube
where Customer.[Organization Type].&[School]

but the all value out of this query is truly "All".. it doesn't seem to make use of the filtering:

select [sales] on 0,
[Customer].[Location Tree].[All] on 1
from cube
where Customer.[Organization Type].&[School]

One thing to note is that in the first query, the territory members are filtered by the organization type I chose - so part of it is getting sliced, just not the [all] of the location tree.

Please check whether the query works as you want with SP2 CTP - this entry from Mosha's blog explains the changes made in SP2 for these type of scenarios:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/1.aspx

>>

Slicer and axes interaction in MDX Part 1 - coordinate overwrites

...

Well, the solution here is simple. We realized that the way this shipped in AS2005 is clearly broken and inconsistent, and this is why this is fixed in SP2. SP2 should become available really soon now, and the change to the interaction between axes and WHERE is one of the most important changes there.

...

>>

|||

Thank you for the response, Deepak.

Since ctp shouldn't go on a production machine, I really would hate to install it there.

I've tried writing the query different ways, but to my dismay, it still gives the total value of all sales. Then I tried something that I should have done sooner:

select [sales] on 0,
Customer.[Organization Type].Members on 1
from cube

For each row, this shows the total amount of sales! So something's linked wrong and I will take it back to the cube developer again. If you have an idea of the general area to change, please let me know.

If I look in the cube designer, and then 'dimension usage', the customer dimension has a regular relationship to the sales fact table. I'm not sure of where else to look. Other attributes from the customer dimension will slice as desired, but not the [Organization Type].

|||

I've renamed the post subject to better reflect the updated problem:
Each member in my attribute has the same value as All (when used with a particular measure - [sales])

The measure in question, aforementioned as 'sales', does not split out at all when looking at a member of [Organization Type].

To provide more info: The granularity of the customer dimension is down to a customer id, but the measure has a regular relationship type to the customer dimension at the Territory level. Is there something more that needs to be done? I've tried to create new attribute relationships between the all items in the customer location tree with the Customer.[Organization Type], but the results did not change.

If anyone has gets a spark of an idea, please let me know. Thank you!

|||

Since the granularity attribute of the Customer dimension for this measure group (ie. Territory) is above the customer id key attribute, has the [Organization Type] attribute been explicitly related to Territory?

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

>>

SQL Server 2005 Books Online

Defining a Regular Relationship and Regular Relationship Properties

...

When you specify the granularity attribute to be an attribute other than the key attribute, you must guarantee that all other attributes in the dimension are directly or indirectly linked to this other attribute through attribute relationships.

...

>>

|||Territory had [Organization Type] as an attribute relationship and this did not seem to help. I can see if making [Organization Type] have Territory as a relationship would help.|||But the attribute relationship that you define should reflect the real relationships which exist in your dimension - for example, the Sales Targets measure group in Adventure Works relates to the Date dimension at the Calendar Quarter granularity. Calendar Quarter has an attribute relationship to Calendar Semester, which in turn rolls up to Calendar Year. Could you describe your Customer dimension data attributes, and how they are related?|||

Sure.

Customer Dimension:
(these 3 attributes are also visible outisde of the hierarchy)
Customer Location Tree (holds a hierarchy that splits geographical areas)
Section
Sub Section - related to section
Territory - related to sub sectiion

Organization Type - should relate to Territory, as its members can filter out territories. A territory can only have a single organization type.

in the fact table for [sales], the [sales] measure (just renamed for obscurity), only goes down to the territory level.

|||Just to clarify - does [Customer Location Tree] define a Parent-Child hierarchy, because that might have something to do with it?|||

The [Customer].[Location Tree] is a hierarchy, and it contains the 3 levels I mentioned before as well as Parent Customer and then the lowest level, Customer.

The 'Customer' (ID/Key, or lowest level) has attribute relationships to -every- attribute in the customer dimension. I'm not sure if this was done by default or if someone added them.

|||Surprisingly, adding the linking again from Territory to Organization Type (flexible), it was found to work correctly. I'm not sure of any differences from before, but at least it works now!

Dynmaic parameter on Subscription report

Is it possible to have a dynamic parameter value such as current date
to a subscription base report?
Thanks,
Voss.Yes. You can set the default parameter =today() in the designer.
"Voss" wrote:
> Is it possible to have a dynamic parameter value such as current date
> to a subscription base report?
> Thanks,
> Voss.
>sql

Dynamically removing table rows

How can I dynamically remove a table row if a value is blank?
(i.e. Address 2 is blank)If you select the entire table row in report designer, you will notice that
there is a Visibility.Hidden property for the table row. You can use an
expression that evaluates to a boolean value to dynamically hide a table row
then. E.g. =IsNothing(Fields!Address2.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
> How can I dynamically remove a table row if a value is blank?
> (i.e. Address 2 is blank)|||Thank you.
However, I tried on several occasions to get help with columns and no one is
able to assist.
Could someone provide me with a basic understanding how to setup columns?
The columns will be used for mailing labels.
Your assistance is greatly appreciated.
"Robert Bruckner [MSFT]" wrote:
> If you select the entire table row in report designer, you will notice that
> there is a Visibility.Hidden property for the table row. You can use an
> expression that evaluates to a boolean value to dynamically hide a table row
> then. E.g. =IsNothing(Fields!Address2.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
> > How can I dynamically remove a table row if a value is blank?
> >
> > (i.e. Address 2 is blank)
>
>|||Not sure I understand your follow up question correctly. Are you asking
about dynamically hiding table columns in a report? Table columns have a
visibility property like table rows.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5E4E4127-A82C-487C-8AB1-8B1D1B7D4C46@.microsoft.com...
> Thank you.
> However, I tried on several occasions to get help with columns and no one
> is
> able to assist.
> Could someone provide me with a basic understanding how to setup columns?
> The columns will be used for mailing labels.
> Your assistance is greatly appreciated.
> "Robert Bruckner [MSFT]" wrote:
>> If you select the entire table row in report designer, you will notice
>> that
>> there is a Visibility.Hidden property for the table row. You can use an
>> expression that evaluates to a boolean value to dynamically hide a table
>> row
>> then. E.g. =IsNothing(Fields!Address2.Value)
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Terry" <Terry@.discussions.microsoft.com> wrote in message
>> news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
>> > How can I dynamically remove a table row if a value is blank?
>> >
>> > (i.e. Address 2 is blank)
>>|||Dynamically removing table rows have been resolved.
Thank you for your assistance.
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand your follow up question correctly. Are you asking
> about dynamically hiding table columns in a report? Table columns have a
> visibility property like table rows.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:5E4E4127-A82C-487C-8AB1-8B1D1B7D4C46@.microsoft.com...
> > Thank you.
> >
> > However, I tried on several occasions to get help with columns and no one
> > is
> > able to assist.
> >
> > Could someone provide me with a basic understanding how to setup columns?
> >
> > The columns will be used for mailing labels.
> >
> > Your assistance is greatly appreciated.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If you select the entire table row in report designer, you will notice
> >> that
> >> there is a Visibility.Hidden property for the table row. You can use an
> >> expression that evaluates to a boolean value to dynamically hide a table
> >> row
> >> then. E.g. =IsNothing(Fields!Address2.Value)
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> >> news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
> >> > How can I dynamically remove a table row if a value is blank?
> >> >
> >> > (i.e. Address 2 is blank)
> >>
> >>
> >>
>
>

Dynamically number of parameters

I have 3 paramemters. How i can dynamically show/hide other two parameter based on the value as 1 or 2 input from the user from the first parameter? (for example: if the user enter 1, i will show the second parameter, if tyhe user enter 2, i will sho the thirs parameter on the report so the user can enter other value in these dynamical parameters?)Thats not possible. What is possible to still display them , but to clean them to show no value. That can be done using a query in the parameter definition.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de