delete duplicate record problem

create table tbl1(
fld1 varchar(10),
fld2 varchar(10))
insert into tbl1 Values('joe','x')
insert into tbl1 Values('joe','y')
insert into tbl1 Values('joe','z')
insert into tbl1 Values('bill','x')
insert into tbl1 Values('sam','z')
insert into tbl1 Values('ted','x')
insert into tbl1 Values('ted','y')
insert into tbl1 Values('ed','x')
insert into tbl1 Values('mary','x')
insert into tbl1 Values('sue','z')
insert into tbl1 Values('tom','x')
insert into tbl1 Values('tom','y')
insert into tbl1 Values('tom','z')
insert into tbl1 Values('frank','x')
insert into tbl1 Values('samir','x')
insert into tbl1 Values('li','x')
insert into tbl1 Values('li','z')
insert into tbl1 Values('cindy','z')
insert into tbl1 Values('bert','x')
insert into tbl1 Values('jon','x')
--the statement below contains duplicate records.
SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 )
--I want to remove/delete the records from tbl1 where fld1 has count(fld1)
> 1 and fld2 = 'z' -- joe, x, joe, y, joe, z| tom, x, tom, y, tom, z| li, x,[/color
]
li, z.
joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld2.
I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
remove rows where count(fld1)>1 and fld2 = 'z'.
--Here is what I tried that did not work:
Delete from tbl1
Where fld2 in
(SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 ))
and fld2 = 'z'
Any suggestions appreciated how this could be accomplished.
Thanks,
RichTry THis:
Delete tbl1
Where fld1 In
(SELECT fld1
FROM tbl1
GROUP BY fld1
HAVING Count(*)>1 )
And Fld2 = 'z'
"Rich" wrote:

> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich|||Thanks very much.
"CBretana" wrote:
> Try THis:
> Delete tbl1
> Where fld1 In
> (SELECT fld1
> FROM tbl1
> GROUP BY fld1
> HAVING Count(*)>1 )
> And Fld2 = 'z'
> "Rich" wrote:
>|||And for the future, use UNIQUE constraints to ensure your data doesn't get
this way in the first place.
-- Alex
"Rich" wrote:

> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich

Delay Insert

I am parsing a file where along the flow I use a conditional split. One path of the split is the primary table (with IDENTITY) values. The rest of the paths have a FOREIGN KEY to the primary table.

It seems that SSIS is trying to insert the rows at the same time (which makes sense) but this is causing a problem with the secondary tables and their FK constraint since the primary table is not yet written.

Is there a way to delay the secondary tables until the primary table is done?

(I guess one way is to run through the file twice... once for the primary table and another for the rest but that seems wasteful to me...)

Thanks.

There is no way to delay paths inside a data flow, or set any precedence. One option I like is to stage the "secondary" data in a raw file. This is very efficient compared to most source and destination combinations. In your current Data Flow write the secondary data to a raw file then add another Data Flow task, with a raw file source and the your final destination.

define/set parameter values in Management Studio?

If you have a query w/ a parameter in it (copied from reporting services, but now working with management studio), how do you declare the parameter and set its value in sql server management studio? I've had to unfortunately find all occurrences of the parameters and replace their values each time I want to change them when executing the mdx.

Unfortunately, I don't believe their is an easy and straightforward way to do this. About the only option I've been able to find is wrapping the MDX query in an XMLA query, which allows you to have parameters and define their values. The problem with this approach is that the result of the XMLA query is an XML response which contains a lot of metadata as well as the data (but it is not in any type of format that would allow you to easily look at just the query results).

Here's a link to a topic in BOL that shows an example of this:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/a4754d16-d9c4-49f6-9be0-392180b912e4.htm

If your query is a relatively simple one that returns a relatively simple result, this approach might work...

HTH,

Dave Fackler

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
Regards
Hello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

defaults in the DB

How do i know whats the default values on which table and which column and whats the default value

It should be like this

Table column constraint_name value

emp emp_id DF_emp_emp_no 1
emp join_date DF_emp_cur_date getdate()

How can i get these results on all the tables in my DB.

Thanks.sp_help '<table_name>'

After the column list of index(es), the proc will list any constraints by column name.|||select object_name(c.id)+'.'+col_name(c.id, c.colid)+' - '+
object_name(c.constid)+' value: '+text
from sysconstraints c
inner join syscomments sc on c.constid = sc.id
where(c.status & 5)= 5
and objectproperty(c.id,'IsMSShipped')= 0
order by object_name(c.id)|||Buy that man a drink!

Defaulting Date Parameter

Hello,

I have a report parameter StartDate. Properties are

DataTypeataTime

Prompt: StartDate

Default Values:

Non Queried : =NOW()

I set the default value to Now(). When I go to preview, the StartDate parameter is blank and its been locked & grayed out. I also tried

Today() and Globals!SystemTime but that does not work either. Is there any other solution to make this work?

Thanks

Raj

I have not seen any responses on this. Wanted to see if there are any ideas/ thoughts on this.|||

Try:

Code Snippet

=cDate(FormatDateTime(Now, DateFormat.ShortDate))

Larry|||HI,
You can give the following expression for the default value of startdate;
DateValue(now()).

Cheers,
Shri|||

Is this the only parameter in your report? If it is, then you shouldn't be having any problems.

If not, try entering the values for all the parameters that come before StartDate. Then you should be able to see the default value & it should not be greyed out any more.

-Aayush

Default Values properties (table level) not working.

I am using SQL Server Management Studio Express (SSMSE) with SQL Server Express as my database tools/database to assign the ‘Default Value’ for a column at the table level.

Going over the basics… using the database tools (SSMSE) and when inserting a new row; all rows by default have a 'Null' value. Ok.
If a default value is assigned to a column (table level) using the database tools, the default value is inserted correctly if that column has a null value upon the creation of a new row. Ok.

This works fine when I am working with SSMSE on tables (inserting, deleting editing rows etc.) within my database…

But this doesn’t apply or work for adding new rows with datasets (example: using the default insert, update, delete statements provided by the wizard and using a DataGridView). My table level default values are not inserted into the new row, instead my column that had a default value assigned; now has a 'Null' value in the new row that was created by the dataset.

Isn’t a Null value is still a Null value for a new row?

Shouldn’t the database engine supply that ‘default value’ for a field that had a ‘Null’ value upon row creation?

I have always thought of a table level column ‘Default Value property’ as a trigger that tests for nulls and inserts the default value if that column has a null value when the new row is created. So I am expecting the database engine to insert the default value for that column, not the dataset when the value inserted into that column is null for a new row. I really don't need a (table level) column default property that only works with database tools for inserting new rows, that doesn't help me... totally baffled here...
Thanks

Hey Rick.

Default values will be applied to a column when NO explicit value is specified for the column in the corresponding insert (this includes a <NULL> explicit value)...so, for example, assume I have a table with 2 columns, colA and colB, and on colB I have a default value of 'colBDefault' specified...the following statement will end up with a record that includes a row with 'colAvalue' for colA, and null for colB, because I am explicitly saying to use a null value for colB:

insert table (colA, colB) select 'colAvalue', null

However, the following statement will end up with a value of 'colAvalue' for colA, and the default value of 'colBDefault' for colB, because no explicit value is specified for colB:

insert table (colA) select 'colAvalue'

I'd bet that the DataGridView is specifying all columns with a null value for anything you don't specify. To prove this, you could run a trace on the Sql server to see what the actual insert command being executed is...

HTH,

|||Hello Chad,

Thanks for the reply. That did help.

Unfortunately I couldn't get the ADO.Net trace logging to work... my tracing abilities are pretty much nil...

Another way to look at this is I am only pulling certain text fields that I want (no default value assigned) from the adapter / dataset for that table, not all of the fields from that particlular table.
The fields that I have designated a default value for are not included in the dataset, so they do not have an insert command etc. (or value assigned) for those fields for that table.
But... those fields that are *not* included in the insert statement etc. for that table do in fact show a value of 'Null' for the new row even though they have a default value assigned for them at the table level...

Example:
Fields: (ID), (LastName), (FirstName), ((Age) - default value set to 0), ((DeptNo) - default set to 100)

The adapter is only pulling fields: (ID), (LastName) and (FirstName); the insert etc. commands only pertain to those fields.
When a new row is inserted fields: (Age) and (DeptNo) do show a 'Null' value, not their assigned default value.

Thanks,
Rick

|||Whooooops...

My apologies!

It does work as you suggested!
I literally had six different forms to test things and simply got them mixed up, of what worked and what didn't!!!

Thanks,
Rick

Default Values not Selected on Report Manager

I have a report with quite a few parameters, both multivalue and single, all populated from a query. In the Report Designer I have set up default values and everything works great. When I deploy these reports the default values are not being set. Am I missing a step?It's a SQL report or an MDX report?

In the report manager when you choose the parameters and execute the report. It's rendered fine?|||

The server has behavior that it keeps the old parameter defaults that are set, even after republishing. Try deleting the report and republishing.

|||I have not seen the Report Server maintain default parameter values when a report is redeployed. After redeploying the report I have to manually reset the defaults (Properties tab --> Parameters page --> Override Default button).

If there is some property of the server that would persist the defaults between deployments, I'd love to learn about it. Thanks.

Default values in tables

Hello all,
I thought that I knew how to do this, but I must be having a mental
block. I have a SQL table, populated with 14,000 records. I have
several int columns which I need to change the default value to 0
(zero), and a datetime column which I would like to default to todays
date and current time. I have gone into design view for each of the
int columns and I have simply entered 0 in the "Default value" box.
For the datetime column, I have changed the Default Value to
getdate().
I have gone to enter a new record in the table, and none of the
defaults work. All still default to <null>.
Any ideas why this is not working? Does it only work on a table
without existing data?
Thanks
Colin
Bobby
It does work
CREATE TABLE #Test (c1 datetime DEFAULT GETDATE(),c2 INT DEFAULT 0)
INSERT INTO #Test DEFAULT VALUES
SELECT * FROM #Test
Check out that you have created default constraints
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('tblname')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174466348.874762.198710@.e1g2000hsg.googlegro ups.com...
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
>
|||On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
Existing data values in columns will not change when you introduce
default values. Only new records (insertions) will have default
values .
If you want to change for the existing records , you need to update
the value.
After updating the value , alter table not to allow nulls on these
columns
|||Hi[vbcol=seagreen]
He said that he inserted a new row as I understood it.
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1174467529.301295.252230@.l77g2000hsb.googlegr oups.com...
> On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Existing data values in columns will not change when you introduce
> default values. Only new records (insertions) will have default
> values .
> If you want to change for the existing records , you need to update
> the value.
> After updating the value , alter table not to allow nulls on these
> columns
>
|||On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> He said that he inserted a new row as I understood it.
>
That's correct. I didn't mean that I wanted to change existing values,
what I meant was will default values only work on tables without
existing data.
However, I know now that they will work on tables with existing data.
I guess I've been working with Access too long. In Access, if I set
default values and then go to insert a new record directly into the
table, I can see the default values appear in the new row as I type.
However, if I do the same in SQL server, a load of nulls appear in the
new row, where the defaults should be. The defaults only appear after
I have closed the table and gone back into it. That's what confused
me.
Thanks for your help
Colin
|||Boddy
See my first reply to you. After setting DEFAULT constraint have save the
table's setting ? Can you show us step by step what you did so far?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174470005.140442.271260@.e65g2000hsc.googlegr oups.com...
> On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
> That's correct. I didn't mean that I wanted to change existing values,
> what I meant was will default values only work on tables without
> existing data.
> However, I know now that they will work on tables with existing data.
> I guess I've been working with Access too long. In Access, if I set
> default values and then go to insert a new record directly into the
> table, I can see the default values appear in the new row as I type.
> However, if I do the same in SQL server, a load of nulls appear in the
> new row, where the defaults should be. The defaults only appear after
> I have closed the table and gone back into it. That's what confused
> me.
> Thanks for your help
> Colin
>
|||Remember also that defaults are only assigned to columns that are NOT
part of the INSERT. If you do not use a column list you will not get
a default, even if you assign NULL.
INSERT TableName VALUES('abc', NULL)
That will only work with a two column table, and will not use defaults
for either column.
INSERT TableName (col1, col10) VALUES ('abc', NULL)
That will assign the specified values to those two columns, but assign
the default - or NULL if there is no default - to all other columns.
Roy Harvey
Beacon Falls, CT
On 21 Mar 2007 01:39:08 -0700, "Bobby" <bobby2@.blueyonder.co.uk>
wrote:

>Hello all,
>I thought that I knew how to do this, but I must be having a mental
>block. I have a SQL table, populated with 14,000 records. I have
>several int columns which I need to change the default value to 0
>(zero), and a datetime column which I would like to default to todays
>date and current time. I have gone into design view for each of the
>int columns and I have simply entered 0 in the "Default value" box.
>For the datetime column, I have changed the Default Value to
>getdate().
>I have gone to enter a new record in the table, and none of the
>defaults work. All still default to <null>.
>Any ideas why this is not working? Does it only work on a table
>without existing data?
>Thanks
>Colin

Default values in tables

Hello all,
I thought that I knew how to do this, but I must be having a mental
block. I have a SQL table, populated with 14,000 records. I have
several int columns which I need to change the default value to 0
(zero), and a datetime column which I would like to default to todays
date and current time. I have gone into design view for each of the
int columns and I have simply entered 0 in the "Default value" box.
For the datetime column, I have changed the Default Value to
getdate().
I have gone to enter a new record in the table, and none of the
defaults work. All still default to <null>.
Any ideas why this is not working? Does it only work on a table
without existing data?
Thanks
ColinBobby
It does work
CREATE TABLE #Test (c1 datetime DEFAULT GETDATE(),c2 INT DEFAULT 0)
INSERT INTO #Test DEFAULT VALUES
SELECT * FROM #Test
Check out that you have created default constraints
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('tblname')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174466348.874762.198710@.e1g2000hsg.googlegroups.com...
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
>|||On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
Existing data values in columns will not change when you introduce
default values. Only new records (insertions) will have default
values .
If you want to change for the existing records , you need to update
the value.
After updating the value , alter table not to allow nulls on these
columns|||Hi
>> I have gone to enter a new record in the table, and none of the
>> defaults work. All still default to <null>.
He said that he inserted a new row as I understood it.
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1174467529.301295.252230@.l77g2000hsb.googlegroups.com...
> On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
>> Hello all,
>> I thought that I knew how to do this, but I must be having a mental
>> block. I have a SQL table, populated with 14,000 records. I have
>> several int columns which I need to change the default value to 0
>> (zero), and a datetime column which I would like to default to todays
>> date and current time. I have gone into design view for each of the
>> int columns and I have simply entered 0 in the "Default value" box.
>> For the datetime column, I have changed the Default Value to
>> getdate().
>> I have gone to enter a new record in the table, and none of the
>> defaults work. All still default to <null>.
>> Any ideas why this is not working? Does it only work on a table
>> without existing data?
>> Thanks
>> Colin
> Existing data values in columns will not change when you introduce
> default values. Only new records (insertions) will have default
> values .
> If you want to change for the existing records , you need to update
> the value.
> After updating the value , alter table not to allow nulls on these
> columns
>|||On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> >> I have gone to enter a new record in the table, and none of the
> >> defaults work. All still default to <null>.
> He said that he inserted a new row as I understood it.
>
That's correct. I didn't mean that I wanted to change existing values,
what I meant was will default values only work on tables without
existing data.
However, I know now that they will work on tables with existing data.
I guess I've been working with Access too long. In Access, if I set
default values and then go to insert a new record directly into the
table, I can see the default values appear in the new row as I type.
However, if I do the same in SQL server, a load of nulls appear in the
new row, where the defaults should be. The defaults only appear after
I have closed the table and gone back into it. That's what confused
me.
Thanks for your help
Colin|||Boddy
See my first reply to you. After setting DEFAULT constraint have save the
table's setting ? Can you show us step by step what you did so far?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174470005.140442.271260@.e65g2000hsc.googlegroups.com...
> On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> >> I have gone to enter a new record in the table, and none of the
>> >> defaults work. All still default to <null>.
>> He said that he inserted a new row as I understood it.
> That's correct. I didn't mean that I wanted to change existing values,
> what I meant was will default values only work on tables without
> existing data.
> However, I know now that they will work on tables with existing data.
> I guess I've been working with Access too long. In Access, if I set
> default values and then go to insert a new record directly into the
> table, I can see the default values appear in the new row as I type.
> However, if I do the same in SQL server, a load of nulls appear in the
> new row, where the defaults should be. The defaults only appear after
> I have closed the table and gone back into it. That's what confused
> me.
> Thanks for your help
> Colin
>|||Remember also that defaults are only assigned to columns that are NOT
part of the INSERT. If you do not use a column list you will not get
a default, even if you assign NULL.
INSERT TableName VALUES('abc', NULL)
That will only work with a two column table, and will not use defaults
for either column.
INSERT TableName (col1, col10) VALUES ('abc', NULL)
That will assign the specified values to those two columns, but assign
the default - or NULL if there is no default - to all other columns.
Roy Harvey
Beacon Falls, CT
On 21 Mar 2007 01:39:08 -0700, "Bobby" <bobby2@.blueyonder.co.uk>
wrote:
>Hello all,
>I thought that I knew how to do this, but I must be having a mental
>block. I have a SQL table, populated with 14,000 records. I have
>several int columns which I need to change the default value to 0
>(zero), and a datetime column which I would like to default to todays
>date and current time. I have gone into design view for each of the
>int columns and I have simply entered 0 in the "Default value" box.
>For the datetime column, I have changed the Default Value to
>getdate().
>I have gone to enter a new record in the table, and none of the
>defaults work. All still default to <null>.
>Any ideas why this is not working? Does it only work on a table
>without existing data?
>Thanks
>Colin

Default values in tables

Hello all,
I thought that I knew how to do this, but I must be having a mental
block. I have a SQL table, populated with 14,000 records. I have
several int columns which I need to change the default value to 0
(zero), and a datetime column which I would like to default to todays
date and current time. I have gone into design view for each of the
int columns and I have simply entered 0 in the "Default value" box.
For the datetime column, I have changed the Default Value to
getdate().
I have gone to enter a new record in the table, and none of the
defaults work. All still default to <null>.
Any ideas why this is not working? Does it only work on a table
without existing data?
Thanks
ColinBobby
It does work
CREATE TABLE #Test (c1 datetime DEFAULT GETDATE(),c2 INT DEFAULT 0)
INSERT INTO #Test DEFAULT VALUES
SELECT * FROM #Test
Check out that you have created default constraints
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('tblname')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174466348.874762.198710@.e1g2000hsg.googlegroups.com...
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
>|||On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
Existing data values in columns will not change when you introduce
default values. Only new records (insertions) will have default
values .
If you want to change for the existing records , you need to update
the value.
After updating the value , alter table not to allow nulls on these
columns|||Hi
He said that he inserted a new row as I understood it.
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1174467529.301295.252230@.l77g2000hsb.googlegroups.com...[vbcol=seagreen]
> On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Existing data values in columns will not change when you introduce
> default values. Only new records (insertions) will have default
> values .
> If you want to change for the existing records , you need to update
> the value.
> After updating the value , alter table not to allow nulls on these
> columns
>|||On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> He said that he inserted a new row as I understood it.
>
That's correct. I didn't mean that I wanted to change existing values,
what I meant was will default values only work on tables without
existing data.
However, I know now that they will work on tables with existing data.
I guess I've been working with Access too long. In Access, if I set
default values and then go to insert a new record directly into the
table, I can see the default values appear in the new row as I type.
However, if I do the same in SQL server, a load of nulls appear in the
new row, where the defaults should be. The defaults only appear after
I have closed the table and gone back into it. That's what confused
me.
Thanks for your help
Colin|||Boddy
See my first reply to you. After setting DEFAULT constraint have save the
table's setting ? Can you show us step by step what you did so far?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174470005.140442.271260@.e65g2000hsc.googlegroups.com...
> On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
> That's correct. I didn't mean that I wanted to change existing values,
> what I meant was will default values only work on tables without
> existing data.
> However, I know now that they will work on tables with existing data.
> I guess I've been working with Access too long. In Access, if I set
> default values and then go to insert a new record directly into the
> table, I can see the default values appear in the new row as I type.
> However, if I do the same in SQL server, a load of nulls appear in the
> new row, where the defaults should be. The defaults only appear after
> I have closed the table and gone back into it. That's what confused
> me.
> Thanks for your help
> Colin
>|||Remember also that defaults are only assigned to columns that are NOT
part of the INSERT. If you do not use a column list you will not get
a default, even if you assign NULL.
INSERT TableName VALUES('abc', NULL)
That will only work with a two column table, and will not use defaults
for either column.
INSERT TableName (col1, col10) VALUES ('abc', NULL)
That will assign the specified values to those two columns, but assign
the default - or NULL if there is no default - to all other columns.
Roy Harvey
Beacon Falls, CT
On 21 Mar 2007 01:39:08 -0700, "Bobby" <bobby2@.blueyonder.co.uk>
wrote:

