|
Hi
I am working in sql 2000 database and I want to retrieve a select statement and generate an xml. Everything goes perfect except the fact that I can't add a general root for the entire result from dhe generated xml. My code is like this:
SELECT FirstName , LastName
FROM Employees
FOR XML AUTO, ELEMENTS
and the result for this is
<Tab;e>
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>
but what I want is that this xml to look like this
<Table1>
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>
</Table1>
thanks for any advice.
p.s. I meantioning again that im workin in sql 2000Qendro
|
|
|
|
|
I use neither 2000 or xml but have you tried the following...
SELECT FirstName , LastName
FROM Table1.Employees
FOR XML AUTO, ELEMENTS
it seems logical, I have no idea if it will work!Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Isn't that what the OP had 
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Not quite, I added Table1. to employees. I was hoping the dot notation would work.
I note you gave him the correct answer so I learn something yet again!Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is totally illogical unless the Table employees exists in the Table1 schema; and why would you even have a schema called table1?
|
|
|
|
|
Take a look at the XML supplied by the OP, note the hierarchy of nodes, see where he added Table1 as the PARENT node to the employee records, it it not logical that the parent node may prefix the employee node when defining the location in the xml data.
SilimSayo wrote: unless the Table employees exists in the Table1 schema
Besides what has schema got to do with XML data?
SilimSayo wrote: and why would you even have a schema called table1?
Schema, what schema, who mentioned schema.
If you think a schema called table1 is dumb, hang around here for a while, you see some doozies in the forums.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you were to use a fully qualified name when selecting from table employee, you would write
Select * from mydatabase.myschema.employee.
If you use a partially qualified name you would write:
Select * from myschema.employee
or
Select * from mydatabase..employee
So Table1.Employee is interpreted as a table called Employee in a schema called Table1.
|
|
|
|
|
 The OP is using XML. I know what a schema is I have been working with databases for 20+ years. What I have not worked with is XML and they are NOT the same thing. You're discussion about schema is totally irrelevant to the OPs question.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I understand what you are saying but you're not getting my point at all. I am saying that the "compiler" would think that Table1 is a schema because it prefixes the table name employee. In other words, don't write Table1.employee because Table1 would understood differently by the "compiler". If you're worked for 20 years plus with a database, this should be pretty much common sense. I don't know much about how to generate XML but if do a little googling, you should be able to find the answer especially for someone with 20+ years in the IT industry.
|
|
|
|
|
SilimSayo wrote: but you're not getting my point at all
You are quite correct in that, I was only focusing on the question of the OP, not on a general discussion of how the SQL was going to interpret the statement.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I have a project which has been dormant for a year but was just fine when I last used it.
Now, one year on, with nothing changed, when I publish and install, it insists on re-installing SQL Server Express even though it is already installed.
No changes made to the bootstrapper, which used to work just fine ....
And just to add to the fun, the previous Dotfuscator stored settings also fail to obfuscate the code.
I could understand it if I'd been tinkering but this literally hasn't been touched since the last publish a year ago!
Cheers,
Rich
|
|
|
|
|
Oooh nasty
So your app has not changed, now it comes down to your environment, you know all those lovely security and product updates courtesy of every vendor with software on your platform, one (or more) of them has probably changed something.
I would think that rebuilding your install may make a difference. You may want to look at changing your references away from 'specific version'Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Morning All,
I have queries that I developed and run in SQL Server that export the results to a Tab delimited text file (no header row). That text file is linked to an Ms Access database using a text file link spec.
The same query run in Server 2005 and Server 2008 results in text files that have the same data but structural differences. I have to have a separate spec for 2005 and 2008.
Also suprising is that the 2008 version of the text file approx half the physical size.
The output of the processing in Access is exactly the same with either version of the text file.
Any ideas in what the difference(s) is between 2005 and 2008? Creating two link specs can be pain if the text file contains dozens of fields.
Thanks for any Help
Gene
|
|
|
|
|
I have a situation where i need to optimize the query to perform better. possibly a solution.
Here is the situation.
I have a table with the following fields(I am putting only those are significant to the scenirio)
WorkItems[Table]
StartDate
EndDate
Stage
CompletedDate
I need to calculate a value named status according to various conditions involving the above parameters.
Like If StartDate < getdate()
Status = Green
etc..
I created a function called CalculateStatus Passing all the above parameters and added one computed colum in the WorkItems table to call this function.
Since column is non deterministic i cant add a index to this column.
Now when i blindly do a select of status the same is getting calculated very fast
The issue is when i use this computed column with aggragate function like
1) SELECT count(Status) FROM WorkItems
Or when doing a group by
2) SELECT Count(WorkItemId),Status FROM WorkITems
Group BY Status
Could you please guid me how i could optimize the situation so that i can make things faster.
Thanks in advance for your valuable time
Sreehari ARanghatThanks
Laddie
Kindly rate if the answer was helpful
|
|
|
|
|
Instead of a computed column, use a normal column and use an after update/insert trigger on the table that calls your function and sets the column value for the row in question. You then also have the advantage of adding an index on this column.  Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I received the following error (see line *********error***********) when trying to transfer data from excel to sql using the code below. The sql table is in the database and columns all match the store procedure, does anyone have any idea what creates this problem.
Thanks in advance, Michael
Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable).
void ExcelToSqlBulkCopy(String strPath, String strFile, String strTableName)
{
strExcelStoredProcedure = "SELECT ID,City,State FROM [Cities$]";
String excelConnectionString = y;
String sqlConnectionString = z;
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand(strExcelStoredProcedure, connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "[MyDatabase].[dbo].[" + strTableName + "]";
bulkCopy.WriteToServer(dr); *********error***********
}
}
connection.Close();
}
}
|
|
|
|
|
Does the table in strTableName exist in the database MyDatabase? And is dbo the owner of the table?Wout Louwers
|
|
|
|
|
bulkCopy.DestinationTableName = strTableName
is sufficient, de database is in the sqlConnectionString
|
|
|
|
|
Hi All,
Does anyone know the sql statement/switch or anything of that nature that I can use to create a case-sensative password field in my sybase DB?
Thanks,
Mel
|
|
|
|
|
|
Passwords should not be case-sensitive, but you're welcome to if you want. You should not be storing clear-text passwords.
I recommend hashing the password (I use SHA-1). That should also allow case-sensitivity. Two birds; one stone.
|
|
|
|
|
That sounds perfect for what I'm looking for.
Do you have an example or a link?
Thanks
|
|
|
|
|
Here are my hashing methods:
public static string
Hash
(
string Subject
)
{
return ( Hash
(
System.Text.Encoding.Unicode.GetBytes ( Subject )
,
new System.Security.Cryptography.SHA1Managed()
) ) ;
}
public static string
Hash
(
string Subject
,
System.Security.Cryptography.HashAlgorithm Provider
)
{
return ( Hash
(
System.Text.Encoding.Unicode.GetBytes ( Subject )
,
Provider
) ) ;
}
public static string
Hash
(
byte[] Subject
)
{
return ( Hash
(
Subject
,
new System.Security.Cryptography.SHA1Managed()
) ) ;
}
public static string
Hash
(
byte[] Subject
,
System.Security.Cryptography.HashAlgorithm Provider
)
{
System.Text.StringBuilder result =
new System.Text.StringBuilder ( Provider.OutputBlockSize ) ;
foreach
(
byte b
in
Provider.ComputeHash ( Subject )
)
{
result.Append ( b.ToString ( "X2" ) ) ;
}
return ( result.ToString() ) ;
}
|
|
|
|
|
| How do you mean 'passwords should not be case-sensitive'. I think they should be! But your'e right about not storing them as clear text. They should be hashed indeed. Wout Louwers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
