Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=re
> sql statement or SP) on a database and gives me the record count from each=
> one...
> Because I need to know the table wich has more records on it! Can you help=
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=re
> sql statement or SP) on a database and gives me the record count from each=
> one...
> Because I need to know the table wich has more records on it! Can you help=
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=b-ca87e89bbc02@.d4g2000prg.googlegroups.com...
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> > Hi, I would like to know if is possible to loop through all tables (via
> > pure
> > sql statement or SP) on a database and gives me the record count from ea=ch
> > one...
> > Because I need to know the table wich has more records on it! Can you he=lp
> > me ?
> > Thanks!
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx|||It works for 7.0, 2000 and 2005.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> You can use the undocumented sp_foreachtable:
>> sp_foreachtable ('select ''?'', count (*) from ?')
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Hi, I would like to know if is possible to loop through all tables (via
>> pure
>> sql statement or SP) on a database and gives me the record count from
>> each
>> one...
>> Because I need to know the table wich has more records on it! Can you
>> help
>> me ?
>> Thanks!
>>
>|||Take your pick..........
select o.name tablename ,i.rows tblrowcount
from sysobjects o
inner join sysindexes i on (o.id = i.id)
where o.xtype = 'U' and o.name <> 'dtproperties'
and i.indid < 2 Order by tablename
"Paulo" wrote:
> Hi, I would like to know if is possible to loop through all tables (via pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>
>|||sysindexes is not guaranteed to be maintained to actual row count values.
See DBCC UPDATEUSAGE in BOL.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"SB" <othellomy@.yahoo.com> wrote in message
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 8:25=A0pm, DXC <D...@.discussions.microsoft.com> wrote:
> Take your pick..........
> select o.name tablename ,i.rows tblrowcount
> from sysobjects o
> =A0inner join sysindexes i on (o.id =3D i.id)
> where o.xtype =3D 'U' and o.name <> 'dtproperties'
> =A0and i.indid < 2 Order by tablename
>
> "Paulo" wrote:
> > Hi, I would like to know if is possible to loop through all tables (via =pure
> > sql statement or SP) on a database and gives me the record count from ea=ch
> > one...
> > Because I need to know the table wich has more records on it! Can you he=lp
> > me ?
> > Thanks!- Hide quoted text -
> - Show quoted text -
You need to refer this as well
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx

Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!
You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!
|||On Dec 27, 5:26Xpm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name
|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>
|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name
|||On Dec 27, 5:00Xpm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> X XTom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON X Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'
|||It works for 7.0, 2000 and 2005.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>
|||On Dec 27, 5:11Xpm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
>
>
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id = so.id
> and so.type in ('U')
> and si.status = 2066
> order by so.name
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>
|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>
sql

Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=[/vbcol
]
[vbcol=seagreen]
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||It works for 7.0, 2000 and 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=[/vbcol
]
b-ca87e89bbc02@.d4g2000prg.googlegroups.com...[vbcol=seagreen]
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
>
ch[vbcol=seagreen]
>
lp[vbcol=seagreen]
>
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=[/url
]
o-count-rows-from-a-table.aspx|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>

Looping problem in DTS with ActiveX

I have a DTS package that downloads files via FTP, then processes each file. A few tasks are done when each file is processed:
(a) a holding table is truncated and 1 blank record is inserted into the holding table,
(b) the XML data in the file is inserted into the holding table via TextCopy.exe,
(c) the XML data is parsed using OPENXML and inserted into 2 additional holding tables, and
(d) the XML file is archived to another directory.
After (a), (b), (c), and (d) are completed, the DTS package loops back and executes (a), (b), (c), and (d) for the other remaining files.

It all worked flawlessly in testing, until I commented out a MsgBox line in the ActiveX task for item (b) above. Without the MsgBox command, the other tasks (b) and (c) don't appear to execute, though I can see that the looping is working, since the source files get moved to the archive location (in step (d)).

Attached is a screenshot of the DTS package (it can also be viewed at http://www.nmwildlife.org/images/DTS_screenshot.gif).

I think that the MsgBox issue is a red herring; in other words, I'm thinking that when I click the OK button on the MsgBox, there might be something about the return code which allows the tasks to be executed properly. However, I'm not a VBScript expert, so can't figure out where the problem lies or how to fix it.

Here's the code for the "Import w/ShellCmd" ActiveX task:

Function Main()
Dim objShell
Dim strPath
Dim strCmd

strPath = CSTR(DTSGlobalVariables("gv_FileFullName").Value)

strCmd = """C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TextCopy.exe"" /S ""GROVER"" /U sa /P """" /D TESTProlaw /T dc_XML /C myXML /F " & _
strPath & _
" /W ""WHERE 1=1"" /I /Z"

Set objShell = CreateObject("WScript.Shell")
objShell.Run strCmd
Set objShell = nothing

MsgBox ""

Main = DTSTaskExecResult_Success
End Function

And here's the code for the "Begin Loop" ActiveX task:

Option Explicit

Function Main()

dim pkg
dim stpEnterLoop
dim stpFinished

set pkg = DTSGlobalVariables.Parent
set stpEnterLoop = pkg.Steps("DTSStep_DTSExecuteSQLTask_2") 'Start loop at the "Truncate dc_XML" task
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")

' We want to continue with the loop only of there are more than 1 text file in the directory.
' If the function ShouldILoop returns true then we disable the step that takes us out of the package and continue processing

if ShouldILoop = True then
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop

dim fso
dim fil
dim fold
dim pkg
dim counter

set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")

set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

counter = fold.files.count

'So long as there is more than 1 file carry on

if counter >= 1 then

for each fil in fold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.path
ShouldILoop = CBool(True)
Next

else
ShouldILoop = CBool(False)
End if

End Function

The goal is to get the DTS package to run without having to manually click OK on the MsgBox; that way, I can schedule it to run automatically.

Any help would be greatly appreciated. Thanks in advance!no, here the MsgBox is not returning anything. it should run perfectly without the MsgBox. i think it was used to give the shell the time to complete the process.
what u can try is - executing the shell in sync mode and drop the MsgBox. use this code and see if it works

....
Ret = objShell.Run (strCmd,,true)
.....sql

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> > I want to set a server up to be able to reference itself
> > via an alias in a 4-part name. Let us call the server
> > SQLSERVER and its alias BILLING. The motive for this is
> > to be able to, move the so-referenced databases to another
> > server when I want to and reference that truely remote
> > server by the BILLING alias.
> >
> > I have the alias set-up correctly and the server linked
> > via that alias with the proper security contexts.
> >
> > When I query:
> >
> > select * from [billing].master.dbo.sysdatabases
> >
> > I get the resultset I want.
> >
> > When I query:
> >
> > begin tran
> > select * from [billing].master.dbo.sysdatabases
> > rollback tran
> >
> > I get the following error:
> >
> > Server: Msg 7391, Level 16, State 1, Line 2
> > The operation could not be performed because the OLE DB
> > provider 'SQLOLEDB' was unable to begin a distributed
> > transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> >
> > If I use BILLING to alias another server (a truely remote
> > server), the transactioned query works without error.
> >
> > I've came to understand this as a loopback linked server limitation
> > in that loopback linked servers cannot enlist in a distributed
> transaction.
> >
> > But, I made a table, x, on sqlserver.Master with one int column, x
> > I made a stored proc, spx, on sqlserver.Master that takes one parameter
> > and inserts it's value into table x
> >
> > On Sql Server I did the following
> >
> > Use Master
> > go
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 1
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 commit tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 1 and no errors so transaction worked.
> > go
> >
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 2
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 rollback tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 2 and no errors so transaction worked.
> > go
> >
> > begin tran
> > select * from [BILLING].Master.dbo.x
> > -- Saw same error as cited above as expected.
> > if @.@.trancount != 0 rollback tran
> >
> > What is going on? Transactions should have failed in stored proc usage
> > as well as a direct query if this were a loopback failure to enlist a
> > distributed transaction, shouldn't they?
> >
> >
> >
> >
>

Loop container to process all excel files

What I'm trying to achieve is a SSIS package that will pick up 1 or more excel files, process the data in them via the conditional splitter, pushing the good data into a table, and all other rows into an error table.
I'm having some issues using the ForEach container to process multiple excel spreadsheets into tables. The excel import into the tables is more or less working (imports data for good cases, but uses a null if the Excel Source when it gets an unexpected value - but that's a seperate problem).
I found something related to this when searching, but it related to CTPs (June and September) and trying to reuse the connection strings they built up there (using my own variable names, naturally) causes a 'Property Value failure':
--
The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
--
I attemtpted to use this:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::RankingFileFullPath] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
The excel importer works fine as a stand-alone component. Trying to use the process defined in 'Profession SQL Server Integration Services' pp140, I tried to use an expression to assign the variable value to the connection string. I get a validation error:
--
Error at Import TPNB Ranking Excel spreadsheets [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: One or more component failed validation.
Error at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.
Error at Excel Importer [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D.
--
Any advice?
....
.... in addition ....
I attempted to change the package - I set the Data Flow validation to Delay Validation, and changed the expression to change from:
ConnectionString @.[User::RankingFileFullPath]
to
ExcelFilePath @.[User::RankingFileFullPath]
This allowed the package to start debugging, and gave more information in the failure:
--
SSIS package "Excel Importer.dtsx" starting.
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Excel Importer' has been hit
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Foreach Loop Container' has been hit
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
Information: 0x4004300A at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Import TPNB Ranking Excel spreadsheets, ProductSalesRank Table [278]: Truncation may occur due to inserting data from data flow column "Rank" with a length of 1000 to database column "SalesRank" with a length of 50.
Error: 0xC0202009 at Excel Importer, Connection manager "Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'D:\Testing\TestRanking.xls'.".
Error: 0xC020801C at Import TPNB Ranking Excel spreadsheets, Excel Source [1]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error: 0xC0047017 at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
SSIS breakpoint 'Break when the container receives the OnWarning event' at executable 'Excel Importer' has been hit
Warning: 0x80019002 at Excel Importer: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS breakpoint 'Break when the container receives the OnPostExecute event' at executable 'Excel Importer' has been hit
SSIS package "Excel Importer.dtsx" finished: Failure.
The program '[2460] Excel Importer.dtsx: DTS' has exited with code 0 (0x0).--
Apparently, I added too much detail, and poeple are avoiding the question :-)
I've tested doing exactly the same thing, but using CSV's as the source (i.e. text files and not excel files). And it works fine. So there's nothing wrong with the environment, DB etc.
...aaarg|||

I do recall other people having similar problems with Excel but I don't know if there was a resolution.

Sorry...not much help I'm afraid.

-Jamie

|||Thanks for the response - at least I have had one, now
The only reference's I've found pointed me to (a) re-installing service pack 1 to ensure SQL and SSIS etc. are all good. I did that, but no go. The other reference seemed to say I need to write an activeX (or .Net script) component to handle the files. That's just got to be wrong - what's the point of having to continuously kludge different sections of SSIS together with .Net scripting... In that case it seems that we should rather do the whole thing in 'Net ... *sighs* ...
I guess I'm just a bit frustrated that something as simple as importing a few excel files into a SQL table doesn't work simply ... after all the forEach and loop controls was one of the NEW BIG THINGS that SSIS was bringing us.... oh well, it's getting later on a Friday, and it looks like it's going to be a long, frustrating weekend (... it might be better if I knew how to write the .Net )
Well, I hope you (and anyone else reading my ranting) has a good weekend.
|||

I was able to complete your scenario successfully following the instructions at: http://msdn2.microsoft.com/en-us/library/ms345182.aspx

Note that I paid particular attention to this paragraph:

To avoid validation errors later as you configure tasks and data flow components to use this connection manager, assign a default Excel workbook in the Excel Connection Manager Editor. After you create and configure the package, you can delete this value in the Properties window. However, after you delete this value, a validation error may occur because the connection string property of the Excel connection manager is no longer valid until the Foreach Loop runs. In this case, set the DelayValidation property to True on the connection manager, on the tasks in which the connection manager is used, or on the package.

If I did not have DelayValidation set True on the connection manager and have a valid default value in the connection manager I received the same errors as you. I also set DelayedValidation = True on the Data Flow Task for extra caution.

Donald

|||

Hi Donald,

Thanks for the link and the reponse ... I appreciate it.

I'll have a look - the article loks promising. I did already have all my 'delay validations' set to true, and I initially created the excel connection with a valid connection - I had it pointing at one of the file I want to impoty. When it is hard-coded to the excel file, my import runs fine... as soon as I delete the connection name, and replace it witht eh variable name @.[User::RankingFileFullPath], mr problems occur. I'll read through the article, and create a new package, just to ensure I don't inherit some issues I created in my attempts to appease the SSIS connections gods... :-)

thanks again

|||

I followed the article's guidelines, but am having an issue.

The sequence of actions:

Create New Project.

Create ForEach container.

Create Variables ExcelFileFullPath and ExcelConnectionProperties. Set the Container to map the ExcelFileFullPath.

Create Excel Connection, pointing to valid, existing excel file.

Alter Excel connection properties: Delay Validation = true. Expression: ConnectionString:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFilefullPath] + ";Extended Properties=\"" + @.[User::ExcelConnectionProperties] + "\""

All good, so far.

Create a data flow control in the container. Add an excel source. Try connecting excel source to excel connection - error: Initial error was an Hresult type error (not captured). Trying to go further, I now get 'Invlaid Arguement' when trying to choose name of excel sheet - this happens on new excel source as well.

I'm going to start again, this time creating an excel import that works correctly. Once that's done, I'll try and move it into the container, and apply the properties as described in article, and see if that makes a difference (surely it shouldn't?)....

|||

Status:

I have created a Data Flow Task that opens a HardCoded (or direct) Excel Connection, does a data conversion, then a conditional split, and inserts rows into a OLE DB table connection. I have a ForEach container that correctly populates the excel file path into the ExcelFullFilePath variable. The Data flow task is OUTSIDE the container. All runs through successfully.

Next:

I intend to move the data flow task into the Container, but not change the excel connection, and confirm that it runs successfully. Assumming success, I will then apply the process from the article, specifically:

Set Delay Validation = True on the Package, Containter, Data Flow and Excel Connection.

Set the ConnectionString (via expressions) =

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFilefullPath] + ";Extended Properties=\"" + @.[User::ExcelConnectionProperties] + "\""

I'll respond on progress/results

-- so far:

Run with Data Flow in ForEach container - still runs through, USING Hard-coded connection

