I have an xml datastream and would like to know the syntax to map the contents to a specific excel worksheet and xml map. This is to automate 30+ complex reports in excel (must be excel - and no iis available to make my job much easier!). I anyone has a better solution I welcome it. This is my syntax so far: Dim objConn As ADODB.Connection Dim objCMD As ADODB.Command Dim objResultStream As ADODB.Stream Dim xmpCustomMap As XmlMap On Error GoTo Handle_Error Set xmpCustomMap = ActiveWorkbook.XmlMaps("triangle_Map") Set objConn = New ADODB.Connection objConn.Open "Provider=SQLOLEDB;server=mydb;Database=Development;" & _ "Integrated Security=SSPI" Set objCMD = New ADODB.Command Set objCMD.ActiveConnection = objConn 't-SQL DIALECT objCMD.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}" objCMD.CommandText = "Exec dbo.proc_xml 1394" objCMD.CommandType = adCmdText Set objResultStream = New ADODB.Stream objResultStream.Open objCMD.Properties("Output stream") = objResultStream objCMD.Properties("XML Root") = "Root" objCMD.Execute , , adExecuteStream Debug.Print objResultStream.ReadText(adReadAll)

If it is mission critical I would be more inclined to go with something more proven. I use sql Server and Mysql. Mysql is a really good open source database with a lot of documentation and users behind it - worth checking out. Were I to start a business today I would most likely go with Mysql over Sql Server. B

Just my 2 cents.... Sql S 2000? I would put this part into a table valued function: For sex I would use and in (i.e WHERE ttester.sexid in(@sexid): IF @sexid = '*' SET @sexid = 'm,f' That would narrow it down a bit BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID) WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID Then I would call the function: SELECT Distinct dayspast,etc* From dbo.myfunction(@sexid,@vars...etc) f WHERE not exists ( SELECT tTester.TesterID (don't need distinct or group.) FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID) WHERE tGroup.ScheduleStamp > @DD and f.testerid = tTester.TesterID ) AND f.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp) FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID) WHERE cor_tTester.TesterID = f.TesterID) and tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp) FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID) WHERE cor_tTester.TesterID = f.TesterID) order by f.LastName

your welcome yes - it is for the grant tables. I suppose you might need it if something happened to that db. michanne

I don't see the error. What is the error msg? You might try printing the result instead of exec right off the bat. Paste the result into another qa window and see what errors you get. Hope that helps michanne