February 12, 2014 at 4:26 am
I need to check the library used by several stored procs that extract data from a remote IBM iSeries server.
I have built the below code:
DECLARE @tProcs TABLE
(
procID int IDENTITY,
procObjectID nvarchar(100),
procName nvarchar(100)
);
insert into @tProcs
SELECT object_id, name
FROM sys.objects
WHERE type in (N'P', N'PC') and name like '%_Extract'
declare @countProcs int, @I int=0
select @countProcs=COUNT(*) from @tProcs
while @I<@countProcs
Begin
declare @source_code nvarchar(max)
declare @objectID nvarchar(50)
declare @proc_Name nvarchar(200)
select @objectID=procObjectID from @tProcs where procID=@I
select @proc_Name=procName from @tProcs where procID=@I
select @source_code = definition
from sys.sql_modules
where object_id = @objectID
SELECT PATINDEX('BOCTEST.%', @proc_Name) as Pos, @proc_Name
-- or SELECT charindex(@source_code, '%BOCTEST%')
set @I=@I+1
End
Inside each of the target stored procs there is a line like this:
DECLARE YPnnnP_cursor CURSOR FOR SELECT * FROM BOCTEST.xxx.LIVEBOC_A.YPnnnP
I need to know for each of the stored procs the part 'LIVEBOC_A' (which can either be 'LIVEBOC_A' or LIVEBOC_B) so that my new process can target the correct library when checking our records in SQL Server against tghe current records in the iSeries.
I tried to use PATINDEX and CHARINDEX to get the location of the start opf that string in the definition from `sysmodules` but all I get back is either zero or an error that string or binary data would be truncated.
what am I doing wrong please
February 12, 2014 at 5:36 am
don't you think that you are missing wild-card at the begining of the pattern:
PATINDEX('%BOCTEST.%', @source_code) as Pos, @proc_Name
February 12, 2014 at 5:53 am
I don't see any reason to use a cursor or lopp structure here;
you can do it all in a single pass.
since the proc definitions can be large, you'd want to use EXECUTE AT instead of a four part call;
I'll give both examples here.
this example would be slow, but would work. it would be slow because, for a linked server, all the data is copied locally to tempdb, and then the WHERE statements are performed,a dn then the results are returned. you might grab a million rows of data and filter it down to a handful of results:
SELECT
objz.name,
PATINDEX('BOCTEST.%', objz.name) AS NamePosition,
charindex('%BOCTEST%', defz.definition) AS CodePosition
FROM BocTest.LIVEBOC_A.sys.procedures objz
LEFT OUTER JOIN BocTest.LIVEBOC_Asys.sql_modules defz
ON objz.object_id = defz.object_id
WHERE PATINDEX('BOCTEST.%', objz.name) > 0
OR charindex('%BOCTEST%', defz.definition) > 0
EXECUTE ...AT lets you put the work on the remote server, and just return the matching results; you eliminate all the unnecessary copying to tempdb
EXECUTE('SELECT
objz.name,
PATINDEX(''BOCTEST.%'', objz.name) AS NamePosition,
charindex(''%BOCTEST%'', defz.definition) AS CodePosition
FROM LIVEBOC_A.sys.procedures objz
LEFT OUTER JOIN LIVEBOC_A.sys.sql_modules defz
ON objz.object_id = defz.object_id
WHERE PATINDEX(''BOCTEST.%'', objz.name) > 0
OR charindex(''%BOCTEST%'', defz.definition) > 0 '
) AT BOCTEST
Lowell
February 12, 2014 at 6:04 am
hi,
Thanks for your reply.
I should mention that the remote linked server referenced by BOCTEST is an iSeries (so not a SQL Server). The stored procs I am checking are stored in a local SQL Server db called ALPSProduction
So I need to determine which library is used in each stored procedure - basically each stored proc (%_extract) grabs the latest records from the iSeries and brings them into local tables ...
But it can be either LIVEBOC_A or LIVEBOC_B, and that's what I need to determine
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply