Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How To Create A Stored Procedure at run time using ADOX object
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/31/2001 :  11:50:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Yogesh writes "Dear SqlTeam,

I ahve a problem in creating a stored procedure at run time using ADOX Object. I belive Adox is the only object which you can use to create stored proc in SQL Server. As a sample I am including my VB code for your referance.

Kindly arrange to solve this problem at the earliest.

Thanks & Regards

Sincerly Yours

Yogesh Ajmere


Public Sub CreateProc()

Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim DnsStr As String, NewLine As String, Sql As String

NewLine = Chr(10)

DsnStr = "DSN=yamuna;uid=yogesh;pwd=;"
'*********************************
Sql = "SET QUOTED_IDENTIFIER ON" & NewLine & "GO " & NewLine & "SET ANSI_NULLS ON " & NewLine & "GO "
Sql = Sql & NewLine & "CREATE PROCEDURE FullSoundex123 -- <procedure_name, sysname, proc_test>" & NewLine & "(@strIn CHAR(255))" & NewLine & " AS"
Sql = Sql & NewLine & "DECLARE @OneWord AS VARCHAR(100), @iLn AS INT, @iCount AS INT, @cChr AS CHAR, @ThisSoundex AS VARCHAR(5), @Soundex AS VARCHAR(100)"
Sql = Sql & NewLine & "SET @iCount = 1" & NewLine & "SET @iLn = LEN(@strIn)" & NewLine & "SET @ThisSoundex = ''" & NewLine & "SET @Soundex = ''" & NewLine & "SET @OneWord = ''"
Sql = Sql & NewLine & "WHILE (@iCount <= @iLn)" & NewLine & " BEGIN"
Sql = Sql & NewLine & "IF (@cChr = ' ' )" & NewLine & " BEGIN"
Sql = Sql & NewLine & " SET @ThisSoundex = SOUNDEX (@OneWord)" & NewLine & " SET @Soundex = @Soundex + @ThisSoundex + ';'" & NewLine & " SET @OneWord = ''" & NewLine & " End"
Sql = Sql & NewLine & " Else" & NewLine & " BEGIN" & NewLine & " SET @OneWord = @OneWord + @cChr" & NewLine & " End" & NewLine & " SET @iCount = @iCount + 1" & NewLine & " End"
Sql = Sql & NewLine & " IF (@OneWord <> '')" & NewLine & " BEGIN" & NewLine & " SET @ThisSoundex = SOUNDEX (@OneWord)" & NewLine & " SET @Soundex = @Soundex + @ThisSoundex + ';'" & NewLine & " End"
Sql = Sql & NewLine & " --PRINT @Soundex" & NewLine & "GO" & NewLine & "SET QUOTED_IDENTIFIER OFF" & NewLine & "GO" & NewLine & "SET ANSI_NULLS ON" & NewLine & "GO"


MsgBox Sql

On Error GoTo AdoError
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = Sql
Cmd.CommandType = adCmdStoredProc
Cmd.Execute



AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

End Sub"

allyanne
Starting Member

USA
18 Posts

Posted - 07/31/2001 :  12:19:28  Show Profile  Reply with Quote
quote:

I belive Adox is the only object which you can use to create stored proc in SQL Server


I'm not familiar with ADOX, but you can use SQL-DMO to create and alter stored procedures.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000