opc.three (6/24/2013)
dwilliscp (6/24/2013)
is there a limit on what gets printed?Yes. In SSMS 2012 go to Tools > Options > Query Results > SQL Server > Results to ... and increase the number.
Instead of PRINT though, put this at the end of your batch and you'll never have to worry about that truncation problem again:
SELECT @sql AS [processing-instruction(query)]FOR XML PATH(''),
TYPE;
I am using SQL 2008R2... Not sure writing it to a xml file is the better option.. how about a cursor? I made some sort of mistake though ...
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @l_name varchar(MAX);
SET nocount on;
DECLARE i_Cursor insensitive cursor
FOR
SELECT [name]
FROM sys.views
where [name] like 'zvw%';
open i_Cusor
FETCH NEXT FROM i_Cusor into @l_name
while @@FETCH_STATUS = 0
BEGIN
SELECT @sql = @sql + 'GRANT SELECT ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name) + ' TO [ro_ZemeterNet_Access];
'
FROM sys.views
WHERE name LIKE @l_name;
PRINT @sql;
--EXEC(@sql);
FETCH NEXT FROM i_Cursor INTO @l_name
END
GO
CLOSE i_Cursor
deallocate i_Cursor
set NOCOUNT off;
error returned
-------------
Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'i_Cusor' does not exist.
Msg 16916, Level 16, State 1, Line 14
A cursor with the name 'i_Cusor' does not exist.
Msg 16917, Level 16, State 1, Line 1
Cursor is not open.