Re: Retreiving Data as XML from MS SQL server - Database Discussion Boards

I am using XML on PPC. Check out this snippet from Inventory sample program:

    Private Sub loadXMLData()<br />
<br />
        Try<br />
<br />
            prog.Value = 1<br />
            sbStatus.Text = "Loading XML data..."<br />
<br />
            prog.Value = 2<br />
            Dim fs As New FileStream(txtXMLFileName.Text, FileMode.Open)<br />
<br />
            prog.Value = 3<br />
            Dim xr As New XmlTextReader(fs)<br />
<br />
            prog.Value = 4<br />
            data.ReadXml(xr)<br />
<br />
            prog.Value = 8<br />
            sbStatus.Text = "Loaded XML data"<br />
            xr.Close()<br />
            fs.Close()<br />
<br />
        Catch e As System.IO.FileNotFoundException<br />
<br />
            prog.Value = 8<br />
            MessageBox.Show("The file '" + txtXMLFileName.Text + "' could not be found. Please select a different file name.")<br />
<br />
        Catch e As XmlException<br />
<br />
            prog.Value = 8<br />
            sbStatus.Text = "Exception occured: " + e.ToString()<br />
<br />
        End Try<br />
<br />
    End Sub<br />

where data is a DataSet.

"For all of our languages, we cannot communicate" - Christy Moore, Natives

Hi everybody,

there is a way to hide the system-tables, views and stored procedures in the Enterprise Management Console. I know this because I've done it before. But, believe it or not, I forgot how Mad | :mad:

. Can somebody tell me? (SQL 2000)

Thanks in advance,

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

Figured it out. For all those that are interested: You have to untick a checkbox in the Registration-Properties.

Anyway thanks for reading.

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

To all masters:
my database is db2 & oracle.

could OdbcCommandBuilder create InsertCommand, UpdateCommand and DeleteCommand against db2 or oracle tables?
(SQL Server is ok.)

i always get an exception about that there is no key infomation after i execute adapter.Update(table).

Could anyone give me a help?

Thank u very much!

paul.

Hi
I want to bind Relational tables with controlls ,e.g i have a table customerOrder in which Order_ID is the Primary key and this field is foreign key in CustomerTrasaction table.
Now i want such a mechanisam in which some text boxes should bound to CustomerOrder Table and a data grid should bound to CustomerTrasaction table.
when a person click Next, Previous Button on the form the relational records should navigate so that OrderID and the related fields comes to the text boxes which they are bound. (This is basically a parent record to which many child records exist in the child table CustomerTrasaction) and the rest of the related child fields should come to the datagrid .
This navigation should work on each Next click and Previous click event.
Thanks.

Eman Ali Moughal
Senior Software Engineer
Soon Soft (pvt) Ltd
www.soonsoft.com

Is it possible to generate a SQL Script (SQL Server 2000), that will be as insert statements script for my existing data in my tables

Any 3rd party tools will also be of help...

Thanks Rose | [Rose]

There is nothing new under the sun,
But there are lots of old things we don't know.
- Ambrose Bierce

<hello,

I am trying to develop a simple application to retrieve data from MS SQL server using XML Templates. The application consists of a main HTML page with a form. The form has 3 text box to filter data (ID, name and title).
When the user pushes the "submit" button a XML template is loaded and the 3 text box values are passed as parameters:

For Example:
http://localhost/nwind/template/EmployeesList.xml?EmployeeID=1&Name=Nancy&Title=Sales+Representative&Submit=Go

The XML template is this:

<?xml version='1.0' ?>
<EmployeesList xmlns:sql='urn:schemas-microsoft-com:xml-sql'
sql:xsl='EmployeesList.xsl'>
<sql:header>
<sql:param name='EmployeeID'></sql:param>
<sql:param name='Name'></sql:param>
<sql:param name='Title'></sql:param>
</sql:header>
<sql:query>
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeID
AND FirstName = @Name
AND Title = @Title
FOR XML AUTO
</sql:query>
</EmployeesList>
</code>

