Re: XML Save and Load to SQL Server 2005 database. - Database Discussion Boards

Re: regarding foreignkey data

kheer26-Aug-09 22:01

kheer

26-Aug-09 22:01

ok thanks for ur advice

Merging multi results into one row

Wouter Coinless24-Aug-09 9:12

Wouter Coinless

24-Aug-09 9:12

ID					Name			Access1	Access2	Access3	Access4	Access5 	
918a6fb8-573f-4744-b4de-019bc8322ce4	Gast			False	False	False	False	True
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc	Administrator		True	True	True	True	False

I have multiple results for 1 user. In the example I want the results to get to one row, with the final result (True, True, True, True, True).
So the query has to go through all the results and check if the Access Right is True or False. Is one of them is True the result of the column is true.

I tried to use Temp Tables but im stuck Frown | :(

Anyone has an idea?

I use MSSQL

Re: Merging multi results into one row

Luc Pattyn24-Aug-09 9:39

Luc Pattyn

24-Aug-09 9:39

That doesn't look the way I think it should. I'd rather have a table with more rows and fewer columns, and because of normalization I'd suggest two tables, one with names, one with access rights:

ID					Name
918a6fb8-573f-4744-b4de-019bc8322ce4	Gast
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc	Administrator

ID					AccessNumber	AccessRight
918a6fb8-573f-4744-b4de-019bc8322ce4	1		False
918a6fb8-573f-4744-b4de-019bc8322ce4	2		False
918a6fb8-573f-4744-b4de-019bc8322ce4	3		False
918a6fb8-573f-4744-b4de-019bc8322ce4	4		False
918a6fb8-573f-4744-b4de-019bc8322ce4	5		True
etc.
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc	4		False

which would be much easier to query (you can join on ID; you can select on right=true, etc), as well as easier to extend when new access numbers become necessary.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Re: Merging multi results into one row

Eddy Vluggen24-Aug-09 9:51

Eddy Vluggen

24-Aug-09 9:51

The MAX operator won't work on bits, but you can cast them to integers;

SELECT MAX(CAST(Access1 AS INT))
      ,MAX(CAST(Access2 AS INT))
      ,MAX(CAST(Access3 AS INT))
      ,MAX(CAST(Access4 AS INT))
      ,MAX(CAST(Access5 AS INT))
  FROM dbo.TestTable

You'd have to specify a decent WHERE-clause, though.

Another option would be the use of a function, that takes the ID as a parameter and which returns those five fields. It could then be used in a view Smile | :)

-- Edit --

..you should really consider Luc's post carefully, a simpeler design makes a lifetime of simple maintenance Cool | :cool:

"Every normal man must be tempted at times to spit on his hands, hoist the black flag, and begin to slit throats."

-- H.L. Mencken, US editor (1880 - 1956)

Re: Merging multi results into one row

Wouter Coinless24-Aug-09 9:54

Wouter Coinless

24-Aug-09 9:54

Yes i will consider that Smile | :)

Havent thought about it that way.

XML Save and Load to SQL Server 2005 database.

ashwath197924-Aug-09 1:55

ashwath1979

24-Aug-09 1:55

Hi All,

Please tell me how to do the following:

XML save and load of data to SQL Server 2005 database?

Thanking you in advance,

Regards,
Ashwath Hegde.

Re: XML Save and Load to SQL Server 2005 database.

Henry Minute24-Aug-09 2:18

Henry Minute

24-Aug-09 2:18

Look at the MSDN documentation for either DataSet or DataTable. Both of them have ReadXml and WriteXml methods.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: XML Save and Load to SQL Server 2005 database.

N a v a n e e t h24-Aug-09 6:19

N a v a n e e t h

24-Aug-09 6:19

You can have your column type as XML and SqlDbType[^] enum supports XML type. You can write something like,

using (SqlConnection conn = /* ... */)
using (SqlCommand cmd = new SqlCommand("insert into your_table VALUES(@xml_content)", conn))
{
     cmd.Parameters.Add("@xml_content", SqlDbType.Xml).Value = new SqlXml(reader);     
     // other code
}

SqlXml can take a Stream or a XmlReader instance.

For reading data, work with the SqlDataReader.GetSqlXml()[^] method. SqlXml.ReadOuterXml() will give you a string representation of the XML.

Smile | :)

Navaneeth

How to use google | Ask smart questions

Re: XML Save and Load to SQL Server 2005 database.

ashwath197925-Aug-09 1:40

ashwath1979

25-Aug-09 1:40

Can you please give the complete code snippet so that i can understand fully.

Re: XML Save and Load to SQL Server 2005 database.

N a v a n e e t h25-Aug-09 2:06

N a v a n e e t h

25-Aug-09 2:06

ashwath1979 wrote:
Can you please give the complete code snippet so that i can understand fully.

That is laziness. What have you tried and where you are stuck?

Navaneeth

How to use google | Ask smart questions

Found some strange code. [moved]

Gandalf_TheWhite24-Aug-09 1:23

Gandalf_TheWhite

24-Aug-09 1:23

Here's a strange code which I found while maintaining D'Oh! | :doh:

Convert(varchar, Select Convert(varchar,getdate(),106),106)

Do anyone have idea why this is written like this?
Actually it is used in a stor_proc where this phrase is used for comparing two dates.

Do you think is it going to make any difference when we don't convert it again?

Believe Yourself™ $Shucks | :-\$ ™

modified on Monday, August 31, 2009 12:40 PM

Re: Found some strange code.

PIEBALDconsult24-Aug-09 4:08

PIEBALDconsult

24-Aug-09 4:08

Wrong forum; try "general database".

Transferring SQL Express data

hairy_hats24-Aug-09 0:07

hairy_hats

24-Aug-09 0:07

Hi all,

Is it possible to move SQL Express databases between users and computers?

TIA.

I hope you realise that hamsters are very creative when it comes to revenge. - Elaine

Re: Transferring SQL Express data

Henry Minute24-Aug-09 0:29

Henry Minute

24-Aug-09 0:29

Yes.

At least, between computers is fairly easy (either copy or move), never tried between users but I would assume that it would also be relatively easy.

If you have SQL Server Management Studio, use the Search option from the Help Menu and search for move database.
If not, Google for sqlserver move database.

Either way you will get loads of hits, as always, some more useful than others. Smile | :)

Re: Transferring SQL Express data

hairy_hats24-Aug-09 0:36

hairy_hats

24-Aug-09 0:36

Thanks Henry, I wasn't sure if the restrictions in the Express version meant that the databases were limited to one user or computer.

I hope you realise that hamsters are very creative when it comes to revenge. - Elaine

Re: Transferring SQL Express data

Henry Minute24-Aug-09 5:51

Henry Minute

24-Aug-09 5:51

Coincidentally I have just found this[^] article on Database Journal about attaching Databases. Just in case you aren't aware of the method.

Database design questions

Saksida Bojan23-Aug-09 22:51

Saksida Bojan

23-Aug-09 22:51

Here i have a few question:

Is Xml Good for large databases?
Shoud i use XmlDocument or XmlReader and XmlWriter?
If i chose XmlReader and XmlWriter how would i manage to quickly delete data?

I am developing OpenSource application, that uses plugin based database.
Is it good to connect to remote server(MySQL) using .NET Connector?

I Also thinking of creating server app that will be on server betwen mysql and Client.
Is TCP good protocol or shoud i consider UDP. Currently i am reading about TCP and i am thinking if i should send XML data through TCP.
Does this data need to be encoded (Base64) or encrypted (AES)?

Re: Database design questions

Mycroft Holmes23-Aug-09 23:34

Mycroft Holmes

23-Aug-09 23:34

Probably belongs in the Design and Architecture[^] forum!

xml is NOT good for large databases, xml is a descriptive platform it is NOT a database. Some have used it for minute data requirements where it may be valid but certainly not for any valume.

This is dependant on winforms/web application (among other things) you do not say.
We use OLEDB (SQL) connections for winforms

The server app is probably a Web Service - there is plenty of articles and samples around for these.
XML vs Binary transport layer is an old and well used argument, you need to do some research to determine which will suit your needs best.

Encryption is dependant on your data sensitivity, general info may not require encryption where authentication almost certainly does.

Re: Database design questions

Saksida Bojan23-Aug-09 23:51

Saksida Bojan

