|
Quite similar to Oracle then, which still lacks the TOP function.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I Have two Tables like Code and Details...
In Code(SrNo,Name) Table Records Like A,B,C etc
In Details(SrNo,CodeName)Table looks like
SrNo A B C
1 0 1 0
2 0 0 0
3 1 0 0
4 0 0 0
5 0 0 1
6 1 1 1
7 0 0 0
If I add another Name in Code Table Like 'D'. Select query for Details table also contain column 'D'.
So Select query like this
DECLARE @Code NVARCHAR(MAX)
SET @Code = (SELECT ('CONVERT(VARCHAR,'+Name+ ')'''+Name+''',') FROM Code ORDER BY SrNo FOR XML PATH(''))
SET @Code = SUBSTRING(@Code,0,LEN(@Code))
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT '+@Code+' FROM Details'
EXEC (@SQL)
Output:I need to check any '1' in columns and srno in order by without mentioning column names. It has to be like below. How can I do in SqlServer2005
SrNo A B C
6 1 1 1
1 0 1 0
3 1 0 0
5 0 0 1
2 0 0 0
4 0 0 0
7 0 0 0
|
|
|
|
|
You can refer to columns by ordinal as select foo from bar order by 1, 2 .
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
you mean to say by Srno. If so its not my requirement.
|
|
|
|
|
So elucidate as it did appear that's what you asked for.
In any case you can use select foo from bar order by 2 or 3 or 1000
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
modified on Monday, August 2, 2010 6:26 AM
|
|
|
|
|
Has anyone used this product? Is there anything out there free and better?
I did some research but chose this product because;
1) It was free
2) Fairly lightweight
3) C# API
4) GNU General Public License which if I understand correctly allows me to just about anything with it. Any gotchas there if I go commercial with my product?
5) Integrates with VS2005 and VS2008
6) Ease of use (Seems to be drop-dead easy?)
Guess I answered my own question unless there is something that just absolutely sucks about it?
Thanks,
Mike
|
|
|
|
|
Never heard of it; got a link?
|
|
|
|
|
|
I just read through their Wikipedia page. It looks like it only persists objects, it seems it doesn't support SQL, it might not allow you to use queries to create different subsets of data.
Looking at their site now...
|
|
|
|
|
PIEBALDconsult wrote: it seems it doesn't support SQL
That's one of the things I didn't like about it, but they have a nice Native Query alternative that looks to be pretty sophisticated.
I've only been working with it for a couple of hours and have found a couple of things I don't like about it but can live with.
Also the documentation is decent so that helps a lot, at least for me.
|
|
|
|
|
If it does what you need, then use it.
|
|
|
|
|
PIEBALDconsult wrote: If it does what you need, then use it.
It looks like it will do what I want but I haven't used many embedded DB's so the only reason I posted was so if I got 3/4 of the way down the line and started finding problems, restrictions, etc. then maybe someone with more experience could save me the frustration and point me to a better EDB.
Anyway thanks for your input
|
|
|
|
|
for an embedded DB it's simply the fastest approach.
Not saying it's faster than SQL Lite or SQL Compact just that it's a lot
faster to create the app. No need to map or use ORM tools in order to fill
the gap between the ObjectModel and the DB Layer/Model.
I used it a lot as a small embedded DB and it's more than fine.
I wouldn't risk buying it and using it as a DB Server.
But for small/local/embedded DBs it just works.
I bug
|
|
|
|
|
Thanks that's exactly the feedback I was looking for. I'm just using it as a local DB as I didn't want users of application I am developing to have to install SqlExpress or other server just to run this app.. Just needed something fast, lite and easy to learn/use.
Thanks for the input,
Mike
|
|
|
|
|
you're welcome
I bug
|
|
|
|
|
Hi!
I"ve to add values to a field but the previous values should be deleted. Which query do I've to use?
|
|
|
|
|
T.RATHA KRISHNAN wrote: I"ve to add values to a field
A single field has a single value. The UPDATE -command would replace the current value of a field with a new value.
I are Troll
|
|
|
|
|
I'll ask it anyway, having already tried Google and MSDN.
I'm fiddling with a SQL Server app to track substation equipment - one of several projects I'm working on. I've got a skeleton working on a local instance of SQL Server Express, but I'm thinking ahead to the day I deploy this on the company server, which has the full version of SQL Server installed. I don't want to manually recreate the database on the server - that way madness lies - and I don't want to Export/Import my existing database, as it's full of test cases I don't want to have to delete. I'm sure there's a nice, efficient way to create a database in SQL Server programmatically when my app runs for the first time, but I haven't a clue how to do it. Google returns a plethora of responses, enough to completely confuse me, and MSDN returns none of any relevance - the usual ratio.
Can someone point me to a tutorial of some sort that my aged brain can comprehend?
[EDIT]
While MSDN didn't help, I finally asked the right question in Google - which led straight back to Microsoft, of course:
using System;
using System.Data.SqlClient;
String str;
SqlConnection myConn = new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");
str = "CREATE DATABASE MyDatabase ON PRIMARY " +
"(NAME = MyDatabase_Data, " +
"FILENAME = 'C:\\MyDatabaseData.mdf', " +
"SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = MyDatabase_Log, " +
"FILENAME = 'C:\\MyDatabaseLog.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
Sorry about the formatting - that's Opera at work...
The article further states that, if I want just to use the Model database, eliminate all the crud and use simply, str = "CREATE DATABASE MyDatabase" .
I haven't tried it yet as it's getting rather late, but I think I'll play a bit later this week. I presume that I can then use CREATE TABLE and all its sundry relatives to fill out the details once this part works, and I've already found a way to enumerate the available SQL Server instances, which will allow the end user to decide which server to host the app on.
Assuming that this works as advertised, and since it doesn't seem to be common knowledge, I suspect that it will make a decent article or tip.
By the way, the article is located at: support.microsoft.com[^]
[/EDIT] We now return you to our regular programming...
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
modified on Wednesday, August 4, 2010 1:47 AM
|
|
|
|
|
Roger Wright wrote: I'm sure there's a nice, efficient way to create a database in SQL Server
Not programatically but there are 2 very simple ways to move a copy of your database to the production server. You can either backup your express version and restore it to the server or take a copy of the .mdf and .ldf files to the server and attach them. I think the attach one has the benefit of bringing the diagram which the backup/restore misses. Both are VERY simple processes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Have you ever tried coding this[^] into a SqlCommand? I think I may give it a try...
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I haven't done it in your kind of environment, but the following approach has worked for me elsewhere:
connect_to_server();
if (error_db_not_exist == open_database(my_db))
{
read_from_file(canned_sql);
run_query(canned_sql);
if (success != open_database(my_db))
}
where canned_sql is the appropriate set of creates, etc. Hopefully you can get this by querying system tables on your test server. An alternative is to export your test db, then delete all the *data* from that, leaving just the "structure".
HTH
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
It sounds to me that it's something similar to this[^] you need.
It creates a script for recreating a database on another server.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
That's worth a look, Jorgen - Thanks!
It just seems silly to me that I can't export my database to a XML file, then regenerate the database on the target server using that file. I've used db-based product before that create the database on installation, but I assumed there was a CREATE DATABASE function, in addition to the CREATE TABLE function used regularly. It never occurred to me that they were scripting a copy and import process in the installer.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I agree with Mycroft. I detach the database, copy the files, and reattach.
|
|
|
|
|
It sounds like scripting is the best way as you do not want everything to go. However I do not know if Express has scripting.
Another way would be to 1) backup the database 2) truncate tables 3) backup to second file then restore the second backup to new server.
|
|
|
|