Re: Create Storedprocedure with VB6? Is this possible?

The Following code actually works
VB6 and ADO no .NET

strSql = "CREATE PROCEDURE [dbo].[ReturnAmtTest1] " & vbLf & _
"/*-- Add the parameters for the stored procedure here */ " &
vbLf & _
"@RetBal int OUTPUT " & vbLf & _
"AS " & vbLf & _
"BEGIN " & vbLf & _
"/* Procedure body */" & vbLf & _
"SELECT " & vbLf & _
"@RetBal = Sum(dbo.TableName.FieldName) " & vbLf & _
"From dbo.TableName" & vbLf & _
"End " & vbLf & _
"RETURN @RetBal;"

Problem was I had customised my error message from a previous version and
just cut the source in and modified the SQL string
the exception message that was appearing was a end user message anyway
sorted.

Hope the sample code is of some use to someone

Thanks for your help

Sam


"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> wrote in message
news:uhnkGWf0HHA.5644@xxxxxxxxxxxxxxxxxxxxxxx

"sam" <sam@xxxxxxxxxx> wrote in message
news:uyghXMZ0HHA.3536@xxxxxxxxxxxxxxxxxxxxxxx
Mark,

William Vaughn did not answer my question
Quote
"Sure. I have examples in all of my Hitchhiker Guide books.
Basically, you can create and execute an SQL query that includes the
CREATE
PROCEDURE call.
don't think the VB6 IDE itself could create a stored procedure, but there
are lots of other tools that will. SQL Server Enterprise Manager can and
so
can SQL Server Management Studio (there's even a free Express version)."

This does NOT answer my simple question.
I want to know if VB6 can do it.

Ralph answer was a remark to William

Please answer my question as I am here for help not to
be put down by peoples replys.

To explain my question again.

Can VB6 create a stored procedure on the fly.
In the same way as I can create a table with VB
Alter a Table With VB Not a SQL manager Tool

Sample Code
Stored procedure I want to create in MSSQL
--Start Here
' CREATE PROCEDURE [dbo].[ReturnAmt]
/*-- Add the parameters for the stored procedure here */
@RetBal int OUTPUT
AS
BEGIN
/* Procedure body */
SELECT
@RetBal = Sum(dbo.Tick.MyVal)
From dbo.Tick
End
RETURN @RetBal
--Ends Here

Sample VB6 Code
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection

On Error GoTo ErrorHandler ' Enable error-handling routine.

MyConn.Open SqlConn

strSql = "CREATE PROCEDURE [dbo].[ReturnAmt] " & _
"'/*-- Add the parameters for the stored procedure here */ "
& _
"@RetBal int OUTPUT" & _
"AS " & _
"BEGIN " & _
"/* Procedure body */" & _
"SELECT" & _

This looks like a syntax error (no space after SELECT.) To solve problems
like this it is almost always useful to output (using Debug.Print or
something similar) exactly what you're sending to the db engine (via ADO),
for a reality check. If you don't see any problems with it, paste it into
Query Analyzer -- if it doesn't work in QA, chances it will work with ADO
are quite poor.

Another useful technique for dealing with problems is to examine the
Connection.Errors collection, but at the very least your error handler
should make it easy for you to see what's in Err.Description. Error
output may not always be entirely forthcoming, but it is always a
reasonable place to start...

Which would be exactly why I suggested you include any error information,
"does not work" is much too vague, and in a larger sense, it isn't even
accurate. "Does not work as you expected," is a given, but in this case,
you passed a program statement that did not conform to syntax rules; ADO
threw an error, which is exactly what it's supposed to do.

Rule #1 of database programming is that exceptions will occur. Dealing
with them constructively should be integral to your design.


-Mark



"@RetBal = Sum(dbo.Tick.MyVal) " & _
"From dbo.Tick " & _
"End " & _
"RETURN @RetBal;"
MyConn.Execute strSql


MyConn.Close
Set MyConn = Nothing

This Does Not work

Any Help Appreciated

Sam


"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> wrote in message
news:O0RsLlH0HHA.3940@xxxxxxxxxxxxxxxxxxxxxxx

"sam" <sam@xxxxxxxxxx> wrote in message
news:ePeYU9G0HHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
I don't want to use a third party tool.
Is there any way I can create a storedprocedure
from a VB6 Application. Or can itbe done another way,
I do not want to use .Net

To paraphrase the answer you've already been given...

1. Create and open a Connection object, using a login context that's
permitted to create stored procedures;
2. Call the connection's Execute method, to execute a CREATE PROCEDURE
statement;

This, of course, assumes you have already composed the CREATE PROCEDURE
statement, and that it is valid, and that a procedure with the same name
doesn't already exist. In actuality, it assumes dozens of other things
too tedious to go into here, so if, having been given accurate advice,
you are still unable to do this, try posting some code so we can see
what you've tried, along with whatever errors it may have thrown.


-Mark



TIA

Sam


"William Vaughn" <billvaNoSPAM@xxxxxxxxx> wrote in message
news:3DB62B88-E5C2-4F75-9D49-11E5F033AE9B@xxxxxxxxxxxxxxxx
Sure. I have examples in all of my Hitchhiker Guide books.
Basically, you can create and execute an SQL query that includes the
CREATE PROCEDURE call.
I don't think the VB6 IDE itself could create a stored procedure, but
there are lots of other tools that will. SQL Server Enterprise Manager
can and so can SQL Server Management Studio (there's even a free
Express version).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"sam" <sam@xxxxxxxxxx> wrote in message
news:eb%23upnA0HHA.5152@xxxxxxxxxxxxxxxxxxxxxxx
Can a Storedprocedure be created using code in VB6?

If so how?

Is there some sample code?

TIA

Sam












.

Relevant Pages

  • Re: Very bad response time from stored procedure call via ADO
    ... We have a stored procedure that returns some statistical information ... SQL Server is version 2000 ... When run in SQL Query Analyser the typical duration is 400ms. ... When run from the VB6 application via ADO the duration increases to ...
    (microsoft.public.data.ado)
  • Re: Very bad response time from stored procedure call via ADO
    ... Turn on the Show Query Plan and show statistics option in SQL Server ... VB6 application for display via ADO. ... The stored procedure structure is as follows: ...
    (microsoft.public.data.ado)
  • Re: Call SP from VB using optional parameters
    ... free from msdn (search for "ADO Stored Procedure Add-In"). ... >I am using VB6 ADO command object to call Stored procedure. ...
    (microsoft.public.sqlserver.clients)
  • SQL Server Express 2005
    ... Is it possible to access the 2005-sql-server-generation by way of VB6 and ... ADO? ... Will there be, or is there, an ADO-Version that works with the SQL Server ... Prev by Date: ...
    (microsoft.public.vb.database.ado)
  • VB in a stored procedure
    ... Is it possible to use VB or VB.NET in a stored procedure? ... I can call a stored procedure using ADO or ADO.NET from VB or VB.NET, ... the SQL Server can use in the Stored Procedure? ... good example of how it is done I would be very grateful. ...
    (microsoft.public.sqlserver.programming)