In article <4B7B2DAF-28ED-4A86-9175-F4F21BFEA136[ at ]microsoft.com>, dbguru316
[ at ]discussions.microsoft.com says...
[Quoted Text] > 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.
>
> When I execute the query I get an error message like
> "Could not find the stored procedure BOM2ECO_Difference_Get" (#2812)
>
> What am I missing here?
>
>
Maybe expand the database menu tree:
Databases/your database name/Programmability/Stored Procedures
Right click on Stored Procedures
Select 'New Stored Procedure'
You are provided a template for a new stored procedure
Note that I am a very casual user of SQL Server Express, so certainly someone
else can give you better advice.
|