April 25, 2014 at 9:10 pm
Hi Experts,
I am new to T-SQL development and here's what I am trying to do.
declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'
while (@cmd <=13)
begin
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1
end
but it is giving me an error
'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.
'
Note - #dbcheck has only one column id int
--Plz help
April 26, 2014 at 1:47 am
is this what you are trying to achieve?
SELECT dbid
INTO #dbcheck
FROM sys.sysdatabases
WHERE dbid > 4
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 26, 2014 at 1:56 am
J Livingston's solution is the proper way of doing this but for clarification, here is a corrected version of what you where trying to do using dynamic sql.
😎
declare @cmd nvarchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4) and dbid <=13'
create table #dbcheck (dbid int not null);
insert into #dbcheck(dbid)
exec sp_executesql @cmd;
select * from #dbcheck;
drop table #dbcheck;
April 26, 2014 at 8:22 am
sysdatabases is deprecated and should not be used any longer. It's included only for backward compatibility with SQL 2000
Use sys.databases instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply