|
select * from TableName order by Column1 asc,Column2 desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
This is not working mannn!!!!
It affects only one column.
It consider only 1st order not for another.....
there is no efect for 2nd column...
Believe Yourself™ ™
|
|
|
|
|
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi Blue
I have already checked this one this states the same as you told early.
This is not working mannn... it takes for 1st order only.
Believe Yourself™ ™
|
|
|
|
|
Thanks Blue You were correct, I was making mistake at some point.
Actually It is two time sorting.
I m trying on this. If not worked then will ask you.
Thanks for soln.
Believe Yourself™ ™
|
|
|
|
|
Gandalf - The White wrote: If not worked then will ask you.
You are welcome
Gandalf - The White wrote: Thanks for soln.
You are welcome
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
While I don't like to contradict, I have yet to come across a database that doesn't sort as specified. Obviously the records will be sorted by the second column within the order of the first.
SELECT id, lastName, title, age
from #employee ORDER BY
title DESC, age ASC;
gives
id lastName title age
3 Pandit Web Designer 24
4 Anchor Web Designer 27
14 Gibson System Administrator 31
13 Lewis System Administrator 32
so lets start at the beginning. What database are you using? Where is your query and the results?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob.
Your answer is correct but I have to go for more sorting on this result.
I m using SQl server 2005.
I have to make sorting on the result I got from this query and that I had to do with single query.
Believe Yourself™ ™
|
|
|
|
|
Sorry, I don't understand what you are trying to do. If you could post some sample data and the result you want maybe I can help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I would like to be able to copy the data in a table from an old version of a sqlce database into the current version of the sqlce database. The datatable must be replaced ie the row ID numbers must remain the same.
I thought that writing the old datatable to to an xml file and then reading that data back in would do the trick but I can't get it to work.
Has anyone achieved what I wish and if so, how did they go about it as I've tried a few alternatives without any luck.
Thanks,
Glen Harvy
|
|
|
|
|
Glen Harvy wrote: The datatable must be replaced ie the row ID numbers must remain the same.
Is the RowID an identity column? If so, disable Identity by setting SET IDENTITY INSERT [Table Name] ON and import the data from the older database.
Glen Harvy wrote: I thought that writing the old datatable to to an xml file and then reading that data back in would do the trick but I can't get it to work.
That statement is devoid of any useful information. Is there any particular error you face?
|
|
|
|
|
Thanks for your help.
Your suggestion would seem to resolve my problem however I am not aware of a way to achieve my end result without using datasets and datatables as in the following code:
string tempDBLocation = MainForm.dataDirectory + @"Temp\";
string tempXMLFile = tempDBLocation + @"tempData.xml";
File.Copy(MainForm.dataDirectory + "my.sdf", MainForm.dataDirectory + "my_org.sdf", true);
File.Copy(tempDBLocation + "my.sdf", MainForm.dataDirectory + "my.sdf", true);
this.configTableAdapter1.Fill(this.adminDataSet1.Config);
this.adminDataSet1.Config.WriteXml(tempXMLFile, XmlWriteMode.WriteSchema);
File.Copy(MainForm.dataDirectory + "my_org.sdf", MainForm.dataDirectory + "my.sdf", true);
File.Delete(tempDBLocation + "my.sdf");
File.Delete(MainForm.dataDirectory + "my_org.sdf");
this.configTableAdapter1.Fill(this.adminDataSet1.Config);
for (int i = 0; i < this.adminDataSet1.Config.Rows.Count; i++)
{
this.adminDataSet1.Config.Rows[i].Delete();
}
this.configTableAdapter1.Update(this.adminDataSet1.Config);
this.adminDataSet1.AcceptChanges();
this.adminDataSet1.Config.ReadXmlSchema(tempXMLFile);
this.adminDataSet1.Config.ReadXml(tempXMLFile);
this.configTableAdapter1.Update(this.adminDataSet1.Config);
this.adminDataSet1.AcceptChanges();
I would prefer not to use ado.net but am unable to locate how to achieve what I want without it.
It seems I have a lot more research to do.
Thanks for your assistance.
Glen Harvy
|
|
|
|
|
Here's my latest effort:
string tempDBLocation = MainForm.dataDirectory + @"Temp\";
string tempXMLFile = tempDBLocation + @"tempData.xml";
string conString = "Data Source=" + tempDBLocation + "my.sdf;Persist Security Info=False";
SqlCeConnection conn = new SqlCeConnection(conString);
SqlCeCommand command = conn.CreateCommand();
conn.Open();
string tableName = "Config";
SqlCeDataAdapter mySqlCeDataAdapter = new SqlCeDataAdapter("select * from " + tableName, conn);
DataTable myDT = new DataTable(tableName);
mySqlCeDataAdapter.Fill(myDT);
myDT.WriteXml(tempXMLFile,XmlWriteMode.WriteSchema);
conn.Close();
conString = Properties.Settings.Default.mycourtsConnectionString.ToString();
SqlCeConnection conn2 = new SqlCeConnection(conString);
SqlCeCommand command2 = conn2.CreateCommand();
conn2.Open();
SqlCeDataAdapter mySqlCeDataAdapter2 = new SqlCeDataAdapter("select * from " + tableName, conn2);
DataTable myDT2 = new DataTable(tableName);
mySqlCeDataAdapter2.Fill(myDT2);
SqlCeCommandBuilder ccmdBuilder = new SqlCeCommandBuilder(mySqlCeDataAdapter2);
for (int i = 0; i < myDT2.Rows.Count; i++)
{
myDT2.Rows[i].Delete();
}
myDT2.ReadXmlSchema(tempXMLFile);
myDT2.ReadXml(tempXMLFile);
command2.CommandText = "SET IDENTITY_INSERT " +tableName + " ON";
command2.ExecuteScalar();
mySqlCeDataAdapter2.Update(myDT2);
conn2.Close();
And of course the Update command now generates this error:
The column cannot contain null values. [ Column name = ID,Table name = Config ]
It makes sense of course but how do I get around this - or must I generate an insert command and do it that way.
Glen Harvy
|
|
|
|
|
Check this this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi guys
I got a weird error which iam struggling to fix for soo many days. I need u guys help. please help me. I got a web service in win 2003 server which access the database to do insert operation (dataadapter.update(dataset)). This webservice is called by 2 applications. one is client server application(winform poped up in browser at client side takes input file and processes to a dataset and calls webservice to insert into database using (dataadapter.update(dataset)). the second application(asp.net app) rests in same server calls webservice and does same operation inserting into database using same statement works fine). The problem arose when i migrated from .net 1.1 to .net 2.0 framework.
The error message iam getting is:
"System.data.odbc.odbcexception ERROR [07002] [IBM][iSeries Access ODBC Driver]Wrong number of parameters".
Please help me. iam inserting it into db2 database(as400) . The server has v5r4 client access software. If u need further info please let me know.
Note:
I caught the dataset just before the insert operation and wrote it to a xml to check the condition of dataset(to verify any damage in network). It looks fine and i used same dataset to build insert statements to enter manually into database. So i believe there is some security or access provider issue.
|
|
|
|
|
I have a SQL table that I am importing that has 2 columns title and description of type text that need to be converted to varchar(max). none of the 2 have more than 4000 chars and its English only.
I created a data flow task with a transformation to convert both columns to string [DT_STR] 4000. My destination has both columns as varchar(MAX).
I get a very informative error of Cannot create an OLE DB accessor. Verify that the column metadata is valid.
When I GIS most suggest the conversion which I am already doing.
Anyone been through this?
modified on Monday, August 4, 2008 2:30 PM
|
|
|
|
|
If your destination column is Varchar(MAX), you've to convert it to DT_WSTR in your transformation.
|
|
|
|
|
I tried this but then I get an error "Error at Insert Articles [Data Conversion [3463]]: Conversion from "DT_TEXT" to "DT_WSTR" is not supported".
|
|
|
|
|
I figured it out I had to convert to text stream and then use derived column add as new column (DT_STR,4000,1252)(DT_TEXT,1252)[Description]
What a pain!
|
|
|
|
|
Hi guys,
Is there any way to have SQL Server return the table name as part of the column please? In my example, I would like to do something akin to...
SELECT * FROM [Address]
and have SQL Server return a result set containing columns with "Address." prepended to the column name.
Address.ID, Address.Line1, Address.Line2
Many Thanks for any help you can provide!
|
|
|
|
|
Try this
select ID as 'Address.ID', Line1 as 'Address.Line1' ....
But why do you need it? Column names are unique in a select.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi!
Thank you for your reply.
Unfortunately explicitly naming the columns is the only way I've found to do this so far. I would however like to be able to do something like SELECT * FROM Address and retrieve the columns as [Address].[name].
Do you know of a way of doing this without explicitly aliasing the column names please?
|
|
|
|
|
Sorry, no. I assume your query sometimes joins several tables, otherwise it would be a trivial thing to auto-generate your query.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
That is correct. In some instances I will be selecting fields from a view and would like to simply have
SELECT * FROM vAddress AS Address
which would output
Address.ID, Address.Line1, Address.Line2
Thank you for your help. Looks like I'm gonna have to explicitly state the aliases in my procs.
Cheers
|
|
|
|
|
I dont know why you need to do this but you can just
SELECT 'Address.' + ID AS ID, 'Address.' + Line1 AS Line1
FROM [Address]
If your trying to use this to make it relational on the client side this is a bad way to do it.
|
|
|
|