Change Delay Validation properties on all objects - still runs through, USING Hard-coded connection

Change ConnectionString property to use dynamic ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFilefullPath] + ";Extended Properties=\"" + @.[User::ExcelConnectionProperties] + "\"") - SUCCESS.

Add additional .xls file to folder for processing - failure. BUT that was (due to a GOOD, DESCRIPTIVE ERROR MESSAGE) because the connection requires the same sheet name for the 2 excel files. I can live with that.

Update 2nd test file sheet name, re-run... SUCCESS. 2 Excel file successfully imported.

Many thanks for the response, especially yours, Donald.

I still don't understand why I had to go through the sequence I had to, i.e. create a fully working excel import, and then alter the properties, but it worked. Also, a useful note for those doing this in the future - make sure the sheets for the multiple files have the same sheet names.

HTH and thanks

|||

Thanks for these detailed notes. They will help us to improve this experience in the future.

Donald

|||Wow, thanks GethWho. I've been looking for this answer all day!

Everyone looking at this make sure the set DelayValidation to true on ALL of your components!

-Matt

Loop container to process all excel files

What I'm trying to achieve is a SSIS package that will pick up 1 or

more excel files, process the data in them via the conditional

splitter, pushing the good data into a table, and all other rows into

an error table.

I'm having some issues using the ForEach container to process multiple

