CraigBroadman
asked on
Create Stored Procedure In Visual Basic
I have code to create a stored procedure, see below. It is in a text box and then when i execute the SQL connection i get an error to say "Incorrect Syntax near the keyword 'ObjectRS'"
If i take out the section of the statement that says
CLOSE ObjectRS
DEALLOCATE ObjectRS
then it will execute the SQL statement and create the stored procedure fine.
Any ideas?
CREATE Procedure GrantExecPermissions(@User OrGroup VarChar(255)) AS
SET NOCOUNT ON
-- Declare and set cursor
DECLARE @SQL VarChar(500)
DECLARE @ObjectName VarChar(255)
DECLARE ObjectRS CURSOR FOR (SELECT [Name] FROM dbo.SysObjects WHERE (xType = 'P' OR xType = 'FN') AND Category = 0)
-- Open and fetch first record from cursor
OPEN ObjectRS
FETCH ObjectRS INTO @ObjectName
-- Loop cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Grant EXECUTE
SET @SQL = 'GRANT EXECUTE ON ' + @ObjectName + ' TO ' + @UserOrGroup
EXEC(@SQL)
IF @@ERROR = 0
-- Print confirmation
PRINT 'Granted EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
ELSE
-- Print failure
PRINT 'Failed to grant EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
-- Get next object
FETCH ObjectRS INTO @ObjectName
END
CLOSE ObjectRS
DEALLOCATE ObjectRS
If i take out the section of the statement that says
CLOSE ObjectRS
DEALLOCATE ObjectRS
then it will execute the SQL statement and create the stored procedure fine.
Any ideas?
CREATE Procedure GrantExecPermissions(@User
SET NOCOUNT ON
-- Declare and set cursor
DECLARE @SQL VarChar(500)
DECLARE @ObjectName VarChar(255)
DECLARE ObjectRS CURSOR FOR (SELECT [Name] FROM dbo.SysObjects WHERE (xType = 'P' OR xType = 'FN') AND Category = 0)
-- Open and fetch first record from cursor
OPEN ObjectRS
FETCH ObjectRS INTO @ObjectName
-- Loop cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Grant EXECUTE
SET @SQL = 'GRANT EXECUTE ON ' + @ObjectName + ' TO ' + @UserOrGroup
EXEC(@SQL)
IF @@ERROR = 0
-- Print confirmation
PRINT 'Granted EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
ELSE
-- Print failure
PRINT 'Failed to grant EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
-- Get next object
FETCH ObjectRS INTO @ObjectName
END
CLOSE ObjectRS
DEALLOCATE ObjectRS
This worked fine for me: VB6 + SQL Server 2000 + ADO 2.5
ASKER
I dont know what ADO i have? How do i find that out?
Anyhow, here an amended version:
CREATE Procedure GrantExecPermissions ( @UserOrGroup VarChar(255)) AS
SET NOCOUNT ON
-- Declare and set cursor
DECLARE @SQL VarChar(500)
DECLARE @ObjectName sysname
DECLARE @ownername sysname
DECLARE ObjectRS CURSOR FOR (SELECT user_name(uid), [Name] FROM dbo.SysObjects WHERE (xType = 'P' OR xType = 'FN') AND Category = 0)
-- Open and fetch first record from cursor
OPEN ObjectRS
FETCH ObjectRS INTO @ownername, @ObjectName
-- Loop cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Grant EXECUTE
SET @SQL = 'GRANT EXECUTE ON [' + @Ownername + '].[' + @ObjectName + '] TO ' + @UserOrGroup
EXEC(@SQL)
IF @@ERROR = 0
-- Print confirmation
PRINT 'Granted EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
ELSE
-- Print failure
PRINT 'Failed to grant EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
-- Get next object
FETCH ObjectRS INTO @ownername, @ObjectName
END
CLOSE ObjectRS
DEALLOCATE ObjectRS
go
CREATE Procedure GrantExecPermissions ( @UserOrGroup VarChar(255)) AS
SET NOCOUNT ON
-- Declare and set cursor
DECLARE @SQL VarChar(500)
DECLARE @ObjectName sysname
DECLARE @ownername sysname
DECLARE ObjectRS CURSOR FOR (SELECT user_name(uid), [Name] FROM dbo.SysObjects WHERE (xType = 'P' OR xType = 'FN') AND Category = 0)
-- Open and fetch first record from cursor
OPEN ObjectRS
FETCH ObjectRS INTO @ownername, @ObjectName
-- Loop cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Grant EXECUTE
SET @SQL = 'GRANT EXECUTE ON [' + @Ownername + '].[' + @ObjectName + '] TO ' + @UserOrGroup
EXEC(@SQL)
IF @@ERROR = 0
-- Print confirmation
PRINT 'Granted EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
ELSE
-- Print failure
PRINT 'Failed to grant EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
-- Get next object
FETCH ObjectRS INTO @ownername, @ObjectName
END
CLOSE ObjectRS
DEALLOCATE ObjectRS
go
ASKER
It doesn't like that either. Doesnt like the word go at all.
Could you add how you are executing the statement - i am passing it into this routine...
Sub GetADOrst(rst As adodb.Recordset, SQL As String, RecordCount As Long)
Set rst = New adodb.Recordset
Set rst = gobjConn.Execute(SQL)
End Sub
Thanks
Could you add how you are executing the statement - i am passing it into this routine...
Sub GetADOrst(rst As adodb.Recordset, SQL As String, RecordCount As Long)
Set rst = New adodb.Recordset
Set rst = gobjConn.Execute(SQL)
End Sub
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats all i needed, it works now!!
Just that I have a whole big program and for any sql statements it calls that procedure, just have to change it a bit though.
Thanks alot for your help
Just that I have a whole big program and for any sql statements it calls that procedure, just have to change it a bit though.
Thanks alot for your help