xml with SP_Makewebtask in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

xml with SP_Makewebtask in stored procedure

Author

Topic

giovi2002
Starting Member

46 Posts

Posted - 2005-07-18 : 16:08:10

Goal: Exporting xml from my adp (access project database) to a xml file by using a parameter date range on a form of my access application

What did I do: I've embedded sp_makewebtask within a stored procedure. I call to the stored procedure with ADO code from my form and linkup the parameters.
The hellish thing is: it doesn't fetch my parameters so the xml is empty, it seems like the date datatypes don's correspond

Here you can see the code, DATUM is a user-defined datatype and stands for datetime

Procedure
---------
CREATE PROCEDURE Qry_Punctualiteitenxml
@Begindatum DATUM, @Einddatum DATUM
AS
execute sp_makewebtask @outputfile = 'c:\testdata\xml\Punctualiteiten.xml', @templatefile = 'c:\testdata\xml\templatepunctualiteiten.tpl',
@query=' DECLARE @Begindatum DATUM SELECT * FROM REP_PUNCTUALITEITEN FOR XML AUTO'
If @@Error<>0
Begin
raiserror('error exporting xml consult dba',16,1)
End
GO

And my ADO code to call the SP
------------------------------
Private Sub KnopXML_Punctualiteiten_Click()

On Error GoTo Err_KnopXML_Punctualiteiten_Click
Dim myado As ADODB.Command
Dim rec As Single
Dim StrBegindatum As Date
Dim StrEinddatum As Date
Set myado = New ADODB.Command

myado.ActiveConnection = CurrentProject.Connection
myado.CommandType = adCmdStoredProc
myado.CommandText = "Qry_Punctualiteitenxml"
StrBegindatum = Forms![Frm_El14_Conv_Punctualiteiten]![Selectie_Begindatum]
StrEinddatum = Forms![Frm_El14_Conv_Punctualiteiten]![Selectie_Einddatum]
myado.Parameters.Append myado.CreateParameter("Begindatum", adDate, adParamInput, 10, StrBegindatum)
myado.Parameters.Append myado.CreateParameter("Einddatum", adDate, adParamInput, 10, StrEinddatum)
myado.Execute

Exit_KnopXML_Punctualiteiten_Click:
Exit Sub

Err_KnopXML_Punctualiteiten_Click:
MsgBox Err.Description
Resume Exit_KnopXML_Punctualiteiten_Click

End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources