Views:

I created a new SQL view and wanted to use it in Smartlist Builder for all companies. This client has 30+ companies. I didn't want to go through each one and add the view in Smartlist Builder Security so I created this cursor that will generate insert statements that gives all companies access to the defined view.

SET NOCOUNT ON

DECLARE @INTERID CHAR(5)
DECLARE @COMPNAME CHAR(65)

DECLARE Company_Cursor CURSOR FOR SELECT CMPNYNAM, INTERID FROM DYNAMICS..SY01500 where INTERID not in (Select SQL_Database_Name from DYNAMICS..SLB80400 where TBLPHYSNM='V_MDA_GL_Vend')

OPEN Company_Cursor
FETCH NEXT FROM Company_Cursor INTO @COMPNAME, @INTERID

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT 

'insert into DYNAMICS..SLB80400 VALUES ('''','''+@INTERID+''',''V_MDA_GL_Vend'',1) 

'

FETCH NEXT FROM Company_Cursor INTO @COMPNAME, @INTERID

END

CLOSE Company_Cursor
deallocate Company_Cursor

 

Keywords: security, view, smartlist builder, SQL