|
|
I have a Stored Proccedure in my SQL Server Database that queries the
database and returnsa hierarchical XML string.
How can I call this stored procedure from MS Access so that I can retrieve
this XML string and write it out to a file.
My Code looks like this
Sub test081207_1()
Dim x As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={SQL Native
Client};Server=TEMPEST5;Database=XelonReleases;Trusted_Connection=yes"
conn.Open
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "usp_FetchTestXML"
cmd.CommandType = adCmdStoredProc
x = cmd.Execute
Debug.Print x
End Sub
My code falls over at
x = cmd.Execute
with a 'Type Mismatch" error
This is my stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Alter PROCEDURE [dbo].[usp_FetchTestXML]
AS
BEGIN
select [GUID] as "[ at ]LabelDigitalGUID", 'insert' as DeliveryType, [LabelName]
as LabelName, [CatNumber] as TitleCatalogueNumber , [ReleaseTitle] as
TitleName ,[ArtistName] as TitleArtistName , convert(Char,[UPCBarcode]) as
TitleUPC,[PhysicallyReleased] as TitlePhysicallyReleased ,
dbo.udf_GetJunoGenre('Juno',[AlbumGenre],1) as TitleFirstGenreId,
[TitleType],
(
select
(
select Restriction as "[ at ]Restriction", Code "*"
from XelonReleaseTerritory where XelonReleaseTerritory.ReleaseID
= XR2.ReleaseID for xml path ('CountryCode'), type
)
from XelonReleases as XR2 where XR2.ReleaseID = XelonReleases.ReleaseID
for xml path( 'TitleTerritory'), type
)
from XelonReleases inner join LabelGuids on LabelGuids.LabelID =
XelonReleases.LabelID inner join labels on XelonReleases.LabelID =
Labels.LabelID inner Join Artists on XelonReleases.ReleaseArtistID =
Artists.ArtistID
where LabelGuids.DSPid = 1 for xml path('release') , type
END
TIA
Tony
PS. I apologize fro the cross posting. I was not sure which newsgroup was
the most appropriate.
|
|
|