I have 2 tables:
Table: UserTable
Field: Id (pk)
Field: FullName
Table: MarriageTable
Field: MarriageId (pk)
Field: Husband
Field: Wife
The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
valid users from the UserTable.
In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
MarriageTable.Wife. But, the Diagram has trouble saving the Wife
relationship. Any ideas? Am I doing this correctly?
Here's the error:
'UserTable' table saved successfully
'MarriageTable' table
- Unable to create relationship 'FK_MarriageTable_UserTable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_MarriageTable_UserTable1' on table
'MarriageTable' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
Thanks.
On Wed, 29 Dec 2004 10:01:20 -0500, VB Programmer wrote:
>I have 2 tables:
>Table: UserTable
>Field: Id (pk)
>Field: FullName
>Table: MarriageTable
>Field: MarriageId (pk)
>Field: Husband
>Field: Wife
>The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
>valid users from the UserTable.
>In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
>UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
>MarriageTable.Wife. But, the Diagram has trouble saving the Wife
>relationship. Any ideas? Am I doing this correctly?
Hi VB Programmer,
Simple point and click interfaces can be great if you don't know the nitty
gritty details and don't want to know them. However, when dealing with a
complex DBMS such as SQL Server, it's best to do know the nitty gritty
details. You should get used to defining all your tables and constraints
in pure SQL.
CREATE TABLE Users (UserID int NOT NULL,
FullName varchar(60) NOT NULL,
PRIMARY KEY (UserID)
)
CREATE TABLE Marriages (MarriageID int NOT NULL,
Husband int NOT NULL,
Wife int NOT NULL,
PRIMARY KEY (MarriageID),
UNIQUE (Husband, Wife),
FOREIGN KEY (Husband)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Wife)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE
)
Run this in Query Analyzer and you'll be 100% sure that you didn't
accidentally create a foreign key "in the wrong direction".
Unfortunately, you'll still get an error. This is a limitation in SQL
Server 2000. If you use the cascades option on foreign key constraints,
SQL Server demands that there never be more than one cascading path from
any table to any other table. You'll have to modify at least one of your
foreign key constraints to do ON DELETE NO ACTION ON and ON UPDATE NO
ACTION (the default options). You'll also have to use another solution if
your applications requires cascading behaviour. Possible solutions are to
encapsulate all logic in a stored procedure or to use a trigger.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Question about Nulls in SELECT statement
Hi
Can someone please help me. I have 2 code examples that (i thought)
should produce the same results. The field in question (intNumber)
does allow Nulls. However, the results from Code 1 include the Null
record but the results from Code 2 do not.
- Table Data -
intNumber
4
5
NULL
- Code 1 -
SET ANSI_NULLS OFF
DECLARE @.intVar int
SELECT @.intVar = 4
SELECT * FROM tblTest WHERE (intNumber <> @.intVar)
- Results 1 -
5
NULL
- Code 2 -
SET ANSI_NULLS OFF
SELECT * FROM tblTest WHERE (intNumber <> 4)
- Results 2 -
5
Many thanks.
JuliaYes, the results are different. I strongly recommend that if possible
you avoid the ANSI_NULLS OFF setting. ANSI_NULLS OFF is a legacy
feature and most people recognize ANSI_NULLS ON as the standard to be
adopted for new code since SQL Server version 7.0.
If you must use ANSI_NULLS OFF then you'll have to cope with its
peculiarities (yes, even more peculiar than ANSI NULLs!). NULLs are
treated differently in constants, columns and variables.
David Portas
SQL Server MVP
--|||Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4. I
s
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this tabl
e
but can be found else where.
Many thanks.
Julia.|||To return all NULL rows:
WHERE COALESCE(intNumber,0) <> 4
WHERE intNumber IS NULL OR intNumber <> 4
To return no NULL rows:
WHERE COALESCE(intNumber,4) <> 4
WHERE intNumber IS NOT NULL AND intNumber <> 4
(What is an intNumber, anyway? Kind of a funny name for a piece of data.)
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
> Hi David
> Thanks for your reply and I take on board what you are saying about
> ANSI_NULLS.
> So now I have a different question really – I have a table that allows
> Nulls
> in certain columns. How to I find these records in a select statement,
> i.e.
> I want the Null records also when I say all records where intNumber <> 4.
> Is
> the ‘best practice’ solution to also include ‘or intNumber is
> null’? Or
> should I not be using Nulls at all? The reason is that my table is
> actually
> representing data that can be inherited from another source – I used
> null
> fields to recognize that this particular bit of data is not set in this
> table
> but can be found else where.
> Many thanks.
> Julia.
>|||That's the standard way, so your query would be:
select
*
from
MyTable
where
intNumber <> 4
or intNumber is null
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4.
Is
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this
table
but can be found else where.
Many thanks.
Julia.
Can someone please help me. I have 2 code examples that (i thought)
should produce the same results. The field in question (intNumber)
does allow Nulls. However, the results from Code 1 include the Null
record but the results from Code 2 do not.
- Table Data -
intNumber
4
5
NULL
- Code 1 -
SET ANSI_NULLS OFF
DECLARE @.intVar int
SELECT @.intVar = 4
SELECT * FROM tblTest WHERE (intNumber <> @.intVar)
- Results 1 -
5
NULL
- Code 2 -
SET ANSI_NULLS OFF
SELECT * FROM tblTest WHERE (intNumber <> 4)
- Results 2 -
5
Many thanks.
JuliaYes, the results are different. I strongly recommend that if possible
you avoid the ANSI_NULLS OFF setting. ANSI_NULLS OFF is a legacy
feature and most people recognize ANSI_NULLS ON as the standard to be
adopted for new code since SQL Server version 7.0.
If you must use ANSI_NULLS OFF then you'll have to cope with its
peculiarities (yes, even more peculiar than ANSI NULLs!). NULLs are
treated differently in constants, columns and variables.
David Portas
SQL Server MVP
--|||Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4. I
s
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this tabl
e
but can be found else where.
Many thanks.
Julia.|||To return all NULL rows:
WHERE COALESCE(intNumber,0) <> 4
WHERE intNumber IS NULL OR intNumber <> 4
To return no NULL rows:
WHERE COALESCE(intNumber,4) <> 4
WHERE intNumber IS NOT NULL AND intNumber <> 4
(What is an intNumber, anyway? Kind of a funny name for a piece of data.)
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
> Hi David
> Thanks for your reply and I take on board what you are saying about
> ANSI_NULLS.
> So now I have a different question really – I have a table that allows
> Nulls
> in certain columns. How to I find these records in a select statement,
> i.e.
> I want the Null records also when I say all records where intNumber <> 4.
> Is
> the ‘best practice’ solution to also include ‘or intNumber is
> null’? Or
> should I not be using Nulls at all? The reason is that my table is
> actually
> representing data that can be inherited from another source – I used
> null
> fields to recognize that this particular bit of data is not set in this
> table
> but can be found else where.
> Many thanks.
> Julia.
>|||That's the standard way, so your query would be:
select
*
from
MyTable
where
intNumber <> 4
or intNumber is null
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4.
Is
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this
table
but can be found else where.
Many thanks.
Julia.
Question about Null value in cross tab
Dear all,
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,
Right Click the summary field, select "Format Field", go to "Common" Tab.
You can find X-2 for "Display" String option. Click that button and write the following formula
If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue
This will display 0 if that field contains Null and the actual value if it is not null.
Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue
for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue
both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,
For the first question, Hope you have written the formula in "Display String" (Format)section.
Try to check CurrentFieldValue instead of {table.field}.
I checked with zero, it worked.
For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database
How come this happens??
Thanks~|||I have a new question again.
The crystal report have not show the record that is zero
How can I show the zero record??
Thanks very muchsql
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,
Right Click the summary field, select "Format Field", go to "Common" Tab.
You can find X-2 for "Display" String option. Click that button and write the following formula
If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue
This will display 0 if that field contains Null and the actual value if it is not null.
Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue
for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue
both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,
For the first question, Hope you have written the formula in "Display String" (Format)section.
Try to check CurrentFieldValue instead of {table.field}.
I checked with zero, it worked.
For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database
How come this happens??
Thanks~|||I have a new question again.
The crystal report have not show the record that is zero
How can I show the zero record??
Thanks very muchsql
question about lowercase in sql
Hi,
the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
case).
I did this sql statement:
select myfield from mytable where myfield='MYVALUE'
and .. i got the record.
May i conclude that SQL does make no difference between 'MYVALUE' and
'myvalue'?
Thanks
BobOn Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
> Hi,
> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
> case).
> I did this sql statement:
> select myfield from mytable where myfield='MYVALUE'
> and .. i got the record.
> May i conclude that SQL does make no difference between 'MYVALUE' and
> 'myvalue'?
> Thanks
> Bob
The answer is yes. SQL server is not case sensitive.|||Thanks
"SB" <othellomy@.yahoo.com> schreef in bericht
news:1189151637.431898.75760@.o80g2000hse.googlegroups.com...
> On Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
>> Hi,
>> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
>> case).
>> I did this sql statement:
>> select myfield from mytable where myfield='MYVALUE'
>> and .. i got the record.
>> May i conclude that SQL does make no difference between 'MYVALUE' and
>> 'myvalue'?
>> Thanks
>> Bob
> The answer is yes. SQL server is not case sensitive.
>|||This answer isn't necessarily true. The column collation is used which
determines case sensitivity. It can be overridden at the TSQL statement level
using COLLATE, but if this is not there, you have to refer to the column's
collation.
HTH,
Paul Ibison|||Hello Bob,
I just want to prevent a possible misunderstanding.
SQL Server is not always "case insensetive". This is about the collation
settings that you set when you setup SQL Server.
If you selected "Case Sensetive" when you set it up, then your current query
would return nothing.
Please read below document from BOL for more information about this setting:
http://msdn2.microsoft.com/en-us/library/ms143508.aspx
--
Ekrem Önsoy
"Bob" <no@.spam.ur> wrote in message
news:OpBGlTS8HHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks
> "SB" <othellomy@.yahoo.com> schreef in bericht
> news:1189151637.431898.75760@.o80g2000hse.googlegroups.com...
>> On Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
>> Hi,
>> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in
>> lower
>> case).
>> I did this sql statement:
>> select myfield from mytable where myfield='MYVALUE'
>> and .. i got the record.
>> May i conclude that SQL does make no difference between 'MYVALUE' and
>> 'myvalue'?
>> Thanks
>> Bob
>> The answer is yes. SQL server is not case sensitive.
>|||Respectfully, this is also only partially true. The collation setting chosen
during the installation becomes the default for the new databases, but it may
be overridden during database creation (or afterwards). Likewise, the
database collation becomes the default for new text-based columns, but this
may also be overridden. The column-level collation is the one being used in
the query, provided that the keyword COLLATE isn't used.
Cheers,
Paul Ibison|||Thanks for addition ;)
--
Ekrem Ã?nsoy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:A8FE3DCB-F05A-4A4A-B76C-D8CBE3C0C957@.microsoft.com...
> Respectfully, this is also only partially true. The collation setting
> chosen
> during the installation becomes the default for the new databases, but it
> may
> be overridden during database creation (or afterwards). Likewise, the
> database collation becomes the default for new text-based columns, but
> this
> may also be overridden. The column-level collation is the one being used
> in
> the query, provided that the keyword COLLATE isn't used.
> Cheers,
> Paul Ibison
>sql
the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
case).
I did this sql statement:
select myfield from mytable where myfield='MYVALUE'
and .. i got the record.
May i conclude that SQL does make no difference between 'MYVALUE' and
'myvalue'?
Thanks
BobOn Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
> Hi,
> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
> case).
> I did this sql statement:
> select myfield from mytable where myfield='MYVALUE'
> and .. i got the record.
> May i conclude that SQL does make no difference between 'MYVALUE' and
> 'myvalue'?
> Thanks
> Bob
The answer is yes. SQL server is not case sensitive.|||Thanks
"SB" <othellomy@.yahoo.com> schreef in bericht
news:1189151637.431898.75760@.o80g2000hse.googlegroups.com...
> On Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
>> Hi,
>> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
>> case).
>> I did this sql statement:
>> select myfield from mytable where myfield='MYVALUE'
>> and .. i got the record.
>> May i conclude that SQL does make no difference between 'MYVALUE' and
>> 'myvalue'?
>> Thanks
>> Bob
> The answer is yes. SQL server is not case sensitive.
>|||This answer isn't necessarily true. The column collation is used which
determines case sensitivity. It can be overridden at the TSQL statement level
using COLLATE, but if this is not there, you have to refer to the column's
collation.
HTH,
Paul Ibison|||Hello Bob,
I just want to prevent a possible misunderstanding.
SQL Server is not always "case insensetive". This is about the collation
settings that you set when you setup SQL Server.
If you selected "Case Sensetive" when you set it up, then your current query
would return nothing.
Please read below document from BOL for more information about this setting:
http://msdn2.microsoft.com/en-us/library/ms143508.aspx
--
Ekrem Önsoy
"Bob" <no@.spam.ur> wrote in message
news:OpBGlTS8HHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks
> "SB" <othellomy@.yahoo.com> schreef in bericht
> news:1189151637.431898.75760@.o80g2000hse.googlegroups.com...
>> On Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
>> Hi,
>> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in
>> lower
>> case).
>> I did this sql statement:
>> select myfield from mytable where myfield='MYVALUE'
>> and .. i got the record.
>> May i conclude that SQL does make no difference between 'MYVALUE' and
>> 'myvalue'?
>> Thanks
>> Bob
>> The answer is yes. SQL server is not case sensitive.
>|||Respectfully, this is also only partially true. The collation setting chosen
during the installation becomes the default for the new databases, but it may
be overridden during database creation (or afterwards). Likewise, the
database collation becomes the default for new text-based columns, but this
may also be overridden. The column-level collation is the one being used in
the query, provided that the keyword COLLATE isn't used.
Cheers,
Paul Ibison|||Thanks for addition ;)
--
Ekrem Ã?nsoy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:A8FE3DCB-F05A-4A4A-B76C-D8CBE3C0C957@.microsoft.com...
> Respectfully, this is also only partially true. The collation setting
> chosen
> during the installation becomes the default for the new databases, but it
> may
> be overridden during database creation (or afterwards). Likewise, the
> database collation becomes the default for new text-based columns, but
> this
> may also be overridden. The column-level collation is the one being used
> in
> the query, provided that the keyword COLLATE isn't used.
> Cheers,
> Paul Ibison
>sql
Tuesday, March 20, 2012
Question about incrementing a field
Hi everyone. I'm a new user to ASP.NET using VB.NET and SQL Server.
I have one question.
I am currently creating a website for my dissertation whereby whenever acustomer purchases a product, a number is incremented within the'Product' table. The column that needs to be incremented is called'ProductSold'.
I have got an Insert query to store the orders in the 'Orders' tablealready within my webpage. Is there a SQL Query that I could use toincrement the number in the 'Product' Table straight after my InsertQuery?
I really appreciate if anyone could help me in this matter!
you could write a stored proc in which you query in for theMAX(productnumber) and increment it and then do the insert. Why not usean Identity column ?
|||Hi Ndinakar,
Thanks for your reply!
You may think I'm really dumb for saying this. But the Identity Columnwill only increment when a new record is made within the productstable? How can I program the Identity Seed to increment when thatproduct is purchased?
And now going on to the Stored Procedure, I will write code toincrement the number and then write the Insert Statement? What do youmean by the MAX(productnumber)?
Could I write the Insert Statment first and then do an Update Statementto increment the number? Can you do that in an Update statement?
Sorry! I'm just really confused.
|||
I read your post in a hurry. Sorry about that.
Yes. Write your insert stmt first. then followed by an update. Dont worry about the MAX. You could do something like:
UPDATE
Product
SET
ProductSold = ProductSold + 1 ( or however if more than one order wa placed you could use a variable)
WHERE
Productid = ...
|||Oh I see now!
Thanks for all your help! Much appreciated!
I have one question.
I am currently creating a website for my dissertation whereby whenever acustomer purchases a product, a number is incremented within the'Product' table. The column that needs to be incremented is called'ProductSold'.
I have got an Insert query to store the orders in the 'Orders' tablealready within my webpage. Is there a SQL Query that I could use toincrement the number in the 'Product' Table straight after my InsertQuery?
I really appreciate if anyone could help me in this matter!
you could write a stored proc in which you query in for theMAX(productnumber) and increment it and then do the insert. Why not usean Identity column ?
|||Hi Ndinakar,
Thanks for your reply!
You may think I'm really dumb for saying this. But the Identity Columnwill only increment when a new record is made within the productstable? How can I program the Identity Seed to increment when thatproduct is purchased?
And now going on to the Stored Procedure, I will write code toincrement the number and then write the Insert Statement? What do youmean by the MAX(productnumber)?
Could I write the Insert Statment first and then do an Update Statementto increment the number? Can you do that in an Update statement?
Sorry! I'm just really confused.
|||
You may think I'm really dumb for saying this. But the Identity Columnwill only increment when a new record is made within the productstable? How can I program the Identity Seed to increment when thatproduct is purchased?
I read your post in a hurry. Sorry about that.
Could I write the Insert Statment first and then do an Update Statementto increment the number? Can you do that in an Update statement?
Yes. Write your insert stmt first. then followed by an update. Dont worry about the MAX. You could do something like:
UPDATE
Product
SET
ProductSold = ProductSold + 1 ( or however if more than one order wa placed you could use a variable)
WHERE
Productid = ...
|||Oh I see now!
Thanks for all your help! Much appreciated!
Question about if condition in ReportBuilder
I have a question about table layout report. I want to do that:
I have a table report and on that report i have a Total field of money. And i want to do that if condition on that field, so if the prize is less then 150, the color of the text it should be red otherwise it should be other color. So how can i achieve that. I know that this is possible in Reporting services in Report Designer is like =IIF(field.?.Value<150;"red";"gree")
But if i make that same in report builder, the what it does, it displays not the value in color, but it displays the text "red" od "green".
I will be very happy and gratefull if somebody can help me.
thank you in advance.
Hello,
You need to place your expression in the color property, not the textbox.
=Iif(Fields!?.Value < 150, "Red", "Green")
Jarret
|||Hello,
yes this works in Report designer, when you work on reports RDL, but what if you want to do that in Report Builder?
I know that this work in Color property in report designer. But when you want to make that in Report Builder (the client app od RS), then it just get the word out, it doesn't set the color.
Andrej
|||=IIF(FEILD.TEXTBOX.VALUE < 150,"RED","BLUE"I AM YOGESH FROM HYD IF THIS DOES'NT WORKS JUST CALL ME AT 09391323274 I AM FROM HYDERABAD INDIA
Question about IDENTITY
Just a quick question.
I have a table with an IDENTITY field. I inserted a record using the is 1024 so as to distinguish the record. There are only about 40 records in the table, including id 1024.
What I need is for the IDENTITY to go back to 39 and start it's count from there to fill the gap up to 1024. How is this done?
Delete the record with id 1024 then edit the table and set the "Identity Seed" to 39. You could probably write an alter table statement to handle this or go into enterprise manager and design the table.
Why can't the ID's continue after 1024? Other than looks it shouldn't matter as long as the ID's are unique.
-- LZ
|||True. It doesn't matter, the IDs are unique, and 1024 serves it purpose...
For the most part, I am just trying to get a better handle on IDENTITY and the various ways to control it.
I only have access to the database through code (and a form that I hid on the server that allows me to insert SQL statements), and I am just trying to make sure I have as much control over the data as I would if I had direct access to it.
Thanks for your input...
question about how to access column/field information
Hi,
I am just starting out in SQL Server and, as always, I am throwing myself in
at the deep end by trying more advanced things. I understand the basics like
INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass some kind
of SQL object to a function which will then extract the field names, field
types, and field sizes from that object.
I have tried using DataSet and DataTable but I cannot see how I can extract
all three things I need from the column information. I'd also rather do what
I am doing on the actual table structure itself, rather than on a returned
set of records from the table (dataset).
Any help would be appreciated.
Thanks in advance,
RichardAs far as you are writing in the SQL Server group I think that you want to
get this information via TSQL ?!
If so, just look at this Query where #Orders can be replaced with the Name
of your Object to be queried. The Best way woud be to solve the Object name
to a ObjectID and to search for then, just for easy understanding it is
filtered via the object name here. :-)
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
That will give you all the information youll wanted. If you want the
information via C# oder ADO.NET in common you could get i with FILLSCHEMA:
http://msdn.microsoft.com/library/d...r />
atopic.asp
HTH, Jens Smeyer.
"Richard" <acorn@.acorn.net> schrieb im Newsbeitrag
news:425cb509$1_3@.news.melbourne.pipenetworks.com...
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing myself
> in at the deep end by trying more advanced things. I understand the basics
> like INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass
> some kind of SQL object to a function which will then extract the field
> names, field types, and field sizes from that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself, rather
> than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
>|||Richard wrote:
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing
> myself in at the deep end by trying more advanced things. I
> understand the basics like INSERT, DELETE, UPDATE, SELECT etc What I
> am trying to do is pass some kind of SQL object to a function which
> will then extract the field names, field types, and field sizes from
> that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself,
> rather than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
You can interrogate the system tables or ideally use the
INFORMATION_SCHEMA views. See BOL for more information.
I'm not sure what you mean by "pass a SQL object to a function", unless
what you're saying is that you want a stored procedure that returns some
meta data for a table.
If you're executing a query and want the result set details, you can do
that easily from client in ADO or ADO.Net.
Can you provide some additional detail about what you're trying to do?
David Gugick
Imceda Software
www.imceda.com|||What I'd like to be able to do is, given the name of a table or a table
object of some description, interrogate that table for all the fields in
that table, and also information about those fields, so that I can process
that information.
As much information as possible about the fields would be nice, but at the
minimum I need the field name, type (char, bigint etc) and size of the
field.
Thanks again,
Richard
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> You can interrogate the system tables or ideally use the
> INFORMATION_SCHEMA views. See BOL for more information.
> I'm not sure what you mean by "pass a SQL object to a function", unless
> what you're saying is that you want a stored procedure that returns some
> meta data for a table.
> If you're executing a query and want the result set details, you can do
> that easily from client in ADO or ADO.Net.
> Can you provide some additional detail about what you're trying to do?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Richard
Read up this article
http://www.sommarskog.se/dynamic_sql.html
"Richard" <acorn@.acorn.net> wrote in message
news:425cc012_1@.news.melbourne.pipenetworks.com...
> What I'd like to be able to do is, given the name of a table or a table
> object of some description, interrogate that table for all the fields in
> that table, and also information about those fields, so that I can process
> that information.
> As much information as possible about the fields would be nice, but at the
> minimum I need the field name, type (char, bigint etc) and size of the
> field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>|||Use the INFORMATION_SCHEMA views as suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard" <acorn@.acorn.net> wrote in message news:425cc012_1@.news.melbourne.pipenetworks.co
m...
> What I'd like to be able to do is, given the name of a table or a table ob
ject of some
> description, interrogate that table for all the fields in that table, and
also information about
> those fields, so that I can process that information.
> As much information as possible about the fields would be nice, but at the
minimum I need the
> field name, type (char, bigint etc) and size of the field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
I am just starting out in SQL Server and, as always, I am throwing myself in
at the deep end by trying more advanced things. I understand the basics like
INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass some kind
of SQL object to a function which will then extract the field names, field
types, and field sizes from that object.
I have tried using DataSet and DataTable but I cannot see how I can extract
all three things I need from the column information. I'd also rather do what
I am doing on the actual table structure itself, rather than on a returned
set of records from the table (dataset).
Any help would be appreciated.
Thanks in advance,
RichardAs far as you are writing in the SQL Server group I think that you want to
get this information via TSQL ?!
If so, just look at this Query where #Orders can be replaced with the Name
of your Object to be queried. The Best way woud be to solve the Object name
to a ObjectID and to search for then, just for easy understanding it is
filtered via the object name here. :-)
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
That will give you all the information youll wanted. If you want the
information via C# oder ADO.NET in common you could get i with FILLSCHEMA:
http://msdn.microsoft.com/library/d...r />
atopic.asp
HTH, Jens Smeyer.
"Richard" <acorn@.acorn.net> schrieb im Newsbeitrag
news:425cb509$1_3@.news.melbourne.pipenetworks.com...
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing myself
> in at the deep end by trying more advanced things. I understand the basics
> like INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass
> some kind of SQL object to a function which will then extract the field
> names, field types, and field sizes from that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself, rather
> than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
>|||Richard wrote:
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing
> myself in at the deep end by trying more advanced things. I
> understand the basics like INSERT, DELETE, UPDATE, SELECT etc What I
> am trying to do is pass some kind of SQL object to a function which
> will then extract the field names, field types, and field sizes from
> that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself,
> rather than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
You can interrogate the system tables or ideally use the
INFORMATION_SCHEMA views. See BOL for more information.
I'm not sure what you mean by "pass a SQL object to a function", unless
what you're saying is that you want a stored procedure that returns some
meta data for a table.
If you're executing a query and want the result set details, you can do
that easily from client in ADO or ADO.Net.
Can you provide some additional detail about what you're trying to do?
David Gugick
Imceda Software
www.imceda.com|||What I'd like to be able to do is, given the name of a table or a table
object of some description, interrogate that table for all the fields in
that table, and also information about those fields, so that I can process
that information.
As much information as possible about the fields would be nice, but at the
minimum I need the field name, type (char, bigint etc) and size of the
field.
Thanks again,
Richard
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> You can interrogate the system tables or ideally use the
> INFORMATION_SCHEMA views. See BOL for more information.
> I'm not sure what you mean by "pass a SQL object to a function", unless
> what you're saying is that you want a stored procedure that returns some
> meta data for a table.
> If you're executing a query and want the result set details, you can do
> that easily from client in ADO or ADO.Net.
> Can you provide some additional detail about what you're trying to do?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Richard
Read up this article
http://www.sommarskog.se/dynamic_sql.html
"Richard" <acorn@.acorn.net> wrote in message
news:425cc012_1@.news.melbourne.pipenetworks.com...
> What I'd like to be able to do is, given the name of a table or a table
> object of some description, interrogate that table for all the fields in
> that table, and also information about those fields, so that I can process
> that information.
> As much information as possible about the fields would be nice, but at the
> minimum I need the field name, type (char, bigint etc) and size of the
> field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>|||Use the INFORMATION_SCHEMA views as suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard" <acorn@.acorn.net> wrote in message news:425cc012_1@.news.melbourne.pipenetworks.co
m...
> What I'd like to be able to do is, given the name of a table or a table ob
ject of some
> description, interrogate that table for all the fields in that table, and
also information about
> those fields, so that I can process that information.
> As much information as possible about the fields would be nice, but at the
minimum I need the
> field name, type (char, bigint etc) and size of the field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
Question about Grouping rows in Select statements with joins
Hi
I am trying to use the select statement to return rows which have a MAX
date and then associate the date with a Title field in the #c table. At
the moment when I add the Title field to the Select list I am getting
the latest date for each Title. How do I exclude the Title field form
the GROUP BY clause but display it with the date in the select list?
/* correct number of records but without the required Title field */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp)
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
DROP TABLE #h
DROP TABLE #c
GO
/* adding the Title field gives me extra rows. I only want the
Title associated with the latest date */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID, #c.Title
DROP TABLE #h
DROP TABLE #c
GO
Cheers
j055try
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
select email, max_date, title from (
SELECT #h.EmailID email, MAX(#h.DateStamp) max_date
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
) t
inner join #h on #h.EmailID=t.email and #h.DateStamp = t.max_date
inner join #c on #h.ContentID=#c.ContentID
DROP TABLE #h
DROP TABLE #c
GO
"j055" wrote:
> Hi
> I am trying to use the select statement to return rows which have a MAX
> date and then associate the date with a Title field in the #c table. At
> the moment when I add the Title field to the Select list I am getting
> the latest date for each Title. How do I exclude the Title field form
> the GROUP BY clause but display it with the date in the select list?
>
> /* correct number of records but without the required Title field */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp)
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID
> DROP TABLE #h
> DROP TABLE #c
> GO
> /* adding the Title field gives me extra rows. I only want the
> Title associated with the latest date */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID, #c.Title
> DROP TABLE #h
> DROP TABLE #c
> GO
> Cheers
> j055
>
I am trying to use the select statement to return rows which have a MAX
date and then associate the date with a Title field in the #c table. At
the moment when I add the Title field to the Select list I am getting
the latest date for each Title. How do I exclude the Title field form
the GROUP BY clause but display it with the date in the select list?
/* correct number of records but without the required Title field */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp)
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
DROP TABLE #h
DROP TABLE #c
GO
/* adding the Title field gives me extra rows. I only want the
Title associated with the latest date */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID, #c.Title
DROP TABLE #h
DROP TABLE #c
GO
Cheers
j055try
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
select email, max_date, title from (
SELECT #h.EmailID email, MAX(#h.DateStamp) max_date
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
) t
inner join #h on #h.EmailID=t.email and #h.DateStamp = t.max_date
inner join #c on #h.ContentID=#c.ContentID
DROP TABLE #h
DROP TABLE #c
GO
"j055" wrote:
> Hi
> I am trying to use the select statement to return rows which have a MAX
> date and then associate the date with a Title field in the #c table. At
> the moment when I add the Title field to the Select list I am getting
> the latest date for each Title. How do I exclude the Title field form
> the GROUP BY clause but display it with the date in the select list?
>
> /* correct number of records but without the required Title field */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp)
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID
> DROP TABLE #h
> DROP TABLE #c
> GO
> /* adding the Title field gives me extra rows. I only want the
> Title associated with the latest date */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID, #c.Title
> DROP TABLE #h
> DROP TABLE #c
> GO
> Cheers
> j055
>
Question about GridView update query
I try to calculate a field value and update other. It almost works
UPDATE t_Shopping_cart SET [Product_code] = @.Product_code, [Quantity] = @.Quantity, [Total] = Quantity * Price WHERE ([Product_code] = @.Product_code)
I try to update t_Shopping_cart table with quantity values from grid view and calculate sum of products (quantity*Price). Best I can get is that quantity values are old. Some comments about my query:Price is also a field in table and control. [Product_code] = @.Product_code part is probably not needed.
Any ideas?
Leif
So there is a column in t_Shopping_cart with the name Price?
Then maybe you can use this:
UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)
|||"So there is a column in t_Shopping_cart with the name Price?" Yes. I'll try that "UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)" tomorrow.
Thanks
Leif
|||Np, feel free to get back to me if it doesn't work!
|||Hi. A short question. Is this a good way to this. The more I read about ways to do this the more ways there seems to be calculating sub totals and probably checking duplicates too.
Regards
Leif
question about Gridview and sqlDataSource
i am now writing a web page which can allow users to edit the field(display in the gridview->gridview source is from sqldatasource), there is a problem(it seems that i have followed all steps in the book):
when the i edit the field and click update, in the web site, i can see those changes. however, i cant see any change in database table.
i cant find solution on this, could anyone help me to solve this problem? thx!
Hi, are you checking the right database's table? If you can see the records changed on your gridview, it is CHNAGED in your source table. Please check again.
|||it seems that i have made some mistake while configure data source...but now is ok...thx!!
Question about getting the latest identity field in a specific table
Dear All,
Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?
Thanks for your kind attention
Yours faithfully,
BennyI would rather use SCOPE_IDENTITY() or @.@.IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.
- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.
--
- Anith
( Please reply to newsgroups only )|||Anith Sen (anith@.bizdatasolutions.com) writes:
> - IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.
One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @.@.identity
cannot.
Thus, ident_current() is rarely the function you should call in application
code.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
question about full-text search
Hi all,
I have a single table (TAB_FULL_TEXT_NET) , with only one field indexed
in my sql catalog.
I am runing queries agaist the catalog using CONTAINSTABLE
select [KEY] objDocumento, tf.RANK RANK from
CONTAINSTABLE(TAB_FULL_TEXT_NET, *, '42.55.14.1.2', 300) tf
The problem is that some results returned by the query do not to mach
my search. How can this be possible.
For example i am looking for: 42.55.14.1.2
but some results containing similar words like
42.55.14.2
42.55.14.9.3
42.55.14.6.1
are also returned in the results set
Questions:
1.- Does the CONTAINSTABLE have problems to deal with period '.'
2.- How can I limit my search only to exact words
obs: I have also tried to put the search word into "" (ex:
"42.55.14.1.2") , but same results
Thank you very much
Alberto
Yes, it thinks the . is white space, so your search is done on 42 55 14 1 2.
Matches will be to 42 55 14 1 2 or 42!55!14!1!2, in other words any non
alphanumeric character between the numbers will yield a match.
however, I am unable to repro your problem. Can you tell me what version of
SQL you are running, and the version of the OS, also please issue the
following query and tell me what you get.
select @.@.language.
What word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1132582567.679893.208520@.g14g2000cwa.googlegr oups.com...
> Hi all,
> I have a single table (TAB_FULL_TEXT_NET) , with only one field indexed
> in my sql catalog.
> I am runing queries agaist the catalog using CONTAINSTABLE
> select [KEY] objDocumento, tf.RANK RANK from
> CONTAINSTABLE(TAB_FULL_TEXT_NET, *, '42.55.14.1.2', 300) tf
> The problem is that some results returned by the query do not to mach
> my search. How can this be possible.
> For example i am looking for: 42.55.14.1.2
> but some results containing similar words like
> 42.55.14.2
> 42.55.14.9.3
> 42.55.14.6.1
> are also returned in the results set
> Questions:
> 1.- Does the CONTAINSTABLE have problems to deal with period '.'
> 2.- How can I limit my search only to exact words
> obs: I have also tried to put the search word into "" (ex:
> "42.55.14.1.2") , but same results
> Thank you very much
> Alberto
>
|||Thanks for your quick answer!
well I am using English (USA) as word breaker, but most of the text is
in portuguese ! (is that a problem?)
about the version, see below
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
what do you suggest me to deal with that situation?..
thanks
Alberto
|||Are the documents stored in the image data type column? Are there any html
tags in the content?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1132595252.224093.110370@.g49g2000cwa.googlegr oups.com...
> Thanks for your quick answer!
> well I am using English (USA) as word breaker, but most of the text is
> in portuguese ! (is that a problem?)
> about the version, see below
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> what do you suggest me to deal with that situation?..
> thanks
> Alberto
>
|||Hi again..
well, I have 2 tables in my catalog
Table 1: one (only text) field indexed
Table 2: One binary field indexed
about html text...not in the text field (table 1), but i am not sure
about Table 2, because users can include html files in the binary
field...
well, any help to deal with the period ('.') would be apreciated...
thanks
Alberto
I have a single table (TAB_FULL_TEXT_NET) , with only one field indexed
in my sql catalog.
I am runing queries agaist the catalog using CONTAINSTABLE
select [KEY] objDocumento, tf.RANK RANK from
CONTAINSTABLE(TAB_FULL_TEXT_NET, *, '42.55.14.1.2', 300) tf
The problem is that some results returned by the query do not to mach
my search. How can this be possible.
For example i am looking for: 42.55.14.1.2
but some results containing similar words like
42.55.14.2
42.55.14.9.3
42.55.14.6.1
are also returned in the results set
Questions:
1.- Does the CONTAINSTABLE have problems to deal with period '.'
2.- How can I limit my search only to exact words
obs: I have also tried to put the search word into "" (ex:
"42.55.14.1.2") , but same results
Thank you very much
Alberto
Yes, it thinks the . is white space, so your search is done on 42 55 14 1 2.
Matches will be to 42 55 14 1 2 or 42!55!14!1!2, in other words any non
alphanumeric character between the numbers will yield a match.
however, I am unable to repro your problem. Can you tell me what version of
SQL you are running, and the version of the OS, also please issue the
following query and tell me what you get.
select @.@.language.
What word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1132582567.679893.208520@.g14g2000cwa.googlegr oups.com...
> Hi all,
> I have a single table (TAB_FULL_TEXT_NET) , with only one field indexed
> in my sql catalog.
> I am runing queries agaist the catalog using CONTAINSTABLE
> select [KEY] objDocumento, tf.RANK RANK from
> CONTAINSTABLE(TAB_FULL_TEXT_NET, *, '42.55.14.1.2', 300) tf
> The problem is that some results returned by the query do not to mach
> my search. How can this be possible.
> For example i am looking for: 42.55.14.1.2
> but some results containing similar words like
> 42.55.14.2
> 42.55.14.9.3
> 42.55.14.6.1
> are also returned in the results set
> Questions:
> 1.- Does the CONTAINSTABLE have problems to deal with period '.'
> 2.- How can I limit my search only to exact words
> obs: I have also tried to put the search word into "" (ex:
> "42.55.14.1.2") , but same results
> Thank you very much
> Alberto
>
|||Thanks for your quick answer!
well I am using English (USA) as word breaker, but most of the text is
in portuguese ! (is that a problem?)
about the version, see below
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
what do you suggest me to deal with that situation?..
thanks
Alberto
|||Are the documents stored in the image data type column? Are there any html
tags in the content?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1132595252.224093.110370@.g49g2000cwa.googlegr oups.com...
> Thanks for your quick answer!
> well I am using English (USA) as word breaker, but most of the text is
> in portuguese ! (is that a problem?)
> about the version, see below
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> what do you suggest me to deal with that situation?..
> thanks
> Alberto
>
|||Hi again..
well, I have 2 tables in my catalog
Table 1: one (only text) field indexed
Table 2: One binary field indexed
about html text...not in the text field (table 1), but i am not sure
about Table 2, because users can include html files in the binary
field...
well, any help to deal with the period ('.') would be apreciated...
thanks
Alberto
Monday, March 12, 2012
Question about datetime field
Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
Question about datetime field
Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>
Question about datetime field
Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
Question about datetime
Hi,
I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 all
null true
If the user do not enter a date in the text box it is returning a error type
mismatch ever though the field is set to allow Nulls.
What can I do to correct this
Thanks
DibCan you post the datetime format you are using while inserting the data?
Anith|||Check the code that generates an insert from the empty
text box. My guess: the insert is trying to put the empty
string '' into a datetime column. The empty string is not
a valid datetime string.
Steve Kass
Drew University
Dib wrote:
>Hi,
>I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 al
l
>null true
>If the user do not enter a date in the text box it is returning a error typ
e
>mismatch ever though the field is set to allow Nulls.
>What can I do to correct this
>Thanks
>Dib
>
>|||There is no format, however when the form loads if the field is blank I am
inserting "" empty string to the textbox,
if you think this is the problem how can I correct it, When in Debug Mode
the textbox is an "" empty string.
Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Check the code that generates an insert from the empty
> text box. My guess: the insert is trying to put the empty
> string '' into a datetime column. The empty string is not
> a valid datetime string.
> Steve Kass
> Drew University
> Dib wrote:
>
all
type|||I don't know. If there is a newsgroup specific to the particular front
end you are using to create this form and connect it with the database,
that might be a better place to ask. If you want a SQL Server solution,
the best I can suggest is to insert into a varchar-type column in a
staging table or use an instead of trigger to capture the form
information and generate an insert after turning '' into NULL. But
neither of these is as good a solution finding a way to create the
correct insert statement. I would expect most front-end programs to
have some kind of control that corresponds more directly to SQL Server
datetime than just a string/text control.
SK
Dib wrote:
>There is no format, however when the form loads if the field is blank I am
>inserting "" empty string to the textbox,
>if you think this is the problem how can I correct it, When in Debug Mode
>the textbox is an "" empty string.
>Thanks
>Dib
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
>
>all
>
>type
>
>
>|||Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:ePzRSXMKFHA.2764@.tk2msftngp13.phx.gbl...
> I don't know. If there is a newsgroup specific to the particular front
> end you are using to create this form and connect it with the database,
> that might be a better place to ask. If you want a SQL Server solution,
> the best I can suggest is to insert into a varchar-type column in a
> staging table or use an instead of trigger to capture the form
> information and generate an insert after turning '' into NULL. But
> neither of these is as good a solution finding a way to create the
> correct insert statement. I would expect most front-end programs to
> have some kind of control that corresponds more directly to SQL Server
> datetime than just a string/text control.
> SK
> Dib wrote:
>
am
8|||You cannot insert "" into a datefield. What I usually do is create a stored
procedure to do the insert with input variables defined that have a default
value of Null. Then from the form, I only send those fields which have a
value. The insert statment in the SP will then give the value of Null to any
variables not sent and insert a Null value into the table for that field.
If you don't want to use an SP, have the form specifically send Null as the
value for the field.
"Dib" wrote:
> Hi,
> I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 a
ll
> null true
> If the user do not enter a date in the text box it is returning a error ty
pe
> mismatch ever though the field is set to allow Nulls.
> What can I do to correct this
> Thanks
> Dib
>
>
I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 all
null true
If the user do not enter a date in the text box it is returning a error type
mismatch ever though the field is set to allow Nulls.
What can I do to correct this
Thanks
DibCan you post the datetime format you are using while inserting the data?
Anith|||Check the code that generates an insert from the empty
text box. My guess: the insert is trying to put the empty
string '' into a datetime column. The empty string is not
a valid datetime string.
Steve Kass
Drew University
Dib wrote:
>Hi,
>I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 al
l
>null true
>If the user do not enter a date in the text box it is returning a error typ
e
>mismatch ever though the field is set to allow Nulls.
>What can I do to correct this
>Thanks
>Dib
>
>|||There is no format, however when the form loads if the field is blank I am
inserting "" empty string to the textbox,
if you think this is the problem how can I correct it, When in Debug Mode
the textbox is an "" empty string.
Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Check the code that generates an insert from the empty
> text box. My guess: the insert is trying to put the empty
> string '' into a datetime column. The empty string is not
> a valid datetime string.
> Steve Kass
> Drew University
> Dib wrote:
>
all
type|||I don't know. If there is a newsgroup specific to the particular front
end you are using to create this form and connect it with the database,
that might be a better place to ask. If you want a SQL Server solution,
the best I can suggest is to insert into a varchar-type column in a
staging table or use an instead of trigger to capture the form
information and generate an insert after turning '' into NULL. But
neither of these is as good a solution finding a way to create the
correct insert statement. I would expect most front-end programs to
have some kind of control that corresponds more directly to SQL Server
datetime than just a string/text control.
SK
Dib wrote:
>There is no format, however when the form loads if the field is blank I am
>inserting "" empty string to the textbox,
>if you think this is the problem how can I correct it, When in Debug Mode
>the textbox is an "" empty string.
>Thanks
>Dib
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
>
>all
>
>type
>
>
>|||Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:ePzRSXMKFHA.2764@.tk2msftngp13.phx.gbl...
> I don't know. If there is a newsgroup specific to the particular front
> end you are using to create this form and connect it with the database,
> that might be a better place to ask. If you want a SQL Server solution,
> the best I can suggest is to insert into a varchar-type column in a
> staging table or use an instead of trigger to capture the form
> information and generate an insert after turning '' into NULL. But
> neither of these is as good a solution finding a way to create the
> correct insert statement. I would expect most front-end programs to
> have some kind of control that corresponds more directly to SQL Server
> datetime than just a string/text control.
> SK
> Dib wrote:
>
am
8|||You cannot insert "" into a datefield. What I usually do is create a stored
procedure to do the insert with input variables defined that have a default
value of Null. Then from the form, I only send those fields which have a
value. The insert statment in the SP will then give the value of Null to any
variables not sent and insert a Null value into the table for that field.
If you don't want to use an SP, have the form specifically send Null as the
value for the field.
"Dib" wrote:
> Hi,
> I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 a
ll
> null true
> If the user do not enter a date in the text box it is returning a error ty
pe
> mismatch ever though the field is set to allow Nulls.
> What can I do to correct this
> Thanks
> Dib
>
>
Friday, March 9, 2012
Question about Converting Crystal syntax into SQL or VB
I'm having an issue with a date field in Access. I have imported data
from a database and the date field is a HUGE number rather than an
actual date. I have a formula from crystal reports to convert the
number into a date, but I;m not sure how to convert the crystal report
formula into a SQL or VB formula. the formula is below.
NumberVar Yyyy ;
NumberVar Mm;
NumberVar Dd;
Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
Date (Yyyy, Mm, Dd)
I would appreciate any help!
Thanks!
JROn 3 Feb 2006 12:23:12 -0800, jennifer.rodgers@.fidessa.com wrote:
>I'm having an issue with a date field in Access. I have imported data
>from a database and the date field is a HUGE number rather than an
>actual date. I have a formula from crystal reports to convert the
>number into a date, but I;m not sure how to convert the crystal report
>formula into a SQL or VB formula. the formula is below.
>
>NumberVar Yyyy ;
>NumberVar Mm;
>NumberVar Dd;
>Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
>Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
>Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
>Date (Yyyy, Mm, Dd)
>I would appreciate any help!
>Thanks!
>JR
Hi JR,
Try if this works for you:
SELECT DATEADD(year,
(CALLDAT_HDW / 65536) - 2000,
DATEADD(month,
(CALLDAT_HDW % 65536) / 256,
DATEADD(day,
CALLDAT_HDW % 256,
'19991231')))
Hugo Kornelis, SQL Server MVP
from a database and the date field is a HUGE number rather than an
actual date. I have a formula from crystal reports to convert the
number into a date, but I;m not sure how to convert the crystal report
formula into a SQL or VB formula. the formula is below.
NumberVar Yyyy ;
NumberVar Mm;
NumberVar Dd;
Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
Date (Yyyy, Mm, Dd)
I would appreciate any help!
Thanks!
JROn 3 Feb 2006 12:23:12 -0800, jennifer.rodgers@.fidessa.com wrote:
>I'm having an issue with a date field in Access. I have imported data
>from a database and the date field is a HUGE number rather than an
>actual date. I have a formula from crystal reports to convert the
>number into a date, but I;m not sure how to convert the crystal report
>formula into a SQL or VB formula. the formula is below.
>
>NumberVar Yyyy ;
>NumberVar Mm;
>NumberVar Dd;
>Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
>Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
>Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
>Date (Yyyy, Mm, Dd)
>I would appreciate any help!
>Thanks!
>JR
Hi JR,
Try if this works for you:
SELECT DATEADD(year,
(CALLDAT_HDW / 65536) - 2000,
DATEADD(month,
(CALLDAT_HDW % 65536) / 256,
DATEADD(day,
CALLDAT_HDW % 256,
'19991231')))
Hugo Kornelis, SQL Server MVP
Question about converting bigint field to int field
We made a poor decision a long time ago when designing our database
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.
What should I be worried about when changing these fields from bigint
to int? If anything. Your help is appreciated. I did several
searches without much luck.I think you've covered the one biggy - make sure your existing data will
fit!
Others...
a) Make sure anything you join with are the same type, basically make
sure you change it everywhere including your foreign keys table.
b) Remember to do the stored procedures, udfs, triggers that may use them
as parameter.
c) You'll need to drop any constraints on your column definied with the
identity property, see example problem...
drop table t
create table t (
mycol bigint identity primary key,
t char(1) )
insert t ( t) values( 'a' )
alter table t alter column mycol int not null
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<mamorgan1@.gmail.com> wrote in message
news:1137768775.133857.115810@.g44g2000cwa.googlegr oups.com...
> We made a poor decision a long time ago when designing our database
> structure. We used bigint data types as the identity keys for many of
> our base tables. For many reasons I would like to change these fields
> to int at the largest. The largest data in these fields is around
> 200,000. I know that int can easily store this.
> What should I be worried about when changing these fields from bigint
> to int? If anything. Your help is appreciated. I did several
> searches without much luck.|||Just curious... What problems are there with having bigint as an
identity column?|||There aren't any problems - it works just fine.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"pb648174" <google@.webpaul.net> wrote in message
news:1138052526.544803.80680@.z14g2000cwz.googlegro ups.com...
> Just curious... What problems are there with having bigint as an
> identity column?|||I think its because
Bigint takes 8 bytes storage and Int takes 4 bytes.
SQL Server will not automatically promote other integer data types
(tinyint, smallint, and int) to bigint.
Regards
Amish Shah|||> What problems are there with having bigint as an identity column?
extra storage space causing slower performance of everything
Question about by Import oracle data and field is null
Hi, I've a question about importing Oracle data and some fields are null. I get an error 'Conversion failed because the data value overflowed the specified type'. When i look in preview query result, via OLE db Source editor > Preview, this field contains '<value too big to display>'.
What do i do wrong? Can somebody help me?
Thanks in advance
Olaf
It may be helpful to have a few more details. Which version of SQL Server and Oracle are you using? And which method and tool are you using to import data?|||Hello Buck,
I work with SQL server 2005 and Oracle 7.
Olaf
|||Olaf - SQL Server has several methods to export and import data, as does Oracle. For most data cleansing issues, the simplest way to eliminate data format differences is to export the data from Oracle to a text file and then import that to SQL Server. If you're looking for something that is simpler, such as using SQL Server Integration Services (SSIS) then you'll need to check the data types on both systems to ensure that they match up. You can also "push" the data from Oracle using their export tools. Whether you pull the data from Oracle using SSIS or push it from Oracle, both servers need to have the latest client drivers for each other.|||Hi Buck,What is the problem. I have a made a SSIS, in VS2005, with an ole db source, settings to the oracle db, and an ole db destination, settings to sql 2005 db. When i look in preview
query result, via OLE db Source editor > Preview, afield
contains '<value too big to display>'. This field is a datetime oracle field and it's empty. How do i check if it's empty? And what do this field then get for an value so that i don't get problem with importing it in to SQL?
Thanks in advance
Olaf
|||
As I mentioned, you'll need the latest drivers on your SQL Server System for Oracle. Also, you can check the data you're about to import using the OPENQUERY statement in SQL Server. Look in Books Online for more information on how to use that statement.
Subscribe to:
Posts (Atom)