excel spreadsheets into tables. The excel import into the tables is

more or less working (imports data for good cases, but uses a null if

the Excel Source when it gets an unexpected value - but that's a

seperate problem).

I found something related to this when searching, but it related to

CTPs (June and September) and trying to reuse the connection strings

they built up there (using my own variable names, naturally) causes a

'Property Value failure':

--

The connection string format is not valid. It must consist of one or

more components of the form X=Y, separated by semicolons. This error

occurs when a connection string with zero components is set on database

connection manager.

--

I attemtpted to use this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +

@.[User::RankingFileFullPath] + ";Extended Properties=\"Excel

8.0;HDR=YES\";"

The excel importer works fine as a stand-alone component. Trying to use

the process defined in 'Profession SQL Server Integration Services'

pp140, I tried to use an expression to assign the variable value to the

connection string. I get a validation error:

--

Error at Import TPNB Ranking Excel spreadsheets [Excel Source [1]]: The

AcquireConnection method call to the connection manager "Excel

Connection Manager" failed with error code 0xC0202009.

Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]:

component "Excel Source" (1) failed validation and returned error code

0xC020801C.

Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: One or more component failed validation.

Error at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.

Error at Excel Importer [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D.

--

Any advice?

....

.... in addition ....

I attempted to change the package - I set the Data Flow validation to

Delay Validation, and changed the expression to change from:

ConnectionString @.[User::RankingFileFullPath]

to

ExcelFilePath @.[User::RankingFileFullPath]

This allowed the package to start debugging, and gave more information in the failure:

--

SSIS package "Excel Importer.dtsx" starting.

SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Excel Importer' has been hit

SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Foreach Loop Container' has been hit

SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit

Information: 0x4004300A at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: Validation phase is beginning.

Warning: 0x802092A7 at Import TPNB Ranking Excel spreadsheets,

ProductSalesRank Table [278]: Truncation may occur due to inserting

data from data flow column "Rank" with a length of 1000 to database

column "SalesRank" with a length of 50.

Error: 0xC0202009 at Excel Importer, Connection manager "Excel

Connection Manager": An OLE DB error has occurred. Error code:

0x80004005.

An OLE DB record is available. Source: "Microsoft JET Database

Engine" Hresult: 0x80004005 Description: "Unrecognized

database format 'D:\Testing\TestRanking.xls'.".

Error: 0xC020801C at Import TPNB Ranking Excel spreadsheets, Excel

Source [1]: The AcquireConnection method call to the connection manager

"Excel Connection Manager" failed with error code 0xC0202009.

Error: 0xC0047017 at Import TPNB Ranking Excel spreadsheets,

DTS.Pipeline: component "Excel Source" (1) failed validation and

returned error code 0xC020801C.

Error: 0xC004700C at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.

SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit

Warning: 0x80019002 at Foreach Loop Container: The Execution method

succeeded, but the number of errors raised (5) reached the maximum

allowed (1); resulting in failure. This occurs when the number of

errors reaches the number specified in MaximumErrorCount. Change the

MaximumErrorCount or fix the errors.

SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit

SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit

SSIS breakpoint 'Break when the container receives the OnWarning event' at executable 'Excel Importer' has been hit

Warning: 0x80019002 at Excel Importer: The Execution method succeeded,

but the number of errors raised (5) reached the maximum allowed (1);

resulting in failure. This occurs when the number of errors reaches the

number specified in MaximumErrorCount. Change the MaximumErrorCount or

fix the errors.

SSIS breakpoint 'Break when the container receives the OnPostExecute event' at executable 'Excel Importer' has been hit

SSIS package "Excel Importer.dtsx" finished: Failure.

The program '[2460] Excel Importer.dtsx: DTS' has exited with code 0

(0x0).--Apparently, I added too much detail, and poeple are avoiding the question :-)

I've tested doing exactly the same thing, but using CSV's as the source

(i.e. text files and not excel files). And it works fine. So there's

nothing wrong with the environment, DB etc.

...aaarg|||

I do recall other people having similar problems with Excel but I don't know if there was a resolution.

Sorry...not much help I'm afraid.

-Jamie

|||Thanks for the response - at least I have had one, now

The only reference's I've found pointed me to (a) re-installing service

pack 1 to ensure SQL and SSIS etc. are all good. I did that, but no go.

The other reference seemed to say I need to write an activeX (or .Net

script) component to handle the files. That's just got to be wrong -

what's the point of having to continuously kludge different sections of

SSIS together with .Net scripting... In that case it seems that we

should rather do the whole thing in 'Net ... *sighs* ...

I guess I'm just a bit frustrated that something as simple as importing

a few excel files into a SQL table doesn't work simply ... after all

the forEach and loop controls was one of the NEW BIG THINGS that SSIS

was bringing us.... oh well, it's getting later on a Friday, and it

looks like it's going to be a long, frustrating weekend (... it might

be better if I knew how to write the .Net )

Well, I hope you (and anyone else reading my ranting) has a good weekend.|||

I was able to complete your scenario successfully following the instructions at: http://msdn2.microsoft.com/en-us/library/ms345182.aspx

Note that I paid particular attention to this paragraph:

To avoid validation errors later as you configure tasks and data flow components to use this connection manager, assign a default Excel workbook in the Excel Connection Manager Editor. After you create and configure the package, you can delete this value in the Properties window. However, after you delete this value, a validation error may occur because the connection string property of the Excel connection manager is no longer valid until the Foreach Loop runs. In this case, set the DelayValidation property to True on the connection manager, on the tasks in which the connection manager is used, or on the package.

If I did not have DelayValidation set True on the connection manager and have a valid default value in the connection manager I received the same errors as you. I also set DelayedValidation = True on the Data Flow Task for extra caution.

Donald

|||

Hi Donald,

Thanks for the link and the reponse ... I appreciate it.

I'll have a look - the article loks promising. I did already have all my 'delay validations' set to true, and I initially created the excel connection with a valid connection - I had it pointing at one of the file I want to impoty. When it is hard-coded to the excel file, my import runs fine... as soon as I delete the connection name, and replace it witht eh variable name @.[User::RankingFileFullPath], mr problems occur. I'll read through the article, and create a new package, just to ensure I don't inherit some issues I created in my attempts to appease the SSIS connections gods... :-)

thanks again

|||

I followed the article's guidelines, but am having an issue.

The sequence of actions:

Create New Project.

Create ForEach container.

Create Variables ExcelFileFullPath and ExcelConnectionProperties. Set the Container to map the ExcelFileFullPath.

Create Excel Connection, pointing to valid, existing excel file.

Alter Excel connection properties: Delay Validation = true. Expression: ConnectionString:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFilefullPath] + ";Extended Properties=\"" + @.[User::ExcelConnectionProperties] + "\""

All good, so far.

Create a data flow control in the container. Add an excel source. Try connecting excel source to excel connection - error: Initial error was an Hresult type error (not captured). Trying to go further, I now get 'Invlaid Arguement' when trying to choose name of excel sheet - this happens on new excel source as well.

I'm going to start again, this time creating an excel import that works correctly. Once that's done, I'll try and move it into the container, and apply the properties as described in article, and see if that makes a difference (surely it shouldn't?)....

|||

Status:

I have created a Data Flow Task that opens a HardCoded (or direct) Excel Connection, does a data conversion, then a conditional split, and inserts rows into a OLE DB table connection. I have a ForEach container that correctly populates the excel file path into the ExcelFullFilePath variable. The Data flow task is OUTSIDE the container. All runs through successfully.

Next:

I intend to move the data flow task into the Container, but not change the excel connection, and confirm that it runs successfully. Assumming success, I will then apply the process from the article, specifically:

Set Delay Validation = True on the Package, Containter, Data Flow and Excel Connection.

Set the ConnectionString (via expressions) =

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFilefullPath] + ";Extended Properties=\"" + @.[User::ExcelConnectionProperties] + "\""

I'll respond on progress/results

-- so far:

Run with Data Flow in ForEach container - still runs through, USING Hard-coded connection

Change Delay Validation properties on all objects - still runs through, USING Hard-coded connection

Change ConnectionString property to use dynamic ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFilefullPath] + ";Extended Properties=\"" + @.[User::ExcelConnectionProperties] + "\"") - SUCCESS.

Add additional .xls file to folder for processing - failure. BUT that was (due to a GOOD, DESCRIPTIVE ERROR MESSAGE) because the connection requires the same sheet name for the 2 excel files. I can live with that.

Update 2nd test file sheet name, re-run... SUCCESS. 2 Excel file successfully imported.

Many thanks for the response, especially yours, Donald.

I still don't understand why I had to go through the sequence I had to, i.e. create a fully working excel import, and then alter the properties, but it worked. Also, a useful note for those doing this in the future - make sure the sheets for the multiple files have the same sheet names.

HTH and thanks

|||

Thanks for these detailed notes. They will help us to improve this experience in the future.

Donald

|||Wow, thanks GethWho. I've been looking for this answer all day!

Everyone looking at this make sure the set DelayValidation to true on ALL of your components!

-Matt

Lookup transformation problem

Hello all,

I needed to lookup some table values based on a join of two fields...

I've configured the lookup transform to get the values via a SQL statement to minimize loading time.

However, when creating the relationships between the input columns and the lookup columns I receive following error:
input column [BATCH_ID] has a datatype which cannot be joined on

I've checked both input and lookup columns, both are of type DT_R8... Both columns in the different tables do have the same datatypes

Any idea how to solve this problem?

Thanks in advance

DT_R8 is a floating point data type, it is an approximate value which means it cannot be joined on. Try a decimal data type instead.|||

Hello,

Thanks for your reply but how can I change datatypes fetched directly from a SQL statement in the lookup query?

|||

lookup supplied many functions can cast datatype

i think you also can use convert or cast in sql statements

|||

Hello,

I've used Dataconversion and SQL functions to perform the requested conversions...

works fine however when executing the package it fails when loading the cache of the lookup components...
this is the message I get from the progress window:

