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(@UserOrGroup 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
CraigBroadmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
This worked fine for me: VB6 + SQL Server 2000 + ADO 2.5
CraigBroadmanAuthor Commented:
I dont know what ADO i have? How do i find that out?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

CraigBroadmanAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, the GO statement should be removed (was my initial test using Query Analyzer).

now, regarding your VB code:
as CREATE PROCEDURE doesn't return a recordset, you don't need a recordset object

simply:
gobjConn.Execute SQL


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CraigBroadmanAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.