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

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: Problem with the Legacy ASP files and the Sql Server Express
    ... set cmd = Server.CreateObject ... 2000, 2005), as long as the connection is OK and the SP has two parameters ... If the problem is not with the SQL server Exp, then how come the SQL Sever ... unless your stored procedure has some thing that only ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Stored Procedure in SQL Server vs MSDE 2005 SQL Server
    ... Sylvain Lafontaine, ing. ... converted to MSDE 2005 SQL Server Express. ... The stored procedure in the SQL server is called BOM2ECO_Difference_Get. ...
    (microsoft.public.access.externaldata)
  • Re: Stored Function -- SQL Server 2000
    ... The Stored Procedure of SQL server is as below. ... I think the VB6 code should be as below, but how can I get the value of @jul_date. ... Set cmd = New ADODB.Command ...
    (microsoft.public.vb.database.ado)
  • 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: 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)
Loading