>Hello all,
>I thought that I knew how to do this, but I must be having a mental
>block. I have a SQL table, populated with 14,000 records. I have
>several int columns which I need to change the default value to 0
>(zero), and a datetime column which I would like to default to todays
>date and current time. I have gone into design view for each of the
>int columns and I have simply entered 0 in the "Default value" box.
>For the datetime column, I have changed the Default Value to
>getdate().
>I have gone to enter a new record in the table, and none of the
>defaults work. All still default to <null>.
>Any ideas why this is not working? Does it only work on a table
>without existing data?
>Thanks
>Colin

default values in SQL Server 2005

I'm running the September CTP of SQL Server 2005 and I noticed that the way default values are scripted and displayed in the tools has changed.

In SQL Server 2000, if I create a table with an INT field with a default value of 0, it gets scripted as

FIELDNAME INT DEFAULT (0)

Enterprise Manager also shows (0). If you type in 0 for the default (no parentheses) and save, it converts it to (0) in the UI, and it will be scripted with one set of parentheses.

In SQL Server 2005 it seems to add an additional set of parentheses. Hence, if you type in 0 for the default in Management Studio, it changes it to ((0))

Furthermore, if you script the table you get
FIELDNAME INT DEFAULT ((0))

Is this change intended? I couldnt find any references in BOL.

Hi,

I've the same problem, do you find a solution?

Kind regards

|||

Why you are using CTP as already full version of SQL 2005 has been released in Nov-2005?

You can use the RTM edition and test, then install the SP1 in this case.

|||This does appear to be fixed in SP1. I dont think it was in the RTM build, and I'm not sure he's saying he's running the CTP. If you look at the time of my post, it was during the beta of SQL Server 2005, and as I recall, it was not fixed by RTM.|||True and I believe we wait until the second poster comes back.|||

I have the same issue with BIT fields on a server with SP1 applied

has this been addressed in SP2 ?

default values in SQL Server 2005

I'm running the September CTP of SQL Server 2005 and I noticed that the way default values are scripted and displayed in the tools has changed.

In SQL Server 2000, if I create a table with an INT field with a default value of 0, it gets scripted as

FIELDNAME INT DEFAULT (0)

Enterprise Manager also shows (0). If you type in 0 for the default (no parentheses) and save, it converts it to (0) in the UI, and it will be scripted with one set of parentheses.

