Stored Procedure in SQL Server vs MSDE 2005 SQL Server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
With SSMS or SSMSE, you must "run" them; not store them. You must also make
the distinction between Create Procedure and Alter Procedure yourself.
 
Sorry: Run them or Execute them, don't remember the exact word used in the
english version.
 
Back
Top