|
Maybe it's just me, but I can't stand how Microsoft implemented XML in SQL Server, syntax wise, I mean, when it comes to selecting table data to XML. I always thought something like this made much more sense:
Select
Convert(xml, '<?xml version="1.0" encoding="utf-8" ?>
<Employees>
#foreach emp in emp begin#
<Employee id="emp.EmployeeID#">
<Manager id="#emp.BossID#">#emp.BossName#</Manager>
<Addresses>
#foreach addr in emp.Address1 begin#
<Address id="#addr.AddressID#">#addr.Address1 + " " + addr.City + ", " + addr.State + " " addr.ZipCode#</Address>
#end#
</Addresses>
</Employee>
#end#
</Employees>
') As EmployeeDoc
From
(
Select
emp.ID As EmployeeID
,emp.Name As EmployeeName
,boss.ID As BossID
,boss.Name As BossName
,addr.ID As AddressID
,addr.Address1
,addr.City
,addr.State
,addr.ZipCode
From
Employees emp
Left Join Employees boss
On emp.ManagerID = emp.ID
Inner Join EmployeeAddresses empaddr
On emp.ID = empaddr.EmployeeID
Inner Join Addresses addr
On empaddr.AddressID = addr.ID
Where
emp.TerminationDate Is Null
) tbl;
I'm still working on learning the current syntax, so maybe this is a bit of a soapbox, but I feel better now anyways. 
Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan
Portland, Oregon, USA
modified on Monday, October 20, 2008 3:32 PM
|
|
|
|
|
i am MSc(computer science) student. i want overview of database design of "books searching". And internal view of database how we can search books through keywords.
|
|
|
|
|
|
How do i save image into sql server using vb.net
thanks
|
|
|
|
|
you can use this[^] 
Regards
|
|
|
|
|
Hi,
I have a USERS table
in which have the following fields
USER_ID,
COMP_ID,
PASSWORD
CREATED_BY
CREATED_DATE
DELETED_BY
DELETED_DATE
here created_by will contain that user id who is creating this user,
but i want your opinion on deleted_by and deleted_date
Is it feasible put this two field in this table or i put it in another table.
“You will never be a leader unless you first learn to follow and be led.”
–Tiorio
"Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
|
|
|
|
|
It all depends on whether you are really deleting or just flagging as a delete - obviously if its a real delete there will be nothing to update!
Bob
Ashfield Consultants Ltd
|
|
|
|
|
No, it is flagging as delete thru another field ACTIVE.
“You will never be a leader unless you first learn to follow and be led.”
–Tiorio
"Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
|
|
|
|
|
Put it in the same table where ACTIVE is present.
|
|
|
|
|
In which case use the original table.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I have a problem with database.For my application i am using .net and sql server 2000. i am having database for my application in my local system.if i shutdown the system database is not working.i tried to import database from local system to server system.but it is not woking properly.is there any solution to import database.Please help me.
Thanks
pavani
|
|
|
|
|
The easiest way is to back up your local DB and restore it on the server.
You may be able to copy the .MDB and log files to the server and then use attach.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i want to store gujarati(language)data in database sql server2005 and i want to retrieve that data from database using asp.Net 2.0 (2005)
how can i do above thing???
please provide answer and suggestion ASAP it is very important....
|
|
|
|
|
It is not clear what it is you want to do. Are you wanting to store strings that are in a different language (encoded in Unicode perhaps) and then retrieve them for display? Or are you wanting to change the sorting rules for the database to follow the conventions of a different language?
What approaches have you tried, and what problems have you encoutered?
|
|
|
|
|
I see this is your first post on Code Project.
Take some time to read through the following, excellent article: Some guidelines for posting questions in the forums[^].
After that try to solve the problem with the tools you've got (help, google, msdn, etc). When you have a specific problem you cannot solve, post a question providing enough information about your (specific) problem and people try to help you.
About your question: Start with selecting the correct collation to your database. Refer to: Windows Collation Name (Transact-SQL)[^].
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Member 4627609 wrote: i want to store gujarati(language)data in database sql server2005
Use Nvarchar type for your column. Nvarchar can keep Unicode characters.
|
|
|
|
|
Hello All,
I am sorry for bothering you guys with this but I did perform so "due diligence" to find the answer to this and I am just stuck. Any help thrown my way will be greatly appreciated.
I have an ODBC datasource on a PC that connects to a proprietary db. I use Access (link tables) and the datasource to retrieve and examine data.
My question is: I have SQL Server 2005 and I would like to know if there is a way that I could somehow use the ODBC connection to connect with SQL Server. I am certain that I could do a lot more with the data linked to SQL as opposed to using Access.
I hope I have made my intentions clear and again, thank you (whomever you are) for the assistance.
Best regards,
F
|
|
|
|
|
Certainly, SQL Server and all the major DBs support ODBC, try these guys clickety[^]
They will have ODBC examples on there somewhere.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
i'm having a very strange problem on my code, i'm on VS2008 with .net 3.5 and with SQLServer 2005, i have the 'correct' data tracked with the profiler tool;
SELECT x.columnName, x.columnOrdinal, x.columnTypeID, x.columnMaxLength, x.columnPrecision, x.columnScale, x.columnAllowNull, x.columnIsIdentity, x.columnFKTable, Case When y.Name Is Null Then 'No' Else 'Yes' End As columnPrimaryKey
FROM ( select sc.name columnName, sc.colid columnOrdinal, sc.xusertype columnTypeID, sc.length columnMaxLength, sc.xprec columnPrecision, sc.xscale columnScale, sc.isnullable columnAllowNull, (sc.status & 0x80)/0x80 columnIsIdentity, sor.name as columnFKTable, sc.colorder
FROM [teste].[SYS].[syscolumns] sc
INNER JOIN [teste].[SYS].[sysobjects] so ON sc.id=so.id
LEFT OUTER JOIN [teste].[SYS].[sysforeignkeys] sfk ON so.id=sfk.fkeyid AND sc.colid=sfk.fkey
LEFT OUTER JOIN [teste].[SYS].[sysobjects] sor ON sor.id=sfk.rkeyid
WHERE SO.Name='Centro' ) AS x
LEFT JOIN
( SELECT c.name, c.colid
FROM sysindexes i
INNER JOIN [teste].[SYS].[sysobjects] t ON i.id = t.id
INNER JOIN [teste].[SYS].[sysindexkeys] k ON i.indid = k.indid AND i.id = k.ID
INNER JOIN [teste].[SYS].[syscolumns] c ON c.id = t.id AND c.colid = k.colid
WHERE i.id = t.id AND i.indid BETWEEN 1 And 254 AND (i.status & 2048) = 2048 AND t.id = OBJECT_ID('Centro')) As y
ON x.columnName = y.Name
ORDER BY x.colorder
the result will have this row of data:
centroID 1 56 4 10 0 0 1 NULL Yes
the problem it's on the last column, here is a 'Yes' and on VS, while debugging right after the dataadapter.fill i get a 'No', obviously with the same query.
Here is the code i used to get the data from the DB:
public void GetColumnsForTableFromDatabase(string tabela)
{
string queryForColumns = "SELECT x.columnName, x.columnOrdinal, x.columnTypeID, x.columnMaxLength, x.columnPrecision, x.columnScale, x.columnAllowNull, x.columnIsIdentity, x.columnFKTable, Case When y.Name Is Null Then 'No' Else 'Yes' End As columnPrimaryKey \r\n";
queryForColumns += "FROM ( select sc.name columnName, sc.colid columnOrdinal, sc.xusertype columnTypeID, sc.length columnMaxLength, sc.xprec columnPrecision, sc.xscale columnScale, sc.isnullable columnAllowNull, (sc.status & 0x80)/0x80 columnIsIdentity, sor.name as columnFKTable, sc.colorder \r\n";
queryForColumns += "FROM [" + cmbBoxDatabases.Text + "].[SYS].[syscolumns] sc \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] so ON sc.id=so.id \r\n";
queryForColumns += "LEFT OUTER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysforeignkeys] sfk ON so.id=sfk.fkeyid AND sc.colid=sfk.fkey \r\n";
queryForColumns += "LEFT OUTER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] sor ON sor.id=sfk.rkeyid \r\n";
queryForColumns += "WHERE SO.Name='" + tabela + "' ) AS x \r\n";
queryForColumns += "LEFT OUTER JOIN \r\n";
queryForColumns += "( SELECT c.name, c.colid \r\n";
queryForColumns += "FROM sysindexes i \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] t ON i.id = t.id \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysindexkeys] k ON i.indid = k.indid AND i.id = k.ID \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[syscolumns] c ON c.id = t.id AND c.colid = k.colid \r\n";
queryForColumns += "WHERE i.id = t.id AND i.indid BETWEEN 1 And 254 AND (i.status & 2048) = 2048 AND t.id = OBJECT_ID('" + tabela + "')) As y \r\n";
queryForColumns += "ON x.columnName = y.Name \r\n";
queryForColumns += "ORDER BY x.colorder \r\n";
sqlConn.ConnectionString = getConnectionString();
SqlCommand sqlCom = new SqlCommand(queryForColumns, sqlConn);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCom.CommandText, sqlConn);
if (DS.Tables.Contains(tabela))
DS.Tables[tabela].Clear();
sqlConn.Open();
sqlDA.Fill(DS, tabela);
sqlConn.Close();
}
I'm lost here, but on my tests i got the correct result if replace:
queryForColumns += "( SELECT c.name, c.colid \r\n";
queryForColumns += "FROM sysindexes i \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] t ON i.id = t.id \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysindexkeys] k ON i.indid = k.indid AND i.id = k.ID \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[syscolumns] c ON c.id = t.id AND c.colid = k.colid \r\n";
queryForColumns += "WHERE i.id = t.id AND i.indid BETWEEN 1 And 254 AND (i.status & 2048) = 2048 AND t.id = OBJECT_ID('" + tabela + "')) As y \r\n";
with this:
queryForColumns += " ( SELECT 'centroID' as name, 1 as columnID) As y ";
i know this is strange, but it works, it leads me to think that this has something to do with the 'LEFT JOIN'.
Thanks for the help in advance,
Chapas
|
|
|
|
|
Have you grabbed the exec from Profiler and run it in QA/MS.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
thanks for your reply,
yes i've taken the result obtained from the profiler, during the the debug of my program, and paste it to QA/MS and result was the expected, in the debugger was not the same doe.
|
|
|
|
|
Perhaps something odd is happening caused by the way you are building up the query string (not particularly good practice...). Try building up the query in a StringBuilder using the Append method, and pass it to the SqlCommand Object using StringBuilder.ToString() , and omit the "\r\n" business (SQL doesn't need them, if you want them to ease debugging, then use Environment.Newline constant instead). Also consider trimming the variable strings before using them in the query, just to insure there are no stray leading or trailing spaces.
|
|
|
|
|
Rob Graham wrote: Environment.Newline
I love this place, you learn something new every day, I have the control \r\n characters coming from a VB background, this looks like the old VBNewLine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks a lot,
this seems to solve my issue, using the StringBuilder and the Append method.
Don't know how to explain this issue, but the data from the DB got correctly to my app.
|
|
|
|
|
please, I want simple table in SQL to training and query, and thank you.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
