How to trigger the update command of the SQLDataSource

Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select).

Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command.

How do I get the link button to trigger the update command?

Thanks, Mark

in you link button click event handler call

datasourceName.Update();

Hop this help

How to trigger Snapshot and Merge Replication

I have two servers config'd for merge replication (server A is SUB, server B
is PUB)
When insert on server B table1 occur, I need trigger to exec snapshot,
followed by merge replication jobs. The jobs exist on server A.
I know how to create triggers, but not clear on how to trigger a SQL job
from server B to run from server A?
using SQL 2K5
You coyuld set up a linked server and run sp_start_job on server A using a
fully qualified name...
Cheers,
Paul Ibison
sql

How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to track insert volume for each database?

I need some advice to better track the insert volume of each database on a
server.
I'd like to log that number once a month in a mock up table like
Date DbName InsertCount
4/30/04 MyDb1 2000
4/30/04 MyDb2 500
5/31/04 MyDb1 1500 (probably would be based 3500-2000
previous month records)
5/31/04 MyDb2 200 (based on total record count of
700-500)
Bottom line is to get a feel of how busy a database is. I'm not sure
tracking inserts is the best way and open to any suggestions. Thank you.
Steve
A database can be very "busy" and have 0 rows added, deleted, or =
updated.
Consider a database that is used for reporting purposes. The =
connections to this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server =
is...but unless you know what you are looking at (or for) the numbers =
might not mean anything.
--=20
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>=20
>=20
> I need some advice to better track the insert volume of each database =
on a
> server.
>=20
> I'd like to log that number once a month in a mock up table like
>=20
>=20
>=20
> Date DbName InsertCount
>=20
> 4/30/04 MyDb1 2000
>=20
> 4/30/04 MyDb2 500
>=20
> 5/31/04 MyDb1 1500 (probably would be based =
3500-2000
> previous month records)
>=20
> 5/31/04 MyDb2 200 (based on total record count =
of
> 700-500)
>=20
>=20
>=20
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank =
you.
>=20
>=20
>=20
> Steve
>=20
>
|||thank you for the advice. that's very ture.
i think i still would like to track that number for the purpose of server
capacity planning.
any ideas?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
A database can be very "busy" and have 0 rows added, deleted, or updated.
Consider a database that is used for reporting purposes. The connections to
this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server
is...but unless you know what you are looking at (or for) the numbers might
not mean anything.
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>
> I need some advice to better track the insert volume of each database on a
> server.
> I'd like to log that number once a month in a mock up table like
>
> Date DbName InsertCount
> 4/30/04 MyDb1 2000
> 4/30/04 MyDb2 500
> 5/31/04 MyDb1 1500 (probably would be based
3500-2000
> previous month records)
> 5/31/04 MyDb2 200 (based on total record count of
> 700-500)
>
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank you.
>
> Steve
>
|||sp_helpdb
it returns the database name, size, and other info. You could store =
this data to a table as often as you want. =20
These examples should get you started if you want rowcounts. I have =
credited the authors=20
basic rowcount:
use pubs
go
SELECT A.name, B.rows=20
FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2=20
ORDER BY A.Name
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=
=3D=3D
select name, rows from sysobjects
where type =3D 'U' order by rows desc
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=
=3D=3D
The script below will give information about the row count and space =
used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have =
to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the =
data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.co...TOPIC_ID=3D629
The script that is attached to this message isn't mine btw, but I can't =
find
back who the original author is.
--=20
Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname =3D DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + =
@.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType =3D 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =
Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =
Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=
=3D=3D
The following was adapted from SP_SPACEUSED, and will also give the size =
in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS =3D (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID =
=3D
O.ID),
RESERVED =3D (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES =
I
WHERE I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128),
DATA =3D (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I =
WHERE
I.INDID < 2 AND I.ID =3D O.ID)) / 128),
TEXT =3D (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES =
I
WHERE I.INDID =3D 255 AND I.ID =3D O.ID),0)) / 128),
USED =3D (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE =3D 'U'
Mike Kruchten
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=
=3D=3D
http://www.aspfaq.com/2428
Aaron Bertrand
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=
=3D=3D
--=20
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =
news:%23uCwPM%23IEHA.3512@.TK2MSFTNGP10.phx.gbl...
> thank you for the advice. that's very ture.
> i think i still would like to track that number for the purpose of =
server
> capacity planning.
> any ideas?
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
> A database can be very "busy" and have 0 rows added, deleted, or =
updated.
>=20
> Consider a database that is used for reporting purposes. The =
connections to
> this database will only be reading data.
>=20
> You can run Profiler and PerfMon in order to see how "busy" a server
> is...but unless you know what you are looking at (or for) the numbers =
might[vbcol=seagreen]
> not mean anything.
>=20
> --
> Keith
>=20
>=20
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
database on a[vbcol=seagreen]
> 3500-2000
of[vbcol=seagreen]
you.
>=20
>
sql

How to track insert volume for each database?

I need some advice to better track the insert volume of each database on a
server.
I'd like to log that number once a month in a mock up table like
Date DbName InsertCount
4/30/04 MyDb1 2000
4/30/04 MyDb2 500
5/31/04 MyDb1 1500 (probably would be based 3500-2000
previous month records)
5/31/04 MyDb2 200 (based on total record count of
700-500)
Bottom line is to get a feel of how busy a database is. I'm not sure
tracking inserts is the best way and open to any suggestions. Thank you.
SteveA database can be very "busy" and have 0 rows added, deleted, or =
updated.
Consider a database that is used for reporting purposes. The =
connections to this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server =
is...but unless you know what you are looking at (or for) the numbers =
might not mean anything.
--=20
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>=20
>=20
> I need some advice to better track the insert volume of each database =
on a
> server.
>=20
> I'd like to log that number once a month in a mock up table like
>=20
>=20
>=20
> Date DbName InsertCount
>=20
> 4/30/04 MyDb1 2000
>=20
> 4/30/04 MyDb2 500
>=20
> 5/31/04 MyDb1 1500 (probably would be based =
3500-2000
> previous month records)
>=20
> 5/31/04 MyDb2 200 (based on total record count =
of
> 700-500)
>=20
>=20
>=20
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank =
you.
>=20
>=20
>=20
> Steve
>=20
>|||thank you for the advice. that's very ture.
i think i still would like to track that number for the purpose of server
capacity planning.
any ideas?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
A database can be very "busy" and have 0 rows added, deleted, or updated.
Consider a database that is used for reporting purposes. The connections to
this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server
is...but unless you know what you are looking at (or for) the numbers might
not mean anything.
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>
> I need some advice to better track the insert volume of each database on a
> server.
> I'd like to log that number once a month in a mock up table like
>
> Date DbName InsertCount
> 4/30/04 MyDb1 2000
> 4/30/04 MyDb2 500
> 5/31/04 MyDb1 1500 (probably would be based
3500-2000
> previous month records)
> 5/31/04 MyDb2 200 (based on total record count of
> 700-500)
>
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank you.
>
> Steve
>|||sp_helpdb
it returns the database name, size, and other info. You could store =
this data to a table as often as you want. =20
These examples should get you started if you want rowcounts. I have =
credited the authors=20
basic rowcount:
use pubs
go
SELECT A.name, B.rows=20
FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2=20
ORDER BY A.Name
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
select name, rows from sysobjects
where type =3D 'U' order by rows desc
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
The script below will give information about the row count and space =
used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have =
to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the =
data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.c...?TOPIC_ID=3D629
The script that is attached to this message isn't mine btw, but I can't =
find
back who the original author is.
--=20
Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname =3D DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + =
@.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType =3D 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =
Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =
Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
The following was adapted from SP_SPACEUSED, and will also give the size =
in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS =3D (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID =
=3D
O.ID),
RESERVED =3D (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES =
I
WHERE I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128),
DATA =3D (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I =
WHERE
I.INDID < 2 AND I.ID =3D O.ID)) / 128),
TEXT =3D (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES =
I
WHERE I.INDID =3D 255 AND I.ID =3D O.ID),0)) / 128),
USED =3D (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE =3D 'U'
Mike Kruchten
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
http://www.aspfaq.com/2428
Aaron Bertrand
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
--=20
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =
news:%23uCwPM%23IEHA.3512@.TK2MSFTNGP10.phx.gbl...
> thank you for the advice. that's very ture.
> i think i still would like to track that number for the purpose of =
server
> capacity planning.
> any ideas?
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
> A database can be very "busy" and have 0 rows added, deleted, or =
updated.
>=20
> Consider a database that is used for reporting purposes. The =
connections to
> this database will only be reading data.
>=20
> You can run Profiler and PerfMon in order to see how "busy" a server
> is...but unless you know what you are looking at (or for) the numbers =
might
> not mean anything.
>=20
> --
> Keith
>=20
>=20
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
database on a[vbcol=seagreen]
> 3500-2000
of[vbcol=seagreen]
you.[vbcol=seagreen]
>=20
>

How to track insert volume for each database?

I need some advice to better track the insert volume of each database on a
server.
I'd like to log that number once a month in a mock up table like
Date DbName InsertCount
4/30/04 MyDb1 2000
4/30/04 MyDb2 500
5/31/04 MyDb1 1500 (probably would be based 3500-2000
previous month records)
5/31/04 MyDb2 200 (based on total record count of
700-500)
Bottom line is to get a feel of how busy a database is. I'm not sure
tracking inserts is the best way and open to any suggestions. Thank you.
SteveA database can be very "busy" and have 0 rows added, deleted, or =updated.
Consider a database that is used for reporting purposes. The =connections to this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server =is...but unless you know what you are looking at (or for) the numbers =might not mean anything.
-- Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
> > > I need some advice to better track the insert volume of each database =on a
> server.
> > I'd like to log that number once a month in a mock up table like
> > > > Date DbName InsertCount
> > 4/30/04 MyDb1 2000
> > 4/30/04 MyDb2 500
> > 5/31/04 MyDb1 1500 (probably would be based =3500-2000
> previous month records)
> > 5/31/04 MyDb2 200 (based on total record count =of
> 700-500)
> > > > Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank =you.
> > > > Steve
> >|||thank you for the advice. that's very ture.
i think i still would like to track that number for the purpose of server
capacity planning.
any ideas?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
A database can be very "busy" and have 0 rows added, deleted, or updated.
Consider a database that is used for reporting purposes. The connections to
this database will only be reading data.
You can run Profiler and PerfMon in order to see how "busy" a server
is...but unless you know what you are looking at (or for) the numbers might
not mean anything.
--
Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
>
> I need some advice to better track the insert volume of each database on a
> server.
> I'd like to log that number once a month in a mock up table like
>
> Date DbName InsertCount
> 4/30/04 MyDb1 2000
> 4/30/04 MyDb2 500
> 5/31/04 MyDb1 1500 (probably would be based
3500-2000
> previous month records)
> 5/31/04 MyDb2 200 (based on total record count of
> 700-500)
>
> Bottom line is to get a feel of how busy a database is. I'm not sure
> tracking inserts is the best way and open to any suggestions. Thank you.
>
> Steve
>|||sp_helpdb
it returns the database name, size, and other info. You could store =this data to a table as often as you want.
These examples should get you started if you want rowcounts. I have =credited the authors
basic rowcount:
use pubs
go
SELECT A.name, B.rows FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2 ORDER BY A.Name
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D
select name, rows from sysobjects
where type =3D 'U' order by rows desc
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D
The script below will give information about the row count and space =used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have =to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the =data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3D629
The script that is attached to this message isn't mine btw, but I can't =find
back who the original author is.
-- Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname =3D DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + =@.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType =3D 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total =Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D
The following was adapted from SP_SPACEUSED, and will also give the size =in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS =3D (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID ==3D
O.ID),
RESERVED =3D (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES =I
WHERE I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128),
DATA =3D (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I =WHERE
I.INDID < 2 AND I.ID =3D O.ID)) / 128),
TEXT =3D (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES =I
WHERE I.INDID =3D 255 AND I.ID =3D O.ID),0)) / 128),
USED =3D (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID =3D O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE =3D 'U'
Mike Kruchten
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D
http://www.aspfaq.com/2428
Aaron Bertrand
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D
-- Keith
"Steve Lin" <lins@.nospam.portptld.com> wrote in message =news:%23uCwPM%23IEHA.3512@.TK2MSFTNGP10.phx.gbl...
> thank you for the advice. that's very ture.
> i think i still would like to track that number for the purpose of =server
> capacity planning.
> any ideas?
> > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uL7Gvf9IEHA.2720@.TK2MSFTNGP11.phx.gbl...
> A database can be very "busy" and have 0 rows added, deleted, or =updated.
> > Consider a database that is used for reporting purposes. The =connections to
> this database will only be reading data.
> > You can run Profiler and PerfMon in order to see how "busy" a server
> is...but unless you know what you are looking at (or for) the numbers =might
> not mean anything.
> > --
> Keith
> > > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:u9qExS9IEHA.1048@.TK2MSFTNGP12.phx.gbl...
> >
> >
> > I need some advice to better track the insert volume of each =database on a
> > server.
> >
> > I'd like to log that number once a month in a mock up table like
> >
> >
> >
> > Date DbName InsertCount
> >
> > 4/30/04 MyDb1 2000
> >
> > 4/30/04 MyDb2 500
> >
> > 5/31/04 MyDb1 1500 (probably would be based
> 3500-2000
> > previous month records)
> >
> > 5/31/04 MyDb2 200 (based on total record count =of
> > 700-500)
> >
> >
> >
> > Bottom line is to get a feel of how busy a database is. I'm not sure
> > tracking inserts is the best way and open to any suggestions. Thank =you.
> >
> >
> >
> > Steve
> >
> >
> >

how to tell when a database was lasted accessed by general users?

Hi,
In the Subject, by general users, I meant those users who did regular
database operations, like insert, update, delete, etc. rather than the users
who have special privileges to do database maintenance operations, like
backup. We need to clean up those databases that haven't been accessed for
quite a while. Right now, we back up all the databases.
Thanks in advance,
BingHi
AFAIK there is not an easy way without either prolonged profiling or
possibly enabling C2 auditing. Alternatively products like auditdb from
Lumigent http://www.lumigent.com/products/auditdb.html would be able to tell
you, and log readers like their log explorer
http://www.lumigent.com/products/le_sql.html would be show updates.
A maverick approach may be to make the database readonly and see who squeals
!!
John
"bing" wrote:

> Hi,
> In the Subject, by general users, I meant those users who did regular
> database operations, like insert, update, delete, etc. rather than the use
rs
> who have special privileges to do database maintenance operations, like
> backup. We need to clean up those databases that haven't been accessed fo
r
> quite a while. Right now, we back up all the databases.
> Thanks in advance,
> Bing

how to tell when a database was lasted accessed by general users?

Hi,
In the Subject, by general users, I meant those users who did regular
database operations, like insert, update, delete, etc. rather than the users
who have special privileges to do database maintenance operations, like
backup. We need to clean up those databases that haven't been accessed for
quite a while. Right now, we back up all the databases.
Thanks in advance,
Bing
Hi
AFAIK there is not an easy way without either prolonged profiling or
possibly enabling C2 auditing. Alternatively products like auditdb from
Lumigent http://www.lumigent.com/products/auditdb.html would be able to tell
you, and log readers like their log explorer
http://www.lumigent.com/products/le_sql.html would be show updates.
A maverick approach may be to make the database readonly and see who squeals!!
John
"bing" wrote:

> Hi,
> In the Subject, by general users, I meant those users who did regular
> database operations, like insert, update, delete, etc. rather than the users
> who have special privileges to do database maintenance operations, like
> backup. We need to clean up those databases that haven't been accessed for
> quite a while. Right now, we back up all the databases.
> Thanks in advance,
> Bing
sql

how to tell when a database was lasted accessed by general users?

Hi,
In the Subject, by general users, I meant those users who did regular
database operations, like insert, update, delete, etc. rather than the users
who have special privileges to do database maintenance operations, like
backup. We need to clean up those databases that haven't been accessed for
quite a while. Right now, we back up all the databases.
Thanks in advance,
BingHi
AFAIK there is not an easy way without either prolonged profiling or
possibly enabling C2 auditing. Alternatively products like auditdb from
Lumigent http://www.lumigent.com/products/auditdb.html would be able to tell
you, and log readers like their log explorer
http://www.lumigent.com/products/le_sql.html would be show updates.
A maverick approach may be to make the database readonly and see who squeals!!
John
"bing" wrote:
> Hi,
> In the Subject, by general users, I meant those users who did regular
> database operations, like insert, update, delete, etc. rather than the users
> who have special privileges to do database maintenance operations, like
> backup. We need to clean up those databases that haven't been accessed for
> quite a while. Right now, we back up all the databases.
> Thanks in advance,
> Bing

how to syncronize record via ftp?

CIAO all people...!
Ihave 1 database di access 2000 built on 20 table...query and form,
users insert record easy from form.
I have Microsoft SQL Server Service Manager 7.00.623.
I have the same database in web space ...ex ...(ad esempio
ftp.tiscali.it/azienda/db/data.mdb)
how to syncronize record?
the all database is 60 mb
Access replica is ok for localhost or for a pc in lan...is not ok for
FTP...
Replica snapshot di SQL (with server distributore etc etc) is ok only for
SQL database but i have access...
Is this an Access question?
If so, please post it to microsoft.public.access.replication
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"miikii" <m_piccSPAM@.DELCAKKIOhotmail.com> wrote in message
news:42144f3e$0$6316$5fc30a8@.news.tiscali.it...
> CIAO all people...!
> Ihave 1 database di access 2000 built on 20 table...query and form,
> users insert record easy from form.
> I have Microsoft SQL Server Service Manager 7.00.623.
> I have the same database in web space ...ex ...(ad esempio
> ftp.tiscali.it/azienda/db/data.mdb)
> how to syncronize record?
> the all database is 60 mb
>
> Access replica is ok for localhost or for a pc in lan...is not ok for
> FTP...
> Replica snapshot di SQL (with server distributore etc etc) is ok only for
> SQL database but i have access...
>
>

how to suppress zeroes after decimal point at the end in a value

hi,
i have a sql table field price and datatype is
decimal 13(20,6).
when i insert values to this field, values are being
inserted correctly. i.e. 13.45 inserted as 13.45 and
145.653 inserted as 145.653 only.
But while fetching only the values are coming as 13.450000,
145.653000, because the datatype is decimal 13(20,6) with
6 decimals. but i want 13.45, 145.653 as in the table.
How suppress the unwanted zeroes at the end of those
numbers.
any help.
thanks,
hari.
see following example:
drop table test
create table test(c1 decimal (15,5))
insert into test values (3.567000)
insert into test values (232233.567000)
insert into test values (3.567)
query:
select c1,reverse(substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) - 1)
)) 'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||hi thanks,
but it is not working for whole number like ex:11120
it is giving it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||try:
select c1, reverse(
case when substring(substring(reverse(cast(c1 as varchar(25)))
,
patindex('%[^0]%', reverse(cast(c1 as varchar(25))))
,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1)) ,1,1) = '.'
then
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) + 1 ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 2))
else
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1))
end)
'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"hari" <anonymous@.discussions.microsoft.com> wrote in message
news:207601c4a145$b5262d90$a601280a@.phx.gbl...[vbcol=seagreen]
> hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
> thanks,
> hari.
> (25))) ,
> reverse(cast(c1 as
|||See if this works:
select
replace(rtrim(replace(
replace(rtrim(replace(
c1,'0',' ')),' ','0')
,'.',' ')),' ','.')
from test
Steve Kass
Drew University
hari wrote:
[vbcol=seagreen]
>hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
>thanks,
>hari.
>
>(25))) ,
>
>reverse(cast(c1 as
>
>

how to store values during dataflow

The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.

How can i get a value that was calculated before? Can i store it in a variable?

Many Thanks!

On the Control Flow add a 'Execute SQL Task' which has something like SELECT MAX(ID) AS MAXID FROM YOURTABLE, store the result into a variable.

Do your inserts, then SELECT * FROM YOURTABLE WHERE ID > YOURVARIABLE

|||

Thanks Paul,

The only way to store this results on a variable is from an 'Execute SQL Task'? can i do the same from either a OLEDB Command or Agregate Transformation?

|||You can use a script component to store values in a variable inside a data flow. You have to write it in the PostExecute method, though, which runs after all the rows pass thorugh it. However, I don't know if that is the best approach, given the scenario you are describing. But, ultimately, it's your call.|||

Thank you very much jwelch, ill try it!

How to stop running DTS package if my view return zero record?

Hi everyone,

I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.

So.. I need help from you guys, cause I don't know How.

This is my DTS description.

My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.

Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.

BUT How? Can someone please help? Thanks

Kelvin,

This is an SSIS forum (not DTS).

Sorry.

|||Not sure, and I second Rafael's comment, but can you put in a row counter against the view and test it's result?|||

I did, but I don't know how to put it into a package.?

Please Help......

|||

Kelvin Y wrote:

I did, but I don't know how to put it into a package.?

Please Help......

If you are using SSIS, and not DTS, you can use an Execute SQL task in the control flow to query the view and capture the row count. (select count(*) from view) Then in the control flow you can test the variable that was previously populated with the Execute SQL task for a condition of != 0, and if so, continue processing.|||

OK. here's another idea....slightly different from Phil's. Give it a shot. (using SSIS again)

The SQL task has a property "ForceExecutionResult" that you can configure using exprssions. Get the count from the view exactly like Phil explained above but instead of using a conditional split, use the value to set the exprssion to Success or Failure.

You wont be able to log an approiate error message though.

P.S: For some reason the use of the word "e x p r e s s i o n" is causing my post to be truncated. So, had to use "exprssion"

|||

Thanks everyone, but I can't find the "ForceExecutionResult" property.

Can you tell me where is it?

Thanks

|||

If you dont have experience with exprssions, I'd suggest using Phil's approach.

But to answer your question, you can find the property under the "misc" category about 6th in the list.

|||Thanks everyone, it works now. Thanks for helping me out.|||

Kelvin Y wrote:

Thanks everyone, it works now. Thanks for helping me out.

Please mark this thread as answered.