[GET HOLDING_ID [2998]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01722: invalid number ".

and

next line:

[GET HOLDING_ID [2998]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

However, I never changed the SQL command of the transform in the advanced tab...

Any Idea?

Thanks in advance

LookUp Entities and Roles against Multiple Entities?

Apologies if this has already been asked and answered, though I haven't found it via search.

In my report model I have a lookup entity that consists of a Code field (PK) and a Description field this is linked to a parent entity (table) and works fine, e.g. the description is shown in the parent entity instead of the FK code. However this lookup entity would be useful linked to many other entities (tables) which use the same FK code linked to the lookup entity's Code field (PK) for use of the Description field. In my first attempt I linked the lookup to a second table entity but unlike the originally linked table entity, this is not shown as the description in report builder, only the role appears and I have to select the role then the description from the 2 fields displayed (Code, Description), additionally I get shown the related first table entity.

Firstly can a lookup entity be linked to multiple tables?

Secondly, if so am I forgetting something so only the description is shown like in the first linked table entity?

Thirdly, if I correct the problem is there a property I need to enable/disable to stop the first linked table entity appearing in the second table entity entity list in report builder or should I just leave it?

Any help/advice/suggestions would be appreciated, and apologies for long list of questions but all the books and help files I have used don't seem to use imperfect/real life data sources and examples.

Thanks

Andy

This is a bit weird replying to myself, but think I need to let you know, and others who may come across the problem, I believe I have solved it.

The problem appeared to be related to my using more than 1 Identifying Attribute for the lookup entity. Once I amended this to one Identifying Attribute it would appear that all is well. I get the description instead of a role entity appearing and the lookup works with multiple table entities (e.g. more than one table entity is linked to the lookup-table entity). I think I misunderstood the IdentifyingAttributes property thinking this had to be the PK field.

Thanks

Andy

looking to write my own simple SQL Client via TCP/IP

I'm looking to write my own simple SQL Client via TCP/IP. We have some hardware that does not run an OS of sorts, but I can create TCP Connectons. We's like to be able to Query our SQL Server with this hardware.

The SQL Servers we are talking to are MS SQL2000 and MS SQL 2005 both via TCP port 1433. We can telnet to the port of the SQL Server. Any resources to the SQL Protocol you could pass along would be great.

Thank you,
Scott<-

Hi Scott,

In order to create an application driver that can send requests and receive responses from a SQL Server instance, your application must be able to communicate using the Tablular Data Stream (TDS) protocol. The MS TDS protocol is a proprietary protocol and must be licensed from Microsoft. If this interests you, then please let me know and I'll send you information on getting started.

Thanks,
Il-Sung.

|||

Its not too proprietary since there are Linux and Java versions of the TDS Protocol out there with the Source? If its not too much trouble wont hurt to see whats involved in geting the License for the Protocol.

I've found the Following on TDS since I did the original Post:

http://en.wikipedia.org/wiki/Tabular_Data_Stream

http://www.freetds.org/

http://jtds.sourceforge.net/

I'm sure the MS version of it is more full featured when communicating to MS SQL 2005, though I jsut need to return a few Select statements.

I was thinking It might be easier to send a HTTP Request to IIS on the SQL Server and send the Select statement as a parameter and return the Data. I'm looking for something pretty simple as the hardware playform I'm working with is pretty limited.

Thank you,

Scott<-

|||

Hi Scott,

Yes, there are free implimentations available although none are endorsed by Microsoft and they are typically based on reverse-engineering efforts. If you'd like to license TDS start with the MS IP Licensing website (http://www.microsoft.com/about/legal/intellectualproperty) and search for TDS.

Alternatively, if your can send HTTP requests from your hardware platform, you can take advantage of the fact that SQL Server 2005 can active as a native web service for SOAP applications. Take a look at the following documentation for more info:

http://msdn2.microsoft.com/en-us/library/ms191274.aspx
http://msdn2.microsoft.com/en-us/library/ms345123.aspx

Il-Sung.

Looking for some magic

... well, perhaps some sophistication. The setup:

Server A publishes to server B via transactional replication. Server A is to be shut down while some electrical work is done. So we fail over to server B for a couple days. My underpowered brain says to shut down replication, possibly fail over well in advance of the power outage.

Then, when the electrical work is done and server A is restarted: 1) stop work on server B, 2) ?copy? the database on B to A (I suppose after deleting it from A), and 3) redoing the replication from scratch.

Can anyone recommend a better process?

I am very gratefull for any enlightenment.

If you're on SQL Server 2005, you can consider a 2 node Peer to Peer transactional replication. Transactional Replication with queued updating or bidirectional replication may also fit your scenario if you're on SQL Server 2000. For more information, please visit SQL Server Books Online about those topics.

With above replicaiton configuration, there is no need for you to redo replication setup from scratch, just stop/restart replication should be enough

Zhiqiang Feng

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