check if cursor exists

I declare a cusror named crInv inside a loop.
Since I open this cursor a lot of times I want to check if is already opened.
I try to use the Cursor_status function but it always returns -3.
The syntax is:

DECLARE crInv SCROL CURSOR FOR
SELECT Val1, Val2 FROM TABLE1 WHERE Val3=450

If Cursor_Status('local','crInv')>0 BEGIN
CLOSE crInv
DEALLOCATE crInv
END

This code is inside a loop.
If I PRINT Cursor_Status('local','crInv') before and after the DECLARE statement it always returns -3.
What is wrong??

Best regards,
ManolisDECLARE crInv LOCAL SCROLL CURSOR FOR ...

The typical default for a cursor is GLOBAL|||Sounds like an extremely expensive process...

Ever think about a set based process?

Can you post the "Loop" code?sql

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURS
have a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS
|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.