Re: calling a stored procedure in a while loop

On Feb 22, 7:21 pm, "Plamen Ratchev" <Pla...@xxxxxxxxxxxxx> wrote:
I just tested your code on one of my test databases and it retuned 12 rows
(but I have have over 200 tables there, and permissions to all of them).

A few things to check:

* How many tables do you have in the TestData6061 database? You need to have
at least 29 to see 12 rows as result.
* Do you have permissions to the tables in the TestData6061 database? The
information schema views let you see only objects for which you have
permissions.

Also, it just seems a bit odd that your SP is in the Releases database but
references the information schema views in the TestData6061 database.

Try to run the query below. Essentially it is equivalent to your stored
procedure and the loop combined together in one query. You can comment out
the WHERE clause to see all tables. If you are on SQL Server 2005 you can
replace the subquery calculating the seq column using ROW_NUMBER, like
ROW_NUMBER() OVER(ORDER BY table_name).

SELECT table_name, seq
FROM (
SELECT table_name,
(SELECT COUNT(*)
FROM TestData6061.INFORMATION_SCHEMA.Tables AS B
WHERE A.table_name >= B.table_name)
FROM TestData6061.INFORMATION_SCHEMA.Tables AS A)
AS T(table_name, seq)
WHERE seq IN (1, 4, 7, 8, 14, 17, 18, 22, 25, 27, 28, 29)

HTH,

Plamen Ratchevhttp://www.SQLStudio.com


So it seems like I don't really have to look into the Information
shcema for TestData6061, these table names can be accessed as

SELECT DISTINCT ObjectClass
FROM SchemaFields
ORDER BY ObjectClass
and that will give me something like this ->
accounting
fundstructures
investment
party
price
reference
sync
systemsettings
transaction
twr

Now coming back to the looping, I basically want to have a sql query
which will loop through the above list (accounting, investment, etc
etc) giving me the single value as it goes through the loop. Thanks in
advance
.

Relevant Pages

  • Re: calling a stored procedure in a while loop
    ... I just tested your code on one of my test databases and it retuned 12 rows (but I have have over 200 tables there, and permissions to all of them). ... Do you have permissions to the tables in the TestData6061 database? ... Essentially it is equivalent to your stored procedure and the loop combined together in one query. ... If you are on SQL Server 2005 you can replace the subquery calculating the seq column using ROW_NUMBER, ...
    (comp.databases.ms-sqlserver)
  • Re: Permissions for all users for all databases
    ... You can write your own cursor to loop over each database, or possibly use sp_MSForeachdatabase, or some other mechanism. ... Tibor Karaszi, SQL Server MVP ... permissions on each of the databases. ...
    (microsoft.public.sqlserver.server)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)
  • Re: User access on a company intranet
    ... Yes they need full permissions on the folder where the backend is. ... You wouldn't need to do this in your copy of the database. ... However you can toggle the shiftkey bypass from another mdb file. ... When you want to implement security, you create a new mdw file, ...
    (microsoft.public.access.security)
  • Re: Active directory corruption
    ... During an installation of PHP I accidentally changed permissions for the ... Active Directory database is unavailable because it is damaged, ... Open a command prompt and run NTDSUTIL to verify the paths for the ...
    (microsoft.public.windows.server.sbs)