I believe this should achieve what you want to do, just replace 5 with
whatever value you want:
DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value =
5)
Adam J Warne, MCDBA
"Imager" wrote:
> Simple query for you query gurus here:
> Given two tables, related by ID_Adjacency, how can I delete all rows in
> table 1, where the related row in table 2 has some given value? ie.: with
> the following two tables, delete all records from Table1, where the relate
d
> record in Table 2 has a "Value" field with a value of 2.
>
> CREATE TABLE [dbo].[Table2] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Value] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table1] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ID_Table2] [int] NOT NULL ,
> [Value] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
> CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
> CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table2] FOREIGN KEY
> (
> [ID_Table2]
> ) REFERENCES [dbo].[Table2] (
> [ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
>On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>I believe this should achieve what you want to do, just replace 5 with
>whatever value you want:
>DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value = 5)[/col
or]
Or the more protable ANSI-stnadard version:
DELETE FROM table1
WHERE EXISTS (SELECT *
FROM table2
WHERE table2.ID = table1.ID_Table2
AND table2.value = 5)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks chaps.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5mf4g19hbk4rtf9khjg7t428kllhgeudsc@.
4ax.com...
> On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>
> Or the more protable ANSI-stnadard version:
> DELETE FROM table1
> WHERE EXISTS (SELECT *
> FROM table2
> WHERE table2.ID = table1.ID_Table2
> AND table2.value = 5)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Delete
I am trying a delete statement as follows which is taking a long time
although the tables concerned are not large.
delete from table where value not in ('a','b','c'....'s')
table as a trigger operation to delete from further child tables.
Is there any way to speed this query?
bjones wrote:
> I am trying a delete statement as follows which is taking a long time
> although the tables concerned are not large.
> delete from table where value not in ('a','b','c'....'s')
> table as a trigger operation to delete from further child tables.
> Is there any way to speed this query?
NOT IN cannot be index optimized. Every time you perform a delete, the
table has to be scanned and the value column checked against each
element in the IN clause. I'm not sure what you mean by "table as a
trigger operation to delete from further child tables". It would help if
you could provide DDL and full explanation of what you need to do.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql
although the tables concerned are not large.
delete from table where value not in ('a','b','c'....'s')
table as a trigger operation to delete from further child tables.
Is there any way to speed this query?
bjones wrote:
> I am trying a delete statement as follows which is taking a long time
> although the tables concerned are not large.
> delete from table where value not in ('a','b','c'....'s')
> table as a trigger operation to delete from further child tables.
> Is there any way to speed this query?
NOT IN cannot be index optimized. Every time you perform a delete, the
table has to be scanned and the value column checked against each
element in the IN clause. I'm not sure what you mean by "table as a
trigger operation to delete from further child tables". It would help if
you could provide DDL and full explanation of what you need to do.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql
Delete
I am trying a delete statement as follows which is taking a long time
although the tables concerned are not large.
delete from table where value not in ('a','b','c'....'s')
table as a trigger operation to delete from further child tables.
Is there any way to speed this query?bjones wrote:
> I am trying a delete statement as follows which is taking a long time
> although the tables concerned are not large.
> delete from table where value not in ('a','b','c'....'s')
> table as a trigger operation to delete from further child tables.
> Is there any way to speed this query?
NOT IN cannot be index optimized. Every time you perform a delete, the
table has to be scanned and the value column checked against each
element in the IN clause. I'm not sure what you mean by "table as a
trigger operation to delete from further child tables". It would help if
you could provide DDL and full explanation of what you need to do.
David Gugick
Quest Software
www.imceda.com
www.quest.com
although the tables concerned are not large.
delete from table where value not in ('a','b','c'....'s')
table as a trigger operation to delete from further child tables.
Is there any way to speed this query?bjones wrote:
> I am trying a delete statement as follows which is taking a long time
> although the tables concerned are not large.
> delete from table where value not in ('a','b','c'....'s')
> table as a trigger operation to delete from further child tables.
> Is there any way to speed this query?
NOT IN cannot be index optimized. Every time you perform a delete, the
table has to be scanned and the value column checked against each
element in the IN clause. I'm not sure what you mean by "table as a
trigger operation to delete from further child tables". It would help if
you could provide DDL and full explanation of what you need to do.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Delete
I am trying a delete statement as follows which is taking a long time
although the tables concerned are not large.
delete from table where value not in ('a','b','c'....'s')
table as a trigger operation to delete from further child tables.
Is there any way to speed this query?bjones wrote:
> I am trying a delete statement as follows which is taking a long time
> although the tables concerned are not large.
> delete from table where value not in ('a','b','c'....'s')
> table as a trigger operation to delete from further child tables.
> Is there any way to speed this query?
NOT IN cannot be index optimized. Every time you perform a delete, the
table has to be scanned and the value column checked against each
element in the IN clause. I'm not sure what you mean by "table as a
trigger operation to delete from further child tables". It would help if
you could provide DDL and full explanation of what you need to do.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
although the tables concerned are not large.
delete from table where value not in ('a','b','c'....'s')
table as a trigger operation to delete from further child tables.
Is there any way to speed this query?bjones wrote:
> I am trying a delete statement as follows which is taking a long time
> although the tables concerned are not large.
> delete from table where value not in ('a','b','c'....'s')
> table as a trigger operation to delete from further child tables.
> Is there any way to speed this query?
NOT IN cannot be index optimized. Every time you perform a delete, the
table has to be scanned and the value column checked against each
element in the IN clause. I'm not sure what you mean by "table as a
trigger operation to delete from further child tables". It would help if
you could provide DDL and full explanation of what you need to do.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
Monday, March 19, 2012
Defualt Value Question?
Hi,everyone
I have a report.
I List a employees in a table,I use a parameter "DepartmentID"(0-n) to
query employees dataset1.
I have other dataset2 ,the department from a sql text.
the parameter "departmentId" from dataset2,and I need a default value
"All" to list all employees.
But the default value not work.
RedmoonIf you use available values, they should include possible default value (in
your case "All'). If default value does not match any of available values it
is rejected.
--
Lev
http://blogs.msdn.com/levs
This posting is provided "AS IS" with no warranties, and confers no rights.
"Redmoon" <heavenwing@.163.com> wrote in message
news:Xns952DBA9506D1Fheavenwing163com@.207.46.248.16...
> Hi,everyone
> I have a report.
> I List a employees in a table,I use a parameter "DepartmentID"(0-n) to
> query employees dataset1.
> I have other dataset2 ,the department from a sql text.
> the parameter "departmentId" from dataset2,and I need a default value
> "All" to list all employees.
> But the default value not work.
> Redmoon
I have a report.
I List a employees in a table,I use a parameter "DepartmentID"(0-n) to
query employees dataset1.
I have other dataset2 ,the department from a sql text.
the parameter "departmentId" from dataset2,and I need a default value
"All" to list all employees.
But the default value not work.
RedmoonIf you use available values, they should include possible default value (in
your case "All'). If default value does not match any of available values it
is rejected.
--
Lev
http://blogs.msdn.com/levs
This posting is provided "AS IS" with no warranties, and confers no rights.
"Redmoon" <heavenwing@.163.com> wrote in message
news:Xns952DBA9506D1Fheavenwing163com@.207.46.248.16...
> Hi,everyone
> I have a report.
> I List a employees in a table,I use a parameter "DepartmentID"(0-n) to
> query employees dataset1.
> I have other dataset2 ,the department from a sql text.
> the parameter "departmentId" from dataset2,and I need a default value
> "All" to list all employees.
> But the default value not work.
> Redmoon
Friday, March 9, 2012
Defining the Scope of SCOPE_IDENTITY
A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.
Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.
I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).
|||Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.
How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.
I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.
Thanks for the input.
Wednesday, March 7, 2012
Defining a boolean dimension attribute
What's the best way to go about defining a dimension attribute for a boolean data value in SSAS 2005? In our first cut, we used a bit column in the database, but that results in dimension members of 0 and -1, and we'd rather have "true" and "false" displayed in the OLAP browser. In there an easy way to have SSAS map the bit values to the boolean strings?
Thanks for the response. I did think of that solution, but it has a problem for me. As an ISV, our product needs to be localizable, and embedding string translations in the DSV presents a localization problem.
You can create a named query in DSV to map the values on the fly.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for the response. I did think of that solution, but it has a problem for me. As an ISV, our product needs to be localizable, and embedding string translations in the DSV presents a localization problem.
When I do a query against a bit column using SQL Server Management Studio, it displays the contents as true/false, and in the DSV the column data type is displayed as "boolean". So it seems strange to me that SSAS can't do that string conversion automatically. Perhaps in a future release - I'll report an enhancement request for it.
I ended up solving the problem by snowflaking in a "BooleanValues" table which contains members (and corresponding names) for true and false.
|||For posterity, this looks like it's an actual bug in SSAS when using the managed SqlClient provider. When you switch the data source to use the SQL Native client, bit column values are translated to true/false automatically. I've reported it on Product Feedback.
Defining a "partial" foreign Key..how could I ?
I have a table A, which has 3 fields:
- code, subcode and description (code and subcode are PK)
and a table B which has 2 fields:
- date, code, value (date and code are PK)
So far, no problem. It comes up when I try to define code in table B as foreign key pointing to code in table A, because table A has code and subcode as its PK, not only code...but, I need only code to be in table B. How could I overcome this?
Well this is kind of design issue...
You can't do it directly.. Your table design is wrong.
Try to use the following design:
CodeMaster :: code, date, value , codedescription
Here,
Code is Primary Key
SubCodeMaster :: code, subcode, subcodedescription
Here,
Code references CodeMaster::Code
Code, Subcode is Primary Key
Well, unless the code column is unique in an of itself (thus making it the PK), you can't (and you shouldn't). If it is, you could apply a unique constraint to the code column, but that would be silly.
In all likelihood, you actually need a table for a code itself that relates to both tables. This would be the proper solution, since a code is a different thing than a subcode, and any code info would have to be repeate on each row
You can enforce this with triggers too, but I wouldn't suggest that as the "best" case.
define a default value ?
hallo
i am on the very beginning with sql.s,
how can i define a default value for the table field like its possible in access,
for example: the if the user not gonna enter a val so there is a default val predefined in the table field,
for example i would like to predefine the date now on the smalldate field....
thanks
maybe something like:
Code Snippet
create table dbo.yourTable
( anInteger int default (0)
)
Give a look to books online.
|||so if i would like to predefine the date now on the smalldate field its will look like this
table dbo.myTable
( anInteger smalldate default (0)=new
)
|||No, that would be more like:
createtable dbo.myTable
( anInteger smalldate default ( getdate() )
)
|||thanksSaturday, February 25, 2012
Defaut Value for new column in replication
Hi,
I am dealing with merge replication.
I need to add a new column in a table (already in replication) has 500000
records.
I have set 0 as default value for that column.
I want to update that column with a return value of a user-defined function.
Is it possible to call a user-defined function as a default value?
So that it will get sync while adding the column in replication else I have
to update the column separately. Same to be replicated for 20 databases
results in huge amount of data.
Is there any alternative for this?
Please advice.
Thanks,
Soura.
Hi Sounder,
Yes. U can give the UDF as a Default value.
U can do it as a normal default one. In the default Value column specify the
UDF name with the corresponding Owner name and pass the values for parameter.
Thanks,
Herbert
"SouRa" wrote:
> Hi,
> I am dealing with merge replication.
> I need to add a new column in a table (already in replication) has 500000
> records.
> I have set 0 as default value for that column.
> I want to update that column with a return value of a user-defined function.
> Is it possible to call a user-defined function as a default value?
> So that it will get sync while adding the column in replication else I have
> to update the column separately. Same to be replicated for 20 databases
> results in huge amount of data.
> Is there any alternative for this?
> Please advice.
> Thanks,
> Soura.
>
I am dealing with merge replication.
I need to add a new column in a table (already in replication) has 500000
records.
I have set 0 as default value for that column.
I want to update that column with a return value of a user-defined function.
Is it possible to call a user-defined function as a default value?
So that it will get sync while adding the column in replication else I have
to update the column separately. Same to be replicated for 20 databases
results in huge amount of data.
Is there any alternative for this?
Please advice.
Thanks,
Soura.
Hi Sounder,
Yes. U can give the UDF as a Default value.
U can do it as a normal default one. In the default Value column specify the
UDF name with the corresponding Owner name and pass the values for parameter.
Thanks,
Herbert
"SouRa" wrote:
> Hi,
> I am dealing with merge replication.
> I need to add a new column in a table (already in replication) has 500000
> records.
> I have set 0 as default value for that column.
> I want to update that column with a return value of a user-defined function.
> Is it possible to call a user-defined function as a default value?
> So that it will get sync while adding the column in replication else I have
> to update the column separately. Same to be replicated for 20 databases
> results in huge amount of data.
> Is there any alternative for this?
> Please advice.
> Thanks,
> Soura.
>
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 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.
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.
Friday, February 24, 2012
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 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)
Default values for report parameters gets lost in Report Server
I have a report which takes few parameters. I have set some defalut value for
them. The default values work good when i Preview teh report. But when i
deploy the report to the Report Server, the default values get lost and the
user has to type them all again. Any clue on how to solve this issue?I think you can try setting the default value in VS (designer) as
="Yes" instead of Yes
see if that works.
That being said, I am having some difficulty because it appears setting
things in the designer (for parameters) and setting parameter properties on
RS behaves in different ways. The prompt user, prompt string, default value
etc. seems to not be communicating clearly with what was set in the
designer. Example - if you have a date parameter, and in the designer you
set the default to be =Left(today, 10) - an expression, in other words - when
you go to look in RS and tweak the parameter properties it has default value
checked, but it does not sjow the expression and it doesn't see any valid
default value - and gives an error.
"anandr" wrote:
> I have a report which takes few parameters. I have set some defalut value for
> them. The default values work good when i Preview teh report. But when i
> deploy the report to the Report Server, the default values get lost and the
> user has to type them all again. Any clue on how to solve this issue?|||The reason you aren't seeing any value in the Default Value box is because
your default value is an expression. The Default Value textbox on the
Parameters property page only displays default values when they are strings
and not expressions (this does not mean that you do not have a default value
when it is an expression). The reason you might be getting an error is if
your parameter type is date, you're returning a string instead of a date. I
don't know what your parameter type is, but it is just a thought. Hope that
helps you a bit.
David
"Myles" wrote:
> I think you can try setting the default value in VS (designer) as
> ="Yes" instead of Yes
> see if that works.
> That being said, I am having some difficulty because it appears setting
> things in the designer (for parameters) and setting parameter properties on
> RS behaves in different ways. The prompt user, prompt string, default value
> etc. seems to not be communicating clearly with what was set in the
> designer. Example - if you have a date parameter, and in the designer you
> set the default to be =Left(today, 10) - an expression, in other words - when
> you go to look in RS and tweak the parameter properties it has default value
> checked, but it does not sjow the expression and it doesn't see any valid
> default value - and gives an error.
>
> "anandr" wrote:
> > I have a report which takes few parameters. I have set some defalut value for
> > them. The default values work good when i Preview teh report. But when i
> > deploy the report to the Report Server, the default values get lost and the
> > user has to type them all again. Any clue on how to solve this issue?|||Thanks David -
actually, the parm type is a string - I believe that is the only way
something like
=Left(today, 10) would work, because it is a string function (basically).
It is trying, though, when you open up the parameters page of the properties
tab on http://<Server>/Reports and fiddle with a completely different
parameter and try to apply the changes, that it gives you the error on any
string type parameter that has an expression based default value. I would
think either you can have an expression as a default value, or you can't -
the ability to assign the default through the designer makes it appear that
that is ok, while later fiddling through RS makes it seem like it is wrong -
do you think possibly it's a bug?
Thanks,
Pete
"David Siebert" wrote:
> The reason you aren't seeing any value in the Default Value box is because
> your default value is an expression. The Default Value textbox on the
> Parameters property page only displays default values when they are strings
> and not expressions (this does not mean that you do not have a default value
> when it is an expression). The reason you might be getting an error is if
> your parameter type is date, you're returning a string instead of a date. I
> don't know what your parameter type is, but it is just a thought. Hope that
> helps you a bit.
> David
> "Myles" wrote:
> > I think you can try setting the default value in VS (designer) as
> >
> > ="Yes" instead of Yes
> >
> > see if that works.
> >
> > That being said, I am having some difficulty because it appears setting
> > things in the designer (for parameters) and setting parameter properties on
> > RS behaves in different ways. The prompt user, prompt string, default value
> > etc. seems to not be communicating clearly with what was set in the
> > designer. Example - if you have a date parameter, and in the designer you
> > set the default to be =Left(today, 10) - an expression, in other words - when
> > you go to look in RS and tweak the parameter properties it has default value
> > checked, but it does not sjow the expression and it doesn't see any valid
> > default value - and gives an error.
> >
> >
> > "anandr" wrote:
> >
> > > I have a report which takes few parameters. I have set some defalut value for
> > > them. The default values work good when i Preview teh report. But when i
> > > deploy the report to the Report Server, the default values get lost and the
> > > user has to type them all again. Any clue on how to solve this issue?|||I've noticed this also. I've gotten around it by unchecking the Has Default
boxes for any parameters that have a calculated value. The calculated
default values will still be there after you apply changes, and if you go
back to the properties page, you should see that the Has Default boxes are
checked again. I don't know if I would really consider this a major bug, but
a bug it may be. You can definitely use expressions as default values, but I
will caution you that using them can cause odd behavior when editing the
default values when actually running the report. If you have defaults that
are expressions and you change a parameter value, it will recalculate any
calculated default values for all the parameters after the one changed. This
is in order to allow cascading parameters, and can't really be changed as far
as I know. I actually use calculated values a lot (for dates e.g. beginning
of month, end of month, two weeks ago, etc.) but in order to get around the
odd behavior of cascading parameters, I will use sql to get the default
dates, and then set the default in VS to "From Query" and this manages to
work around the recalculation issue.
Hope this helps.
David
"Myles" wrote:
> Thanks David -
> actually, the parm type is a string - I believe that is the only way
> something like
> =Left(today, 10) would work, because it is a string function (basically).
> It is trying, though, when you open up the parameters page of the properties
> tab on http://<Server>/Reports and fiddle with a completely different
> parameter and try to apply the changes, that it gives you the error on any
> string type parameter that has an expression based default value. I would
> think either you can have an expression as a default value, or you can't -
> the ability to assign the default through the designer makes it appear that
> that is ok, while later fiddling through RS makes it seem like it is wrong -
> do you think possibly it's a bug?
> Thanks,
> Pete
>
> "David Siebert" wrote:
> > The reason you aren't seeing any value in the Default Value box is because
> > your default value is an expression. The Default Value textbox on the
> > Parameters property page only displays default values when they are strings
> > and not expressions (this does not mean that you do not have a default value
> > when it is an expression). The reason you might be getting an error is if
> > your parameter type is date, you're returning a string instead of a date. I
> > don't know what your parameter type is, but it is just a thought. Hope that
> > helps you a bit.
> >
> > David
> >
> > "Myles" wrote:
> >
> > > I think you can try setting the default value in VS (designer) as
> > >
> > > ="Yes" instead of Yes
> > >
> > > see if that works.
> > >
> > > That being said, I am having some difficulty because it appears setting
> > > things in the designer (for parameters) and setting parameter properties on
> > > RS behaves in different ways. The prompt user, prompt string, default value
> > > etc. seems to not be communicating clearly with what was set in the
> > > designer. Example - if you have a date parameter, and in the designer you
> > > set the default to be =Left(today, 10) - an expression, in other words - when
> > > you go to look in RS and tweak the parameter properties it has default value
> > > checked, but it does not sjow the expression and it doesn't see any valid
> > > default value - and gives an error.
> > >
> > >
> > > "anandr" wrote:
> > >
> > > > I have a report which takes few parameters. I have set some defalut value for
> > > > them. The default values work good when i Preview teh report. But when i
> > > > deploy the report to the Report Server, the default values get lost and the
> > > > user has to type them all again. Any clue on how to solve this issue?|||Excellent David - thank you! Yes I noticed if you un-check the 'has default
value' check box, the problem does go away and you retain the expression
based value. This is good to know! Thanks!
Pete
"David Siebert" wrote:
> I've noticed this also. I've gotten around it by unchecking the Has Default
> boxes for any parameters that have a calculated value. The calculated
> default values will still be there after you apply changes, and if you go
> back to the properties page, you should see that the Has Default boxes are
> checked again. I don't know if I would really consider this a major bug, but
> a bug it may be. You can definitely use expressions as default values, but I
> will caution you that using them can cause odd behavior when editing the
> default values when actually running the report. If you have defaults that
> are expressions and you change a parameter value, it will recalculate any
> calculated default values for all the parameters after the one changed. This
> is in order to allow cascading parameters, and can't really be changed as far
> as I know. I actually use calculated values a lot (for dates e.g. beginning
> of month, end of month, two weeks ago, etc.) but in order to get around the
> odd behavior of cascading parameters, I will use sql to get the default
> dates, and then set the default in VS to "From Query" and this manages to
> work around the recalculation issue.
> Hope this helps.
> David
> "Myles" wrote:
> > Thanks David -
> >
> > actually, the parm type is a string - I believe that is the only way
> > something like
> > =Left(today, 10) would work, because it is a string function (basically).
> > It is trying, though, when you open up the parameters page of the properties
> > tab on http://<Server>/Reports and fiddle with a completely different
> > parameter and try to apply the changes, that it gives you the error on any
> > string type parameter that has an expression based default value. I would
> > think either you can have an expression as a default value, or you can't -
> > the ability to assign the default through the designer makes it appear that
> > that is ok, while later fiddling through RS makes it seem like it is wrong -
> > do you think possibly it's a bug?
> >
> > Thanks,
> >
> > Pete
> >
> >
> >
> > "David Siebert" wrote:
> >
> > > The reason you aren't seeing any value in the Default Value box is because
> > > your default value is an expression. The Default Value textbox on the
> > > Parameters property page only displays default values when they are strings
> > > and not expressions (this does not mean that you do not have a default value
> > > when it is an expression). The reason you might be getting an error is if
> > > your parameter type is date, you're returning a string instead of a date. I
> > > don't know what your parameter type is, but it is just a thought. Hope that
> > > helps you a bit.
> > >
> > > David
> > >
> > > "Myles" wrote:
> > >
> > > > I think you can try setting the default value in VS (designer) as
> > > >
> > > > ="Yes" instead of Yes
> > > >
> > > > see if that works.
> > > >
> > > > That being said, I am having some difficulty because it appears setting
> > > > things in the designer (for parameters) and setting parameter properties on
> > > > RS behaves in different ways. The prompt user, prompt string, default value
> > > > etc. seems to not be communicating clearly with what was set in the
> > > > designer. Example - if you have a date parameter, and in the designer you
> > > > set the default to be =Left(today, 10) - an expression, in other words - when
> > > > you go to look in RS and tweak the parameter properties it has default value
> > > > checked, but it does not sjow the expression and it doesn't see any valid
> > > > default value - and gives an error.
> > > >
> > > >
> > > > "anandr" wrote:
> > > >
> > > > > I have a report which takes few parameters. I have set some defalut value for
> > > > > them. The default values work good when i Preview teh report. But when i
> > > > > deploy the report to the Report Server, the default values get lost and the
> > > > > user has to type them all again. Any clue on how to solve this issue?|||Where is the "Has Default" checkbox? I could not locate it in "Report
Parameters" screen
"David Siebert" wrote:
> I've noticed this also. I've gotten around it by unchecking the Has Default
> boxes for any parameters that have a calculated value. The calculated
> default values will still be there after you apply changes, and if you go
> back to the properties page, you should see that the Has Default boxes are
> checked again. I don't know if I would really consider this a major bug, but
> a bug it may be. You can definitely use expressions as default values, but I
> will caution you that using them can cause odd behavior when editing the
> default values when actually running the report. If you have defaults that
> are expressions and you change a parameter value, it will recalculate any
> calculated default values for all the parameters after the one changed. This
> is in order to allow cascading parameters, and can't really be changed as far
> as I know. I actually use calculated values a lot (for dates e.g. beginning
> of month, end of month, two weeks ago, etc.) but in order to get around the
> odd behavior of cascading parameters, I will use sql to get the default
> dates, and then set the default in VS to "From Query" and this manages to
> work around the recalculation issue.
> Hope this helps.
> David
> "Myles" wrote:
> > Thanks David -
> >
> > actually, the parm type is a string - I believe that is the only way
> > something like
> > =Left(today, 10) would work, because it is a string function (basically).
> > It is trying, though, when you open up the parameters page of the properties
> > tab on http://<Server>/Reports and fiddle with a completely different
> > parameter and try to apply the changes, that it gives you the error on any
> > string type parameter that has an expression based default value. I would
> > think either you can have an expression as a default value, or you can't -
> > the ability to assign the default through the designer makes it appear that
> > that is ok, while later fiddling through RS makes it seem like it is wrong -
> > do you think possibly it's a bug?
> >
> > Thanks,
> >
> > Pete
> >
> >
> >
> > "David Siebert" wrote:
> >
> > > The reason you aren't seeing any value in the Default Value box is because
> > > your default value is an expression. The Default Value textbox on the
> > > Parameters property page only displays default values when they are strings
> > > and not expressions (this does not mean that you do not have a default value
> > > when it is an expression). The reason you might be getting an error is if
> > > your parameter type is date, you're returning a string instead of a date. I
> > > don't know what your parameter type is, but it is just a thought. Hope that
> > > helps you a bit.
> > >
> > > David
> > >
> > > "Myles" wrote:
> > >
> > > > I think you can try setting the default value in VS (designer) as
> > > >
> > > > ="Yes" instead of Yes
> > > >
> > > > see if that works.
> > > >
> > > > That being said, I am having some difficulty because it appears setting
> > > > things in the designer (for parameters) and setting parameter properties on
> > > > RS behaves in different ways. The prompt user, prompt string, default value
> > > > etc. seems to not be communicating clearly with what was set in the
> > > > designer. Example - if you have a date parameter, and in the designer you
> > > > set the default to be =Left(today, 10) - an expression, in other words - when
> > > > you go to look in RS and tweak the parameter properties it has default value
> > > > checked, but it does not sjow the expression and it doesn't see any valid
> > > > default value - and gives an error.
> > > >
> > > >
> > > > "anandr" wrote:
> > > >
> > > > > I have a report which takes few parameters. I have set some defalut value for
> > > > > them. The default values work good when i Preview teh report. But when i
> > > > > deploy the report to the Report Server, the default values get lost and the
> > > > > user has to type them all again. Any clue on how to solve this issue?|||I have run into the same issue.
I have defined 6 possible values for a parameter in a report. They are team
names - so they would look like:
Team 1
Team 2
and so on... I have defined Team 1 as the default selection in each report.
(by setting the default, unqueried value to: Team 1. If I use ="Team 1" or
something like that then it errors. And this appears to work correctly when
using the vs dev env. However when the report is published the selection
for team does not default to Team 1, it defaults to <Select A Value>.
Any ideas?
Thanks.
--Cory
"anandr" wrote:
> I have a report which takes few parameters. I have set some defalut value for
> them. The default values work good when i Preview teh report. But when i
> deploy the report to the Report Server, the default values get lost and the
> user has to type them all again. Any clue on how to solve this issue?
them. The default values work good when i Preview teh report. But when i
deploy the report to the Report Server, the default values get lost and the
user has to type them all again. Any clue on how to solve this issue?I think you can try setting the default value in VS (designer) as
="Yes" instead of Yes
see if that works.
That being said, I am having some difficulty because it appears setting
things in the designer (for parameters) and setting parameter properties on
RS behaves in different ways. The prompt user, prompt string, default value
etc. seems to not be communicating clearly with what was set in the
designer. Example - if you have a date parameter, and in the designer you
set the default to be =Left(today, 10) - an expression, in other words - when
you go to look in RS and tweak the parameter properties it has default value
checked, but it does not sjow the expression and it doesn't see any valid
default value - and gives an error.
"anandr" wrote:
> I have a report which takes few parameters. I have set some defalut value for
> them. The default values work good when i Preview teh report. But when i
> deploy the report to the Report Server, the default values get lost and the
> user has to type them all again. Any clue on how to solve this issue?|||The reason you aren't seeing any value in the Default Value box is because
your default value is an expression. The Default Value textbox on the
Parameters property page only displays default values when they are strings
and not expressions (this does not mean that you do not have a default value
when it is an expression). The reason you might be getting an error is if
your parameter type is date, you're returning a string instead of a date. I
don't know what your parameter type is, but it is just a thought. Hope that
helps you a bit.
David
"Myles" wrote:
> I think you can try setting the default value in VS (designer) as
> ="Yes" instead of Yes
> see if that works.
> That being said, I am having some difficulty because it appears setting
> things in the designer (for parameters) and setting parameter properties on
> RS behaves in different ways. The prompt user, prompt string, default value
> etc. seems to not be communicating clearly with what was set in the
> designer. Example - if you have a date parameter, and in the designer you
> set the default to be =Left(today, 10) - an expression, in other words - when
> you go to look in RS and tweak the parameter properties it has default value
> checked, but it does not sjow the expression and it doesn't see any valid
> default value - and gives an error.
>
> "anandr" wrote:
> > I have a report which takes few parameters. I have set some defalut value for
> > them. The default values work good when i Preview teh report. But when i
> > deploy the report to the Report Server, the default values get lost and the
> > user has to type them all again. Any clue on how to solve this issue?|||Thanks David -
actually, the parm type is a string - I believe that is the only way
something like
=Left(today, 10) would work, because it is a string function (basically).
It is trying, though, when you open up the parameters page of the properties
tab on http://<Server>/Reports and fiddle with a completely different
parameter and try to apply the changes, that it gives you the error on any
string type parameter that has an expression based default value. I would
think either you can have an expression as a default value, or you can't -
the ability to assign the default through the designer makes it appear that
that is ok, while later fiddling through RS makes it seem like it is wrong -
do you think possibly it's a bug?
Thanks,
Pete
"David Siebert" wrote:
> The reason you aren't seeing any value in the Default Value box is because
> your default value is an expression. The Default Value textbox on the
> Parameters property page only displays default values when they are strings
> and not expressions (this does not mean that you do not have a default value
> when it is an expression). The reason you might be getting an error is if
> your parameter type is date, you're returning a string instead of a date. I
> don't know what your parameter type is, but it is just a thought. Hope that
> helps you a bit.
> David
> "Myles" wrote:
> > I think you can try setting the default value in VS (designer) as
> >
> > ="Yes" instead of Yes
> >
> > see if that works.
> >
> > That being said, I am having some difficulty because it appears setting
> > things in the designer (for parameters) and setting parameter properties on
> > RS behaves in different ways. The prompt user, prompt string, default value
> > etc. seems to not be communicating clearly with what was set in the
> > designer. Example - if you have a date parameter, and in the designer you
> > set the default to be =Left(today, 10) - an expression, in other words - when
> > you go to look in RS and tweak the parameter properties it has default value
> > checked, but it does not sjow the expression and it doesn't see any valid
> > default value - and gives an error.
> >
> >
> > "anandr" wrote:
> >
> > > I have a report which takes few parameters. I have set some defalut value for
> > > them. The default values work good when i Preview teh report. But when i
> > > deploy the report to the Report Server, the default values get lost and the
> > > user has to type them all again. Any clue on how to solve this issue?|||I've noticed this also. I've gotten around it by unchecking the Has Default
boxes for any parameters that have a calculated value. The calculated
default values will still be there after you apply changes, and if you go
back to the properties page, you should see that the Has Default boxes are
checked again. I don't know if I would really consider this a major bug, but
a bug it may be. You can definitely use expressions as default values, but I
will caution you that using them can cause odd behavior when editing the
default values when actually running the report. If you have defaults that
are expressions and you change a parameter value, it will recalculate any
calculated default values for all the parameters after the one changed. This
is in order to allow cascading parameters, and can't really be changed as far
as I know. I actually use calculated values a lot (for dates e.g. beginning
of month, end of month, two weeks ago, etc.) but in order to get around the
odd behavior of cascading parameters, I will use sql to get the default
dates, and then set the default in VS to "From Query" and this manages to
work around the recalculation issue.
Hope this helps.
David
"Myles" wrote:
> Thanks David -
> actually, the parm type is a string - I believe that is the only way
> something like
> =Left(today, 10) would work, because it is a string function (basically).
> It is trying, though, when you open up the parameters page of the properties
> tab on http://<Server>/Reports and fiddle with a completely different
> parameter and try to apply the changes, that it gives you the error on any
> string type parameter that has an expression based default value. I would
> think either you can have an expression as a default value, or you can't -
> the ability to assign the default through the designer makes it appear that
> that is ok, while later fiddling through RS makes it seem like it is wrong -
> do you think possibly it's a bug?
> Thanks,
> Pete
>
> "David Siebert" wrote:
> > The reason you aren't seeing any value in the Default Value box is because
> > your default value is an expression. The Default Value textbox on the
> > Parameters property page only displays default values when they are strings
> > and not expressions (this does not mean that you do not have a default value
> > when it is an expression). The reason you might be getting an error is if
> > your parameter type is date, you're returning a string instead of a date. I
> > don't know what your parameter type is, but it is just a thought. Hope that
> > helps you a bit.
> >
> > David
> >
> > "Myles" wrote:
> >
> > > I think you can try setting the default value in VS (designer) as
> > >
> > > ="Yes" instead of Yes
> > >
> > > see if that works.
> > >
> > > That being said, I am having some difficulty because it appears setting
> > > things in the designer (for parameters) and setting parameter properties on
> > > RS behaves in different ways. The prompt user, prompt string, default value
> > > etc. seems to not be communicating clearly with what was set in the
> > > designer. Example - if you have a date parameter, and in the designer you
> > > set the default to be =Left(today, 10) - an expression, in other words - when
> > > you go to look in RS and tweak the parameter properties it has default value
> > > checked, but it does not sjow the expression and it doesn't see any valid
> > > default value - and gives an error.
> > >
> > >
> > > "anandr" wrote:
> > >
> > > > I have a report which takes few parameters. I have set some defalut value for
> > > > them. The default values work good when i Preview teh report. But when i
> > > > deploy the report to the Report Server, the default values get lost and the
> > > > user has to type them all again. Any clue on how to solve this issue?|||Excellent David - thank you! Yes I noticed if you un-check the 'has default
value' check box, the problem does go away and you retain the expression
based value. This is good to know! Thanks!
Pete
"David Siebert" wrote:
> I've noticed this also. I've gotten around it by unchecking the Has Default
> boxes for any parameters that have a calculated value. The calculated
> default values will still be there after you apply changes, and if you go
> back to the properties page, you should see that the Has Default boxes are
> checked again. I don't know if I would really consider this a major bug, but
> a bug it may be. You can definitely use expressions as default values, but I
> will caution you that using them can cause odd behavior when editing the
> default values when actually running the report. If you have defaults that
> are expressions and you change a parameter value, it will recalculate any
> calculated default values for all the parameters after the one changed. This
> is in order to allow cascading parameters, and can't really be changed as far
> as I know. I actually use calculated values a lot (for dates e.g. beginning
> of month, end of month, two weeks ago, etc.) but in order to get around the
> odd behavior of cascading parameters, I will use sql to get the default
> dates, and then set the default in VS to "From Query" and this manages to
> work around the recalculation issue.
> Hope this helps.
> David
> "Myles" wrote:
> > Thanks David -
> >
> > actually, the parm type is a string - I believe that is the only way
> > something like
> > =Left(today, 10) would work, because it is a string function (basically).
> > It is trying, though, when you open up the parameters page of the properties
> > tab on http://<Server>/Reports and fiddle with a completely different
> > parameter and try to apply the changes, that it gives you the error on any
> > string type parameter that has an expression based default value. I would
> > think either you can have an expression as a default value, or you can't -
> > the ability to assign the default through the designer makes it appear that
> > that is ok, while later fiddling through RS makes it seem like it is wrong -
> > do you think possibly it's a bug?
> >
> > Thanks,
> >
> > Pete
> >
> >
> >
> > "David Siebert" wrote:
> >
> > > The reason you aren't seeing any value in the Default Value box is because
> > > your default value is an expression. The Default Value textbox on the
> > > Parameters property page only displays default values when they are strings
> > > and not expressions (this does not mean that you do not have a default value
> > > when it is an expression). The reason you might be getting an error is if
> > > your parameter type is date, you're returning a string instead of a date. I
> > > don't know what your parameter type is, but it is just a thought. Hope that
> > > helps you a bit.
> > >
> > > David
> > >
> > > "Myles" wrote:
> > >
> > > > I think you can try setting the default value in VS (designer) as
> > > >
> > > > ="Yes" instead of Yes
> > > >
> > > > see if that works.
> > > >
> > > > That being said, I am having some difficulty because it appears setting
> > > > things in the designer (for parameters) and setting parameter properties on
> > > > RS behaves in different ways. The prompt user, prompt string, default value
> > > > etc. seems to not be communicating clearly with what was set in the
> > > > designer. Example - if you have a date parameter, and in the designer you
> > > > set the default to be =Left(today, 10) - an expression, in other words - when
> > > > you go to look in RS and tweak the parameter properties it has default value
> > > > checked, but it does not sjow the expression and it doesn't see any valid
> > > > default value - and gives an error.
> > > >
> > > >
> > > > "anandr" wrote:
> > > >
> > > > > I have a report which takes few parameters. I have set some defalut value for
> > > > > them. The default values work good when i Preview teh report. But when i
> > > > > deploy the report to the Report Server, the default values get lost and the
> > > > > user has to type them all again. Any clue on how to solve this issue?|||Where is the "Has Default" checkbox? I could not locate it in "Report
Parameters" screen
"David Siebert" wrote:
> I've noticed this also. I've gotten around it by unchecking the Has Default
> boxes for any parameters that have a calculated value. The calculated
> default values will still be there after you apply changes, and if you go
> back to the properties page, you should see that the Has Default boxes are
> checked again. I don't know if I would really consider this a major bug, but
> a bug it may be. You can definitely use expressions as default values, but I
> will caution you that using them can cause odd behavior when editing the
> default values when actually running the report. If you have defaults that
> are expressions and you change a parameter value, it will recalculate any
> calculated default values for all the parameters after the one changed. This
> is in order to allow cascading parameters, and can't really be changed as far
> as I know. I actually use calculated values a lot (for dates e.g. beginning
> of month, end of month, two weeks ago, etc.) but in order to get around the
> odd behavior of cascading parameters, I will use sql to get the default
> dates, and then set the default in VS to "From Query" and this manages to
> work around the recalculation issue.
> Hope this helps.
> David
> "Myles" wrote:
> > Thanks David -
> >
> > actually, the parm type is a string - I believe that is the only way
> > something like
> > =Left(today, 10) would work, because it is a string function (basically).
> > It is trying, though, when you open up the parameters page of the properties
> > tab on http://<Server>/Reports and fiddle with a completely different
> > parameter and try to apply the changes, that it gives you the error on any
> > string type parameter that has an expression based default value. I would
> > think either you can have an expression as a default value, or you can't -
> > the ability to assign the default through the designer makes it appear that
> > that is ok, while later fiddling through RS makes it seem like it is wrong -
> > do you think possibly it's a bug?
> >
> > Thanks,
> >
> > Pete
> >
> >
> >
> > "David Siebert" wrote:
> >
> > > The reason you aren't seeing any value in the Default Value box is because
> > > your default value is an expression. The Default Value textbox on the
> > > Parameters property page only displays default values when they are strings
> > > and not expressions (this does not mean that you do not have a default value
> > > when it is an expression). The reason you might be getting an error is if
> > > your parameter type is date, you're returning a string instead of a date. I
> > > don't know what your parameter type is, but it is just a thought. Hope that
> > > helps you a bit.
> > >
> > > David
> > >
> > > "Myles" wrote:
> > >
> > > > I think you can try setting the default value in VS (designer) as
> > > >
> > > > ="Yes" instead of Yes
> > > >
> > > > see if that works.
> > > >
> > > > That being said, I am having some difficulty because it appears setting
> > > > things in the designer (for parameters) and setting parameter properties on
> > > > RS behaves in different ways. The prompt user, prompt string, default value
> > > > etc. seems to not be communicating clearly with what was set in the
> > > > designer. Example - if you have a date parameter, and in the designer you
> > > > set the default to be =Left(today, 10) - an expression, in other words - when
> > > > you go to look in RS and tweak the parameter properties it has default value
> > > > checked, but it does not sjow the expression and it doesn't see any valid
> > > > default value - and gives an error.
> > > >
> > > >
> > > > "anandr" wrote:
> > > >
> > > > > I have a report which takes few parameters. I have set some defalut value for
> > > > > them. The default values work good when i Preview teh report. But when i
> > > > > deploy the report to the Report Server, the default values get lost and the
> > > > > user has to type them all again. Any clue on how to solve this issue?|||I have run into the same issue.
I have defined 6 possible values for a parameter in a report. They are team
names - so they would look like:
Team 1
Team 2
and so on... I have defined Team 1 as the default selection in each report.
(by setting the default, unqueried value to: Team 1. If I use ="Team 1" or
something like that then it errors. And this appears to work correctly when
using the vs dev env. However when the report is published the selection
for team does not default to Team 1, it defaults to <Select A Value>.
Any ideas?
Thanks.
--Cory
"anandr" wrote:
> I have a report which takes few parameters. I have set some defalut value for
> them. The default values work good when i Preview teh report. But when i
> deploy the report to the Report Server, the default values get lost and the
> user has to type them all again. Any clue on how to solve this issue?
default values for database fields yes or no??
Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added.
I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls.
What is the right way??
Pros and cons.......
Newbie
Like a table that holds car information. If you have a field that holds the number of wheels, I would make that a default of 4.
Sort of like, if you don't mention how many wheels the car has, I'm going to assume 4. Now if the car has more/less than 4, you can tell me about it, and I'll remember.
It's not really what is "right" and what is "wrong". You can also say there is no default, and you don't tell me the number of wheels of every car, I'm not going to accept it. It forces you to make a choice for every record. Or you can have each record allow nulls, in which case if you don't mention it, we'll still accept it, but we won't make any assumption on the number of wheels.
My rule of thumb is, if it's necessary field, then no default value, and does not accept nulls.
If you can assume a value if one isn't specified (It rarely isn't a particular value), then I'll make it does not accept nulls, with a default value.
If I really don't care about the field at all, and it's a fluff field that I won't use, then I'll accept nulls and no default.
Default values for a column - From a function
I am trying to set the Default value for a table column to a user defined function . Is this possible?
I have a Function that generates a RandomID as a string and returns that string. I would like that value in the Column to which I am calling the function as the default value.
Thanks
Jawahar
BOL 2000 : "Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default"|||Is it true for SQl Server 2005? If not, can you please point me to a sample of using UDF as a default value for a column?
Thanks in advance.
|||You can use a user-defined function as default but it cannot take any parameters (meaning you can't pass the any column value). Additionally, this will get evaluated only once per statement (INSERT/UPDATE) not for every row.
So can you please explain what you are trying to do by calling a UDF with non-deterministic behavior?
|||I see, thanks a lot. It actually was not my question, I just found this question on another forum and then I tried to make some research. When I found this thread I decided to post this question for clarification.
You're saying, that it would work, but it would be the same value for all inserted records, correct? Because I'm not sure if default value for the column comes into play on UPDATE.
Thanks again for your help and if you have any more information, I would appreciate it.
Default values for a column - From a function
I am trying to set the Default value for a table column to a user defined function . Is this possible?
I have a Function that generates a RandomID as a string and returns that string. I would like that value in the Column to which I am calling the function as the default value.
Thanks
Jawahar
BOL 2000 : "Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default"|||Is it true for SQl Server 2005? If not, can you please point me to a sample of using UDF as a default value for a column?
Thanks in advance.
|||You can use a user-defined function as default but it cannot take any parameters (meaning you can't pass the any column value). Additionally, this will get evaluated only once per statement (INSERT/UPDATE) not for every row.
So can you please explain what you are trying to do by calling a UDF with non-deterministic behavior?
|||I see, thanks a lot. It actually was not my question, I just found this question on another forum and then I tried to make some research. When I found this thread I decided to post this question for clarification.
You're saying, that it would work, but it would be the same value for all inserted records, correct? Because I'm not sure if default value for the column comes into play on UPDATE.
Thanks again for your help and if you have any more information, I would appreciate it.
Default values for a column - From a function
I am trying to set the Default value for a table column to a user defined function . Is this possible?
I have a Function that generates a RandomID as a string and returns that string. I would like that value in the Column to which I am calling the function as the default value.
Thanks
Jawahar
BOL 2000 : "Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default"|||Is it true for SQl Server 2005? If not, can you please point me to a sample of using UDF as a default value for a column?
Thanks in advance.
|||You can use a user-defined function as default but it cannot take any parameters (meaning you can't pass the any column value). Additionally, this will get evaluated only once per statement (INSERT/UPDATE) not for every row.
So can you please explain what you are trying to do by calling a UDF with non-deterministic behavior?
|||I see, thanks a lot. It actually was not my question, I just found this question on another forum and then I tried to make some research. When I found this thread I decided to post this question for clarification.
You're saying, that it would work, but it would be the same value for all inserted records, correct? Because I'm not sure if default value for the column comes into play on UPDATE.
Thanks again for your help and if you have any more information, I would appreciate it.
Default Values for a Column
I have table where I want one column (EquivNo) to default to the primary key
column (PartID) when the row is inserted. This value may be updated later,
but at the point where the row is created, I want the values to be the same.
The PartID column is an Identity column so I don't know the value in
advance.
As far as I understand it, you can't have a calculated value as the default
value for a column; can somebody confirm this?
So I have thought of two alternatives:
1) Create a trigger that takes the identity value for PartID and updates the
EquivNo column
[How do I know what the inserted PartID is within the trigger?]
2) The stored procedure inserts the row on the first pass, and uses
Scope_Identy() to update the EquivNo in an update statement.
I was just wondering about the relative merits of these two solutions. And
are there any better alternatives?
Thanks in advance
ChrisCJM
Second one seems to be good for you.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I have table where I want one column (EquivNo) to default to the primary
>key column (PartID) when the row is inserted. This value may be updated
>later, but at the point where the row is created, I want the values to be
>the same. The PartID column is an Identity column so I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the
> default value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates
> the EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
>|||An advantage of using 1 is that the modifications are performed in the same
transaction. The trigger
code is very straight forward:
USE tempdb
drop table t
GO
create table t(c1 int identity primary key, c2 int NULL)
GO
CREATE TRIGGER tr ON T FOR INSERT
AS
UPDATE t SET c2 = c1
WHERE EXISTS
(
SELECT *
FROM inserted AS i
WHERE i.c1 = t.c1
)
GO
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
SELECT * FROM t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.ph
x.gbl...
>I have table where I want one column (EquivNo) to default to the primary ke
y column (PartID) when
>the row is inserted. This value may be updated later, but at the point wher
e the row is created, I
>want the values to be the same. The PartID column is an Identity column so
I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the defaul
t value for a column;
> can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses Scope_
Identy() to update the
> EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
are there any better
> alternatives?
> Thanks in advance
> Chris
>|||CJM wrote:
> I have table where I want one column (EquivNo) to default to the primary k
ey
> column (PartID) when the row is inserted. This value may be updated later,
> but at the point where the row is created, I want the values to be the sam
e.
> The PartID column is an Identity column so I don't know the value in
> advance.
> As far as I understand it, you can't have a calculated value as the defaul
t
> value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he
> EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
Is EquivNo a self-referencing foreign key? (please post DDL, then we
won't have to guess). I'm of the opinion that IDENTITY isn't a good
choice to use as a foreign key in the same table. Your problem is just
one of one of the reasons why.
If EquivNo is not a key then I'm not sure why you'd want it to be the
same as the PartID. It's a bad idea to expose IDENTITY columns to
users. From the business perspective it shouldn't matter what value is
assigned to PartID and therefore it shouldn't matter whether it's the
same as EquivNo. I think that your real problem is that you need a more
convenient alternative method to generate an incrementing key for
EquivNo. See the following article for suggestions:
http://www.sqlmag.com/Articles/Arti...8165/48165.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tibor
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
Just keep thinking if the user inserts a new row (one transaction) , the
trigger is fired and open another transaction, am I right?
If the trigger fails that an identity peroperty is already in the table , so
actually in my opinion that using
a stored procedure will be more useful in terms of performans as well as
more secure.
CREATE PROC myproc
AS
DECLARE @.idnt INT
BEGIN TRAN
INSERT INTO T1 VALUES (....)
SELECT @.idnt =SCOPE_IDENTITY()
INSERT INTO T2 SELECT @.idnt
--Error handler here
COMMIT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
> USE tempdb
> drop table t
> GO
> create table t(c1 int identity primary key, c2 int NULL)
> GO
> CREATE TRIGGER tr ON T FOR INSERT
> AS
> UPDATE t SET c2 = c1
> WHERE EXISTS
> (
> SELECT *
> FROM inserted AS i
> WHERE i.c1 = t.c1
> )
> GO
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> SELECT * FROM t
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "CJM" <cjmnews04@.newsgroup.nospam> wrote in message
> news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>|||> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
No, the code in a trigger is in the same transaction as the statement that f
ired the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OxFvq8WWGHA.3800@.TK2MSFTNGP03.phx.gbl
..
> Tibor
> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
> If the trigger fails that an identity peroperty is already in the table ,
so
> actually in my opinion that using
> a stored procedure will be more useful in terms of performans as well as
> more secure.
>
> CREATE PROC myproc
> AS
> DECLARE @.idnt INT
> BEGIN TRAN
> INSERT INTO T1 VALUES (....)
> SELECT @.idnt =SCOPE_IDENTITY()
> INSERT INTO T2 SELECT @.idnt
> --Error handler here
> COMMIT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
>|||Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't included in the transaction (Begin/Commit/Rollback)
anyway.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23E1lfUWWGHA.3800@.TK2MSFTNGP03.phx.gbl...
> CJM
> Second one seems to be good for you.
>
I'm erring this way, if only because it's a simple and uncomplicated
solution.|||> Surely the transaction issue is a red herring? That is, we can explicitly define what is
and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
Yes, of course we can. My point (perhaps not explicit enough) was that the t
ransaction handling will
be transparent for those who does INSERTs into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23lbLUbXWGHA.924@.TK2MSFTNGP03.phx
.gbl...
> Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
>|||Did you look at the trigger code I posted? What part of that code did you fi
nd complicated? I'm not
trying to defend my proposal, I just want to make sure you see the simplicit
y of the trigger
alternative.
Oh, and I fully agree with David's point regarding IDENTITY should be comple
mented with a natural
key and identity not be exposed to users, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:u$0KGgXWGHA.1348@.TK2MSFTNGP05.phx.
gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23E1lfUWWGHA.3800@.T
K2MSFTNGP03.phx.gbl...
> I'm erring this way, if only because it's a simple and uncomplicated solut
ion.
>
column (PartID) when the row is inserted. This value may be updated later,
but at the point where the row is created, I want the values to be the same.
The PartID column is an Identity column so I don't know the value in
advance.
As far as I understand it, you can't have a calculated value as the default
value for a column; can somebody confirm this?
So I have thought of two alternatives:
1) Create a trigger that takes the identity value for PartID and updates the
EquivNo column
[How do I know what the inserted PartID is within the trigger?]
2) The stored procedure inserts the row on the first pass, and uses
Scope_Identy() to update the EquivNo in an update statement.
I was just wondering about the relative merits of these two solutions. And
are there any better alternatives?
Thanks in advance
ChrisCJM
Second one seems to be good for you.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I have table where I want one column (EquivNo) to default to the primary
>key column (PartID) when the row is inserted. This value may be updated
>later, but at the point where the row is created, I want the values to be
>the same. The PartID column is an Identity column so I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the
> default value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates
> the EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
>|||An advantage of using 1 is that the modifications are performed in the same
transaction. The trigger
code is very straight forward:
USE tempdb
drop table t
GO
create table t(c1 int identity primary key, c2 int NULL)
GO
CREATE TRIGGER tr ON T FOR INSERT
AS
UPDATE t SET c2 = c1
WHERE EXISTS
(
SELECT *
FROM inserted AS i
WHERE i.c1 = t.c1
)
GO
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
SELECT * FROM t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.ph
x.gbl...
>I have table where I want one column (EquivNo) to default to the primary ke
y column (PartID) when
>the row is inserted. This value may be updated later, but at the point wher
e the row is created, I
>want the values to be the same. The PartID column is an Identity column so
I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the defaul
t value for a column;
> can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses Scope_
Identy() to update the
> EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
are there any better
> alternatives?
> Thanks in advance
> Chris
>|||CJM wrote:
> I have table where I want one column (EquivNo) to default to the primary k
ey
> column (PartID) when the row is inserted. This value may be updated later,
> but at the point where the row is created, I want the values to be the sam
e.
> The PartID column is an Identity column so I don't know the value in
> advance.
> As far as I understand it, you can't have a calculated value as the defaul
t
> value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he
> EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
Is EquivNo a self-referencing foreign key? (please post DDL, then we
won't have to guess). I'm of the opinion that IDENTITY isn't a good
choice to use as a foreign key in the same table. Your problem is just
one of one of the reasons why.
If EquivNo is not a key then I'm not sure why you'd want it to be the
same as the PartID. It's a bad idea to expose IDENTITY columns to
users. From the business perspective it shouldn't matter what value is
assigned to PartID and therefore it shouldn't matter whether it's the
same as EquivNo. I think that your real problem is that you need a more
convenient alternative method to generate an incrementing key for
EquivNo. See the following article for suggestions:
http://www.sqlmag.com/Articles/Arti...8165/48165.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tibor
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
Just keep thinking if the user inserts a new row (one transaction) , the
trigger is fired and open another transaction, am I right?
If the trigger fails that an identity peroperty is already in the table , so
actually in my opinion that using
a stored procedure will be more useful in terms of performans as well as
more secure.
CREATE PROC myproc
AS
DECLARE @.idnt INT
BEGIN TRAN
INSERT INTO T1 VALUES (....)
SELECT @.idnt =SCOPE_IDENTITY()
INSERT INTO T2 SELECT @.idnt
--Error handler here
COMMIT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
> USE tempdb
> drop table t
> GO
> create table t(c1 int identity primary key, c2 int NULL)
> GO
> CREATE TRIGGER tr ON T FOR INSERT
> AS
> UPDATE t SET c2 = c1
> WHERE EXISTS
> (
> SELECT *
> FROM inserted AS i
> WHERE i.c1 = t.c1
> )
> GO
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> SELECT * FROM t
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "CJM" <cjmnews04@.newsgroup.nospam> wrote in message
> news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>|||> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
No, the code in a trigger is in the same transaction as the statement that f
ired the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OxFvq8WWGHA.3800@.TK2MSFTNGP03.phx.gbl
..
> Tibor
> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
> If the trigger fails that an identity peroperty is already in the table ,
so
> actually in my opinion that using
> a stored procedure will be more useful in terms of performans as well as
> more secure.
>
> CREATE PROC myproc
> AS
> DECLARE @.idnt INT
> BEGIN TRAN
> INSERT INTO T1 VALUES (....)
> SELECT @.idnt =SCOPE_IDENTITY()
> INSERT INTO T2 SELECT @.idnt
> --Error handler here
> COMMIT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
>|||Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't included in the transaction (Begin/Commit/Rollback)
anyway.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23E1lfUWWGHA.3800@.TK2MSFTNGP03.phx.gbl...
> CJM
> Second one seems to be good for you.
>
I'm erring this way, if only because it's a simple and uncomplicated
solution.|||> Surely the transaction issue is a red herring? That is, we can explicitly define what is
and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
Yes, of course we can. My point (perhaps not explicit enough) was that the t
ransaction handling will
be transparent for those who does INSERTs into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23lbLUbXWGHA.924@.TK2MSFTNGP03.phx
.gbl...
> Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
>|||Did you look at the trigger code I posted? What part of that code did you fi
nd complicated? I'm not
trying to defend my proposal, I just want to make sure you see the simplicit
y of the trigger
alternative.
Oh, and I fully agree with David's point regarding IDENTITY should be comple
mented with a natural
key and identity not be exposed to users, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:u$0KGgXWGHA.1348@.TK2MSFTNGP05.phx.
gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23E1lfUWWGHA.3800@.T
K2MSFTNGP03.phx.gbl...
> I'm erring this way, if only because it's a simple and uncomplicated solut
ion.
>
Subscribe to:
Posts (Atom)