January 13, 2006 at 9:17 am
Hello
I am writing a script that will verify all files that I have in source safe against my database.
It should be real simple...cursor with all files listed, then do an if exists against the database. When doing this, the dynamic if exists dosent work or in aother test it cant find my database in sysdatabases????
Anyone have any ideas?
Thanks in Advance
Eric Peterson
********************* source Code *********************
if exists ( select name from sysobjects where name = 'cspValidateDBScripts' )
drop procedure cspValidateDBScripts
go
Create procedure cspValidateDBScripts
@DBin varchar(30)
as
begin
declare @procNamevarchar(50)
declare @fileNamevarchar(50)
declare @SQLOutvarchar(1000)
declare @DropSQL varchar(1000)
declare @CheckSQL varchar(1000)
declare @counter int
--set @SQLOut = 'use ' + @DBin
--execute (@SQLOut)
declare ProcedureListCursor cursor
for
select 'admin_CSPListByClient'
--changed for testing....normally it would list all files'
open ProcedureListCursor
Fetch ProcedureListCursor
into @fileName
while @@fetch_status = 0
begin
set @sqlOut = 'echo ' + @fileName
print @SQLOut
set @procName = +replace(@fileName, '.sql', '')
--set @checkSQL = 'if not exists (select name from '''+ @dbin + '.dbo.sysobjects'' where name = ''' + @procName + ''') print ' + @procname +' does not exist'
set @checkSQL = 'select name from '+ @dbin + '.dbo.sysobjects where name = ''' + @procName + ''''
print @checkSQL
execute @checkSQL
-- begin
--set @DropSQL = 'Drop Procedure ' + @dbin + '.dbo.' + @procName
--print @DropSQL
-- end
-- else
--print 'Procedure ' + @procName + ' does not exist'
--set @sqlOut = 'isql -S' + @ServerIn + ' -d' + rtrim(@dbin) + ' -U' + @uid+ ' -P'+@pwd +' -i' + @fileName
--print @SQLOut
--set @sqlOut = ' '
--print @SQLOut
Fetch ProcedureListCursor
into @procName
end
close ProcedureListCursor
deallocate ProcedureListCursor
end -- cspValidateDBScripts
-- -- cspValidateDBScripts Hino
GO
--execute ('select * from Hino.dbo.sysobjects where name = ''admin_CSPListByClient''')
January 13, 2006 at 9:28 am
here is an easier way to test....
declare @checkSQL varchar(500)
set @checkSQL = 'select name from master..sysdatabases'
print @checkSQL
execute @checkSQL
***************** returns the following and error ********************
select name from master..sysdatabases
Server: Msg 911, Level 16, State 1, Line 54
Could not locate entry in sysdatabases for database 'select name from master'. No entry found with that name. Make sure that the name is entered correctly.
now we all know that the master database exists.....so what is the issue here????
January 13, 2006 at 9:42 am
I like it when I solve my own problems.....
the execute has to have () around the sql and it works...
execute(@checkSQL)
***************
Wasnt that straight forward
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply