Failure Details: fields

Showing posts with label fields. Show all posts

Thursday, March 29, 2012

Getting a field from subtotal

hi all,

i have a problem in my report; some fields grouped by matrix and include a subtotal. In column group there is 2 field and one of them contain integer values. I want to sum all of the this fields and minus from a rowgroup field. Is there a way for to sum fields in coloumn group or getting total field from subtotal?

I'm having trouble understanding your problem and your English. Could you try to rephrase your question and add a little more detail?

Thanks

Getting a error in DTS Active x script

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function

Wednesday, March 21, 2012

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?
Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegro ups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>
|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession@.nField1 int = 0,
@.nField2int = 0,
@.nField3int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking(fldField1,
fldField2,
fldField3,
fldField4)
VALUES (@.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction@.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.
|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession @.nField1 int = 0,
@.nField2 int = 0,
@.nField3 int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking( fldField1,
fldField2,
fldField3,
fldField4)
VALUES ( @.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction @.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql

getdate - only want the date

when I am doing a query I want to display just the date.
when I use the getdate function it returns the date and
time from each of the fields...is there a way to just
display the date from the fields and leave out the time?Use CONVERT or CAST. You can find some examples in books online.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||You can use following to display only date.
select convert(char(11),getdate())
If you want to display in different format , then extract
dd, mm, yy from getdate and concatinate it.
Suryakant
>--Original Message--
>when I am doing a query I want to display just the date.
>when I use the getdate function it returns the date and
>time from each of the fields...is there a way to just
>display the date from the fields and leave out the time?
>.
>|||The only way is to convert it to a string. There are several formats so you
should check out CONVERT() in BooksOnLine for the one your after.
--
Andrew J. Kelly
SQL Server MVP
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||worked fine thank you

GETDATE

I have a database that contains, amongst others, the following fields
DocNo, DocDate, DocAmt
I need to extract the data in the these fields, but only where the date (in
yyyy-mm-dd format) is equal to the current date. In other words, I need a
schedule of documents produced on the day of running the query.
I am battling with the WHERE statement. Can anyone help please?Try this:
WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
This statement sets the time to 00:00:00 on both the sides.
Regards,
Peri
"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> I have a database that contains, amongst others, the following fields
> DocNo, DocDate, DocAmt
> I need to extract the data in the these fields, but only where the date
(in
> yyyy-mm-dd format) is equal to the current date. In other words, I need a
> schedule of documents produced on the day of running the query.
> I am battling with the WHERE statement. Can anyone help please?
>
>|||Thanks Peri
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Try this:
> WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
> CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
> This statement sets the time to 00:00:00 on both the sides.
> Regards,
> Peri
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> (in
>|||"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Thanks Peri
> "Peri" <Peri@.newsgroups.nospam> wrote in message
> news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
>
Avoid putting the CONVERT on the column. Instead, an expression like the
following is more likely to make better use of any index on docdate.
...
WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
David Portas
SQL Server MVP
--|||Thanks David
Most helpful
regards
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:kpednTVQCsA5X-jeRVnyhA@.giganews.com...
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Avoid putting the CONVERT on the column. Instead, an expression like the
> following is more likely to make better use of any index on docdate.
> ...
> WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
> AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
> --
> David Portas
> SQL Server MVP
> --
>

GetDate

I am using GetDate() as the default value on selected fields to record the
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql

Monday, March 19, 2012

get value of last item in column?

How can I get the value of the last Item in a coums?
SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC?
something with Fields(0).value?

Thank you in advanceYou can use TOP 1 to return only 1 row (based on the ORDER BY), something
like this:

SELECT TOP 1 COLUMN FROM TABLE ORDER BY COLUMN DESC

Also, MIN(COLUMN) and MAX(COLUMN) may work depending on how you define last
item, like:

SELECT MAX(COLUMN) FROM TABLE

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Thank you very much, but my question is more like how to get the exact
VALUE of the field (not the minimum or maximum)?

is there a command/query for it?

On 7 mar, 18:54, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You can use TOP 1 to return only 1 row (based on the ORDER BY), something
like this:
>
SELECT TOP 1 COLUMN FROM TABLE ORDER BY COLUMN DESC
>
Also, MIN(COLUMN) and MAX(COLUMN) may work depending on how you define last
item, like:
>
SELECT MAX(COLUMN) FROM TABLE
>
Regards,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||<andreas.hei@.googlemail.comwrote in message
news:1173689649.827708.218050@.n33g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Thank you very much, but my question is more like how to get the exact
VALUE of the field (not the minimum or maximum)?

Not sure what you mean by the exact value of the field. How do you define
last item in column.

Generally that means the min or max value.

If you can give us some example data perhaps we can give a better solution?

Quote:

Originally Posted by

>
is there a command/query for it?
>
>
>
>
On 7 mar, 18:54, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

>You can use TOP 1 to return only 1 row (based on the ORDER BY), something
>like this:
>>
>SELECT TOP 1 COLUMN FROM TABLE ORDER BY COLUMN DESC
>>
>Also, MIN(COLUMN) and MAX(COLUMN) may work depending on how you define
>last
>item, like:
>>
>SELECT MAX(COLUMN) FROM TABLE
>>
>Regards,
>>
>Plamen Ratchevhttp://www.SQLStudio.com

>
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||In addition to Greg's comments, this is why you can use TOP 1 and then based
on the ORDER BY define what you need to be selected in the result set.

Or maybe you mean the last inserted row... But a table is unordered set. You
would have to use a column that can determine what the last inserted row is
(the latest ID, date/time stamp, etc.).

Plamen Ratchev
http://www.SQLStudio.com

Monday, March 12, 2012

Get the value of a testbox

Hello, I built a report which returns some calculated fields and I would like to know if it's possible to use the returned values in another field.

Exemple:
textbox10 = Fields!MyColumn.Value
textbox11 = Fields!MyOtherColumn.Value

I would like to know if there is a way to do something like this:
textbox12 = textbox10 + textbox11

Has anyone any idea? Thanks in advance for your help

Use the ReportItems collection i.e.

Code Snippet

textbox12 = ReportItems!textbox10.Value + ReportItems!textbox11.Value

get the rows where the info from one table is not contained in the

hello,
i have 2 tables which have 2 fields.
Common in the 2 tables is the id, the other field is a varchar(256)
example
Table1
Id UserInfo
1 Pc A.Julien-3400
2 Soft V.Noris-2800
3 Liz Barbara -2345
Table2
Id Username
1 Julien
2 Jack
3 Barbara
I want to get the id value where the username is not contained in the UserIn
fo
In the example
for id=1 Julien is contained in Pc A.Julien-3400
for id=2 Jack !!! is not contained .....
for id=3 Barbara is contained in Liz Barbara -2345
For this case i want to get only id=2
thanks
best regardsYou can do something like:
SELECT
<your column list>
FROM
table1
JOIN table2 ON table1.id = table2.id AND CHARINDEX(table2.col,
table1.col) > 0
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:1DB6FF37-9D4A-4A9A-A486-6E0C382F0070@.microsoft.com...
> hello,
> i have 2 tables which have 2 fields.
> Common in the 2 tables is the id, the other field is a varchar(256)
> example
> Table1
> Id UserInfo
> 1 Pc A.Julien-3400
> 2 Soft V.Noris-2800
> 3 Liz Barbara -2345
> Table2
> Id Username
> 1 Julien
> 2 Jack
> 3 Barbara
> I want to get the id value where the username is not contained in the
> UserInfo
> In the example
> for id=1 Julien is contained in Pc A.Julien-3400
> for id=2 Jack !!! is not contained .....
> for id=3 Barbara is contained in Liz Barbara -2345
>
> For this case i want to get only id=2
> thanks
> best regards|||On Wed, 1 Feb 2006 06:52:27 -0800, Xavier wrote:

>hello,
>i have 2 tables which have 2 fields.
>Common in the 2 tables is the id, the other field is a varchar(256)
>example
>Table1
>Id UserInfo
>1 Pc A.Julien-3400
>2 Soft V.Noris-2800
>3 Liz Barbara -2345
>Table2
>Id Username
>1 Julien
>2 Jack
>3 Barbara
>I want to get the id value where the username is not contained in the UserI
nfo
>In the example
>for id=1 Julien is contained in Pc A.Julien-3400
>for id=2 Jack !!! is not contained .....
>for id=3 Barbara is contained in Liz Barbara -2345
>
>For this case i want to get only id=2
Hi Xavier,
SELECT Table1.Id, Table1.UserInfo, Table2.UserName
FROM Table1
INNER JOIN Table2
ON Table2.Id = Table1.Id
WHERE Table1.UserInfo NOT LIKE '%' + Table2.Username + '%'
Hugo Kornelis, SQL Server MVP

Get the Report Model Fields of a particular report?

If I have a Report Path and I want to get at the fields associated with this report's Model (not the fields in the Report!!!), is there a way to do so using the ReportingService2005 or the VS.NET 2005 ReportViewer control?

I have played around with the ReportingService2005.GetItemDataSources() and the ServerReport.GetDataSources() (ReportViewer) methods, but with no luck.

I imagine that this functionality must be available becuase the Model fields are displayed in the Report Builder application once you select a Model.

Thanks!

Brian McCullough

OK...to get back to the Model do this:

New" size="2">Dim datasources() As DataSource = rs.GetItemDataSources(rptViewer.ServerReport.ReportPath)

For Each ds As DataSource In datasources
Dim modelPath As String = DirectCast(ds.Item, DataSourceReference).Reference

Dim modelItems() As ModelItem = rs.ListModelItemChildren(modelPath, Nothing, True)
For Each modelitem As ModelItem In modelItems
If item.Type = ModelItemTypeEnum.Attribute Then
'this is a field in the Model
'now how do i get the FieldType?
End If
Next

Now how do I get the Field Type (i.e. String, Date, etc...)?

-Brian

Friday, March 9, 2012

get the number of days it has been since a record was inserted

when inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.

when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.
eg: 3 days

if the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours.
e.g. 22 hrs

i dont want hours to be displayed if the days is >= 1.

please can anyone guide me with this?

thanks!

use the query like this

Declare @.MyVarasDateTime

Set @.Myvar='22/05/2007'

Select'satya', MyTime=

CASE

WHENDATEDIFF(hh,@.Myvar,GetDate())> 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate()))+' days'

ELSE

Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate()))+' hours'

