Hi,
Can anyone please tell me what's the real purpose of
lastwaittype column in sysprocesses if any?
--
Thanks,
OskarOskar,
Lastwaittype is the same as waittype if waittime = 0. If waittime > 0,
lastwaittype is the waittype causing the wait. In other words, lastwaittype
is the resource type we are waiting on, if we are waiting.
Jon Jahren
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
> Hi,
> Can anyone please tell me what's the real purpose of
> lastwaittype column in sysprocesses if any?
> --
> Thanks,
> Oskar
>|||Thanks Jon! That was very clear.
Seems like it is very difficult with some types of
waittypes to see the waittime larger than a few
miliseconds. So apparently you have to rely on lastwaittype.
>--Original Message--
>Oskar,
>Lastwaittype is the same as waittype if waittime = 0. If
waittime > 0,
>lastwaittype is the waittype causing the wait. In other
words, lastwaittype
>is the resource type we are waiting on, if we are waiting.
>Jon Jahren
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
>news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
>> Hi,
>> Can anyone please tell me what's the real purpose of
>> lastwaittype column in sysprocesses if any?
>> --
>> Thanks,
>> Oskar
>
>.
>|||You can get better aggregations of waittime using DBCC SQLPERF(WAITSTATS).
Tom Davidson has a nice article on this in SQL Server Magazine.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7f01c4cb22$c40f7350$a501280a@.phx.gbl...
> Thanks Jon! That was very clear.
> Seems like it is very difficult with some types of
> waittypes to see the waittime larger than a few
> miliseconds. So apparently you have to rely on lastwaittype.
> >--Original Message--
> >Oskar,
> >Lastwaittype is the same as waittype if waittime = 0. If
> waittime > 0,
> >lastwaittype is the waittype causing the wait. In other
> words, lastwaittype
> >is the resource type we are waiting on, if we are waiting.
> >
> >Jon Jahren
> >
> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
> >news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> Can anyone please tell me what's the real purpose of
> >> lastwaittype column in sysprocesses if any?
> >>
> >> --
> >> Thanks,
> >> Oskar
> >>
> >
> >
> >.
> >
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Monday, March 26, 2012
lastwaittype
Hi,
Can anyone please tell me what's the real purpose of
lastwaittype column in sysprocesses if any?
Thanks,
OskarOskar,
Lastwaittype is the same as waittype if waittime = 0. If waittime > 0,
lastwaittype is the waittype causing the wait. In other words, lastwaittype
is the resource type we are waiting on, if we are waiting.
Jon Jahren
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
> Hi,
> Can anyone please tell me what's the real purpose of
> lastwaittype column in sysprocesses if any?
> --
> Thanks,
> Oskar
>|||Thanks Jon! That was very clear.
Seems like it is very difficult with some types of
waittypes to see the waittime larger than a few
miliseconds. So apparently you have to rely on lastwaittype.
>--Original Message--
>Oskar,
>Lastwaittype is the same as waittype if waittime = 0. If
waittime > 0,
>lastwaittype is the waittype causing the wait. In other
words, lastwaittype
>is the resource type we are waiting on, if we are waiting.
>Jon Jahren
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
>news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
>
>.
>|||You can get better aggregations of waittime using DBCC SQLPERF(WAITSTATS).
Tom Davidson has a nice article on this in SQL Server Magazine.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7f01c4cb22$c40f7350$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Jon! That was very clear.
> Seems like it is very difficult with some types of
> waittypes to see the waittime larger than a few
> miliseconds. So apparently you have to rely on lastwaittype.
>
> waittime > 0,
> words, lastwaittype
Can anyone please tell me what's the real purpose of
lastwaittype column in sysprocesses if any?
Thanks,
OskarOskar,
Lastwaittype is the same as waittype if waittime = 0. If waittime > 0,
lastwaittype is the waittype causing the wait. In other words, lastwaittype
is the resource type we are waiting on, if we are waiting.
Jon Jahren
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
> Hi,
> Can anyone please tell me what's the real purpose of
> lastwaittype column in sysprocesses if any?
> --
> Thanks,
> Oskar
>|||Thanks Jon! That was very clear.
Seems like it is very difficult with some types of
waittypes to see the waittime larger than a few
miliseconds. So apparently you have to rely on lastwaittype.
>--Original Message--
>Oskar,
>Lastwaittype is the same as waittype if waittime = 0. If
waittime > 0,
>lastwaittype is the waittype causing the wait. In other
words, lastwaittype
>is the resource type we are waiting on, if we are waiting.
>Jon Jahren
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
>news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
>
>.
>|||You can get better aggregations of waittime using DBCC SQLPERF(WAITSTATS).
Tom Davidson has a nice article on this in SQL Server Magazine.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7f01c4cb22$c40f7350$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Jon! That was very clear.
> Seems like it is very difficult with some types of
> waittypes to see the waittime larger than a few
> miliseconds. So apparently you have to rely on lastwaittype.
>
> waittime > 0,
> words, lastwaittype
lastwaittype
Hi,
Can anyone please tell me what's the real purpose of
lastwaittype column in sysprocesses if any?
Thanks,
Oskar
Oskar,
Lastwaittype is the same as waittype if waittime = 0. If waittime > 0,
lastwaittype is the waittype causing the wait. In other words, lastwaittype
is the resource type we are waiting on, if we are waiting.
Jon Jahren
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
> Hi,
> Can anyone please tell me what's the real purpose of
> lastwaittype column in sysprocesses if any?
> --
> Thanks,
> Oskar
>
|||Thanks Jon! That was very clear.
Seems like it is very difficult with some types of
waittypes to see the waittime larger than a few
miliseconds. So apparently you have to rely on lastwaittype.
>--Original Message--
>Oskar,
>Lastwaittype is the same as waittype if waittime = 0. If
waittime > 0,
>lastwaittype is the waittype causing the wait. In other
words, lastwaittype
>is the resource type we are waiting on, if we are waiting.
>Jon Jahren
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
>news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
>
>.
>
|||You can get better aggregations of waittime using DBCC SQLPERF(WAITSTATS).
Tom Davidson has a nice article on this in SQL Server Magazine.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7f01c4cb22$c40f7350$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Jon! That was very clear.
> Seems like it is very difficult with some types of
> waittypes to see the waittime larger than a few
> miliseconds. So apparently you have to rely on lastwaittype.
> waittime > 0,
> words, lastwaittype
Can anyone please tell me what's the real purpose of
lastwaittype column in sysprocesses if any?
Thanks,
Oskar
Oskar,
Lastwaittype is the same as waittype if waittime = 0. If waittime > 0,
lastwaittype is the waittype causing the wait. In other words, lastwaittype
is the resource type we are waiting on, if we are waiting.
Jon Jahren
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
> Hi,
> Can anyone please tell me what's the real purpose of
> lastwaittype column in sysprocesses if any?
> --
> Thanks,
> Oskar
>
|||Thanks Jon! That was very clear.
Seems like it is very difficult with some types of
waittypes to see the waittime larger than a few
miliseconds. So apparently you have to rely on lastwaittype.
>--Original Message--
>Oskar,
>Lastwaittype is the same as waittype if waittime = 0. If
waittime > 0,
>lastwaittype is the waittype causing the wait. In other
words, lastwaittype
>is the resource type we are waiting on, if we are waiting.
>Jon Jahren
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
>news:596b01c4cb03$ec7641d0$a401280a@.phx.gbl...
>
>.
>
|||You can get better aggregations of waittime using DBCC SQLPERF(WAITSTATS).
Tom Davidson has a nice article on this in SQL Server Magazine.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7f01c4cb22$c40f7350$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Jon! That was very clear.
> Seems like it is very difficult with some types of
> waittypes to see the waittime larger than a few
> miliseconds. So apparently you have to rely on lastwaittype.
> waittime > 0,
> words, lastwaittype
Friday, March 23, 2012
lastmodified
Hi,
Can someone provide me a simple trigger where it updates a lastmodified
column.
It should update the column lastmodified whenever an update occurs on all
the other columns.
ThnxCREATE TRIGGER tr_YourTrigger
ON YourTable
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
UPDATE YourTable SET [LastModified] = GETDATE()
WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
Andrew J. Kelly SQL MVP
"Jason" <jlewis@.hotmail.com> wrote in message
news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone provide me a simple trigger where it updates a lastmodified
> column.
> It should update the column lastmodified whenever an update occurs on all
> the other columns.
> Thnx
>|||In addition to Andrew's response you can use DEFAULT constraint
CREATE TABLE #Test
(
col DATETIME DEFAULT GETDATE()
)
INSERT INTO #Test DEFAULT VALUES
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> CREATE TRIGGER tr_YourTrigger
> ON YourTable
> FOR UPDATE
> AS
>
> IF @.@.ROWCOUNT > 0
> UPDATE YourTable SET [LastModified] = GETDATE()
> WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jason" <jlewis@.hotmail.com> wrote in message
> news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
>|||That only works for Inserts. He specifically asked for Updates.
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> In addition to Andrew's response you can use DEFAULT constraint
> CREATE TABLE #Test
> (
> col DATETIME DEFAULT GETDATE()
> )
> INSERT INTO #Test DEFAULT VALUES
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> all
>|||Thanks, my mistake.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esONVXbGFHA.560@.TK2MSFTNGP15.phx.gbl...
> That only works for Inserts. He specifically asked for Updates.
> --
> Andrew J. Kelly SQL MVP
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
lastmodified[vbcol=seagreen]
>
Can someone provide me a simple trigger where it updates a lastmodified
column.
It should update the column lastmodified whenever an update occurs on all
the other columns.
ThnxCREATE TRIGGER tr_YourTrigger
ON YourTable
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
UPDATE YourTable SET [LastModified] = GETDATE()
WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
Andrew J. Kelly SQL MVP
"Jason" <jlewis@.hotmail.com> wrote in message
news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone provide me a simple trigger where it updates a lastmodified
> column.
> It should update the column lastmodified whenever an update occurs on all
> the other columns.
> Thnx
>|||In addition to Andrew's response you can use DEFAULT constraint
CREATE TABLE #Test
(
col DATETIME DEFAULT GETDATE()
)
INSERT INTO #Test DEFAULT VALUES
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> CREATE TRIGGER tr_YourTrigger
> ON YourTable
> FOR UPDATE
> AS
>
> IF @.@.ROWCOUNT > 0
> UPDATE YourTable SET [LastModified] = GETDATE()
> WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jason" <jlewis@.hotmail.com> wrote in message
> news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
>|||That only works for Inserts. He specifically asked for Updates.
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> In addition to Andrew's response you can use DEFAULT constraint
> CREATE TABLE #Test
> (
> col DATETIME DEFAULT GETDATE()
> )
> INSERT INTO #Test DEFAULT VALUES
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> all
>|||Thanks, my mistake.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esONVXbGFHA.560@.TK2MSFTNGP15.phx.gbl...
> That only works for Inserts. He specifically asked for Updates.
> --
> Andrew J. Kelly SQL MVP
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
lastmodified[vbcol=seagreen]
>
LAST_ALTERED Stored Procedure
Hi,
Is there anyway to find out the last altered date of a specific stored
procedure.
I tried Information_Schema.Routines but the value in that column doesn't
change after I modify the stored procedure.
When I look at BOL, it said "The last time the function was modified"
How to find out the date of all last_altered sp then'
Thanks
EdmundYou can't. SQL doesn't store this information.
Drop and recreate the procedure.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
> Hi,
> Is there anyway to find out the last altered date of a specific stored
> procedure.
> I tried Information_Schema.Routines but the value in that column doesn't
> change after I modify the stored procedure.
> When I look at BOL, it said "The last time the function was modified"
> How to find out the date of all last_altered sp then'
> Thanks
> Edmund
>|||what is the LAST_ALTERED column for under Information_Schema.Routines since
I
don't see the value of this column can be changed!!!!
Ed
"Raymond D'Anjou" wrote:
> You can't. SQL doesn't store this information.
> Drop and recreate the procedure.
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
>
>|||These views are defined by ANSI SQL, to they "have to" be present in SQL Ser
ver whether the
information is actually available or not. Read the source for the view and y
ou see that it actually
displays creation date. Books Online is not correct, though, as it states it
is last time altered...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:28240995-CC07-4103-A1F7-6992939B7035@.microsoft.com...
> what is the LAST_ALTERED column for under Information_Schema.Routines sinc
e I
> don't see the value of this column can be changed!!!!
> Ed
> "Raymond D'Anjou" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> These views are defined by ANSI SQL, to they "have to" be present in SQL
> Server whether the information is actually available or not. Read the
> source for the view and you see that it actually displays creation date.
> Books Online is not correct, though, as it states it is last time
> altered...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Funny.
It's as if I told my client:
I'm obligated to give you a report of your accounts receivables
The amounts are wrong, but you have your report.|||It is probably just a TBD that just fell through the cracks. I once tried to
add a trigger to the sysobjects table that would update the column with
getdate() when a record where xtype='P' is inserted or updated, but this is
not allowed.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||LOL... I agree. It would be better to display NULL and document it properly.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||You DO know that anything, including SELECTs, on System tables are
unsupported.
That's because these tables could change in any upgrade or service pack.
"JT" <someone@.microsoft.com> wrote in message
news:%23jNUtK$WFHA.3140@.TK2MSFTNGP14.phx.gbl...
> It is probably just a TBD that just fell through the cracks. I once tried
> to
> add a trigger to the sysobjects table that would update the column with
> getdate() when a record where xtype='P' is inserted or updated, but this
> is
> not allowed.
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> in
>|||Technically, the SELECT against the system tables is supported, as long as y
ou don't derive
information from columns that aren't documented. But the intent if correct,
the structure of the
system tables will change in next version. The will be "replaced" by catalog
views, but still exist
for backwards compatibility and most code will run without changes (assuming
reserved and
non-documented columns has been used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
> You DO know that anything, including SELECTs, on System tables are unsuppo
rted.
> That's because these tables could change in any upgrade or service pack.
> "JT" <someone@.microsoft.com> wrote in message news:%23jNUtK$WFHA.3140@.TK2M
SFTNGP14.phx.gbl...
>|||Thanks Tibor.
I've gone over to the dark side a few times and directly queried system
tables but I've never included these in production code.
Off the top of your head, do you know of any system table information that
cannot be obtained by using "Information schema views" or "System stored
procedures".
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGWA6rHXFHA.2540@.tk2msftngp13.phx.gbl...
> Technically, the SELECT against the system tables is supported, as long as
> you don't derive information from columns that aren't documented. But the
> intent if correct, the structure of the system tables will change in next
> version. The will be "replaced" by catalog views, but still exist for
> backwards compatibility and most code will run without changes (assuming
> reserved and non-documented columns has been used).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
>
Is there anyway to find out the last altered date of a specific stored
procedure.
I tried Information_Schema.Routines but the value in that column doesn't
change after I modify the stored procedure.
When I look at BOL, it said "The last time the function was modified"
How to find out the date of all last_altered sp then'
Thanks
EdmundYou can't. SQL doesn't store this information.
Drop and recreate the procedure.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
> Hi,
> Is there anyway to find out the last altered date of a specific stored
> procedure.
> I tried Information_Schema.Routines but the value in that column doesn't
> change after I modify the stored procedure.
> When I look at BOL, it said "The last time the function was modified"
> How to find out the date of all last_altered sp then'
> Thanks
> Edmund
>|||what is the LAST_ALTERED column for under Information_Schema.Routines since
I
don't see the value of this column can be changed!!!!
Ed
"Raymond D'Anjou" wrote:
> You can't. SQL doesn't store this information.
> Drop and recreate the procedure.
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
>
>|||These views are defined by ANSI SQL, to they "have to" be present in SQL Ser
ver whether the
information is actually available or not. Read the source for the view and y
ou see that it actually
displays creation date. Books Online is not correct, though, as it states it
is last time altered...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:28240995-CC07-4103-A1F7-6992939B7035@.microsoft.com...
> what is the LAST_ALTERED column for under Information_Schema.Routines sinc
e I
> don't see the value of this column can be changed!!!!
> Ed
> "Raymond D'Anjou" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> These views are defined by ANSI SQL, to they "have to" be present in SQL
> Server whether the information is actually available or not. Read the
> source for the view and you see that it actually displays creation date.
> Books Online is not correct, though, as it states it is last time
> altered...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Funny.
It's as if I told my client:
I'm obligated to give you a report of your accounts receivables
The amounts are wrong, but you have your report.|||It is probably just a TBD that just fell through the cracks. I once tried to
add a trigger to the sysobjects table that would update the column with
getdate() when a record where xtype='P' is inserted or updated, but this is
not allowed.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||LOL... I agree. It would be better to display NULL and document it properly.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||You DO know that anything, including SELECTs, on System tables are
unsupported.
That's because these tables could change in any upgrade or service pack.
"JT" <someone@.microsoft.com> wrote in message
news:%23jNUtK$WFHA.3140@.TK2MSFTNGP14.phx.gbl...
> It is probably just a TBD that just fell through the cracks. I once tried
> to
> add a trigger to the sysobjects table that would update the column with
> getdate() when a record where xtype='P' is inserted or updated, but this
> is
> not allowed.
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> in
>|||Technically, the SELECT against the system tables is supported, as long as y
ou don't derive
information from columns that aren't documented. But the intent if correct,
the structure of the
system tables will change in next version. The will be "replaced" by catalog
views, but still exist
for backwards compatibility and most code will run without changes (assuming
reserved and
non-documented columns has been used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
> You DO know that anything, including SELECTs, on System tables are unsuppo
rted.
> That's because these tables could change in any upgrade or service pack.
> "JT" <someone@.microsoft.com> wrote in message news:%23jNUtK$WFHA.3140@.TK2M
SFTNGP14.phx.gbl...
>|||Thanks Tibor.
I've gone over to the dark side a few times and directly queried system
tables but I've never included these in production code.
Off the top of your head, do you know of any system table information that
cannot be obtained by using "Information schema views" or "System stored
procedures".
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGWA6rHXFHA.2540@.tk2msftngp13.phx.gbl...
> Technically, the SELECT against the system tables is supported, as long as
> you don't derive information from columns that aren't documented. But the
> intent if correct, the structure of the system tables will change in next
> version. The will be "replaced" by catalog views, but still exist for
> backwards compatibility and most code will run without changes (assuming
> reserved and non-documented columns has been used).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
>
Wednesday, March 21, 2012
Last row ?
Hello Team
How can i get a value from a column in the last row
for ex: select orderno from orders .......... but the last row ?
Thanks lot
Greetings,
SQL Server has no concept of last row, or first row or middle row for that matter.
To determine the last row you need to know by what criteria. If your Order Number column is sequential or your table contains a datetime column then you can get a close approximation by using TOP and ORDER BY.
SELECT TOP 1 OrderNo FROM Orders ORDER BY OrderDate DESC
If there is no column in the table from which you can determine the correct ordering then you're stuck.
--
Regards,
Neale NOON
There is one more work around available to get the last record..
if your table has 3 columns,
declare @.col1 as varchar(100);
declare @.col2 as varchar(100);
declare @.col3 as varchar(100);
Select @.Co1l=Col1,
@.Col2=Col2,
@.Col3=Col3
From
Tablename;
Select @.Col1 as Col1, @.Col2 as Col2, @.Col3 as Col3;
So here you need not to use any order by clause. It always fet the last row.
|||If you are really just looking for the last row, reverse your sort order and use SELECT TOP 1 ... That will get you the first row in the reversed order, ie last row.Last record ?
Hello Team
i want to get a value from a column in the last record
i wrote select ID from (select top 1 * desc from items)
any idea, Thanks lot
If you want to use top you have to use order by clause with desc to fetch the last record..
Code Snippet
Select Top 1 Id from Demo Order By Id Desc
or
Select max(id) from Demo;
Another easy way is,
Code Snippet
Declare @.Id as int;
Select @.ID = ID from Demo;
Select @.ID;
here it always return the last record
|||Just select from the table ordering by the field that can be used to identify if this is a recent row or not.
Code Snippet
Select Top 1 ID
From Items
Order By ID Desc
Assuming ID is a column that is incremental for each new row. If you don't have an incremental ID, you can use a column that contains the date when the row has been added.
I hope this answers your question. If not, please post more information about the table.
Best regards,
Sami Samir
|||Thank you very much
and Mr samy too
|||If you want to obtain the ID value of the last record inserted to a table, and then use that ID value in additional INSERTS/UPDATES, you may wish to explore using the system function: SCOPE_IDENTITY. It can return the value of the immediately preceding insert WITHOUT having to execute a query.sqlMonday, March 19, 2012
last index of character in string?
hello everyone.
i've got a "name" column that i would like to split into "firstname"
and "lastname" columns. the firstname was no problem (the values have
already been LTRIM and RTRIM'd):
UPDATE test
SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
however i'm curious about how to do the last name. you see, there might
be an arbitrary number of ' ' characters in the "name" column values
(in fact the max is seven). however regardless of how many spaces
occur, or what's inbetween those spaces.
now, i can set up a very complicated nested CHARINDEX/SUBSTRING
analysis for each number of ' ' characters that occur, starting with
the seven space condition, and working my way down. but good grief that
would be complicated.
is there any nifty tricks to find the LAST index of a specified
character in a string, by chance? that would save me a world of hurt.
thanks!
jason> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
How about CHARINDEX(' ', REVERSE(TRIM(name)))
However saving you even more of a world of hurt (e.g. not changing the names
of people like Larry da Costa and Filipe de Simone), you should just grab an
off-the-shelf package for data scrubbing. These have much more advanced
rules, and a much higher success ratio, than anything anyone will be able to
write in T-SQL alone.
A|||Try using reverse to flip the string around
UPDATE test
SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
Archer
"jason" wrote:
> hello everyone.
> i've got a "name" column that i would like to split into "firstname"
> and "lastname" columns. the firstname was no problem (the values have
> already been LTRIM and RTRIM'd):
> UPDATE test
> SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
> however i'm curious about how to do the last name. you see, there might
> be an arbitrary number of ' ' characters in the "name" column values
> (in fact the max is seven). however regardless of how many spaces
> occur, or what's inbetween those spaces.
> now, i can set up a very complicated nested CHARINDEX/SUBSTRING
> analysis for each number of ' ' characters that occur, starting with
> the seven space condition, and working my way down. but good grief that
> would be complicated.
> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
> thanks!
> jason
>|||Jason,
Could you use something like this'
declare @.name varchar(50),
@.FirstName varchar(25),
@.lastName varchar(25)
Set @.name = 'Joe Bloggs'
Set @.firstname = ltrim(rtrim(LEFT(@.name, CHARINDEX(' ', LTRIM(@.name),
1) - 1)))
Set @.LastName =ltrim(rtrim(Right(@.Name, (Len(@.Name) -
Len(@.FirstName)))))
select @.name, @.FirstName, @.lastName
HTH
Barry|||Sorry, it probably should be
UPDATE test
SET lastname = LEFT(REVERSE(name), CHARINDEX(' ', LTRIM(REVERSE(name)), 1) -
1)
Archer
"bagman3rd" wrote:
> Try using reverse to flip the string around
> UPDATE test
> SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
> Archer
> "jason" wrote:
>|||> How about CHARINDEX(' ', REVERSE(TRIM(name)))
by TRIM( I meant LTRIM(RTRIM(, of course. Not all of us create a function
called TRIM(). :-)|||AHA! *REVERSE* that's exactly what I was hoping existed. Took a little
tweaking like so:
UPDATE test
SET lastname = REVERSE(LEFT(REVERSE(name), CHARINDEX(' ',
REVERSE(name), 1) - 1))
But that worked like a charm! Thanks very much.
As for the value of the data, it's not worth any scrubbing of note, but
I do appreciate the suggestion for cases that might be.
Jason
i've got a "name" column that i would like to split into "firstname"
and "lastname" columns. the firstname was no problem (the values have
already been LTRIM and RTRIM'd):
UPDATE test
SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
however i'm curious about how to do the last name. you see, there might
be an arbitrary number of ' ' characters in the "name" column values
(in fact the max is seven). however regardless of how many spaces
occur, or what's inbetween those spaces.
now, i can set up a very complicated nested CHARINDEX/SUBSTRING
analysis for each number of ' ' characters that occur, starting with
the seven space condition, and working my way down. but good grief that
would be complicated.
is there any nifty tricks to find the LAST index of a specified
character in a string, by chance? that would save me a world of hurt.
thanks!
jason> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
How about CHARINDEX(' ', REVERSE(TRIM(name)))
However saving you even more of a world of hurt (e.g. not changing the names
of people like Larry da Costa and Filipe de Simone), you should just grab an
off-the-shelf package for data scrubbing. These have much more advanced
rules, and a much higher success ratio, than anything anyone will be able to
write in T-SQL alone.
A|||Try using reverse to flip the string around
UPDATE test
SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
Archer
"jason" wrote:
> hello everyone.
> i've got a "name" column that i would like to split into "firstname"
> and "lastname" columns. the firstname was no problem (the values have
> already been LTRIM and RTRIM'd):
> UPDATE test
> SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
> however i'm curious about how to do the last name. you see, there might
> be an arbitrary number of ' ' characters in the "name" column values
> (in fact the max is seven). however regardless of how many spaces
> occur, or what's inbetween those spaces.
> now, i can set up a very complicated nested CHARINDEX/SUBSTRING
> analysis for each number of ' ' characters that occur, starting with
> the seven space condition, and working my way down. but good grief that
> would be complicated.
> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
> thanks!
> jason
>|||Jason,
Could you use something like this'
declare @.name varchar(50),
@.FirstName varchar(25),
@.lastName varchar(25)
Set @.name = 'Joe Bloggs'
Set @.firstname = ltrim(rtrim(LEFT(@.name, CHARINDEX(' ', LTRIM(@.name),
1) - 1)))
Set @.LastName =ltrim(rtrim(Right(@.Name, (Len(@.Name) -
Len(@.FirstName)))))
select @.name, @.FirstName, @.lastName
HTH
Barry|||Sorry, it probably should be
UPDATE test
SET lastname = LEFT(REVERSE(name), CHARINDEX(' ', LTRIM(REVERSE(name)), 1) -
1)
Archer
"bagman3rd" wrote:
> Try using reverse to flip the string around
> UPDATE test
> SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
> Archer
> "jason" wrote:
>|||> How about CHARINDEX(' ', REVERSE(TRIM(name)))
by TRIM( I meant LTRIM(RTRIM(, of course. Not all of us create a function
called TRIM(). :-)|||AHA! *REVERSE* that's exactly what I was hoping existed. Took a little
tweaking like so:
UPDATE test
SET lastname = REVERSE(LEFT(REVERSE(name), CHARINDEX(' ',
REVERSE(name), 1) - 1))
But that worked like a charm! Thanks very much.
As for the value of the data, it's not worth any scrubbing of note, but
I do appreciate the suggestion for cases that might be.
Jason
Monday, March 12, 2012
last day of the month
Hi all,
I have Date_Mmmyy column in my table that stores dates in MMMYY format
for example; "OCT01","OCT04","AUG04". I need to update another column in
this table, which is DateTime, with the last day of the Date_Mmmyy value.
Here is an example...
DECLARE @.test table
(
Date_Mmmyy varchar(5),
Date_Datetime datetime
)
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT01')
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT04')
INSERT INTO @.test(Date_Mmmyy)
VALUES('AUG04')
SELECT *
FROM @.test
I would like to see...
OCT01 2001-09-30
OCT04 2004-09-30
AUG04 2004-08-31
Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
October is the 10th month not the 9th.
SELECT date_mmmyy,
dateadd(month,1,cast('01' + date_mmmyy as datetime))-1
FROM @.test
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjC8+IechKqOuFEgEQJEdQCeI7osM0fWzMiL
l/e3qENjIa25TV0AoKRZ
SYBhDB51p4ZJy+UuV2Sjy0Rk
=8ZVj
--END PGP SIGNATURE--
sql wrote:
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31|||> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
UPDATE @.test SET Date_Datetime=dateadd(day, -1, dateadd(month, 1,
'1'+Date_Mmmyy))
> SELECT *
> FROM @.test
>
Regards
Mr Tea
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>|||Thank you all for your replies. I got this working with your help.
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>
I have Date_Mmmyy column in my table that stores dates in MMMYY format
for example; "OCT01","OCT04","AUG04". I need to update another column in
this table, which is DateTime, with the last day of the Date_Mmmyy value.
Here is an example...
DECLARE @.test table
(
Date_Mmmyy varchar(5),
Date_Datetime datetime
)
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT01')
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT04')
INSERT INTO @.test(Date_Mmmyy)
VALUES('AUG04')
SELECT *
FROM @.test
I would like to see...
OCT01 2001-09-30
OCT04 2004-09-30
AUG04 2004-08-31
Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
October is the 10th month not the 9th.
SELECT date_mmmyy,
dateadd(month,1,cast('01' + date_mmmyy as datetime))-1
FROM @.test
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjC8+IechKqOuFEgEQJEdQCeI7osM0fWzMiL
l/e3qENjIa25TV0AoKRZ
SYBhDB51p4ZJy+UuV2Sjy0Rk
=8ZVj
--END PGP SIGNATURE--
sql wrote:
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31|||> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
UPDATE @.test SET Date_Datetime=dateadd(day, -1, dateadd(month, 1,
'1'+Date_Mmmyy))
> SELECT *
> FROM @.test
>
Regards
Mr Tea
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>|||Thank you all for your replies. I got this working with your help.
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>
Friday, March 9, 2012
large value data type (nvarchar(max))
hi,
I have inserted a new column in my db table (type: nvarchar(max))
now I can't process the cube anymore ... is there a possibility to store large text files in the cube?
thanks,
rhapsy
Hi Rhapsy
What are you trying to achieve with this data? Would it not be easier to try and have an action to get that text if it is needed?
Regards
Mike
|||hey mike,probably it would be easier, but I'd like to get all my information from the same source ...
and I'm a little bit curious if thias is possible ...
thanks,
rhapsy
Wednesday, March 7, 2012
Large Text FIeld: Easy Way to Copy and Paste Into?
I have a column that I want to store the entire contents of an XML
file. For testing purposes before our .Net program is written, I would
like to populate that column in a few rows with some actual
text-representation of XML files. I thought I would just open the XML
file in a text editor (ultraedit) and then paste it in somehow.
Normaly I can go into Enterprise Manager and paste information into
columns from other apps - as long as the column can accept the data, no
problem.
However, there seems to be a limit on how much text I can paste into a
column via Enterprise Manager. It's not based on linefeeds
necessarily, because it handles multiple lines OK, just as long as you
don't try to paste too much.
Is there any other tool that I could use to slam some data quickly into
some columns. Insert statements using Query Analyzer are a pain (not
to mention needing to worry about apostrophes, etc.) - I was looking
for something rather visual and quick.
I tried this QA Lite tool which showed some promise
(http://www.rac4sql.net/qalite_main.asp) - but the limitation seems to
be similar there as well...
Any hints?
Thanks,
spedlists@.erstads.com
Try bulk insert.
It's just a matter of setting the end tags for the entries.
There are a few bugs associated with bulk inserting into text column though
so post again if you have problems - it might not be your code.
"Sped" wrote:
> I have a column that I want to store the entire contents of an XML
> file. For testing purposes before our .Net program is written, I would
> like to populate that column in a few rows with some actual
> text-representation of XML files. I thought I would just open the XML
> file in a text editor (ultraedit) and then paste it in somehow.
> Normaly I can go into Enterprise Manager and paste information into
> columns from other apps - as long as the column can accept the data, no
> problem.
> However, there seems to be a limit on how much text I can paste into a
> column via Enterprise Manager. It's not based on linefeeds
> necessarily, because it handles multiple lines OK, just as long as you
> don't try to paste too much.
> Is there any other tool that I could use to slam some data quickly into
> some columns. Insert statements using Query Analyzer are a pain (not
> to mention needing to worry about apostrophes, etc.) - I was looking
> for something rather visual and quick.
> I tried this QA Lite tool which showed some promise
> (http://www.rac4sql.net/qalite_main.asp) - but the limitation seems to
> be similar there as well...
> Any hints?
> Thanks,
> spedlists@.erstads.com
>
file. For testing purposes before our .Net program is written, I would
like to populate that column in a few rows with some actual
text-representation of XML files. I thought I would just open the XML
file in a text editor (ultraedit) and then paste it in somehow.
Normaly I can go into Enterprise Manager and paste information into
columns from other apps - as long as the column can accept the data, no
problem.
However, there seems to be a limit on how much text I can paste into a
column via Enterprise Manager. It's not based on linefeeds
necessarily, because it handles multiple lines OK, just as long as you
don't try to paste too much.
Is there any other tool that I could use to slam some data quickly into
some columns. Insert statements using Query Analyzer are a pain (not
to mention needing to worry about apostrophes, etc.) - I was looking
for something rather visual and quick.
I tried this QA Lite tool which showed some promise
(http://www.rac4sql.net/qalite_main.asp) - but the limitation seems to
be similar there as well...
Any hints?
Thanks,
spedlists@.erstads.com
Try bulk insert.
It's just a matter of setting the end tags for the entries.
There are a few bugs associated with bulk inserting into text column though
so post again if you have problems - it might not be your code.
"Sped" wrote:
> I have a column that I want to store the entire contents of an XML
> file. For testing purposes before our .Net program is written, I would
> like to populate that column in a few rows with some actual
> text-representation of XML files. I thought I would just open the XML
> file in a text editor (ultraedit) and then paste it in somehow.
> Normaly I can go into Enterprise Manager and paste information into
> columns from other apps - as long as the column can accept the data, no
> problem.
> However, there seems to be a limit on how much text I can paste into a
> column via Enterprise Manager. It's not based on linefeeds
> necessarily, because it handles multiple lines OK, just as long as you
> don't try to paste too much.
> Is there any other tool that I could use to slam some data quickly into
> some columns. Insert statements using Query Analyzer are a pain (not
> to mention needing to worry about apostrophes, etc.) - I was looking
> for something rather visual and quick.
> I tried this QA Lite tool which showed some promise
> (http://www.rac4sql.net/qalite_main.asp) - but the limitation seems to
> be similar there as well...
> Any hints?
> Thanks,
> spedlists@.erstads.com
>
Large text column
I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.
Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?
After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.
Is this a Microsoft "feature" that is scheduled to be fixed in 2008?
|||Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.
We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.
Large text column
I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.
Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?
After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.
Is this a Microsoft "feature" that is scheduled to be fixed in 2008?
|||Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.
We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.
Large text column
I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.
Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?
After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.
Is this a Microsoft "feature" that is scheduled to be fixed in 2008?
|||Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.
We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.
Friday, February 24, 2012
large size of string in the drill down
Hello,
I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.
Is there any other way to pass the large string in the drill down?
You may be able to write some custom code that would compress the string and then uncompress in the drill down.
Here is some example code.
http://www.developerfusion.co.uk/show/1642/
Otherwise try and pass keys instead of names into the drilldown.
cheers,
Andrew
|||Can someone please help on this?Large number of rows.
Hi all,
A select query returns around 1 million rows. The column in the WHERE condition is indexed. This query takes nearly 1 minute for returning the all the records. Is this normal ?
Does the number of records returned affect the performance inspite of the indexing ?
Thanks,
DBLearner
The index simply allows it to find the data it has to retrieve quickly. It then has to:
load that actual record data in from disk. Depending upon how the records are split across datapages this could be a lot of disk access. Then if you have any sorting or grouping/aggregating on the result it has to do this before it can start passing the results back. After that it has to transfer the data that you are retrieving across the link (shared memory if you are running on the server across the network if not). If you are retrieving say 20 bytes per record (quite small: a couple of ints, a bit of text and a real number can be this size or larger easily) then for a million records that is 20 megabytes.|||The major 'chokepoints' will be the quality of the index vs. the WHERE clause criteria, amount of server memory, other activity, CPU power, and disk 'arrangement'. (Having the TempDb database on a dedicated array or LUN, for example.)
You might benefit from some tuning and optimization and perhaps that will increase the query responsiveness. But you definitely need to get some benchmarks in place.
Here is some information about Performance Audits, Monitoring, and Tuning:
Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp
Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking
Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp
Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
Monday, February 20, 2012
Large FullText tables - slow queries
Hi,
I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.
To query the complete index is fast, for example:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
JOIN Pages ITP ON ITP.PageID = CT.[Key]
JOIN Feeds ITF ON ITP.IPID = ITF.IPID
JOIN Buyers ITB ON ITB.IBID = ITF.IBID
WHERE ITB.ID IN (1342,246)
These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).
One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:
'{ID:1342}' + [Body]
That sounds like a very good idea. I could then change the 2nd query above to be:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT
That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.
If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!
Many thanks for any advice.
Simon
I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||
Hi I am not sure if this can help:
35 Mio. I am looking forward to encounter more soon.
try:
Make sure to use the noiseword concept of FT, get rid of the noice first.
Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.
Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)
Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only
Try to Query also against other fields (not fulltext indexed)
Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!
.
*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!
Of course database needs field AutoId as Clusterd index.
8GB Memory / 64-bit version of SQL
Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)
How many keywords per record do you have, how long are they in average?
How big is the Textfile?
What is the CPU?
Thomas
Large FullText tables - slow queries
Hi,
I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.
To query the complete index is fast, for example:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
JOIN Pages ITP ON ITP.PageID = CT.[Key]
JOIN Feeds ITF ON ITP.IPID = ITF.IPID
JOIN Buyers ITB ON ITB.IBID = ITF.IBID
WHERE ITB.ID IN (1342,246)
These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).
One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:
'{ID:1342}' + [Body]
That sounds like a very good idea. I could then change the 2nd query above to be:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT
That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.
If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!
Many thanks for any advice.
Simon
I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||
Hi I am not sure if this can help:
35 Mio. I am looking forward to encounter more soon.
try:
Make sure to use the noiseword concept of FT, get rid of the noice first.
Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.
Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)
Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only
Try to Query also against other fields (not fulltext indexed)
Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!
.
*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!
Of course database needs field AutoId as Clusterd index.
8GB Memory / 64-bit version of SQL
Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)
How many keywords per record do you have, how long are they in average?
How big is the Textfile?
What is the CPU?
Thomas
Large Fixed width Text files using SSIS
What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.
I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.
If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.
HTH,
Bob
|||Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?
|||Try searching this forum and documentation for samples. Here are a few I found:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1
If you write the code you pretty much contol it, so you should be able to load external metadata definitions.
HTH,
Bob
|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.
Large Fixed width Text files using SSIS
What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.
I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.
If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.
HTH,
Bob
|||Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?
|||Try searching this forum and documentation for samples. Here are a few I found:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1
If you write the code you pretty much contol it, so you should be able to load external metadata definitions.
HTH,
Bob
|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.
Subscribe to:
Posts (Atom)