In SQL Server 2005 it seems to add an additional set of parentheses. Hence, if you type in 0 for the default in Management Studio, it changes it to ((0))

Furthermore, if you script the table you get
FIELDNAME INT DEFAULT ((0))

Is this change intended? I couldnt find any references in BOL.

Hi,

I've the same problem, do you find a solution?

Kind regards

|||

Why you are using CTP as already full version of SQL 2005 has been released in Nov-2005?

You can use the RTM edition and test, then install the SP1 in this case.

|||This does appear to be fixed in SP1. I dont think it was in the RTM build, and I'm not sure he's saying he's running the CTP. If you look at the time of my post, it was during the beta of SQL Server 2005, and as I recall, it was not fixed by RTM.|||True and I believe we wait until the second poster comes back.|||

I have the same issue with BIT fields on a server with SP1 applied

has this been addressed in SP2 ?

Default Values in SQL Server

I am designing a new Database using SQL Server 2000 Enterprise Manager. I have a field called Phone, Fax and Zip.

Under Enterprise Manager I am trying to set up default values for these fields

Phone, Fax = 000-000-000
Zip = 00000

However Sql Server truncates it to 0.

How do I default value as shown about in SQL server?You must have the column set to int?

Change it to varchar(30) and you should be fine.

ScAndal|||I have Phone, Fax nvarchar(12) and Zip as nvarchar(5)

I don't know why this doesn't work !!|||Ok.. I got the solution

For default value instead of putting 000-000-000 with nvarchar(12) I put '000-000-0000' and it works fine.

1) How do I use 'Formula' field in Enterprise Manager works?
2) How do I work with various Timezone issue ?|||love,
How did you determine it is storing as 0? Did you select the data from query analyzer and it shows 0?

ScAndal|||I right clicked on table and selected 'Return All Rows' where I saw 0 instead of 00000

Another way was, once I put value in Default field as 00000 and save table, return to the same column it will indiate data as (0) for zip and (0-0-0) for phone and fax.

This and your data type indicated me that there is something wrong with my default value.

Default Values in Queries

Hello, I'm trying not to have NULL values in columns.
So I defined a default value ('n.d.') on a column named DDT.
How can i retrieve the rows with the default value in DDT column ?
I mean:
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is [DDT Column DEFAULT]
instead of :
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is NULL (or DDT='n.d.')
How does the query change if i define a Default Value DF_NULL char(4) = 'n.d
.'
and assign the defalut value of DDT column to DF_NULL ?
Thank you for help.
MicheleMichele wrote:
> Hello, I'm trying not to have NULL values in columns.
So, have you set the column to "NOT NULL"? That's the best way to prevent
NULLS from being stored in the column ...

> So I defined a default value ('n.d.') on a column named DDT.
> How can i retrieve the rows with the default value in DDT column ?
> I mean:
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is [DDT Column DEFAULT]
> instead of :
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is NULL (or DDT='n.d.')
> How does the query change if i define a Default Value DF_NULL char(4)
> = 'n.d.' and assign the defalut value of DDT column to DF_NULL ?
> Thank you for help.
> Michele
If you set the DDT column to "NOT NULL", then it will never contain NULL.
Are you saying that you won't know at runtime what the default value for DDT
is? Why wouldn't you just use:
WHERE DDT='n.d.'
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> So, have you set the column to "NOT NULL"? That's the best way to prevent
> NULLS from being stored in the column ...
Yes the column DDT is set to NOT NULL.

> Are you saying that you won't know at runtime what the default value for D
DT
> is?
Yes.

