Re: Stored Procedure in SQL Server vs MSDE 2005 SQL Server

Sorry: Run them or Execute them, don't remember the exact word used in the
english version.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ODTSaRnuHHA.3796@xxxxxxxxxxxxxxxxxxxxxxx
With SSMS or SSMSE, you must "run" them; not store them. You must also
make the distinction between Create Procedure and Alter Procedure
yourself.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"dbguru316" <dbguru316@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E053D877-1AD1-4C57-88A9-70A978B35ADE@xxxxxxxxxxxxxxxx
I have a working stored procedure in SQL Server 2000 that needs to be
converted to MSDE 2005 SQL Server Express. When creating the stored
procedure in MSDE 2005 I noticed that they are stored as projects with a
.sql
ending. What changes must I make to call the stored procedures in MSDE
vs
SQL Server. The SQL Server stored procedure is called from Microsoft
Access
as such:

Public Sub Get_BOM_Difference(lngParam1 As Long, lngParam2 As Long,
intOverwrite As Integer)

Dim strTSQL As String
Dim strQueryName As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command

strQueryName = "qry_get_bom_difference"
strTSQL = "EXEC BOM2ECO_Difference_Get " & CStr(lngParam1) & ", " &
CStr(lngParam2) & ", " & CStr(intOverwrite)

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Procedures(strQueryName).Command

cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
cmd.CommandText = strTSQL
Set cat.Procedures(strQueryName).Command = cmd

cmd.Execute

Set cmd = Nothing
Set cat = Nothing

End Sub

The stored procedure in the SQL server is called BOM2ECO_Difference_Get.
I
named the stored procedure the same in MSDE but it adds a .sql at the
end,
and it seems to be stored as a "project", and is not visible under
Progammabilty section of MSDE management studio. Only System Stored
Procedures are visible.

What am I missing here?







.

Relevant Pages

  • Re: Stored Procedure in SQL Server vs MSDE 2005 SQL Server
    ... converted to MSDE 2005 SQL Server Express. ... Set cmd = cat.Procedures.Command ... The stored procedure in the SQL server is called BOM2ECO_Difference_Get. ...
    (microsoft.public.access.externaldata)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Setting control value based on a SQL Select statement
    ... @CRRNumber int OUTPUT, ... Sylvain Lafontaine, ing. ... The actual code I use to call my stored procedure is as follows: ... ClientID and GUID number to the stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Accessing FoxPro Free Table
    ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
    (microsoft.public.data.odbc)
  • Stored procedure does not complete until result set is retrieved from ODBC
    ... I have a SQL Server Stored procedure that I am executing via ODBC. ... -- Start Code without cursor ...
    (microsoft.public.sqlserver.odbc)
Loading