END

Use the appropriate fields according to your database and tables

|||

Thanks Satya, this was really useful. Can you help me modify this so that it returns 1 day and 1 hour instead of 1 days and 1 hours

Appreciate the help!

|||

Sure change the code where its + "days" or + "hours"

Wink

1Declare @.MyVaras DateTime23Set @.Myvar='22/05/2007'45Select'satya', MyTime=67CASE8WHENDATEDIFF(hh,@.Myvar,GetDate()) > 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'910ELSE1112Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'1314END1516

|||

Sorry, i dont think i explained what i meant properly...

I need it to say 'days' and 'hours' all the time but the only exceptions are when days = 1 and when hours = 1...in them cases it should say 1 day and 1hour.

so as an example it could out the following :

11 days

21 days

1 day

...and

22 hours

6 hours

1 hour.

Thanks again!

|||

Declare @.MyVaras DateTime Set @.Myvar='05/22/2007'Select'satya', MyTime=CASEWHENDATEDIFF(hh,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'WHENDATEDIFF(hh,@.Myvar,GetDate()) < 23THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hours'WHENDATEDIFF(d,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'WHENDATEDIFF(d,@.Myvar,GetDate()) > 1THENConvert(varchar(10),DATEDIFF(dd,@.Myvar,GetDate())) +' days'END

|||

Great, worked perfectly :)

Thanks.

|||

You are welcome... Stick out tongue Answer it if solved

Wednesday, March 7, 2012

get table field list using Tsql?

Hello,
Is there a way to get a list of fields in a table using
Tsql?
Thanks,
Ronselect * from information_schema.columns where table_name = 'TableName'
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:35ec01c51f4a$d3cb1570$a601280a@.phx.gbl...
> Hello,
> Is there a way to get a list of fields in a table using
> Tsql?
> Thanks,
> Ron|||http://www.aspfaq.com/2177
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:35ec01c51f4a$d3cb1570$a601280a@.phx.gbl...
> Hello,
> Is there a way to get a list of fields in a table using
> Tsql?
> Thanks,
> Ron

Sunday, February 26, 2012

Get rows fields as xml name value pairs

Hi,
I'm trying to write a procedure to return the data from a query as xml
in the following format:
<root>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
</root>
so basically i need to turn the fields of a row into new rows?
I want to send them to a waiting app for deserialization into an
object but want the object to able to deserialize the data whatever it
is.
Any ideas?
Thanks,
George
Your best bet is probably to use an AUTO mode query and then apply an XSLT
transform.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"george" <8eu1ukg02@.sneakemail.com> wrote in message
news:d90f7cf1.0405270433.27d2fdbd@.posting.google.c om...
> Hi,
> I'm trying to write a procedure to return the data from a query as xml
> in the following format:
> <root>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> </root>
> so basically i need to turn the fields of a row into new rows?
> I want to send them to a waiting app for deserialization into an
> object but want the object to able to deserialize the data whatever it
> is.
> Any ideas?
> Thanks,
> George

Friday, February 24, 2012

Get Primary Key Column

Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards

Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||

Well no, I am running this script against the IssueTrackerStarterKit database. I am getting that table for sure, listing al columns, but I would like to know if I can know the primary key programmatically !!

regards

|||Run a search for sp_helpindex in SQL Server BOL(books online). And your select statement is using SQL- DMO(data management object) which is Microsoft property and all service packs makes changes which will make you code out dated because the tables have moved. Go to Query Analyzer open the object browser and right click on your table and you will have options of select statements without Syscolumns and Sysobjects. Hope this helps.|||Hi,
You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below

declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid

I hope this helps
Eralper
http://www.kodyaz.com

|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||

The Systems tables have a poster but since SQL Server 2005 is almost here Microsoft have removed it and have made others to remove it. But they are all in the SQL Server Master database with the System title next to them. A good book for starting SQL Server for a developer is SQL Server a beginner's guide by Dusan Petkovic but don't let the title fool you it is not really a beginner's book it was given that title because English is not the writer's first language, he is German. The book covers everything usefull to a developer including complex configurations and the language, he also covered XML and Full text that most other books did not cover. I have a lot of them I call crappy but his book is worth the money for a C# developer, while SQL Server Developer's guide is good for VB developer. Try this link for T-SQL tutorial but I have sent you an ANSI SQL tutorial in the mail. Dowload the file because the site is now part of a consolidator so that file may be removed soon. Hope this helps.

http://www.mssqlserver.com/tsql/

|||

The Systems tables have a poster but since SQL Server 2005 is almosthere Microsoft have removed it and have made others to remove it. Butthey are all in the SQL Server Master database with the System titlenext to them.

Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.

Thursday, March 29, 2012

Getting a field from subtotal

Getting a error in DTS Active x script

Wednesday, March 21, 2012

GETDATE() as default value is out of sync

GETDATE() as default value is out of sync

GETDATE() as default value is out of sync

getdate - only want the date

GETDATE

GetDate

Monday, March 19, 2012

get value of last item in column?

Monday, March 12, 2012

Get the value of a testbox

get the rows where the info from one table is not contained in the

Get the Report Model Fields of a particular report?

Friday, March 9, 2012

get the number of days it has been since a record was inserted

Wednesday, March 7, 2012

get table field list using Tsql?

Sunday, February 26, 2012

Get rows fields as xml name value pairs

Friday, February 24, 2012

Get Primary Key Column

Failure Details

Blog Archive

About Me