>Why wouldn't you just use:
> WHERE DDT='n.d.'
Because for some NOT NULL columns the default is 'n.d.' for others is
'<unknown>' for others '000000', so I'd like to treat the default value of
the column as a parameter for the query (if possible), like a ... where DDT
is NULL (in case of DDT column NULL, but this is not the case)
Thank's.|||Michele wrote:
> Yes the column DDT is set to NOT NULL.
>
> Yes.
>
> Because for some NOT NULL columns the default is 'n.d.' for others is
> '<unknown>' for others '000000', so I'd like to treat the default
> value of the column as a parameter for the query (if possible), like
> a ... where DDT is NULL (in case of DDT column NULL, but this is not
> the case)
>
I've never attempted to do this (I always know what the default values are
in my columns ... <g,d&r> )
I suppose you could create a scaler udf that uses the sp_columns procedure,
or queries the INFORMATION_SCHEMA.Columns table, to retrieve the column's
default value.
WHERE DDT=fColDefault(DDT)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Default values in a parameter problem

We have a report that has about five different parameters. One of the parameters is a float value and we have a default value set at 999,999,999.00 and the report parameter accepts this value. The other parameter (a string) has a default value set at 0, however it is not showing up in the textbox for that parameter. The parameter is non-queried and is just a report parameter.

When we preview the report, it is there. Once we deploy the report to the report server, it is not. Now, the development machine is using SP2, but the Report Server is still at SP1. Could this be the problem? If it is, why would the float default value display and the string default value not?

Thanks for the information.

What is the expression you use to set the default value (the one that isn't working)?|||The expression that we are using is =0. We also tried ="0".|||

Could be a simple problem of the RDL file not getting refreshed preoperly in the Report Server. Try deleting the existing RDL file and deploying it again.

Just a thought

-Aayush

|||Thanks, we will try that.|||

I agree, as we run into issues at times to, where I have to delete and re-deploy

or sometimes if I'm lazy, I just modify the parameter on Report Server report page (tab "Parameters" on the left, under "Properties")

FYI 0 is right for default parameter, not "0"

Default values for StartDate and EndDate

Hi,
I have Start date and End date as parameters. Now is there a way to have the
start date always default to the start date of the current month...i.e
1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
month & current year?
How can this be done?
Thanks
--
pmudHi,
I have solved half the question reading from other posts. For the start date
I used =DateTime.Now.Addmonths(-1) . So it went back to the previous month.
But how do i set the day to 30 ?
--
pmud
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Enter this for your (Non-Queried) parameter defaults:
StartDate... (this is in the format "m/d/yyyy" which could be changed)
=CDate(Month(Now()).ToString & "/1/" &
Year(Now()).ToString).ToShortDateString
EndDate...(This takes the first day of the next month and subtracts one day.)
=DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
))).ToShortDateString
Hoep this helps. If anyone has a better solution I'd be happy to see it.
Fred
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Hi,
I put the logic into a Stored procedure and created a new dataset in the
report designer that references this SP. Then just reference the dataset in
the report paramaters section.
It suited what I needed here as multiple reports have the same default start
and end dates so rather than creating logic in each report the single SP will
do for all the reports. But that may not suit others.
"FredP" wrote:
> Enter this for your (Non-Queried) parameter defaults:
> StartDate... (this is in the format "m/d/yyyy" which could be changed)
> =CDate(Month(Now()).ToString & "/1/" &
> Year(Now()).ToString).ToShortDateString
>
> EndDate...(This takes the first day of the next month and subtracts one day.)
> =DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
> CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
> ))).ToShortDateString
> Hoep this helps. If anyone has a better solution I'd be happy to see it.
> Fred
>
> "pmud" wrote:
> > Hi,
> >
> > I have Start date and End date as parameters. Now is there a way to have the
> > start date always default to the start date of the current month...i.e
> > 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> > month & current year?
> >
> > How can this be done?
> >
> > Thanks
> > --
> > pmud

Default values for SQL Server Data Types

Hi,

I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.

Thanks

John

ADO.NET does not have a feature where you can mark a parameter as default (unfortunately).

In general if you don't send the parameter, then the server side default will be used, so you could just omit the parameter to get the default, then add parameters later when you have values to supply. But this may mean rebuilding your parameters collection.

Also, if you need to fetch the actual defaults for a stored procedure parameters, then there is no choice but to make a trip to the server. ADO.NET is not magic. (G)