Database

17-Jul-06 3:55

That doesn't mean they work for MS XML. I use dbnull all the time against oracle and SQL server, however.

Do you get a generated Specified attribute or element?

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Yeah but

Ashley van Gerven17-Jul-06 4:10

Ashley van Gerven

17-Jul-06 4:10

Ennis Ray Lynch, Jr. wrote:
Specified attribute or element?

Not sure what you mean by this... but I don't think so. The schema has this:

<xs:element name="TxnID" type="xs:long" minOccurs="0" />

Now normally for each row you would get the values- e.g.: <TxnID>2</TxnID>

But if it's NULL you don't get this element. And then when reading it into a DataSet it will automatically have the value: 0 - which is not what the original dataset had.

The solution seems to be to read the schema, set a default value, and then read the data. But is there a simpler solution? Is this a bug, or intentional behaviour by microsoft experts? .NET 2.0 seems to have the same behaviour.

"Nothing ever changes by staying the same." - David Brent (BBC's The Office)

~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid

If I use XSD.exe I get

Ennis Ray Lynch, Jr.17-Jul-06 4:27

Re: If I use XSD.exe I get

17-Jul-06 4:27

Using XSD.exe MS will give the following results which leads me to believe they do not like null.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

<xs:element name="sample">
<xs:complexType>
<xs:attribute name="maxQuantity" type="xs:int" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>

will generate

//------------------------------------------------------------------------------
// <autogenerated>
// This code was generated by a tool.
// Runtime Version: 1.1.4322.2032
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </autogenerated>
//------------------------------------------------------------------------------

//
// This source code was auto-generated by xsd, Version=1.1.4322.2032.
//
using System.Xml.Serialization;

/// <remarks/>
[System.Xml.Serialization.XmlRootAttribute(Namespace="", IsNullable=false)]
public class sample {

/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public int maxQuantity;

/// <remarks/>
[System.Xml.Serialization.XmlIgnoreAttribute()]
public bool maxQuantitySpecified;
}

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Ashley van Gerven17-Jul-06 4:43

Ashley van Gerven

17-Jul-06 4:43

Interesting, thanks. They must equate NULL with 'not specified'.

"Nothing ever changes by staying the same." - David Brent (BBC's The Office)

~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid

Thats what I think

Ennis Ray Lynch, Jr.17-Jul-06 4:45

Re: Query to Transfer Data?

17-Jul-06 4:45

really my sample probably doesn't directly apply to your situation but it does illustrate where MS seems to be going with nulls in general. I don't believe there XML validator even supports nillable.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Query to Transfer Data?

MudkiSekhon17-Jul-06 1:06

MudkiSekhon

17-Jul-06 1:06

I have two identical tables in which the columns and data types are of same type.
But one table is having some data whereas the other table is empty. Both are existing tables. I want to transfer/copy all the data from table1 to table2 by using a query.
How can I do this with a query

Thanks,
Sandeep S. Sekhon

Colin Angus Mackay17-Jul-06 1:28

Additonal Data for SQL Server Login/User

17-Jul-06 1:28

INSERT INTO Table2
SELECT * FROM Table1

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

dobrzan16-Jul-06 21:51

dobrzan

16-Jul-06 21:51

Hi,
How can I add additional data to SQL Server 2k login.
I have a database with logins/users for each person that can login I need to add extra information and create a relation (based user login or index) to other tables in my database.
Sample:
"document" has an author, author is a user that can login to SQL server.
How can I add relationship from "document" table to SQL server login?
Thanks for any suggestions how to do that.
If this is not a good idea to create a relationship between SQL server login and other tables, is there other solution for similar problem?

regards
dobrzan

Colin Angus Mackay16-Jul-06 23:05

16-Jul-06 23:05

dobrzan wrote:
How can I add relationship from "document" table to SQL server login?

What sort of relationship? You can, of couse, use commands like:

GRANT SELECT ON Document TO AuthorUserName

See also: GRANT[^] DENY[^] and REVOKE[^]

If this is not what you want you will have to explain what you want to do with this "relationship"

dobrzan17-Jul-06 4:09

dobrzan

17-Jul-06 4:09

What I need is to extend the basic login/account.
There is a number of accounts/logins that have access to database.
Each user (which is a login/account) can do some things depending on other data collected in database.
Example:
There is a "customer" table in database. Each "customer" can order "product". There is relationship between customer and product - lets say it's done by "order" table.
Now when customer connect to database (using his SQL Server login) I want to have a view that shows what "product" "customer" ordered. So there must be a some kind of way to link SQL Server login with entries from "customer" table.
It's quiet simple thing, when I log to codeproject I can track my posts. I can do that without SQL Server, but I want to create secure app - based on SQL Server Login authentication.

Colin Angus Mackay17-Jul-06 5:22

17-Jul-06 5:22

Since all this will be done through an application* you let the business logic in the application (or in stored procedures) filter the data returned to the user of the application.

dobrzan wrote:
but I want to create secure app - based on SQL Server Login authentication.

Current advice is not to use SQL Server Authentication unless you are connecting it to systems that are not windows based. You should be using trusted connections with Windows Authentication. What you are suggesting is less likely to secure your system because your users will have some direct access to your SQL Server (even if they don't know it). This access can be used by an attacker to compromise your system. All an attacker needs is an account to your application and you automatically give them an account to the datbase.

If you have a web application, it will probably try to connect to SQL Server using the ASPNET account if you let it use a trusted connection. Let it do that. Create your own user tables to handle the users of your application. Do not use the sysusers table in SQL Server, it is not designed for that purpose.

you are not going to give your customers direct logins to your SQL Server as that would be nuts - Imagine if Code Project added a login in its SQL Server for each of its 3million+ members - it also screws with connection pooling making it very inefficient

dobrzan17-Jul-06 20:47

dobrzan

17-Jul-06 20:47

So you suggest that I should use one login or windows based auth. to give users access to database (SQL Server) and distinguish each user by my own login logic in my app or web page.
Am I right?
Is this what others do?

Anyway thanks for help.

Colin Angus Mackay17-Jul-06 22:18

How to convert diagrams to SqlServer 2005?

17-Jul-06 22:18

dobrzan wrote:
So you suggest that I should use one login or windows based auth. to give users access to database (SQL Server) and distinguish each user by my own login logic in my app or web page.

Yes.

dobrzan wrote:
Is this what others do?

Of course.

Reza Raad16-Jul-06 21:46

Reza Raad

16-Jul-06 21:46

Hi i opened one of my sqlserver 2000 databases with sqlserver 2005 without any problem,
but i can't see my database diagrams,and i can't create new diagram there.

please help me.
thanks is in forward.

Re: How to convert diagrams to SqlServer 2005?

Colin Angus Mackay16-Jul-06 23:01

Re: How to convert diagrams to SqlServer 2005?

16-Jul-06 23:01

This is not supported. The SQL Server 2005 tools cannot show, modify or create diagrams in a SQL Server 2000 database.

Reza Raad17-Jul-06 0:03

Reza Raad

17-Jul-06 0:03

thanks for your attention and your reply Smile | :)

but if i create and modify tables form 2005 and create and modify diagram from 2000.does any problem occure or not problem?

thanks again.

Human knowlege belongs to the world

Why can't use 2 DataReader with same connection

Heinz_16-Jul-06 21:41

Heinz_

16-Jul-06 21:41

That's it, Why can't i use 2 DataReader with same connection? How can i do multiple queries from the same connection.

Thanks.

Re: Why can't use 2 DataReader with same connection

Colin Angus Mackay16-Jul-06 23:00

Re: Why can't use 2 DataReader with same connection

16-Jul-06 23:00

Heinz_ wrote:
How can i do multiple queries from the same connection

Just open a second identical connection to run the second command.

Heinz_17-Jul-06 9:26

Heinz_

17-Jul-06 9:26

Hi, i just did it but that second connection is what i have been trying to avoid. I remember that with ADODB in VB6 i was able to do multiple command and reads from the same connection.

Thanx, it works this way anyway.

Re: Why can't use 2 DataReader with same connection

Colin Angus Mackay17-Jul-06 9:32

17-Jul-06 9:32

Heinz_ wrote:
i just did it but that second connection is what i have been trying to avoid.

Why?

Heinz_ wrote:
I remember that with ADODB in VB6 i was able to do multiple command and reads from the same connection.

ADO.NET is not ADODB.

To clarify

Ennis Ray Lynch, Jr.17-Jul-06 9:37

17-Jul-06 9:37

If you have a connection you can execute multiple commands on it. Also, keep in mind, that idenctical connection strings will use the connection pool.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: To clarify

Colin Angus Mackay17-Jul-06 11:01

Re: To clarify [modified]

17-Jul-06 11:01

Ennis Ray Lynch, Jr. wrote:
If you have a connection you can execute multiple commands on it

But not at the same time, which is what the OP wanted to do.

Ennis Ray Lynch, Jr. wrote:
Also, keep in mind, that idenctical connection strings will use the connection pool.

Absolutely!

Heinz_18-Jul-06 8:25

Heinz_

18-Jul-06 8:25

Understood.

I was trying to use only one connection to gain performance but now i replaced the second connection with a subquery in a command of the original connection, check this tasty and parameter rich query:

"select SUM(t1.total - COALESCE(t2sum,0)) from ventas as t1 left join (select venta, SUM(cantidad_pagada) as t2sum from pagos_ventas where fecha_pago='" + CDC.Global.SetDBdate(hoy) + "' and metodo_pago<>1 and metodo_pago<>7 group by venta) as t2 on t2.venta=t1.id where t1.nula=0 and t1.pagada=0 and t1.fecha='" + CDC.Global.SetDBdate(hoy) + "'"

Bye.

-- modified at 15:29 Tuesday 18th July, 2006

Re: To clarify

Colin Angus Mackay18-Jul-06 12:31