Remove default value from a table

Hi there.
I cannot figure this out, please help.
I have a table with an attribute with a default value. How do I erase this default value (with sql). I do not want to lose any data in my table.I didn't get that to work, it said I should use ALTER TABLE DROP CONSTRAINT. I have tried that before but without success.

But after a few minutes (almost an hour) I found a way to solve it:

ALTER TABLE voucher DROP CONSTRAINT DF__voucher__voucher__56D3D912;

The Constraint name I found by looking in the sysobjects table.

Thanks anyway!|||just do an alter table ... alter column ... and omit the default.

Remove default parameter

I have a report with default parameters set as ' ' i.e a blank value through a query.

when i deploy this report RS2005 adds a <select a value> as the text listed in the dropdown beacuase of which it does not take my default value and no display of report.

How can I remove this text being shown.

Thanks,

Kiran.

Do you have the "allow blank value" property set? Also, is your query set up to return rows if the parameter is nothing?

Simone

|||

Yes the parameter has allow blank value set. and it returns a different table with values if this parameter is kept blank.

Hence i want users to selcet blank if they do not want to consider this parameter and not just leave the parameter value as

<select a value>

Thanks,

Help appreciated.

Kiran

|||

It seems that if you have a list of available values you must also set a default value. I have a case where I allow no value to be selected but have a list of available values when necessary. To handle this I add an "All" option to my available values list and set this as the default. If this is the chosen value, then I return all records. If another value is filtered the recordset is chosen based on the selection.

Simone

|||

Hello,

So what i should do is if I ahve a list of values and a default value set to the parameter I remove teh allow blank value and that would remove the select a value.

would taht be correct?

thanks

|||

Hello,

I have defqault value for the parameter from the list of available values.

Also i have unchecked the allow blank and allow null text boxes. Still tehre is the <select a value> in the parameter drop-down.

What next.

Thanks

|||

In your query that selects the data, do you have anything such as this:

where column = case when @.variable = '' then column else @.variable end

This tells the query to pull all data when the variable value is blank, otherwise pull only the data matching the variable value. You then need to ensure you have '' as a valid choice in the "available values" section.

Simone

|||

Thanks Simone,

I do have conditions like AND @.Country=' ' AND @.Srv_Level=' '

but there is a value in the dropdown that is ' ' which is also set as default value for some of teh parameters.

the difference is that selecting ' ' does not mean that select all values as in the dropdown there is an all functionality too. Selecting a ' ' matches a specific case and gives values returning to that condition.

I am thinking this is a RS2005 addition as in the designer view do not see the <select a value> in teh parameter dropdowns

Thanks

|||

Maybe this will help you:

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

I misunderstood your intentions with the blank value. The same should be true with having the blank value as your default. If it is a valid selection in the list and selected as your default value, so long as all other parameters also have a default value, your report should run. I am using both "ALL" and "" as defaults and do not have the <select a value> in my lists. I have either the "" or "All". Good luck.

Simone

|||I have used 'ALL' and NULL to accomplish this task on reports I have written|||Hi,

Could you elaborate how using Null u manged to remove <select a value> I need a ' ' as a possible selection....
thanks
|||

I am using a SQL statement to return my list of available values. In the statement I add a UNION to include the '' default.

ex.

Code Snippet

Select FieldA

From TableA

UNION

Select '' as FieldA

Order by 1

I then set '' as the default value.

I hope this helps.

Simone

Remove default parameter

I have a report with default parameters set as ' ' i.e a blank value through a query.

when i deploy this report RS2005 adds a <select a value> as the text listed in the dropdown beacuase of which it does not take my default value and no display of report.

How can I remove this text being shown.

Thanks,

Kiran.

Do you have the "allow blank value" property set? Also, is your query set up to return rows if the parameter is nothing?

Simone

|||

Yes the parameter has allow blank value set. and it returns a different table with values if this parameter is kept blank.

Hence i want users to selcet blank if they do not want to consider this parameter and not just leave the parameter value as

<select a value>

Thanks,

Help appreciated.

Kiran

|||

It seems that if you have a list of available values you must also set a default value. I have a case where I allow no value to be selected but have a list of available values when necessary. To handle this I add an "All" option to my available values list and set this as the default. If this is the chosen value, then I return all records. If another value is filtered the recordset is chosen based on the selection.