23-Aug-09 23:51

Thank you for your anser. I didn't notice that forum, i won't post there that will create double post.

As i have said i think using MySQL .NET connector (Witch clearly says i am using .NET Framework), not OLEDB and i didn't start as web service only as windows service

Re: Database design questions

N a v a n e e t h24-Aug-09 6:33

N a v a n e e t h

24-Aug-09 6:33

Saksida Bojan wrote:
Is Xml Good for large databases?

What do you mean by that? Keeping all data in XML format? If yes, it is a bad idea.

Saksida Bojan wrote:
Shoud i use XmlDocument or XmlReader and XmlWriter?

I am not sure that these 3 classes can be compared as each of them serves different purpose.

XmlDocument is an in-memory DOM representation of the XML and other two are used for reading and writing. If you want to persist the XML in memory and need to manipulate it(querying with XPath etc..), choose XmlDocument. XmlDocument can also make the data editing and writing easy. If you have a custom data structure that has to be created/persisted to XML, prefer XmlReader and XmlWriter.

Saksida Bojan wrote:
If i chose XmlReader and XmlWriter how would i manage to quickly delete data?

Use XmlDocument in such case and call its Save() method for writing to XML file.

Saksida Bojan wrote:
Is it good to connect to remote server(MySQL) using .NET Connector?

No - if it is a stand-alone application. Wrap the database access behind a web service/WCF.

Saksida Bojan wrote:
I Also thinking of creating server app that will be on server betwen mysql and Client.
Is TCP good protocol or shoud i consider UDP. Currently i am reading about TCP and i am thinking if i should send XML data through TCP.

This is exactly what a WCF or web service layer does. WCF uses TCP communication and web services uses SOAP messages.

Smile | :)

Navaneeth

How to use google | Ask smart questions

Re: Database design questions

Saksida Bojan24-Aug-09 7:28

Saksida Bojan

24-Aug-09 7:28

N a v a n e e t h wrote:
Saksida Bojan wrote:
Is it good to connect to remote server(MySQL) using .NET Connector?

No - if it is a stand-alone application. Wrap the database access behind a web service/WCF.

It is dll that has connector and uses socket to connect dirrectly to MySQL database. it is OpenSource and it does not require to install unlike it is with ODBC

N a v a n e e t h wrote:
This is exactly what a WCF or web service layer does. WCF uses TCP communication and web services uses SOAP messages.

I Don not know what WCF is, but i will look into it.

What is diffrent by Windows Service and Web service? Can Windows Service uses protocol such as TCP or UDP?

Thanks for the information, it realy helps

Re: Database design questions

N a v a n e e t h24-Aug-09 16:56

N a v a n e e t h

24-Aug-09 16:56

Saksida Bojan wrote:
What is diffrent by Windows Service and Web service?

Windows services are normal applications that runs all the time and controlled by a service manager. It needs to be installed using installutil utility. On the other hand, web services follow request/response architecture. It resided on a server and serves requests. Communication is done on XML format following SOAP protocol.

Saksida Bojan wrote:
Can Windows Service uses protocol such as TCP or UDP?

Yes.

Navaneeth

How to use google | Ask smart questions

Oracle Equalent for Systypes and Syscolumns...

sandhya1423-Aug-09 22:48

sandhya14

23-Aug-09 22:48

what is Oracle equalent for Systypes,Syscolumns...?

if Systype - All_Types,Syscolumns -All_Tab_Columns

then what is the eualent for

name,xtype,xusertype in All_types and

xtype,ID,name in All_Tab_columns...

please anybody give me a solution for this?

Thanks in advance...

get all parent tables name with child from sql 2005

Abdul Rahman Hamidy23-Aug-09 18:26

Abdul Rahman Hamidy

23-Aug-09 18:26

Dear All, I am trying to list parent and child tables from sql 2005, but i could not find the solution.
well, How can i list all parent tables first then child tables from sql server 2005 in a single statement?

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: get all parent tables name with child from sql 2005

Mycroft Holmes23-Aug-09 19:34

Mycroft Holmes

23-Aug-09 19:34

You may want to look into the sys objects. This view seems to have some interesting info for you sys.foreign_key_columns it is in 2008 but I think it may be there in 2005 as well.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.