How can I skip errors and continue running the SQL-query?

Question

I am writing a piece of code with the intention of searching through all databases in the server for a certain table name, however I ran into some trouble since I do not have permission to read/access all databases in the server.

I am wondering if there is a way for the query to advance if one statement is not available due to security permissions (i.e. if I have ten databases in a particular server with no access to the fourth, I would want it to run 1-2-3 and then 5-6-7-8-9-10 with returned results).

I have tried using TRY-CATCH, but I cannot seem to get the code to bypass the initial problem which is stopping when the security permissions are not available.

declare @tabell varchar(254) = 'JE' -- table name which is supposed to be 
--found.

-- STEP 1: lists all available databases in the server with a row number.
drop table if exists #steg1 select name, row_number() over (order by 
name) as rownumber into #steg1 from sys.databases

-- STEP 2: generates code for all databases in order to identify those 
--with the table name @tabell.
drop table if exists #steg2 select 1 Ordn,'use '+name+' drop table if 
exists #hitta select * into #hitta from sys.tables where name = 
'''+ltrim(@tabell)+'''' as script into #steg2 from #steg1 a 
where rownumber =1
union
select 2 Ordn, 'use '+name+' insert into #hitta select * from sys.tables 
where name = '''+ltrim(@tabell)+'''' from #steg1 a
where rownumber >1
union
select 3 Ordn,'select * from #hitta' as x

-- STEP 3: concatenate the generated code into a single string.
declare @string varchar(max)
select @string = concat(@string + ' ', '')+ script from #steg2
drop table if exists #steg3 select @string as string into #steg3

-- STEP 4: exec the code concatenated in the previous step.
declare     @cmd    varchar(max)
begin 
set @cmd = (select string from #steg3)
exec (@cmd)
end

Getting the error message: Msg 916, level 14, state 1, stating that the user cannot access the database under the current security context.

Which database are you using? Oracle, MySQL, Microsoft SQL Server, or other? — Morten
– Morten, Commented Jul 18, 2019 at 8:56

Kinzerb · Accepted Answer · 2019-07-18 09:39:27Z

I managed to solve my issue using has_dbaccess(database), below you can see how I incorporated it into the code.

declare @tabell varchar(254) = 'JE' -- table name which is 
supposed to be found.

-- STEP 1: lists all available databases in the server with a row number.
drop table if exists #steg1 select name, row_number() over (order by name) as rownumber 
into #steg1 from (SELECT name, has_dbaccess(name) access FROM sys.databases) a where 
access = 1

-- STEP 2: generates code for all databases in order to identify those with the table 
--name @tabell.
drop table if exists #steg2 select 1 Ordn,'use '+name+' drop table if exists #hitta 
select name as [Table], cast('''+name+'''as varchar(max)) as [Databas] into #hitta from 
sys.tables where name = '''+ltrim(@tabell)+'''' as script into #steg2 from #steg1 a 
where rownumber =1
union
select 2 Ordn, 'use '+name+' insert into #hitta select name as [Table], 
cast('''+name+'''as varchar(max)) as [Databas] from sys.tables where name = 
'''+ltrim(@tabell)+'''' from #steg1 a
where rownumber >1
union
select 3 Ordn,'select * from #hitta' as x

-- STEP 3: concatenate the generated code into a single string.
declare @string varchar(max)
select @string = concat(@string + ' ', '')+ script from #steg2
drop table if exists #steg3 select @string as string into #steg3

-- STEP 4: exec the code concatenated in the previous step.
declare     @cmd    varchar(max)
begin 
set @cmd = (select string from #steg3)
exec (@cmd)
end

Collectives™ on Stack Overflow

How can I skip errors and continue running the SQL-query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related