Foreach Loop Enumerator Question

Any help appreciated here...

I'm inserting a CSV file into my SQL Server 2005 Sept CTP which has approx 1700 rows. It contains Companies and Contacts which I split in a data flow and determine if with Fuzzy Lookups whether dupes exist and perform either insert or update (That works great).

Here is my issue:
The database does not use Sequence for the Pkeys and to make matters worse they are prefixed with letters. I have to generate them on my own with a custom function I have written. I need to step through each record of the CSV and assign the Pkey value and upon successfull insert update the counters table.

I cannot seem to figure out how to enumerate through this CSV file? I pull my Pkeys into Variables and thought someone might be able to advise.

Thanks,

David

I assume you are using a Data Flow task to run the import, so could you not use a Script Component to add the new PK column, calling your function?

The Script Component can add a new column, in a similar manner that you may do with the Drived Column Tx for example. You would need to use a script to be able to leverage your existing code I assume. If you could use the Derived Column, that would probably perform better.

Will this work, or have I missed something? Not quite sure what you mean by "update the counters table"?

|||I have added a Derived Column for the Company Primary Key but it generates one primary key for the entire record set from the file (1700 records).

Here is the process I'm using.
1.) DataFlow
a.)Flat File Source Adapter (1700 records)
b.)Add Derived Column for PrimaryKey
c.)ConvertData to match input Table.Column datatypes
d.)Need to Assign Primary Key to each record here.
In the control flow I query my database function to give me the next available primaryKey into a variable. I need to perform an update on the "Counters" Table which holds the current max(primarykey) so I can use the function again for the next row.

I can't seem to figure out how to iterate through the recordset where I can update my variable(pkey) for each of the 1700 records.|||

DarrenSQLIS wrote:

I assume you are using a Data Flow task to run the import, so could you not use a Script Component to add the new PK column, calling your function?

The Script Component can add a new column, in a similar manner that you may do with the Drived Column Tx for example. You would need to use a script to be able to leverage your existing code I assume. If you could use the Derived Column, that would probably perform better.

Will this work, or have I missed something? Not quite sure what you mean by "update the counters table"?

I have added a Derived Column for the Company Primary Key but it generates one primary key for the entire record set from the file (1700 records).

Here is the process I'm using.
1.) DataFlow
a.)Flat File Source Adapter (1700 records)
b.)Add Derived Column for PrimaryKey
c.)ConvertData to match input Table.Column datatypes
d.)Need to Assign Primary Key to each record here.
In the control flow I query my database function to give me the next available primaryKey into a variable. I need to perform an update on the "Counters" Table which holds the current max(primarykey) so I can use the function again for the next row.

I can't seem to figure out how to iterate through the recordset where I can update my variable(pkey) for each of the 1700 records.|||Using the Derived Column, would mean that you assign the PK value as part of the derivation/expression. If you can't, loose this transform.

If you need to call a T-SQL function, then a couple of ideas-

Can you do that in a Lookup, customize the SQL? I haven't tried this in SSIS, but DTS allowed any old SQL to be used.

Or

Use a Script Component. This could open a SQL connection (ADO.Net connection manager), call your function, update your table, do what you want to get your new PK value. Then assign in. The Script component process row method is called once per row, so you should have no problem. The "how to iterate" issue is a non issue when your are doing row by row processing.|||I will work with the Script Component. I'm more DB savvy then Scripting but figured I would have to learn it sooner or later. Thanks for the help. Good to know the best option.

--David

For Each Row Trigger

Hi,

Does anybody know how could I define a for each row trigger in sql Server 2005?

What I need to do is before inserting in the table look through the rows to check wheter that value overlaps with the others.

"Overlap" means that this value cannot be betweent the values fo two different columns, that is why I need to go through all the table.

In case that was not possible, I propose the following, load the table in a dataset and check the constraint with a for loop despite of the performance could be decreased.

Any suggestion?.

Thanks in advance.

Cheers.

You could use an INSTEAD OF INSERT trigger, and then have the trigger only insert the values that match your criteria. I'm not sure how your table is set up, but something similar to the following:

CREATE TRIGGER CheckInsertON Test_Table2INSTEADOF INSERTASBEGININSERT INTO Test_Table2("Time", InsertNumber)SELECT "Time", InsertNumberFROM INSERTEDWHERENOT EXISTS(SELECT InsertNumberFROM Test_Table2WHERE "Time"BETWEEN i."Time" AND i."Time2")END

 
The above query will only insert the records that don't fall between another record.
If you just want the insert to fail, and not insert any records, you could just check if there are any matches, and then return an error if there are.

|||

Hi, thanks for the reply,

That makes sense, I am going to try it and I will get you back whether it works or not.

Cheers.

|||

Hi, well I have defined the trigger and I think that it fires but I get an error during the insertion:

Cannot insert explicit value for identity column in table 'Items' when IDENTITY_INSERT is set to OFF. The statement has been terminated.

I dont know how to set this property...,

my trigger is the following in case it could help you.

ALTER TRIGGERtrgCheckItemInsert

ONdbo.Items

INSTEAD OF INSERT

AS

BEGIN

INSERT INTOItems(itemID,itemName,rackID,itemNumberOfUnits,startU,endU,itemDesc)

SELECTi.itemID,i.itemName,i.rackID,i.itemNumberOfUnits,i.startU,i.endU,i.itemDesc

FROMINSERTED i

WHERE NOT EXISTS(

SELECTstartU

FROMItemsINNER JOINRacksONItems.rackID = Racks.rackID

WHEREItems.rackID = i.rackIDANDItems.itemID != i.itemID

ANDi.endU > Racks.numberOfUnitsANDi.startUBETWEENItems.startUANDItems.endU

)

END

any suggestion?

Thanks

|||

Hi, this problem is solved, I just had to remove the itemID from the insert statement.

Thanks.

|||

Well, finally the trigger doesnt fire, I have put a RAISERROR ('Hello',1,1) and it is not working, I catch the exceptions while the insertion between a try catch block and normally when I get an error from the database like duplicate id for the primary key or whatever I can catch it, but the raise statement I cant.

How could I ensure that the trigger fires? because moreover I am still able to insert worong values.

Thanks.

|||

I tried putting RaiseError in the first line of the trigger, and then ran it from the SQL Management Studio, and the rest of the trigger still ran, so I'm not sure if it will throw an exception in .NET. You could place a ROLLBACK TRANSACTION as the last line in the trigger, or else just comment out the INSERT command, and then see if anything is inserted into the table. Either of those should stop the insert, so you could at least tell if the trigger is running.

My guess is that the trigger is firing, but something in the WHERE clause is never evaluating to True, so that the NOT EXISTS always returns true. To test that, you could take the SELECT statement that you have in the EXISTS function, and see what it returns when you replace all the i.Columns with values that should find duplicates. Then see if any records are found. I don't know what your table schema looks like, so I'm not sure, but a couple of possibilities from the select statement below.

SELECTstartU

FROMItemsINNER JOINRacksONItems.rackID = Racks.rackID

WHEREItems.rackID = i.rackIDANDItems.itemID != i.itemID

ANDi.endU > Racks.numberOfUnitsANDi.startUBETWEENItems.startUANDItems.endU

I don't think you need the "Items.itemID != i.itemID" because i.itemID is an AutoIncrement field, so that will never evaluate to false (I don't even know if i.itemID has a value at this point). That line shouldn't change the outcome, it just isn't needed.

the line "i.endU > Racks.numberOfUnits" sounds like it is checking for a condition you want met for the insert (This is in a NOT EXISTS, so you should only have conditions you do NOT want met). If that evaluates to FALSE even though i.startU is between startU and endU, you still may not get any results. I'm guessing you either need an OR instead of an AND, or else just check for the start and end dates and get rid of that check.

I wonder if you could rewrite the select command as

SELECTstartU

FROMItems

WHEREItems.rackID = i.rackID

ANDi.startUBETWEENItems.startUANDItems.endU

|||

Thanks for the reply,

well finally I got the solution, the trigger was wrong at some conditions and finally I fixed it.

The problem now arises when I define the INSTEAD OF UPDATE TRIGGER, because when I raise the error I cant catch it at any event, I try to catch it within the event Grid_Row_Updating and it doesnt work, and the same in the Row_Updated event, so I get an unhandled exception.

Do you have an idea about that?.

Cheers.

|||

It depends on how you are using the DataBinding. Are you calling DataBind() explicitly, or are you binding the GridView to the DataSource in the aspx page? The DataBind() method is where you need the Try-Catch. If you can bind the data to the GridView in the Page_OnLoad, that should let you catch the exception. I don't know if you can catch the exception if you are setting the DataSource in the aspx page.

FOR EACH LOOP in T-SQL

