Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked

Available values = "From Query"

Dataset = [create a dataset that returns a table w/ a Id and Description column]

Value field = [the Id field from the table]

Label field=[the Description column from the table]

Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default for an int parameter in SP

I'm using a stored procedure that receives one parameter namely @.EmployeeID INT

but when I want to give this parameter a default value, my SP fails.

I did it like with a varchar where it works.

--
@.Employee INT = '%'

--

Is this correct or did I use a wrong syntac/wildcard?

Greetings,
GeoffINT is a numeric datatype only you should use digits to assign defaults.
If not use VARCHAR to assign such special chars.|||Isn't there a numeric default that I can use?

actually i use the SP to do the next thing.

If I don't give a parameter I want all recoreds to be returned. When passing through the ID (as parameter tot the SP) I want only that record to be returned. So if I understand correctly you're saying there is no sucth thing as setting a default for an int-type?

Greetings,
Godofredo|||Only numerics are allowed as default for int.|||Can i define a range then?

like @.EmployeeID INT = [0-9]

so that all numbers can be received? or how precisely do I do this?

If no parameter is given I want al records returned.

Greetings,
Geoff|||CREATE PROCEDURE sp_myproc
@.emp_id int = 0
as

select * from my_table
where emp_id =
case @.emp_id when 0 then emp_id else @.emp_id end

when you call sp_myproc without specifying any parameters then all employees are retrieved.

Is that what you were asking?|||Yes indeed

Default Field Value?

Is it possible to change the default value of a field using a stored proc or query? Any hints on how to do this if it is possible would be appreciated!
Mike BJust supply the new value on the INSERT?

Or to permanently change it you need to use ALTER TABLE...I think...gotta check...go look up ALTER in Books Online (BOL)

OK?

Default Field Value for DateTime & SmallDateTime

In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls.

For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for.

Are there any more developend ideas on this question?

Many thanks
Mike ThomasHi, Mike.
The choise depends directly on the problem U r solving. Sometimes GETDATE() helps... just analize Ur task and make a corresponding conclusion: what value is permitable as a default one in the definite case...

Alex.sql

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
-Scott ElgramScott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> this
>

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
-Scott Elgram
Scott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>
|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> this
>

Default Field Value

Is there a way to set default values for a numeric field? I have several
fields that sometimes are null. I want the nulls to show as zero. I tried
iif(value is null, 0.00 , value) but it gives an error on the â'nullâ' word. I
tried â'IsNullâ', â'IsNothingâ' and â'IsNumericâ' all with the same error.
Any ideas?Try
iif(value=Nothing, 0.00 , value)
"tachtenberg" <tachtenberg@.discussions.microsoft.com> escribió en el mensaje
news:9FF1A567-6AB0-402F-A542-8205EB6AA195@.microsoft.com...
> Is there a way to set default values for a numeric field? I have several
> fields that sometimes are null. I want the nulls to show as zero. I
> tried
> iif(value is null, 0.00 , value) but it gives an error on the "null" word.
> I
> tried "IsNull", "IsNothing" and "IsNumeric" all with the same error.
> Any ideas?
>

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
--
-Scott ElgramScott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > I have a table with a filed set up to use a default Field value of
> > (GetDate()) but is also allowed nulls. However, The default value for
> this
> > field has stopped being set automatically. Has anyone experienced this
> > problem before? Does anyone know why and/or how to fix it?
> >
> > --
> > -Scott Elgram
> >
> >
>

Default field to another field value on db level

Hi All,

I need to create a new field on a table and have that field default to another field value in that same table. Is there a way to do this w/ a default constraint rather than adding a trigger to the table? If i can't use a default constraint does anyone have a template trigger i could use? Below is an example of what i'm trying to do (Field_C is the new field and i want it to use Field_A value if no other value is specified on insert). Any help would be greatly appreciated.

alter table FOO add Field_C varchar(50) not null constraint FOO_default DEFAULT Field_A

thanks,
Davethis worked for me.....

CREATE Trigger TRG_FOO_default_INS
on dbo.FOO
for Insert
as

Declare @.default_FieldC varchar(50)

select @.default_FieldC = Field_C from inserted

If (@.default_FieldC is null) or (@.default_FieldC = '')
BEGIN
Update FOO set Field_C = Field_A where PK_ID in (select PK_ID from inserted)
END -- update externalname

Default Date Value (today)

I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.Hey there,

When creating your table add a default constraint to your column, something like...

create table a_tbl (
col1 int not null,
col2 datetime constraint constr_1 default(getdate())
)

Or if you were creating the table using the table disign GUI, the default value should be getdate().

As for the difference between datetiem and smalldatetime, there is plenty of info in BOL. But basically smalldatetime is rounded to the nearest mintue, where as datetime is detailed to the fraction of a secound.

Hope this helps.

Originally posted by Plarde
I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.|||As referred books online is the best bet to startwith the differences, syntax information and other examples which will give strength to the current knowledge in SQL server and ofcourse this forum is available to help you out in any consequences.

Good luck.

Default Date Value (today)

I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.1.The function that returns the current timestamp is GETDATE()
2.datetime holds date and time from Jan 1,1753 to Dec 31,9999 with a three hundredth of a second accuracy
smalldatetime holds date and time from Jan 1, 1900 to June 6,2079 with accuracy to the minute, that fits with most applications.

Originally posted by Plarde
I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.|||You can find this kind of info in the Books Online which is installed on your machine as you install sql server.|||An extra difference between datetime and smalldatetime types is for VB6 programmers. I've read somewhere that using smalldatetime gives problems in VB6 applications.

Ad.

Originally posted by dbadelphes
1.The function that returns the current timestamp is GETDATE()
2.datetime holds date and time from Jan 1,1753 to Dec 31,9999 with a three hundredth of a second accuracy
smalldatetime holds date and time from Jan 1, 1900 to June 6,2079 with accuracy to the minute, that fits with most applications.|||True its better to use DATETIME than the other with ADO/VB6.sql

Default date problem

Hi,

Good Day!

In my sproc, I m trying to set a default value for a parameter, but it's sending me an error. It seems like having probs with brackets!

Code Snippet

@.Date DateTime = GetDate(),

Code Snippet

Msg 102, Level 15, State 1, Procedure usp_Receive_Add, Line 8
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure usp_Receive_Add, Line 40
Must declare the scalar variable "@.ProductID".

Please tell me what's the prob! I m trying to set todays date to the parameter if nothing was supplied in the @.Date parameter.

Regards

Kapalic

Use the following logic to set the current date..

Code Snippet

Create proc MyProc
(
@.Date datetime = '1900-01-01'
)
as
Begin
Select @.Date = Case When @.Date <> '1900-01-01' Then @.Date Else Getdate() End
Select @.Date
End

go

Exec MyProc '2/2/2007'
Exec MyProc

|||How about setting default value of @.date to NULL and set it to GetDate() in store procedure body? I just wandering 1900-1-1 is valid value. here is valid value of DateTime according SQL Server 2005 document.

datetime

January 1, 1753, through December 31, 9999

|||

Yes.. You can do it with NULL.

Suppose if you want to store the explicit null value on your table then this logic wont work. RITE?

So we are setting some default value which we are assuming that it never passed from our UI.

1900-01-01 is valid value only. it is with in the given range Buddy.. It is a typical sql coders starting value [Cast(0 as Datetime)]

|||

In a variable declaration, you can set a variable to a constant, e.g., a value.

However, you cannot set it to the results of a function. Getdate() is a function.

As suggested, if you wish to make the parameter optional, set a default value of '01/01/1900', and then if you wish to set it to the current date/time, after entering the procedure code (after 'AS'), set the parameter = getdate().

default date

in DateTime column called ExpireDate, I have default value of (1/1/2020)
yet when data entry is made (without ExpireDate value) the value is always
set at 1/1/1900
why is default not entered as 1/1/2020 ?
DEFAULTs only work when you don't provide a value for the column at all, or
use the keyword DEFAULT. What it looks like is that you provide the value 0
for the column, and 0 as a datetime is interpreted by SQL Server as
1/1/1900. See the following example:
CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
INSERT INTO TJS (Expire_Date) VALUES (0)
INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
SELECT Expire_Date FROM TJS
Jacco Schalkwijk
SQL Server MVP
"TJS" <nospam@.here.com> wrote in message
news:1132gu53p4kt9bd@.corp.supernews.com...
> in DateTime column called ExpireDate, I have default value of (1/1/2020)
> yet when data entry is made (without ExpireDate value) the value is always
> set at 1/1/1900
> why is default not entered as 1/1/2020 ?
>
>
|||your example works, but I am trying to use a stored procedure
I have this in the stored procedure:
@.ExpireDate datetime = DEFAULT
The column default value is set as (1/1/2020)
but it still enters 1/1/1900
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uIdTaWhJFHA.576@.TK2MSFTNGP15.phx.gbl...
> DEFAULTs only work when you don't provide a value for the column at all,
> or use the keyword DEFAULT. What it looks like is that you provide the
> value 0 for the column, and 0 as a datetime is interpreted by SQL Server
> as 1/1/1900. See the following example:
> CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
> INSERT INTO TJS (Expire_Date) VALUES (0)
> INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
> SELECT Expire_Date FROM TJS
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "TJS" <nospam@.here.com> wrote in message
> news:1132gu53p4kt9bd@.corp.supernews.com...
>
|||hi,
TJS wrote:
> your example works, but I am trying to use a stored procedure
> I have this in the stored procedure:
> @.ExpireDate datetime = DEFAULT
> The column default value is set as (1/1/2020)
> but it still enters 1/1/1900
>
do you mean your procedure's code is
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = DEFAULT
INSERT INTO #test VALUES ( 1 , @.ExpireDate )
or
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = '20200101'
INSERT INTO #test VALUES ( 2 , @.ExpireDate )
?
the first code will actually raise an exception (Incorrect syntax near the
keyword 'DEFAULT'.) and non data will be entered...
can you please expand?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Alter PROCEDURE dbo.AddUser
(
@.Name nvarchar(50),
@.Email nvarchar(100),
@.Password nvarchar(50),
@.ExpireDate datetime = DEFAULT,
@.EnableNewsLetter bit,
@.UserID int OUTPUT
)
AS
INSERT INTO _Users
(
Name,
Email,
Password,
ExpireDate,
EnableNewsletter
)
VALUES
(
@.Name,
@.Email,
@.Password,
@.ExpireDate,
@.EnableNewsLetter
)
SELECT
@.UserID = @.@.Identity
|||hi TJS,
TJS wrote:
> Alter PROCEDURE dbo.AddUser
> (
> @.Name nvarchar(50),
> @.Email nvarchar(100),
> @.Password nvarchar(50),
> @.ExpireDate datetime = DEFAULT,
> @.EnableNewsLetter bit,
> @.UserID int OUTPUT
>.....
you can not use the DEFAULT keyword that way as you have to provide an
explicit default and not the "DEFAULT" keyword if you want it to be used for
not provided paramenter... that's to say you have perhaps to set it as
@.ExpireDate datetime = 'some date',
if you check your code, @.ExpireDate will always be NULL if not explicit
value has been specified for that parameter...
your code is like
SET NOCOUNT ON
GO
CREATE TABLE dbo._Users (
UserID int IDENTITY
, Name nvarchar (10) --(50)
, Email nvarchar (20) --(100)
, Password varchar(10) --(50)
, ExpireDate datetime DEFAULT '20050101'
, EnableNewsletter bit DEFAULT 0
)
GO
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = DEFAULT -- this value will never be used and the
underlaying
-- column default can not be used
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
-- SELECT @.ExpireDate always returns NULL if no explicit value is passed
INSERT INTO dbo._Users
(
Name
, Email
, Password
, ExpireDate
, EnableNewsletter
)
VALUES
(
@.Name
, @.Email
, @.Password
, @.ExpireDate
, @.EnableNewsLetter
)
SELECT @.UserID = SCOPE_IDENTITY()
GO
DECLARE @.UserId int
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
-- , @.ExpireDate -- param not provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = NULL -- param exlicitely NULL
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = '20050315' -- param provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
SELECT *
FROM dbo._Users
-- WHERE UserID = @.UserId
GO
DROP PROC dbo.AddUser
DROP TABLE dbo._Users
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna NULL
1 -- no value specified
2 Andrea andrea@.andrea.com aerdna NULL
1 -- explicit NULL specified
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
but modifyng the daclaration of the sp's parameters, providing an explicit
value for that parameter like
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = '20050101'
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
....
you will get a different result as
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna 2005-01-01
00:00:00.000 1
2 Andrea andrea@.andrea.com aerdna NULL
1
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
you can perhaps check your parameters like
IF ISNULL ( @.ExpireDate ) BEGIN
-- set it to whatever you want
END
or execute 2 different INSERT statements depending on the IF condition, ie:
do not provide the [ExpireDate] column if you want it to default to your
CREATE TABLE column default like
IF ISNULL ( @.ExpireDate ) BEGIN
INSERT INTO dbo._Users ( Name , Email , Password , EnableNewsletter )
VALUES ...
ELSE
INSERT INTO dbo._Users ( Name , Email , Password , ExpireDate ,
EnableNewsletter ) VALUES ...
but I'd better check for ISNULL and set it accordingly to your needs, as all
you other parameters should be checked as well
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more...
you can even query the INFORMATION_SCHEMA.COLUMNS ANSI view for columns
information like nullability and default to perform your own check and
eventual default settings...
SET NOCOUNT ON
CREATE TABLE dbo.Test (
ID int NOT NULL ,
dt datetime DEFAULT getdate()
)
GO
SELECT c.COLUMN_DEFAULT , c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'Test'
-- AND c.COLUMN_NAME = 'dt'
GO
DROP TABLE dbo.Test
--<--
COLUMN_DEFAULT IS_NULLABLE
-- --
NULL No
(getdate()) YES
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

default data value in gridview?

Hi,

I am trying to set a default value for a date field in my update parameters:

When I try an update with the DefaultValue="<% Now %>", I get this error:
"String was not recognized as a valid DateTime."

The updates work fine if no default value is set and it also works ok if I change the default value to a set string, such as "6/24/06". I've tried using different data sources and datasets, but no luck.

Anyone have any ideas on this?

Thanks!

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:db1ConnectionString%>" ProviderName="<%$ ConnectionStrings:db1ConnectionString.ProviderName%>" SelectCommand="SELECT [ID], [Name], [Date] FROM [Table1]" DeleteCommand="DELETE FROM [Table1] WHERE [ID] = ?" InsertCommand="INSERT INTO [Table1] ([ID], [Name], [Date]) VALUES (?, ?, ?)" UpdateCommand="UPDATE [Table1] SET [Name] = ?, [Date] = ? WHERE [ID] = ?"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" DefaultValue="<%Now()%>" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" /> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> <asp:BoundField DataField="Date" HeaderText="Date" ReadOnly="true" SortExpression="Date" /> </Columns> </asp:GridView>

I'm not sure you're using the right way to call the function in page script, but it works if I set the default value in the code behind:

SqlDatasSource1.SelectParameters.Add("@.OD", TypeCode.DateTime, DateTime.Now.ToString());

|||Great, thanks! I don't know why I didn't think to do it that way.

Just in case anyone else runs this, I ended up using an ObjectDataSource and added this event handler to set the parameters...it works great:

Protected Sub ObjectDataSource1_Inserting(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)Handles ObjectDataSource1.Inserting e.InputParameters("Date") = DateTime.NowEnd Sub Protected Sub ObjectDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)Handles ObjectDataSource1.Updating e.InputParameters("Date") = DateTime.NowEnd Sub

|||I'm glad to hear that you managed to make it works BTW someone told me the reason why your code in page script (set DefaultValue for a parameter a value returned by a function) didn't work is that the Parameter elements can not be boundsql

Default cube view in SSMS?

Hi,

When browsing a cube in management studio, is there a way set up default value in PivotTable?

Let’s say the cube has 20 dimensions and 10 measures, but user see 2 dimensions and one measure more often; when open a cube, can SSMS just show the default view (2 dimensions and one measure

Thanks in advance.
You can create a perspective with just the 2 dimensions and the measure more used. However, SSMS nor Excel will use one as the default (I think). Of course, if you a Pivot Table for the new perspective is just a question of refreshing that PT.|||
Thank you very much Tiago, your information is very helpful.
I really appropriated.

|||

SSMS is not a tool for end user.

If you, as administrator, want to see some predefined MDX queries you constuct it once then intercept the MDX with profiler and save the MDX in file. Then you can call those MDX queries in SSMS every time you want.

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

Default Column Values

I want to be able to set the default value of a column to be the next number
available, ie. max(MyColumn) + 1 (?).
Ordinarily, you would use an identity field for this, but a) we already have
one (primary key) and b) this value will possibly change such that several
rows will have the same MyColumn value.
Any suggestions?
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]You could either a) let your application control the insertion of data
(which is probaby the best solution because it simplifies the
validation of data on the data level) OR b) write a INSERT trigger to
find the max value and if this column is not specified, then insert the
business rule you specified.
I try to avoid triggers when I can, because I think it places a burden
on your database performance, and I do a lot of DTS bulk inserts (which
don't fire triggers by default).
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
> You could either a) let your application control the insertion of data
> (which is probaby the best solution because it simplifies the
> validation of data on the data level) OR b) write a INSERT trigger to
> find the max value and if this column is not specified, then insert the
> business rule you specified.
> I try to avoid triggers when I can, because I think it places a burden
> on your database performance, and I do a lot of DTS bulk inserts (which
> don't fire triggers by default).
>
So the formula can't be used in the columns Default Value property?
Why would a simple trigger like that burden the server any more than an
extra query to the Db to determine the appropriate value? I'm not
disagreeing with you, I'm just curious...
Server load is not such a big issue for me, but then again, that's not
really a reason to ignore it...
Chris|||You could write a function that returns the max + 1, and use it as a default
value, but this solution will have issues:
create function dbo.fn_nextkey() returns int
as
begin
return coalesce((select max(keycol) + 1 from t1), 1);
end
go
create table t1
(
keycol int not null primary key default dbo.fn_nextkey(),
datacol varchar(10) not null
);
go
insert into t1(datacol) values('a');
insert into t1(datacol) values('b');
insert into t1(datacol) values('c');
select * from t1;
keycol datacol
-- --
1 a
2 b
3 c
Multiple processes inserting at the same time will get the same value, and
you will get pk violation errors that you'd need to trap and handle.
A better option would be to create a table that maintains the last assigned
value:
create table seq(val int not null);
insert into seq values(0);
And increment the value every time you need a new key using a stored
procedure:
create proc usp_nextkey @.o as int output
as
update seq set @.o = val = val + 1;
go
When you need a new key, invoke the proc as follows:
declare @.i as int;
exec usp_nextkey @.i output;
insert into t1 values(@.i, 'd');
BG, SQL Server MVP
www.SolidQualityLearning.com
"CJM" wrote:

> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
>
> So the formula can't be used in the columns Default Value property?
> Why would a simple trigger like that burden the server any more than an
> extra query to the Db to determine the appropriate value? I'm not
> disagreeing with you, I'm just curious...
> Server load is not such a big issue for me, but then again, that's not
> really a reason to ignore it...
> Chris
>
>|||> (which is probaby the best solution because it simplifies the
> validation of data on the data level)
In general, unless you have a magical way of preventing users from accessing
the data *except* through your application, there is no good place to put
data validation *except* in the data layer. YMMV.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> In general, unless you have a magical way of preventing users from
> accessing the data *except* through your application, there is no good
> place to put data validation *except* in the data layer. YMMV.
>
Aaron,
I agree with you here... So out of interest, would you calculate the next
value within the same SP that inserts the row, or would you us a trigger?
(or another alternative?)
Chris|||I guess it's a matter of scale; we tend to insert a lot of data at one
time, and I try to minimize the queries to my database as much as
possible. In this particular case, the trigger would not be onerous,
but I've seen some really, really bad triggers written that can suck
the life out a server. I just tend to avoid them; not that they're
always bad, but in most of our applications we try to have the data be
as clean as possible before inserting it into the database. In other
words, we do all the lookups and data prep on the business logic layer,
not in the database.
Again, it's a matter of scale; we insert a lot of data at a very high
rate of speed; the simpler the INSERT process is, the better.
Stu|||Personally, I like Itzik's solution, I just kind of cringe a bit at the
syntax:
update table set @.variable = column = column + 1;
But that's just a minor pet peeve I guess.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OSaS2Z$oFHA.568@.TK2MSFTNGP10.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> Aaron,
> I agree with you here... So out of interest, would you calculate the next
> value within the same SP that inserts the row, or would you us a trigger?
> (or another alternative?)
> Chris
>|||no magic; we just lock our data servers down pretty tight, using
application roles, etc. You are correct in that someone could bypass
our application, but we do our best to limit that possibility.
As far as validation goes, I agree. I'm just saying that validation
should be as simple as possible on the database level (e.g., is the
value with constrained parameters? Does it exist in a relationship
with other values?), and that more complex permutations should be
assigned at the business tier level before it gets written to the
database.
Stu
PS: in my previous posts, I used the term application in a broad sense,
encompassing both presentation and business logic tiers. Just wanted
to clarify.

default column value

as current time..
how can i set that in mssql 2005 ?Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
CREATE TABLE test_defaults
(keycol smallint,
process_id smallint DEFAULT @.@.SPID, --Preferred default definition
date_ins datetime DEFAULT getdate(), --Preferred default definition
mathcol smallint DEFAULT 10 * 2, --Preferred default definition
char1 char(3),
char2 char(3) DEFAULT 'xyz') --Preferred default definition;
GO
HTH. Ryan
"gary" <admin@.newsgroup.com.hk> wrote in message
news:eHFDHarEGHA.3100@.tk2msftngp13.phx.gbl...
> as current time..
> how can i set that in mssql 2005 ?
>|||Note that naming constraints is considered good practice. Sooner or later yo
u will want to change
some defaults, and if you don't know the name, you have to look up the auto-
generated name in the
system tables. This makes implementation of such scripts a mess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uwM3JhrEGHA.916@.TK2MSFTNGP10.phx.gbl...
> Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
> CREATE TABLE test_defaults
> (keycol smallint,
> process_id smallint DEFAULT @.@.SPID, --Preferred default definition
> date_ins datetime DEFAULT getdate(), --Preferred default definition
> mathcol smallint DEFAULT 10 * 2, --Preferred default definition
> char1 char(3),
> char2 char(3) DEFAULT 'xyz') --Preferred default definition;
> GO
>
> --
> HTH. Ryan
>
> "gary" <admin@.newsgroup.com.hk> wrote in message news:eHFDHarEGHA.3100@.tk2
msftngp13.phx.gbl...
>

Default additional field to identity value?

I've got a record that has an identity column and secondary identifier that I need to have default to the same value as the identity column:

id int identity(1,1) not null ,
name varchar(20) not null default CAST($IDENTITY AS VARCHAR(20))

Problem is, using @.@.identity or scope_identity() as the default for name gives me the prior insert's identity value, not the current record's value. Using an AFTER trigger doesn't work because the initial insert fails due to the not null constraint, and using an INSTEAD OF trigger does not work because the identity value is not set on the inserted row.

Is there any way to set a not-null field on a record equal to the identity value assigned to the record?

There is no way to do this declaratively. You can use a computed column instead of a persisted column if the name column is just string representation of the identity value. Do you allow the name value to be modified later? If so then you will have to use a trigger to update the value and set default to 0 or -1.sql