Create a Stored Procedure via ADO. Is it Possible????

[faeryfyrre]

I was wondering whether it is possible to create a stored procedure using the command object from the ADO library?

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[hmckillop]

You can create it using the execute method of the connection so I would guess you should also be able to using the command object.
You just need to have the correct permissions.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[faeryfyrre]

So if i used the following CommandString and i have all the relevant permissions on the SQL Server i should be able to create a Stored Procedure?

Create Procedure "TebsSP_SI_TypeofStaffChange_Insert"

/* This Stored Procedure is designed to insert the Parameter @TypeOfStaffChange
into the table TebsR_SI_TypeofStaffChange */

@TypeOfStaffChange varchar(100)

As

-- The actual insert sql statement
INSERT INTO TebsR_SI_TypeofStaffChange ([Type of Staff Change])
VALUES (@TypeOfStaffChange)

Return


How robust are the connection and command objects when the CommandString contains crlf info

IE would the following assignment cause problems? I'm using VBA as my language.

cmd1.CommandText = "Create Procedure " & Chr(34) & _
"TebsSP_SI_TypeofStaffChange_Insert" & Chr(34) & vbCrLf & _
"/* This Stored Procedure is designed to insert the " & _
"Parameter @TypeOfStaffChange " & vbCrLf & _
" into the table TebsR_SI_TypeofStaffChange */" & _
vbCrLf & vbCrLf & _
"@TypeOfStaffChange varchar(100) " & _
"-- The input parameter" & vbCrLf & vbCrLf & _
"As" & vbCrLf & vbCrLf & "-- The SQL Statement" & _
vbCrLf & _
"INSERT INTO TebsR_SI_TypeofStaffChange ([Type of Staff Change])" & vbCrLf & _
"VALUES (@TypeOfStaffChange)" & vbCrLf & vbCrLf & "Return"



Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"