Simone

|||

Hello,

So what i should do is if I ahve a list of values and a default value set to the parameter I remove teh allow blank value and that would remove the select a value.

would taht be correct?

thanks

|||

Hello,

I have defqault value for the parameter from the list of available values.

Also i have unchecked the allow blank and allow null text boxes. Still tehre is the <select a value> in the parameter drop-down.

What next.

Thanks

|||

In your query that selects the data, do you have anything such as this:

where column = case when @.variable = '' then column else @.variable end

This tells the query to pull all data when the variable value is blank, otherwise pull only the data matching the variable value. You then need to ensure you have '' as a valid choice in the "available values" section.

Simone

|||

Thanks Simone,

I do have conditions like AND @.Country=' ' AND @.Srv_Level=' '

but there is a value in the dropdown that is ' ' which is also set as default value for some of teh parameters.

the difference is that selecting ' ' does not mean that select all values as in the dropdown there is an all functionality too. Selecting a ' ' matches a specific case and gives values returning to that condition.

I am thinking this is a RS2005 addition as in the designer view do not see the <select a value> in teh parameter dropdowns

Thanks

|||

Maybe this will help you:

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

I misunderstood your intentions with the blank value. The same should be true with having the blank value as your default. If it is a valid selection in the list and selected as your default value, so long as all other parameters also have a default value, your report should run. I am using both "ALL" and "" as defaults and do not have the <select a value> in my lists. I have either the "" or "All". Good luck.

Simone

|||I have used 'ALL' and NULL to accomplish this task on reports I have written|||Hi,

Could you elaborate how using Null u manged to remove <select a value> I need a ' ' as a possible selection....
thanks
|||

I am using a SQL statement to return my list of available values. In the statement I add a UNION to include the '' default.

ex.

Code Snippet

Select FieldA

From TableA

UNION

Select '' as FieldA

Order by 1

I then set '' as the default value.

I hope this helps.

Simone

sql

Remove anoter SQL 2005 instance

I have installed SQL 2005 Instance on the existing default instance and
noww I will need to remove the second Instance . how can I do that The add
and remove do not have the second instance name.You must have seen the choice for your all SQL Server instances to remove or
change in Add\Remove Programs window.
I guess, you might have taken it wrong. There will be only one item which is
Microsoft SQL Server 2005. Click on it and click Uninstall button. When you
do that you'll see a list of your installed instances of SQL Server and
choose an instance according to your needs and proceed.
--
Ekrem Önsoy
<msnews.microsoft.com> wrote in message
news:%23Ym%23ZGPFIHA.5328@.TK2MSFTNGP05.phx.gbl...
>I have installed SQL 2005 Instance on the existing default instance and
>noww I will need to remove the second Instance . how can I do that The add
>and remove do not have the second instance name.
>

Remote Timeout Option setting

Hey All,
Very simple one...how do I simply change the default from 20 seconds to 60 in the sp_config table...
Thanks,
DJLook up sp_configure in Books Online and you will be well on your way to answering your question.|||The bible? Read it. i'll go back and look. Is it as easy as this?

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'remote login timeout', 60;
GO
RECONFIGURE;
GO

Remote server time

Hi,

I have a datetime field with the default as getdate() for every record that is added to the DB.

However my server is 12 hours back from where I am.
How can I adjust the default time so it will show my time?

Many thanks,

JackYou could use DATEADD(hour,-12,GetDate()) as the default...

Remote Server into a named Instance of SQL Server 2005

We are setting up a database server with 2 instances of SQL Server 2005. One for the default and another for Reporting Service. I have them installed but when I try to a registration from another Server for the Reporting Service Instance I can not get in. I am using the sa userid and password, the same one that works when I am on the server but I get an error that says Remote Server connections may not be allowed. When I go to properties the Allow remote connections box is checked. Anyone know what else I need to do to get to the named instance?If you have another instance on the server it probably uses not the default port. If you have chosen another port than the default 1433 for the named instance, you should either start SQL Browser (which will handle request and will forward to the appropiate port on the server) or you directly name it within your connecting application by using the following syntax:

Servername\InstanceName,portnumber.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de