A mindboggingly basic question

I'm very new to being an SQL DBA (I'm an MS Access and Cold Fusion
developer), and I've been tossed in the deep with a grant project
where we're going to be pushing out data in XML format. So I'm trying
to bring myself up to speed on, well, how to do this. I've been going
through an online XML tutorial (http://www.w3schools.com/xml/) and I
think I get the basic idea of XML and schemas.
I've lately been trying to apply what I learned there to get XML
output from SQL. I started with the code snippet in the SQL Books
Online for the Northwind Db. I created a stored procedure with this in
it:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
GO
This is what it outputs in the query analyzer:
<Customers CustomerID="ALFKI" ContactName="Maria Anders"
CompanyName="Alfreds Futterkiste"><Orders CustomerID="ALFKI"
OrderDate="1997-08-25T00:00:00"/><Orders CustomerID="ALFKI"
OrderDate="1997-10-03T00:00:00"/><Orders CustomerID="ALFKI"
OrderDate="1997-10
I'm assuming that it's being truncated because of something to do with
the query analyzer.
So I tried linking to the SP via an Access DB, and outputting it into
a text format (well, I've tried a couple of different things). When I
try the text format, this is what I get:
| XML_F52E2B61-18A1- |
| <Customers |
I just must be missing something about how I can get the information
the SP retrieves into a plain file that can be used as XML. I know
this is really basic programming stuff, but I seem to be hitting a
wall in my head somewhere in how I'm thinking about this.
So what I'm looking for is an idea of what to use on the front end to
get the SP I have into a file that can be pushed around places. Access
doesn't seem to have what I'm looking for (or maybe I'm just not
looking at the right commands). This is the Access code I'm using to
create the files in question:
DoCmd.OutputTo acOutputQuery, "qryXMLOrdersAndCustomers", txt,
"C:\CFusionMX\wwwroot\TestingXMLStuff\xmlfromnorth wind.xml"
I know this is a longwinded post, and I know that this is a really
basic question, but if someone could help point me in the right
direction, I'd really appreciate it.
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
You are correct, there are limitations with Query Analyzer regarding column
size which causes the result set to be truncated. Go to
TOOLS/OPTIONS/RESULTS and set the "Maximum characters per column:" to 8192.
That is the max allowed in QA.
To get the results into a file go to QUERY and select "Results to File".
If you get results greater than 8192 chars, you might need to edit the file
to get rid of unneccesary line breaks that occur at 8192 intervals. However,
this size should allow you to run most test queries.
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:j552g0pefgfmvtchbb6tes9ah0vp61cgbo@.4ax.com...
> I'm very new to being an SQL DBA (I'm an MS Access and Cold Fusion
> developer), and I've been tossed in the deep with a grant project
> where we're going to be pushing out data in XML format. So I'm trying
> to bring myself up to speed on, well, how to do this. I've been going
> through an online XML tutorial (http://www.w3schools.com/xml/) and I
> think I get the basic idea of XML and schemas.
> I've lately been trying to apply what I learned there to get XML
> output from SQL. I started with the code snippet in the SQL Books
> Online for the Northwind Db. I created a stored procedure with this in
> it:
> SELECT Customers.CustomerID, ContactName, CompanyName,
> Orders.CustomerID, OrderDate
> FROM Customers, Orders
> WHERE Customers.CustomerID = Orders.CustomerID
> AND (Customers.CustomerID = N'ALFKI'
> OR Customers.CustomerID = N'XYZAA')
> ORDER BY Customers.CustomerID
> FOR XML AUTO
> GO
> This is what it outputs in the query analyzer:
> <Customers CustomerID="ALFKI" ContactName="Maria Anders"
> CompanyName="Alfreds Futterkiste"><Orders CustomerID="ALFKI"
> OrderDate="1997-08-25T00:00:00"/><Orders CustomerID="ALFKI"
> OrderDate="1997-10-03T00:00:00"/><Orders CustomerID="ALFKI"
> OrderDate="1997-10
> I'm assuming that it's being truncated because of something to do with
> the query analyzer.
> So I tried linking to the SP via an Access DB, and outputting it into
> a text format (well, I've tried a couple of different things). When I
> try the text format, this is what I get:
> --
> | XML_F52E2B61-18A1- |
> --
> | <Customers |
> --
> I just must be missing something about how I can get the information
> the SP retrieves into a plain file that can be used as XML. I know
> this is really basic programming stuff, but I seem to be hitting a
> wall in my head somewhere in how I'm thinking about this.
> So what I'm looking for is an idea of what to use on the front end to
> get the SP I have into a file that can be pushed around places. Access
> doesn't seem to have what I'm looking for (or maybe I'm just not
> looking at the right commands). This is the Access code I'm using to
> create the files in question:
> DoCmd.OutputTo acOutputQuery, "qryXMLOrdersAndCustomers", txt,
> "C:\CFusionMX\wwwroot\TestingXMLStuff\xmlfromnorth wind.xml"
> I know this is a longwinded post, and I know that this is a really
> basic question, but if someone could help point me in the right
> direction, I'd really appreciate it.
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)
|||On Fri, 23 Jul 2004 11:26:08 -0400, "John Kotuby" <jkotuby@.snet.net>
wrote:

>You are correct, there are limitations with Query Analyzer regarding column
>size which causes the result set to be truncated. Go to
>TOOLS/OPTIONS/RESULTS and set the "Maximum characters per column:" to 8192.
>That is the max allowed in QA.
Ok, I've done that. Thanks!

>To get the results into a file go to QUERY and select "Results to File".
>If you get results greater than 8192 chars, you might need to edit the file
>to get rid of unneccesary line breaks that occur at 8192 intervals. However,
>this size should allow you to run most test queries.
What I'm really asking is what do I use to be able to make a file on a
regular basis. I mean, I'm not going to go into query analyzer and
pull this data every day. I was afraid that my question was so low
level that people would think I couldn't possibly be really asking
what I am.
Say you have a pile of data in SQL that you want to shove out into an
XML file that you can then ship off to someone. How do you do that? (I
guess that's how basic a question I'm asking here) I just seem to be
missing something in the documents and books I'm reading. They are all
assuming a level of expertise or experience I just don't have. Maybe
I'm skimming too much.

>"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
>news:j552g0pefgfmvtchbb6tes9ah0vp61cgbo@.4ax.com.. .
>
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
|||Siobhan,
Not sure what you're VB is like but assuming you can read it then the
following would save the results of a stored procedure 'SQL_First' to a
result file
Sub SaveXml()
Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=."
oCmd.CommandText = "SQL_First"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\temp\results.xml"
End Sub
{The project would need to reference ADO and msxml4.dll )
Hope that helps some
Graham
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:41m2g0djdtibvtqmpoave9oa7il74vgdae@.4ax.com... [vbcol=seagreen]
> On Fri, 23 Jul 2004 11:26:08 -0400, "John Kotuby" <jkotuby@.snet.net>
> wrote:
column[vbcol=seagreen]
8192.[vbcol=seagreen]
> Ok, I've done that. Thanks!
file[vbcol=seagreen]
However,[vbcol=seagreen]
> What I'm really asking is what do I use to be able to make a file on a
> regular basis. I mean, I'm not going to go into query analyzer and
> pull this data every day. I was afraid that my question was so low
> level that people would think I couldn't possibly be really asking
> what I am.
> Say you have a pile of data in SQL that you want to shove out into an
> XML file that you can then ship off to someone. How do you do that? (I
> guess that's how basic a question I'm asking here) I just seem to be
> missing something in the documents and books I'm reading. They are all
> assuming a level of expertise or experience I just don't have. Maybe
> I'm skimming too much.
>
message
>
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)
|||On Sun, 25 Jul 2004 19:26:39 GMT, "Graham Shaw" <Graham@.somewhere.com>
wrote:
Hi Graham,
Thanks so much for responding.

>Not sure what you're VB is like but assuming you can read it then the
>following would save the results of a stored procedure 'SQL_First' to a
>result file
I'm not great. I came into Db programming from being a super user,
rather than coming at it from actual training in languages. So I'm
trying to work my way through what you've so kindly provided. I'm not
sure I understand everything you've put in here, but I'm able to grok
a fair amount from context.

>Sub SaveXml()
>Dim oCmd As Command
>Dim oPrm As Parameter
>Dim oDom As IXMLDOMDocument2
When I compile this, it pops up an "User-defined type not defined"
error at the last line of this block. I've never done a "user-defined
type" before. Is this something I should set up somewhere?
Or would this part of your post:

>{The project would need to reference ADO and msxml4.dll )
Be the explanation for that? If so, I'm not sure what to do with that
information.
One thing I'm wondering now, is there a command I can put at the end
of my stored procedure in SQL that'll just run it and pop it out to a
file? I have been trying to find such, but I suspect I'm not looking
for the right syntax in books online.
I don't think I really have to have this in an MS Access Db...
*ponder*
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
|||Hi Siobhan
Yes the unknown type is the bit about referencing in VB goto
Project/References on the menu and scroll down to Microsoft XML, v4.0 (if
you only have v3.0 then select that but change the Set oDom = New
DOMDocument40 to Set oDom = New DOMDocument30. For the ADO follow the same
procedure and select "Microsoft ActiveX Data Objects 2.X Library" where X is
prefereably 6 or above although 5 will work with what I gave you.
Another alternative would be to look in BOL for the command line version of
the query analyser iql that can output its results to a file.
G
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:f5cag05sstqbuam2cggtbermgu0urqq8gs@.4ax.com...
> On Sun, 25 Jul 2004 19:26:39 GMT, "Graham Shaw" <Graham@.somewhere.com>
> wrote:
> Hi Graham,
> Thanks so much for responding.
>
> I'm not great. I came into Db programming from being a super user,
> rather than coming at it from actual training in languages. So I'm
> trying to work my way through what you've so kindly provided. I'm not
> sure I understand everything you've put in here, but I'm able to grok
> a fair amount from context.
>
> When I compile this, it pops up an "User-defined type not defined"
> error at the last line of this block. I've never done a "user-defined
> type" before. Is this something I should set up somewhere?
> Or would this part of your post:
>
> Be the explanation for that? If so, I'm not sure what to do with that
> information.
> One thing I'm wondering now, is there a command I can put at the end
> of my stored procedure in SQL that'll just run it and pop it out to a
> file? I have been trying to find such, but I suspect I'm not looking
> for the right syntax in books online.
> I don't think I really have to have this in an MS Access Db...
> *ponder*
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)
|||Sorry iql should be isql
"Graham Shaw" <Graham@.somewhere.com> wrote in message
news:3qbNc.663$C85.648@.newsfe1-gui.ntli.net...
> Hi Siobhan
> Yes the unknown type is the bit about referencing in VB goto
> Project/References on the menu and scroll down to Microsoft XML, v4.0 (if
> you only have v3.0 then select that but change the Set oDom = New
> DOMDocument40 to Set oDom = New DOMDocument30. For the ADO follow the same
> procedure and select "Microsoft ActiveX Data Objects 2.X Library" where X
is
> prefereably 6 or above although 5 will work with what I gave you.
> Another alternative would be to look in BOL for the command line version
of
> the query analyser iql that can output its results to a file.
> G
> "Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
> news:f5cag05sstqbuam2cggtbermgu0urqq8gs@.4ax.com...
>
|||On Mon, 26 Jul 2004 17:44:31 GMT, "Graham Shaw" <Graham@.somewhere.com>
wrote:

>Yes the unknown type is the bit about referencing in VB goto
>Project/References on the menu and scroll down to Microsoft XML, v4.0 (if
>you only have v3.0 then select that but change the Set oDom = New
>DOMDocument40 to Set oDom = New DOMDocument30. For the ADO follow the same
>procedure and select "Microsoft ActiveX Data Objects 2.X Library" where X is
>prefereably 6 or above although 5 will work with what I gave you.
Ok, I have those things set, and it's giving me an error I wouldn't
have expected. It says:
Runtime error '-2147467259 (80004005)':
[DBNETLIB] [ConnectionOpen(Connect()).] SQL Server does not exist or
access denied.
I would have thought that since I'm running this from a Db that has a
connection to the server set up it'd run.
I haven't looked for an explantion of the error yet (clicking help
basically says "automation error" but nothing else). But I'm starting
with looking at the ActiveConnection line. Just posting here in case
you know off the top of your head what might cause this.

>Another alternative would be to look in BOL for the command line version of
>the query analyser iql that can output its results to a file.
I've tried looking for this, but I'm embarassed to say that I'm not
certain how to use it. BOL is great about giving details on command
syntax, but not great on methods of applying the commands. *sigh* I
know, I need a class...

>"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
>news:f5cag05sstqbuam2cggtbermgu0urqq8gs@.4ax.com.. .
>
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
|||Hi Siobhan
If you already have a connection object open then just do
set oCmd.ActiveConnection=YourConnectionObjectName
instead of the connection string
as for isql just go into BOL click on the search tab and type 'isql utility'
in the text box and hit "list topics", it should come up ranked about number
9 ;o)
Graham
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:asfcg0hebo7pjs29sdnd3n8u19bv541qde@.4ax.com... [vbcol=seagreen]
> On Mon, 26 Jul 2004 17:44:31 GMT, "Graham Shaw" <Graham@.somewhere.com>
> wrote:
same[vbcol=seagreen]
is[vbcol=seagreen]
> Ok, I have those things set, and it's giving me an error I wouldn't
> have expected. It says:
> Runtime error '-2147467259 (80004005)':
> [DBNETLIB] [ConnectionOpen(Connect()).] SQL Server does not exist or
> access denied.
> I would have thought that since I'm running this from a Db that has a
> connection to the server set up it'd run.
> I haven't looked for an explantion of the error yet (clicking help
> basically says "automation error" but nothing else). But I'm starting
> with looking at the ActiveConnection line. Just posting here in case
> you know off the top of your head what might cause this.
of[vbcol=seagreen]
> I've tried looking for this, but I'm embarassed to say that I'm not
> certain how to use it. BOL is great about giving details on command
> syntax, but not great on methods of applying the commands. *sigh* I
> know, I need a class...
message[vbcol=seagreen]
a
>
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)