Hello,
I am inserting a list of database names from sysdatabases into a temp
table, below is the T-SQL.
CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
INSERT INTO ##SpringClean
SELECT DISTINCT dbo.sysdatabases.name
FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
AND dbo.sysdatabases.name NOT IN
('master','model','msdb','Northwind','pubs','tempdb')
I would like to code a loop in T-SQL that will cycle through each database
name in the above temp table and execute the following select
USE (db name from temp table)
SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
FROM sysfiles
GO
I am trying to get an accurate query of the size of my databases. Any help
would be greatly appreciated.
JoeYou can use a cursor for that, and loop the cursor. See DECLARE (CURSOR) in
Books Online. You would not need a temptable for this.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joe G" <invalid@.invalid.com> wrote in message
news:3fb2525c@.usenet01.boi.hp.com...
> Hello,
> I am inserting a list of database names from sysdatabases into a temp
> table, below is the T-SQL.
> CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> INSERT INTO ##SpringClean
> SELECT DISTINCT dbo.sysdatabases.name
> FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> AND dbo.sysdatabases.name NOT IN
> ('master','model','msdb','Northwind','pubs','tempdb')
> I would like to code a loop in T-SQL that will cycle through each database
> name in the above temp table and execute the following select
> USE (db name from temp table)
> SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> FROM sysfiles
> GO
> I am trying to get an accurate query of the size of my databases. Any
help
> would be greatly appreciated.
> Joe
>
>|||declare @.sql varchar(4000)
declare @.db varchar(64)
set @.db=''
SELECT @.db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
and name > @.db
while @.db is not null
begin
set @.sql='use '+@.db+'
SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
'+@.db+'"
FROM sysfiles'
exec (@.sql)
SELECT @.db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb')
and name > @.db
end
Hope this helps,
Gert-Jan
Joe G wrote:
> Hello,
> I am inserting a list of database names from sysdatabases into a temp
> table, below is the T-SQL.
> CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> INSERT INTO ##SpringClean
> SELECT DISTINCT dbo.sysdatabases.name
> FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> AND dbo.sysdatabases.name NOT IN
> ('master','model','msdb','Northwind','pubs','tempdb')
> I would like to code a loop in T-SQL that will cycle through each database
> name in the above temp table and execute the following select
> USE (db name from temp table)
> SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> FROM sysfiles
> GO
> I am trying to get an accurate query of the size of my databases. Any help
> would be greatly appreciated.
> Joe|||Wow,
Thanks very much, this was extremely helpful. I am now going to try and
figure out what you did in your code. I appreciate the effort.
Joe
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FB27693.F0B27DA1@.toomuchspamalready.nl...
> declare @.sql varchar(4000)
> declare @.db varchar(64)
> set @.db=''
> SELECT @.db=min(name)
> FROM sysdatabases
> WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
> and name > @.db
> while @.db is not null
> begin
> set @.sql='use '+@.db+'
> SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
> '+@.db+'"
> FROM sysfiles'
> exec (@.sql)
> SELECT @.db=min(name)
> FROM sysdatabases
> WHERE name NOT IN ('master','msdb','tempdb')
> and name > @.db
> end
> Hope this helps,
> Gert-Jan
>
> Joe G wrote:
> >
> > Hello,
> >
> > I am inserting a list of database names from sysdatabases into a temp
> > table, below is the T-SQL.
> >
> > CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> > INSERT INTO ##SpringClean
> > SELECT DISTINCT dbo.sysdatabases.name
> > FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > AND dbo.sysdatabases.name NOT IN
> > ('master','model','msdb','Northwind','pubs','tempdb')
> >
> > I would like to code a loop in T-SQL that will cycle through each
database
> > name in the above temp table and execute the following select
> >
> > USE (db name from temp table)
> > SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > FROM sysfiles
> > GO
> >
> > I am trying to get an accurate query of the size of my databases. Any
help
> > would be greatly appreciated.
> >
> > Joe|||PSS.
It worked, I just want to figure out what you did now.
Joe
"Joe G" <invalid@.invalid.com> wrote in message
news:3fb283fc@.usenet01.boi.hp.com...
> Wow,
> Thanks very much, this was extremely helpful. I am now going to try and
> figure out what you did in your code. I appreciate the effort.
> Joe
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FB27693.F0B27DA1@.toomuchspamalready.nl...
> > declare @.sql varchar(4000)
> > declare @.db varchar(64)
> > set @.db=''
> >
> > SELECT @.db=min(name)
> > FROM sysdatabases
> > WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
> > and name > @.db
> >
> > while @.db is not null
> > begin
> >
> > set @.sql='use '+@.db+'
> > SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
> > '+@.db+'"
> > FROM sysfiles'
> > exec (@.sql)
> >
> > SELECT @.db=min(name)
> > FROM sysdatabases
> > WHERE name NOT IN ('master','msdb','tempdb')
> > and name > @.db
> > end
> >
> > Hope this helps,
> > Gert-Jan
> >
> >
> > Joe G wrote:
> > >
> > > Hello,
> > >
> > > I am inserting a list of database names from sysdatabases into a
temp
> > > table, below is the T-SQL.
> > >
> > > CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> > > INSERT INTO ##SpringClean
> > > SELECT DISTINCT dbo.sysdatabases.name
> > > FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > > WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > > AND dbo.sysdatabases.name NOT IN
> > > ('master','model','msdb','Northwind','pubs','tempdb')
> > >
> > > I would like to code a loop in T-SQL that will cycle through each
> database
> > > name in the above temp table and execute the following select
> > >
> > > USE (db name from temp table)
> > > SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > > FROM sysfiles
> > > GO
> > >
> > > I am trying to get an accurate query of the size of my databases. Any
> help
> > > would be greatly appreciated.
> > >
> > > Joe
>|||Joe, another method is this single command... Bruce
exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>--Original Message--
>Hello,
> I am inserting a list of database names from
sysdatabases into a temp
>table, below is the T-SQL.
>CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
KEY)
>INSERT INTO ##SpringClean
>SELECT DISTINCT dbo.sysdatabases.name
>FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
>WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>AND dbo.sysdatabases.name NOT IN
>('master','model','msdb','Northwind','pubs','tempdb')
>I would like to code a loop in T-SQL that will cycle
through each database
>name in the above temp table and execute the following
select
>USE (db name from temp table)
>SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
>FROM sysfiles
>GO
>I am trying to get an accurate query of the size of my
databases. Any help
>would be greatly appreciated.
>Joe
>
>.
>|||I can't seem to run this against a remote server, only my personal copy of
SQL Server located on my laptop. Is this a requirement for this stored
proc?
By the way, this was an amazing command none the less.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> Joe, another method is this single command... Bruce
> exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>
>
> >--Original Message--
> >Hello,
> >
> > I am inserting a list of database names from
> sysdatabases into a temp
> >table, below is the T-SQL.
> >
> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> KEY)
> >INSERT INTO ##SpringClean
> >SELECT DISTINCT dbo.sysdatabases.name
> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >AND dbo.sysdatabases.name NOT IN
> >('master','model','msdb','Northwind','pubs','tempdb')
> >
> >I would like to code a loop in T-SQL that will cycle
> through each database
> >name in the above temp table and execute the following
> select
> >
> >USE (db name from temp table)
> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >FROM sysfiles
> >GO
> >
> >I am trying to get an accurate query of the size of my
> databases. Any help
> >would be greatly appreciated.
> >
> >Joe
> >
> >
> >
> >.
> >|||Actually, it has nothing to do with me executing it locally, when I execute
it on other databases I get the following error
"Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSforeachDB'."
Does anyone know why?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> Joe, another method is this single command... Bruce
> exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>
>
> >--Original Message--
> >Hello,
> >
> > I am inserting a list of database names from
> sysdatabases into a temp
> >table, below is the T-SQL.
> >
> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> KEY)
> >INSERT INTO ##SpringClean
> >SELECT DISTINCT dbo.sysdatabases.name
> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >AND dbo.sysdatabases.name NOT IN
> >('master','model','msdb','Northwind','pubs','tempdb')
> >
> >I would like to code a loop in T-SQL that will cycle
> through each database
> >name in the above temp table and execute the following
> select
> >
> >USE (db name from temp table)
> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >FROM sysfiles
> >GO
> >
> >I am trying to get an accurate query of the size of my
> databases. Any help
> >would be greatly appreciated.
> >
> >Joe
> >
> >
> >
> >.
> >|||I'm running it on SQL 2000. I THINK it's available on
SQL 7 also? are you on SQL 2000? Can you see that
proc in the master database? If it's there and you have
permission to run it, not sure why you get that message.
I use the DB and TABLE ForEach procs all the time for
short commands like that... Bruce
>--Original Message--
>Actually, it has nothing to do with me executing it
locally, when I execute
>it on other databases I get the following error
> "Server: Msg 2812, Level 16, State 62, Line 1
>Could not find stored procedure 'sp_MSforeachDB'."
>Does anyone know why?
>
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
>> Joe, another method is this single command... Bruce
>> exec sp_MSforeachDB @.command1="SELECT SUM(size *
8/1024)
>> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>>
>>
>> >--Original Message--
>> >Hello,
>> >
>> > I am inserting a list of database names from
>> sysdatabases into a temp
>> >table, below is the T-SQL.
>> >
>> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
>> KEY)
>> >INSERT INTO ##SpringClean
>> >SELECT DISTINCT dbo.sysdatabases.name
>> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
>> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>> >AND dbo.sysdatabases.name NOT IN
>> >('master','model','msdb','Northwind','pubs','tempdb')
>> >
>> >I would like to code a loop in T-SQL that will cycle
>> through each database
>> >name in the above temp table and execute the following
>> select
>> >
>> >USE (db name from temp table)
>> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
>> >FROM sysfiles
>> >GO
>> >
>> >I am trying to get an accurate query of the size of my
>> databases. Any help
>> >would be greatly appreciated.
>> >
>> >Joe
>> >
>> >
>> >
>> >.
>> >
>
>.
>|||Perhaps the SQL Server is case sensitive? The name of the procedure is
sp_MSforeachdb.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:069801c3a98f$5f32fb60$a401280a@.phx.gbl...
> I'm running it on SQL 2000. I THINK it's available on
> SQL 7 also? are you on SQL 2000? Can you see that
> proc in the master database? If it's there and you have
> permission to run it, not sure why you get that message.
> I use the DB and TABLE ForEach procs all the time for
> short commands like that... Bruce
> >--Original Message--
> >Actually, it has nothing to do with me executing it
> locally, when I execute
> >it on other databases I get the following error
> >
> > "Server: Msg 2812, Level 16, State 62, Line 1
> >Could not find stored procedure 'sp_MSforeachDB'."
> >
> >Does anyone know why?
> >
> >
> >
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> >> Joe, another method is this single command... Bruce
> >>
> >> exec sp_MSforeachDB @.command1="SELECT SUM(size *
> 8/1024)
> >> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> > I am inserting a list of database names from
> >> sysdatabases into a temp
> >> >table, below is the T-SQL.
> >> >
> >> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> >> KEY)
> >> >INSERT INTO ##SpringClean
> >> >SELECT DISTINCT dbo.sysdatabases.name
> >> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >> >AND dbo.sysdatabases.name NOT IN
> >> >('master','model','msdb','Northwind','pubs','tempdb')
> >> >
> >> >I would like to code a loop in T-SQL that will cycle
> >> through each database
> >> >name in the above temp table and execute the following
> >> select
> >> >
> >> >USE (db name from temp table)
> >> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >> >FROM sysfiles
> >> >GO
> >> >
> >> >I am trying to get an accurate query of the size of my
> >> databases. Any help
> >> >would be greatly appreciated.
> >> >
> >> >Joe
> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Another save by the good of the community. I was so deep into the issue at
hand yesterday I didn't even think to check the case sensitivity. That was
the issue. I remember inspecting all of the databases it was running
against and finding the stored proc but I couldn't figure out why it
wouldn't run. Case sensitivity.
Thanks a million.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OgiVv7bqDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Perhaps the SQL Server is case sensitive? The name of the procedure is
> sp_MSforeachdb.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> news:069801c3a98f$5f32fb60$a401280a@.phx.gbl...
> > I'm running it on SQL 2000. I THINK it's available on
> > SQL 7 also? are you on SQL 2000? Can you see that
> > proc in the master database? If it's there and you have
> > permission to run it, not sure why you get that message.
> > I use the DB and TABLE ForEach procs all the time for
> > short commands like that... Bruce
> >
> > >--Original Message--
> > >Actually, it has nothing to do with me executing it
> > locally, when I execute
> > >it on other databases I get the following error
> > >
> > > "Server: Msg 2812, Level 16, State 62, Line 1
> > >Could not find stored procedure 'sp_MSforeachDB'."
> > >
> > >Does anyone know why?
> > >
> > >
> > >
> > >
> > >
> > >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> > >news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> > >> Joe, another method is this single command... Bruce
> > >>
> > >> exec sp_MSforeachDB @.command1="SELECT SUM(size *
> > 8/1024)
> > >> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
> > >>
> > >>
> > >>
> > >>
> > >> >--Original Message--
> > >> >Hello,
> > >> >
> > >> > I am inserting a list of database names from
> > >> sysdatabases into a temp
> > >> >table, below is the T-SQL.
> > >> >
> > >> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> > >> KEY)
> > >> >INSERT INTO ##SpringClean
> > >> >SELECT DISTINCT dbo.sysdatabases.name
> > >> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > >> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > >> >AND dbo.sysdatabases.name NOT IN
> > >> >('master','model','msdb','Northwind','pubs','tempdb')
> > >> >
> > >> >I would like to code a loop in T-SQL that will cycle
> > >> through each database
> > >> >name in the above temp table and execute the following
> > >> select
> > >> >
> > >> >USE (db name from temp table)
> > >> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > >> >FROM sysfiles
> > >> >GO
> > >> >
> > >> >I am trying to get an accurate query of the size of my
> > >> databases. Any help
> > >> >would be greatly appreciated.
> > >> >
> > >> >Joe
> > >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>