Delete and insert data in same transaction!

Hi!
I try to add records in one table from a VB6 com+ component using ADO
(provider=SQLOLEDB, SQL-server2000).
Basically what I try to do is to
1. Start a transaction
2. Delete old records in one table
3. Add new values in the same table
4. commit transaction
The problem is that I get a duplicate key when I insert my new data in
step3. If I commit the data between step 2 and 3 everything works fine. If
step 4 then fails I will end up with no data in the table which I don't want
(i mean that is what transaction is used for).
This must be a pretty common scenario so I hope there will be a solution
that will not force me to commit the transaction in the middle.
Regards
/HansHi Hans,
as you descibed the scenario should be fine, allowing the transaction
to commit without problems, perhaps there is a logical problem in your
code. Could you please post the code here. This would make error
searching much easier for us.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hans wrote:
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't wa
nt
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
Do you mean you want to delete and then insert new row(s) with the same
key values? That may not be an optimal solution since you could
accomplish the same thing with an UPDATE. The following works for me.
If this example doesn't help then please post some code so that we can
reproduce the problem.
CREATE TABLE tbl (x INT PRIMARY KEY);
INSERT INTO tbl(x) VALUES (1);
BEGIN TRAN;
DELETE FROM tbl WHERE x=1;
INSERT INTO tbl(x) VALUES (1);
COMMIT TRAN;
SELECT x FROM tbl;
I recommend you put the DELETE/INSERT code in a stored procedure and
execute the proc from your VB code.
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
--|||> The problem is that I get a duplicate key when I insert my new data in
> step3.
This indicates that INSERTs are occurring on a different connection that the
DELETE and not within the same transaction context. You can run a SQL
Profiler trace to see the actual behavior.
Note that ADO is particularly nasty about opening additional connections
behind your back. It is important to include 'SET NOCOUNT ON' in
procs/scripts and process all results returned so that connections can be
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:OXTNpYnSGHA.2156@.tk2msftngp13.phx.gbl...
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't
> want
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
>
>|||Hi Jens, David and Dan!
Thanks for your replies.
Here is the code. The code is used to store default values for a user. The
table have fields for which user it is (idUser), which field (idfld) and
some other fields about the default values. The code is most likely not the
most efficient (the "IN" operator is slow but we are talking about pretty
small tables here with a couple of 1000 records) but it is only executed a
couple of times/year for a normal user (and it only takes like 100
milliseconds to excecute as it is). The key in the table is idUser (user id)
and idfld (Field id) together and I'm not sure if there is a way to update
or add in one single SQL-statement and also delete records where I earlier
had defaultvalues but where the user no longer want to have default values.
Therfor I delete all defaultvalues for the current user for the current
table (I join in another table which holds the table id). For example there
may be 5 rows before the save and maybe only 3 rows left in the table after
the update.
If OS_WIN2000 Then
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Else
Set rs = CtxCreateObject("ADODB.Recordset")
Set con = CtxCreateObject("ADODB.Connection")
End If
con.Open GetConnectionString()
'Use transaction
con.BeginTrans
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where iduser="
& userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & " where
vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
rs.Open sSQL, con
'If I commit here it works OK but I want to commit after the entire
operation is finished
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
rs.Open sSQL, con
End If
Next l
Next i
con.CommitTrans
Regards
/Hans|||The main problem is that you are using recordsets when no data are returned.
Additional connections are probably acquired for the subsequent INSERT
statements and outside scope of the first transaction.
The example below shows how to use Command objects for these DML statements.
I would also suggest using parameters instead of concatenating literal
values. Parameters are more secure, eliminate the need to quote values,
escape quotes, format dates, etc.
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
con.Open GetConnectionString()
cmd.ActiveConnection = con
con.BeginTrans
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "SET NOCOUNT ON Delete from " & _
TSP("vmo_base_defaultvalues") & _
" where iduser=" & _
userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & _
" where vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
cmd.CommandText = sSQL
cmd.Execute
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "SET NOCOUNT ON Insert into " & _
TSP("vmo_base_defaultvalues") & _
"(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
cmd.CommandText = sSQL
cmd.Execute
End If
Next l
Next i
con.CommitTrans
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:%23%23EoJT2SGHA.1148@.TK2MSFTNGP10.phx.gbl...
> Hi Jens, David and Dan!
> Thanks for your replies.
> Here is the code. The code is used to store default values for a user. The
> table have fields for which user it is (idUser), which field (idfld) and
> some other fields about the default values. The code is most likely not
> the
> most efficient (the "IN" operator is slow but we are talking about pretty
> small tables here with a couple of 1000 records) but it is only executed a
> couple of times/year for a normal user (and it only takes like 100
> milliseconds to excecute as it is). The key in the table is idUser (user
> id)
> and idfld (Field id) together and I'm not sure if there is a way to update
> or add in one single SQL-statement and also delete records where I earlier
> had defaultvalues but where the user no longer want to have default
> values.
> Therfor I delete all defaultvalues for the current user for the current
> table (I join in another table which holds the table id). For example
> there
> may be 5 rows before the save and maybe only 3 rows left in the table
> after
> the update.
> If OS_WIN2000 Then
> Set rs = CreateObject("ADODB.Recordset")
> Set con = CreateObject("ADODB.Connection")
> Else
> Set rs = CtxCreateObject("ADODB.Recordset")
> Set con = CtxCreateObject("ADODB.Connection")
> End If
> con.Open GetConnectionString()
> 'Use transaction
> con.BeginTrans
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenStatic
> rs.LockType = adLockOptimistic
> 'idtbl and idUser is already singlequoted
> For i = LBound(userList) To UBound(userList)
> sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where
> iduser="
> & userList(i) & " and "
> sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
> TSP("vmo_base_defaultvalues") & ","
> sSQL = sSQL & TSP("vmo_base_field") & " where
> vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
> sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
> rs.Open sSQL, con
> 'If I commit here it works OK but I want to commit after the entire
> operation is finished
> For l = LBound(sArg) To UBound(sArg) Step 4
> If sArg(l) <> "" And sArg(l + 1) <> "" Then
> sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
> (idUser,idfld,vValue,datevalue, deftype) " & _
> "values (" & _
> userList(i) & "," & _
> sArg(l) & "," & _
> sArg(l + 1) & "," & _
> sArg(l + 2) & "," & _
> sArg(l + 3) & ")"
> rs.Open sSQL, con
> End If
> Next l
> Next i
> con.CommitTrans
>
> Regards
> /Hans
>|||Thanks Dan for the tip!
Yes that seems to fix the problem. The code is pretty old and written for
Oracle in the first place where I did not have any problems with the
transaction (so I guess the real problem is inside the oledb provider). Yes
you are right using parameters is much safer but at least I don't like
touching code that has been working for years, well at least for other
databases than SQL-server :-)
/Hans

Delete a row and all other rows thats linked to it

Hi
i want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.

Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.

an suggestions?That's what cascading deletes are for, but that's in the database design. Do searches on Declaritive Referential Integrity or DRI.sql

Delete

hi
i have a stored procedure for Delete with following code
Even though i delete thru UI the delete procedure gets called but when i
again open my UI i find the record that is being deleted.
could u please eloborate especially what that ROWSTATUS | 0x890
mean and also probable reasons for not deleting
UPDATE C_SetupDet
set RowStatus = RowStatus | 0x890
WHERE GUID = @.GUID
thanks and regards
MadhaviHi Madhu
The question is not very clear. Are you really trying to delete or are you
updating?
The query that you gave here is updating a value
the Hexa decimal value 0x890 is actually 888 or 100010001000
as per the code here you are using the OR operation on the column value.
You might be better aware of the business logic involved here
Please let me know if you have any concerns
thanks and regards
Chandra
"madhavi" wrote:

> hi
> i have a stored procedure for Delete with following code
> Even though i delete thru UI the delete procedure gets called but when i
> again open my UI i find the record that is being deleted.
> could u please eloborate especially what that ROWSTATUS | 0x890
> mean and also probable reasons for not deleting
> UPDATE C_SetupDet
> set RowStatus = RowStatus | 0x890
> WHERE GUID = @.GUID
> thanks and regards
> Madhavi
>
>|||hi
Actually the Record is not deleted but instead a flag variable is set 1
which indicates that that record is deleted and it should not be visible in
the User Interface(UI)
thanks and regards
Madhavi
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:708E0CD0-6FDF-469C-8F1A-E67036B8D083@.microsoft.com...
> Hi Madhu
> The question is not very clear. Are you really trying to delete or are you
> updating?
> The query that you gave here is updating a value
> the Hexa decimal value 0x890 is actually 888 or 100010001000
> as per the code here you are using the OR operation on the column value.
> You might be better aware of the business logic involved here
> Please let me know if you have any concerns
> thanks and regards
> Chandra
>
> "madhavi" wrote:
>
i

Defining custom Roles with limited access to SQL Objects

Hi!

I'm assisting in the creation of a development enviroment with SQL Server 2005, and I need to assign some custom roles, in particular, a Stored Developer Role should be able to create, modify and execute Stored Procedures but they should not be able to alter tables or views, but should be able to retrieve/insert data from those tables.

I've tried with the default roles in 2005 to no avail.

Is there a relatively easy way to accomplish this with a database alredy populated with objects of both kinds? (SP's and Tables / Views)

Thanks!

You should create your own role and assign the required permissions to it. See CREATE ROLE for information on how to create a role.

Thanks
Laurentiu

define Select parameters

Hi

I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId

The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Art %>"SelectCommand="Select * from Option WhereSourceId=@.SourceId AndCatId=@.CatId"><SelectParameters><asp:ControlParameterControlID="Drop1"Name="SourceId"/><asp:ControlParameterControlID="Drop1"Name="CatId"/></SelectParameters></asp:SqlDataSource>

Can anyone help me to define the parameters?

thanks

Hi engnouna,

We can bind the select parameters to Label controls' Text property. And set the Text every time DropDownList select index changed. Here is the demo code:

<asp:GridViewID="GridView1"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:testConnectionString%>"

SelectCommand="SELECT [SourceID], [CatID], [Name] FROM [ForDynamicGridView] WHERE (([SourceID] = @.SourceID) AND ([CatID] = @.CatID))">

<SelectParameters>

<asp:ControlParameterControlID="SourceID"DefaultValue="1"Name="SourceID"PropertyName="Text"

Type="Int32"/>

<asp:ControlParameterControlID="CatID"DefaultValue="2"Name="CatID"PropertyName="Text"

Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

<asp:ListItem>1-2</asp:ListItem>

<asp:ListItem>2-4</asp:ListItem>

<asp:ListItem>3-4</asp:ListItem>

</asp:DropDownList>

<asp:LabelID="SourceID"runat="server"Text="1"Visible="false"></asp:Label>

<asp:LabelID="CatID"runat="server"Text="2"Visible="false"></asp:Label>

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string[] value = DropDownList1.SelectedValue.Split('-');

SourceID.Text = value[0];

CatID.Text = value[1];

}

|||

Hi,

The ControlParameter itself cannot parse the text in your dropdownlist directly. So, I suggest you parse it in your select statement.

For example, if they are all 2 digits numbers, you can use

Select * from Option Where SourceId=LEFT(@.Input,2) And CatId=RIGHT(@.Input,2)

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Define relationship betwen Fact and Dimension (Fact have data but Dimension does not have data i

Hi

I have one problem in defining relationship between Fact and Dimension.

I have one Fact Table: FactTests

FactTests Fields: KeyDatetime, UnitId

and Two Dimension table : DimTests and DimASM

DimTests Fields: KeyDateTime, UnitId, OverallResult, TestCycle

DimASM Fields: KeyDateTime, UnitId, ASMResult

(DimASM row will exist only If TestCycle is 'A' in DimTests table)

KeyDatetime and UnitId is primary key in all Table.

I have define Regular Relationship between FactTests and DimTests, DimASM.

FactTests have data but DimASM have a data only when TestCycle is 'A'.

I am getting following error when I process the Cube.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactTests, Column: KeyDateTime, Value: 2/1/2006 7:02:58 AM; Table: FactTests, Column: UnitId, Value: AA986495. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Dim Tests ASM of Dimension: Dim ASM from Database: SLC OLAP Database, Cube: OLAP Test Cube, Measure Group: Tests, Partition: Tests, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Tests' partition of the 'Tests' measure group for the 'OLAP Test Cube' cube from the SLC OLAP Database database.

Regards,

Dinesh Patel

This has nothing to do with the type of relationships.

The problem is; during partition processing Analysis server saw the dimension keys coming from partition talbe (fact table) that it could match to the keys it read previously during dimension processing from the dimension table.

You need to make sure fact table has only the keys that are present in the dimension table. Make sure the columns you are joining between fact and dimension have the same data types.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Our Test Table is splited in different tables. all common Data we are inserting in DimTests so FactTests and DimTests have no problem. some data we are inserting in DimAsm when we are performing ASM Test (When TestCycle = 'A') . If OBD Test is perform then we are inserting data in DimOBD (When TestCycle = 'O') that case KeyDateTime and UnitID not exist in DimASM but exist in DimTests and DimOBD.

so exect maching is not found between DimASM / DimOBD and FactTests.

what i will do in this case?

Define relationship betwen Fact and Dimension (Fact have data but Dimension does not have da

Hi

I have one problem in defining relationship between Fact and Dimension.

I have one Fact Table: FactTests

FactTests Fields: KeyDatetime, UnitId

and Two Dimension table : DimTests and DimASM

DimTests Fields: KeyDateTime, UnitId, OverallResult, TestCycle

DimASM Fields: KeyDateTime, UnitId, ASMResult

(DimASM row will exist only If TestCycle is 'A' in DimTests table)

KeyDatetime and UnitId is primary key in all Table.

I have define Regular Relationship between FactTests and DimTests, DimASM.

FactTests have data but DimASM have a data only when TestCycle is 'A'.

I am getting following error when I process the Cube.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactTests, Column: KeyDateTime, Value: 2/1/2006 7:02:58 AM; Table: FactTests, Column: UnitId, Value: AA986495. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Dim Tests ASM of Dimension: Dim ASM from Database: SLC OLAP Database, Cube: OLAP Test Cube, Measure Group: Tests, Partition: Tests, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Tests' partition of the 'Tests' measure group for the 'OLAP Test Cube' cube from the SLC OLAP Database database.

Regards,

Dinesh Patel

This has nothing to do with the type of relationships.

The problem is; during partition processing Analysis server saw the dimension keys coming from partition talbe (fact table) that it could match to the keys it read previously during dimension processing from the dimension table.

You need to make sure fact table has only the keys that are present in the dimension table. Make sure the columns you are joining between fact and dimension have the same data types.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Our Test Table is splited in different tables. all common Data we are inserting in DimTests so FactTests and DimTests have no problem. some data we are inserting in DimAsm when we are performing ASM Test (When TestCycle = 'A') . If OBD Test is perform then we are inserting data in DimOBD (When TestCycle = 'O') that case KeyDateTime and UnitID not exist in DimASM but exist in DimTests and DimOBD.

so exect maching is not found between DimASM / DimOBD and FactTests.

what i will do in this case?

Default value: ISNULL()

Hi!

I'm wondering whether it's possible to set up the MS SQL function
ISNULL() as a default value to avoid NULL entries when importing data
into a table?!

For example, I want the column1, to have a 0 (zero) as default value,
when entering/importing data: isnull("column1",0)

I remember that it is possible to set up with a date function like
now(), having for each record the current time as default value. Is
that also with isnull() somehow possible?

Thx a lot!
PeterYou can create a default constraint to specify a default value for a column.
For example:

CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 int NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0
)
GO
INSERT INTO MyTable (Col1) VALUES(1)
SELECT * FROM MyTable
GO

However, an explicit NULL will override the default constraint value:

INSERT INTO MyTable (Col1, Col2) VALUES(2, NULL)
SELECT * FROM MyTable
GO

If you need to import data containing a mix of nulls and not nulls, you have
options depending on your data source and import tool. In the case of a
query, you could use ISNULL or COALESCE to specify the desired value when
NULL. With DTS, a column transformation could do the job.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1138013760.433105.245790@.g43g2000cwa.googlegr oups.com...
> Hi!
> I'm wondering whether it's possible to set up the MS SQL function
> ISNULL() as a default value to avoid NULL entries when importing data
> into a table?!
> For example, I want the column1, to have a 0 (zero) as default value,
> when entering/importing data: isnull("column1",0)
> I remember that it is possible to set up with a date function like
> now(), having for each record the current time as default value. Is
> that also with isnull() somehow possible?
> Thx a lot!
> Peter

Default value for multi select parameter

Hi

I am generating this report which has ten parameters. All parameters are multi value parameter.

Now is there any way to set all parameters to "Select All" by default? What I mean is that user dont have to go each an every parameter and click 'Select All' option to view report.

Regards

If you are using a query to select the possible values you can use the same query to do a select all. Otherwise, I believe you can specify the value a an array ['value1'],['value2'],...|||

Hi Lonnie

Thanks for your reply.

I am using a dataset to select the possible values for parameter.

When you use the same dataset for default value it does not select anything.

Amit

|||Are you using RS2005 or RS2000? I know that for RS2005 that works. For RS2000 you have to create an 'All' option, then do some coding in the SQL to get it to work.|||I am using RS2005 sp1 hotfix applied (which put back in the <Select All> option that sp1 took out ). I am using queries for my parameter lists. I do not see a way to pick a default of the <Select All> option that RS adds into the list. Under default you specify the dataset and the value field (which is a drop down of the field names - not values).

Default value for Datetime column

Hi
I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
My tables have several columns that are defined as datetime fields which
need to default to the empty date that I have chosen.
I type in 01/01/1753 as the default value of the table designer, however
when I insert a new line into any of the tables, the value that gets
inserted is 01/01/1900.
Does anyone know why this is happening? I am assuming that I have some
configuration value set incorrectly.
I realise that I could simply use 1900 as the empty date. I could also
include the field and a value in the insert statement, but that isn't the
point. I'd rather know why the system behaves like this.
Hope someone can help
Thanks in anticipationDATETIME is just that. It is not date or time, it is both. If you set the
default value of 1753-01-01 then it is 1753-01-01 12:00:00.000 AM if no
value is inserted. If you enter a date or a time, *that* value determines
the other part. If you enter a time only in Query Analyzer, you get
1900-01-01 hh:mm:ss, if you do it in Enterprise Manager, you get 1899-12-31
hh:mm:ss. A value you enter overrides the entire value, even if you only
specify a portion. You can't say the date is x if I enter a time of y. If
you want a specific date to go along with your time, enter that date as
well. You should handle this in a stored procedure so that it is
consistent.
http://www.aspfaq.com/2206
http://www.aspfaq.com/2455
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>|||>> I am using 01/01/1753 to define the "Empty Date" for my datetime fields [sic].
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the MANY differences in
fields and columns is that a field can be missing or empty while a
column cannot.
Next, the proper format for dates in SQL is '1753-01-01' as per the
ISO-11179 Standards.
That has to do with the internal representation of DATETIME data types
in SQL Server. And Great Britain and its colonies went to the
Gregorian (Common Era) calendar in 1752, nobody started on
'1753-01-01' as far as I can tell.
No, you cannot. That is an integer and not a temporal data element at
all. Temporal data types, like all other SQL data types, can have a
NULL value. You can also add a DEFAULT CURRENT_TIMESTAMP or
DEFAULT '1753-01-01' clause to the DDL.
Exactly what are you trying to do?|||> Exactly what are you trying to do?
Sounds like when he enters a "datetime" value, like '4:30 PM', he wants the
date that goes with that to be 1753-01-01.
A|||Mark_S (Mark_S@.nospam.nospam) writes:
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
Why not use NULL?
There are those who think NULL are evil, but just face it. Any in-band
value that is used to mean "no value" is going to cause problem. If
nothing else, there can be confusion about which that value is.
(Although I will have to admit having used 17530101, in one place in our
system. I had a need to distinguish between "it has always been this
way" (which is 17530101) and "it has never been this way" (which is NULL).)

> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
When you specify a string for a datetime value in SQL Server, all parts
have a default value which is applied. So an empty string, results in
1900-01-01 00.00.00:000.
Since you get this value, you are apparently explicitly specifying a value.
Had you specified a NULL value, you would have gotten a NULL (or a NOT
NULL violation). The default value is only used when nothing at all
is specified.
This script illustrates:
CREATE TABLE datedemo (a int NOT NULL,
d datetime NULL DEFAULT '17530101')
go
INSERT datedemo (a) VALUES (1)
INSERT datedemo (a, d) VALUES (2, NULL)
INSERT datedemo (a, d) VALUES (3, '')
INSERT datedemo (a, d) VALUES (4, '12:24:34')
go
SELECT * FROM datedemo ORDER BY a
go
DROP TABLE datedemo
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I type in 01/01/1753 as the default value of the table designer
Without quotes, this is likely being interpreted as 1 divided by 1 divided
by 1753, which is going to round (dur to integer math) to 0, which is the
base date, 1900-01-01.
If you use a proper and unambiguous format for your date, you can avoid this
kind of thing. (And don't use the table designer.)
CREATE TABLE dbo.MyTable
(
i INT IDENTITY(1,1),
dt DATETIME NOT NULL DEFAULT '17530101'
);
GO
SET NOCOUNT ON;
INSERT dbo.MyTable DEFAULT VALUES;
INSERT dbo.MyTable(dt) SELECT '19000101';
INSERT dbo.MyTable(dt) SELECT '20050101';
INSERT dbo.MyTable(dt)
SELECT CONVERT(DATETIME, 01/01/1753);
GO
SELECT i,dt FROM dbo.MyTable ORDER BY i,dt;
GO
DROP TABLE dbo.MyTable;
GO|||The group is on a rampage for some odd reason.
Make sure you didn't accidentally define the data type as SMALLDATETIME,
which has a lower bound of '1900-01-01 00:00:00.000'.
Sincerely,
Anthony Thomas
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>

Default Value

Hi!
I have the following sp:
ALTER PROCEDURE dbo.[Buscar Clientes]
(
@.Begin As DateTime = Null,
@.End As DateTime = Null
)
In VS 2003, If i dont enter any value in the parameters, shouldnt both be
Null?
If i dont enter any value for the parameters i get the following error when
i use the Fill method: "String was not recognized as a valid datetime"
When i look into the Parameters in my DataAdpter, the value field is empty.
Can anyboy help me out?
Thanks,
BRuno N> In VS 2003, If i dont enter any value in the parameters, shouldnt both be
> Null?
YEs, they are NULL as far as T-SQL is concerned.

> If i dont enter any value for the parameters i get the following error
when
> i use the Fill method: "String was not recognized as a valid datetime"
I assume this stored procedure then returns a resultset, and this is what
you are using for Fill. Your C# or VB.Net code needs to deal with DBNull...
or, you need to change your SELECT statement so that IT deals with the NULL
values appropriately.
In other words, your error is not because your date is NULL within SQL
Server, your error is because your .NET code does not deal with it
correctly.|||Thank you!
I will look into my code
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:%23Z5g3pkLFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I have the following sp:
> ALTER PROCEDURE dbo.[Buscar Clientes]
> (
> @.Begin As DateTime = Null,
> @.End As DateTime = Null
> )
> In VS 2003, If i dont enter any value in the parameters, shouldnt both be
> Null?
> If i dont enter any value for the parameters i get the following error
> when i use the Fill method: "String was not recognized as a valid
> datetime"
> When i look into the Parameters in my DataAdpter, the value field is
> empty.
>
> Can anyboy help me out?
> Thanks,
> BRuno N
>|||Bruno,
You can assign DBNull.Value to the parameter value. You can also not append
the parameters to the collection at all, ADO.NET use named parameters when
using sqlClient data provider.
AMB
"Bruno N" wrote:

> Thank you!
> I will look into my code
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:%23Z5g3pkLFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>