As you can see, the template use the 3 params to filter data.

This runs ok if the user sets well the 3 values in the text boxes but if the user leaves a text box blank it does not.
What i want to do is a filter: if the user only sets text in a text box the application has to list every employees with this text box value.

Could you help me? How can i do this?
thank you very much!

ok,

I have solved my problem. I have changed the XML template. Now, the XML template is this:

<?xml version='1.0' ?>
<EmployeesList xmlns:sql='urn:schemas-microsoft-com:xml-sql'
sql:contenttype='html;charset=euc-kr' sql:xsl='EmployeesList.xsl'>
<sql:header>
<sql:param name='EmployeeID'></sql:param>
<sql:param name='Name'></sql:param>
<sql:param name='Title'></sql:param>
</sql:header>
<sql:query>
exec StoreProcedure @EmployeeID, @Name, @Title
</sql:query>
</EmployeesList>

As you can see, it executes a store procedure and pass it the 3 params.
Then, in the store procedure the params are checked and in function of their values a query is executed. Problem: I dont know much about SQL and it must be a query for each one of the possible combinations. I think this is very inefficient. The store procedure is this:

CREATE PROCEDURE StoreProcedure
@EmployeeID int, @Name nvarchar(30), @Title nvarchar(60)
AS

IF (@EmployeeID < 1 ) AND (@Name = '') AND (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

IF (@Name = '') AND (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeID
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

IF (@EmployeeID < 1 ) AND (@Name = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE Title = @Title
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

IF (@EmployeeID < 1 ) AND (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE FirstName=@Name
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

IF (@EmployeeID < 1 )
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE FirstName=@Name
AND Title = @Title
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

IF (@Name = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE Title = @Title
AND EmployeeID = @EmployeeID
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

IF (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE FirstName = @Name
AND EmployeeID = @EmployeeID
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END

FIN:
GO

Is there another way to do this more efficiently?
Thank you very much! much!

It looks like your stored procedure can be simplified to one select statement:

<br />
SELECT <br />
    EmployeeID, FirstName, LastName, Title<br />
FROM <br />
    Employees<br />
WHERE <br />
    (@EmployeeID < 1 or EmpolyeeID = @EmployeeID)<br />
and<br />
    (@Name = '' or FirstName = @Name)<br />
and<br />
    (@Title = '' or Title = @Title)<br />
ORDER BY <br />
    EmployeeID<br />
FOR XML AUTO<br />

My articles and software tools

I am trying to import into SqlServerCe from an XML file. There is also a schema file available.

I have opened up the target .sdf database.
I used ReadXML to fill a DataSet from the xml file. Where do I go from here?

"For all of our languages, we cannot communicate" - Christy Moore, Natives

Does anybody know of free apps which can convert one database (Sybase Anywhere) to another (MSSQL)?

All that I seem to find are whitepapers (theory only, of course Suspicious | :suss:

) from Microsoft and Sybase on how to migrate from one DBMS to another.

--
Ich bin Joachim von Hassel, und ich bin Pilot der Bundeswehr.
Welle: Erdball - F104-G Starfighter

DTS? Don't know if it supports Sybase, but could be worth a look....

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

I'm going to look into that. Thanks!

--
Ich bin Joachim von Hassel, und ich bin Pilot der Bundeswehr.
Welle: Erdball - F104-G Starfighter

It worked. Sort of. Nothing but tables and data was copied. Triggers, indices and other bells and whistles were not copied.

I wish modern commercial databases had SQL dumping facilities (or at least mention them in the docs - I can't find them). Because I know I could probably hack up a perl script to convert most of the Sybase SQL into MS SQL. I know one can script tables and such in both Sybase Anywhere and MSSQL, but it doesn't script the data, just the definitions. I need both. Sigh | :sigh:

--
Ich bin Joachim von Hassel, und ich bin Pilot der Bundeswehr.
Welle: Erdball - F104-G Starfighter

Hmmmm...have seen something similar before...

Seem to remember it being something to do with dependencies. The triggers and sprocs are dependent on the tables, but think that MSSQL tries to DTS them all at the same time, so if a table isn't present, then DTSing a trigger will fail.

You could try doing just tables, indices and data, then the other objects as a second run....

Good luck! Wink | ;)

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Pls I need anybody who can help with a data provider that I can use for accessing DB2 data on an AS400 machine using ADO.NET
I currently have one that works with ADO but when I use same connection sring with ADO.NET it throws an IUnknown error

Tanx in anticipation

Hi, I had the same problem, the solution I used is here.
You can also try IBM Client Access. (we have version 5 release 2)
"IBM DB2 UDB for iSeries OLE DB Provider"
With this I managed to set up the connection through vs ide (server explorer) and view the data.
Connection string I got from UDL file:
Provider=IBMDA400;Password="";Persist Security Info=True;User ID=ME;Data Source=xxx.xxx.xxx.xxx;Protection Level=None;Initial Catalog=AVISPAR2;Transport Product=Client Access;SSL=DEFAULT;Force Translate=65535;Default Collection="";Convert Date Time To Char=TRUE;Catalog Library List="";Cursor Sensitivity=3

Good luck!!

something like Profier of SQL Server, thanks!

No such luck!! Access (since it's essentially a single user desktop DB) generally has no need for such niceties.

You may however be able to turn on ODBC tracing....go to datsources dialog, specify a log file and click Start Tracing. Once you have the info you need MAKE SURE YOU STOP THE TRACE - this is V Important - tracing will cripple the performance of your machine, and is persistent across reboots....

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

thanks!yes, ODBC tracing can do for ODBC-Based App, but how can i monitor the app based on ADO or OLEDB?

hi,
currently i doing on merge replication.I need to configure my computer as publisher and distributor.When configure the publishing and distribution using wizard i get this message:

Error: 18483 : Could not connect to server "se13" because "distributor_admin" is not defined as a remote login at the server.

i try to reinstall my sql server service pack 3 and the message disappear but come out a new message:

Error:14114: '(null)' is not configured as a distributor

"se13" is my computer name.

thanks. Smile | :)

Hello all,

When I close a web form that has a connection to my SQL Server, I am not seeing the memory process close in task manager (of the SQL Server). I am using the "open late close early" theory of database connections. I am using the "close" method for my database connections. Is there any automated utility that will shut down these processes? I thought when the user was disconnected from the database, the memory process would automatically shut down.

Any suggestions, thoughts, or ideas?

TYIA,
lonelobo

Suspect that what you are seeing is the SQLServer process itself...you really don't want this to be shutting down (do you?)...if it shuts down, then the server shuts down, and when you attempt to connect, it will fail.

When you CONNECT to a SQL Server DB, you just connect to a running instance of it, not (a la Access) run up a process etc.

Hope this helps, and apologies if have been barking up wrong tree!

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Hi All, I am reporting with Crystal Reports for .net off of a live access database up on a web server. The database design sort of replicates a parts inventory and the way it was designed almost replicates recursion. For example there is a parts table and each row in the table has a part number, details, and a style number. The style number tells us whether it is a level 1 part, level 2 part, or a level 3 part. This database design supports an infinite number of nesting of parts within other parts using another table linked to the parts tables called packages with two links to the parts table. I am using ADO.Net datasets to report off of I am looking for a way to report on for instance all the level 1 or level 2 parts in a level 3 part or any combination like that. Has anyone ever created a crystal report that recursively looked up records in a database. Thanks in advance my head is going to explode soon I think
Mad | :mad:

Frank

SHIKAKA

I think you should use Hierarchical Grouping option in the Report Menu.