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 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. :-)

Dynamically Drop FK Restraints?

I was wondering if anyone had anything in their bag of tricks that
would detect any foreign key contraints on a table and break them. I
am constantly dropping/adding tables as part of a MSSQL 2000 utlity
function and I keep hitting an error if I drop a parent table that has
FKs.
Thanks.See if this helps you get on the right track:
http://sqljunkies.com/WebLog/amacha...onstraints.aspx
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"xenophon" <xenophon@.online.nospam> wrote in message
news:ulrn51pb732k09237hfk9u8iri2h7vl9pq@.
4ax.com...
> I was wondering if anyone had anything in their bag of tricks that
> would detect any foreign key contraints on a table and break them. I
> am constantly dropping/adding tables as part of a MSSQL 2000 utlity
> function and I keep hitting an error if I drop a parent table that has
> FKs.
> Thanks.
>

Dynamically Drop and re-create all indexes for all tables

Hi There

I have a database where i want to move all data and indexes to separate drives via filegroups.

Apparantly there is no better way to do this in 2005 than in 2000, which means i need to drop and re-created clustered and non clustered index accordingly on the new filegroups.

What i did in 2000 was loop through tables and indexes dropping the indexes and dynamically re-creating the index defintions from sysindexes etc.

Is there an easier way to do this is 2005, in a nutshell how would i dynamically script the create index statements for all indexes for all tables in 2005.

Thanx

If you need just rebuild indexes, you could use ALTER with REBUILD instead of DELETE and CREATE|||Implementing partitions would allow you to shift things around as desired.|||Hi Konstantin i do not see the ON FILEGROUP syntax in BOL with ALTER INDEX ?

Dynamically Change FilterExpression

I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.

Something like this example...http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx

Here is some of my code..

PrivateSub ApplyFilter()

Dim _filterExpressionAsString =""

If (Not DropDownList1.SelectedIndex = 0)And (DropDownList2.SelectedIndex = 0)And (DropDownList3.SelectedIndex = 0)Then

_filterExpression ="CategoryName = '{0}'"

EndIf

Me.SqlDataSource1.FilterExpression = _filterExpression

EndSub

ProtectedSub DropDownList1_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles DropDownList1.SelectedIndexChanged

ApplyFilter()

EndSub

But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.

Hi nfalk,

a) :It seems to me that you have specified an filterexpression to use parameter while didn't add any values to the FilterParameters collection. As to how to use filter expression, see the code example below:

msdn:

 <asp:DropDownList id="DropDownList1" runat="server" AutoPostBack="True"> <asp:ListItem Selected>Sales Representative</asp:ListItem> <asp:ListItem>Sales Manager</asp:ListItem> <asp:ListItem>Vice President, Sales</asp:ListItem> </asp:DropDownList></p> <asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" FilterExpression="Title='{0}'"> <FilterParameters> <asp:ControlParameter Name="Title" ControlId="DropDownList1" PropertyName="SelectedValue"/> </FilterParameters> </asp:SqlDataSource>

b): The filter expression only take affect when datasource.select() method is called. Thus only modifying the filter expression won't change your gridview content. You need to call gridview.databind() again

in your DropDownList1_SelectedIndexChanged method.

Hope my suggestion helps

|||

Hello Bo Chen,

a) I did have the filter parameters set I just forgot to mention it in the posting.

b) I was forgetting to call the DataBind() in the event handler. After I added it everything works fine.

Thanks for the reply.

|||

I am trying to do something similar but can't get it to work. I have the following code running every time the web page reloads:

ReportDataSource.FilterExpression ="SurveyID=" & SurveyList.SelectedValue.ToString

SurveyReportViewer.DataBind()

I have a ReportViewer (SurveyReportViewer) based on an ObjectDataSource (ReportDataSource). Every time the page reloads it checks the SurveyList drop-down to get the new ID. This works fine the first time the page is loaded, but the filter doesn't change after that. I know it's not supposed to work until the Select is called again, but I thought that's what the DataBind method did (I had tried using DataBind on the ObjectDataSource as well as the ReportViewer but it didn't make any difference). Since it does work the first time through, I'm assuming most everything is set up correctly and that I'm just missing something that's required to refresh the data source. Any suggestions?

|||

What I did was create a ApplyFilter() method with my logic to change the FilterExpression. And in the DropDownList SelectedIndexChanged event call the applyfilter and rebind to data source...

ProtectedSub DropDownList2_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles DropDownList2.SelectedIndexChanged

ApplyFilter()

Me.SqlDataSource1.DataBind()

EndSub

Also I don't know if it makes a difference but I used Filter parameters...

<asp:ControlParameterControlID="DropDownList2"DefaultValue="*"Name="Filter"PropertyName="SelectedValue"/>

In the ApplyFilter()...

Me.SqlDataSource1.FilterExpression="CategoryName = '{0}' "

Hope this helps

|||

Well I gave it a try but had the same results. I may have to try using a SqlDataSource instead of ObjectDataSource and see if there's a difference there. This is probably what I get for using wizards instead of coding it myself...

Dynamic updating of Parameters.

Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select “select all” option from the parameter list all 20 parameters value should be false.

I will be very thankful.

Regards,

Faisal Saleem

Hi Faisal,

try to use "cascading parameters" and "default values".

More info here:
http://msdn2.microsoft.com/en-us/library/ms170246.aspx

cheers

Markus

|||

Firstly cascading/dependent parameters only work when they are populated from a dataset that uses another parameter. This is not the case here as these are boolean parameters.

Secondly, default values are only applied when the report first loads. So once the user changes any of the parameters, default values are no longer considered and hence trying to use use default values with deperndent parameters will not work.

Cascading parameters are used to modify/effect the list of available values rather than the one that's actually selected.

Although at first is seems like this should be possible, I don't think it is.

Instead, can I suggest that you change the "select all" parameter to contain 3 values:

All

None

Custom

|||

Adam Tappis - IMGroup wrote:

Secondly, default values are only applied when the report first loads. So once the user changes any of the parameters, default values are no longer considered and hence trying to use use default values with deperndent parameters will not work.

Cascading parameters are used to modify/effect the list of available values rather than the one that's actually selected.

I just ran into this and it seems like a functional bug to me. It's great that cascading parameters can be used to alter drop-down options for subsequent parameters, but that's only part of the story. If subsequent parameter choices can be altered by a previous parameter selection, it is completely misleading not to do the same for setting defaults. The users find it very confusing.

Anyone know if this has been logged to Connect?

|||

GregsListAcct wrote:

Anyone know if this has been logged to Connect?

Just answered my own question. It is logged in Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=268032

Microsoft is considering it "by design". :^(

Dynamic updating of Parameters.

Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select “select all” option from the parameter list all 20 parameters value should be false.

I will be very thankful.

Regards,

Faisal Saleem

Hi Faisal,

try to use "cascading parameters" and "default values".

More info here:
http://msdn2.microsoft.com/en-us/library/ms170246.aspx

cheers

Markus

|||

Firstly cascading/dependent parameters only work when they are populated from a dataset that uses another parameter. This is not the case here as these are boolean parameters.

Secondly, default values are only applied when the report first loads. So once the user changes any of the parameters, default values are no longer considered and hence trying to use use default values with deperndent parameters will not work.

Cascading parameters are used to modify/effect the list of available values rather than the one that's actually selected.

Although at first is seems like this should be possible, I don't think it is.

Instead, can I suggest that you change the "select all" parameter to contain 3 values:

All

None

Custom

|||

Adam Tappis - IMGroup wrote:

Secondly, default values are only applied when the report first loads. So once the user changes any of the parameters, default values are no longer considered and hence trying to use use default values with deperndent parameters will not work.

Cascading parameters are used to modify/effect the list of available values rather than the one that's actually selected.

I just ran into this and it seems like a functional bug to me. It's great that cascading parameters can be used to alter drop-down options for subsequent parameters, but that's only part of the story. If subsequent parameter choices can be altered by a previous parameter selection, it is completely misleading not to do the same for setting defaults. The users find it very confusing.

Anyone know if this has been logged to Connect?

|||

GregsListAcct wrote:

Anyone know if this has been logged to Connect?

Just answered my own question. It is logged in Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=268032

Microsoft is considering it "by design". :^(

Dynamic updating of Parameters.

Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select “select all” option from the parameter list all 20 parameters value should be false.

I will be very thankful.

Regards,

Faisal Saleem

Hi Faisal,

try to use "cascading parameters" and "default values".

More info here:
http://msdn2.microsoft.com/en-us/library/ms170246.aspx

cheers

Markus

|||

Firstly cascading/dependent parameters only work when they are populated from a dataset that uses another parameter. This is not the case here as these are boolean parameters.

Secondly, default values are only applied when the report first loads. So once the user changes any of the parameters, default values are no longer considered and hence trying to use use default values with deperndent parameters will not work.

Cascading parameters are used to modify/effect the list of available values rather than the one that's actually selected.

Although at first is seems like this should be possible, I don't think it is.

Instead, can I suggest that you change the "select all" parameter to contain 3 values:

All

None

Custom

|||

Adam Tappis - IMGroup wrote:

Secondly, default values are only applied when the report first loads. So once the user changes any of the parameters, default values are no longer considered and hence trying to use use default values with deperndent parameters will not work.

Cascading parameters are used to modify/effect the list of available values rather than the one that's actually selected.

I just ran into this and it seems like a functional bug to me. It's great that cascading parameters can be used to alter drop-down options for subsequent parameters, but that's only part of the story. If subsequent parameter choices can be altered by a previous parameter selection, it is completely misleading not to do the same for setting defaults. The users find it very confusing.

Anyone know if this has been logged to Connect?

|||

GregsListAcct wrote:

Anyone know if this has been logged to Connect?

Just answered my own question. It is logged in Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=268032

Microsoft is considering it "by design". :^(

Dynamic SQL Invalid Column name error

Any idea why this is telling me that the field value I want to pass in
is an invalid column name?
This is for a "Search By" query. I have a drop down of choices and a
text box for the value. I need to search for the value entered in the
text box in the drop down field. The list is populated from fields in
more than 1 table.
STORED PROC:
ALTER PROCEDURE dbo.searchQuery2
(
@.searchTxt varchar(50) = NULL, @.searchField varchar(50)
)
AS
Declare @.sql varchar(4000)
Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
r.dateSubmitted, a.Name
From Request r, AppComm a
Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
a.badgeID'
Select @.sql
exec (@.sql)
OUTPUT:
Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
a.Name
From Request r, AppComm a
Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
Invalid column name 'a111111'.
(1 row(s) returned)
@.RETURN_VALUE = 0
Finished running dbo."searchQuery2".
Why is it returning the correct SQL but telling me that a111111 is a
column? - it is not a column at all, it is the value. I have tried
playing around with single and double quotes but nothing seems to work.
Please help.
Thanks!Because the value a11111 is a string, and you need to enclose string in sing
le quotes:

> Where ' + @.searchField + ''' = ' + @.searchTxt + ''' and r.reviewedBy =
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Shenoy.D@.gmail.com> wrote in message news:1147981295.111755.280880@.j73g2000cwa.googlegroup
s.com...
> Any idea why this is telling me that the field value I want to pass in
> is an invalid column name?
> This is for a "Search By" query. I have a drop down of choices and a
> text box for the value. I need to search for the value entered in the
> text box in the drop down field. The list is populated from fields in
> more than 1 table.
> STORED PROC:
> ALTER PROCEDURE dbo.searchQuery2
> (
> @.searchTxt varchar(50) = NULL, @.searchField varchar(50)
> )
> AS
> Declare @.sql varchar(4000)
> Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
> r.dateSubmitted, a.Name
> From Request r, AppComm a
> Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
> a.badgeID'
> Select @.sql
> exec (@.sql)
>
> OUTPUT:
> Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
> a.Name
> From Request r, AppComm a
> Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
>
> Invalid column name 'a111111'.
> (1 row(s) returned)
> @.RETURN_VALUE = 0
> Finished running dbo."searchQuery2".
> Why is it returning the correct SQL but telling me that a111111 is a
> column? - it is not a column at all, it is the value. I have tried
> playing around with single and double quotes but nothing seems to work.
> Please help.
> Thanks!
>