|
I am very new to sql and databases.....
I am currently using Access and I have a database with about 1000 rows. I am trying to select 50 RANDOM rows at a time, how do you write an access sql statement for that? So far I have this....
SELECT*
FROM Computer
ORDER BY RND();
It returns all the results, but I just want to set it up so I can have it return as many as I want.
Please Help!
Steve Vondras
Red Lake Nation
MIS Tech
svondras@redlakenation.org
|
|
|
|
|
In sql you may try this:
select top 50 * from computer Order By NewId()
Gautham
|
|
|
|
|
Thanks for the quick response, It is still giving me the whole list it isn't cutting it down to 50.
Steve Vondras
Red Lake Nation
MIS Tech
svondras@redlakenation.org
|
|
|
|
|
Hi,
I have the following query. my message field is of datatype text. But I want to grag just first 50 characters + ...
from that "message" field. How can I do that.
The following is my sql statement
select tck.ticketid,tckmsg.message
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid and tcktstatusId = 1
seema
seema
|
|
|
|
|
Seema,
You can use the 'substring' function in sqlserver 2000.
select substring(column_name, start_position, length)
so your query might be:
select tck.ticketid,substring(tckmsg.message, 0, 50)
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid and tcktstatusId = 1
Gautham
|
|
|
|
|
you can use following :
-----------------------
CAST (tckmsg.message AS VARCHAR(50))
your query should be the following
select tck.ticketid,CAST (tckmsg.message AS VARCHAR(50))
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid and tcktstatusId = 1
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
Hi, i have a class library which implements a data access tier. As i used the automated designer for the dataset it automatically added an app.config in the class library with the string to the database:
<connectionstrings>
<add name="AppDatabase.Properties.Settings.dbConnectionString"
="" connectionstring="Data Source ="D:\Chris\db.sdf"" providername="Microsoft.SqlServerCe.Client">
I then use this data access class library in my main application, however how can i change and update this connection string and point it to a different file at runtime? I would like to be able to select a different database but it seems (from breakpoints in the generated table adapter code - InitConnection()) the default values are being called (in this case from the xml app config above).
How can i get, or indeed override the connection string setting for the database, whilst at the same maintain all the other settings which are in place for the main app (ie user preferences, persistent settings etc)? I tried creating a setting in the main default application of dbConnectionString, but it always picks up the one from the AppDatabase class library namespace.
Thanks in advance,
Chris
|
|
|
|
|
Move the settings from the app.cofig in your data access library to the app.config in the main applicaiton. .NET Framework will search first for a configuration file as the same name as the assembly, if not found it will use the app.config, then machine.config
only two letters away from being an asset
|
|
|
|
|
do you mean physically open the config files? i copied:
<connectionStrings>
<add name="AppDatabase.Properties.Settings.dbConnectionString"
connectionString="Data Source =D:\Chris\Debug\db.sdf"
providerName="Microsoft.SqlServerCe.Client" />
</connectionStrings>
into the app.config of the main app and all seems to be good. I guess when accessing the properties and settings through the property manager i will have to use the full namespace reference 'AppDatabase.Properties.Settings.dbConnectionString' rather than just dbConnectionString?
How come microsoft does not have an automated way to copy all the class library settings into one composite settings file when the class libaries are referenced from the main application?
Thanks so much for the help
|
|
|
|
|
Yes will need to use 'AppDatabase.Properties.Settings.dbConnectionString' because that is the name, not 'dbConnectionString'
<connectionStrings>
<add name="AppDatabase.Properties.Settings.dbConnectionString"
connectionString="Data Source =D:\Chris\Debug\db.sdf"
providerName="Microsoft.SqlServerCe.Client" />
</connectionStrings>
ConfigurationManager.ConnectionString["dbConnectionString"] will search the configuration file of the same name as the assembly if it exists, then the one in the next folder, if any, then web.config, etc.
So if the above entry was in myassembly.dll.config and this one was in app.config (or web.cofig)
<connectionStrings>
<add name="dbConnectionString"
connectionString="Data Source =D:\Chris\Debug\db.sdf"
providerName="Microsoft.SqlServerCe.Client" />
</connectionStrings>
Then ConfigurationManager.ConnectionString["dbConnectionString"] would use the app.cofing entry not the one in myassembly.dll.config.
only two letters away from being an asset
|
|
|
|
|
Mark,
you are most informative thank you. I have one final question, when i edit the projects settings in visual studio, it automatically adds the fully qualified namespace for the setting, is this default behaviour? Not that this is a problem it just means i have to fully reference the entire setting name, or i can edit the setting name and remove the AppDatabase.Properties.Settings to leave just dbConnectionString which indeed works just fine as you say.
Many thanks for your time you have saved me a big headache!
Chris
|
|
|
|
|
I couldn't find a menu item on Enterprise Manager for viewing database logs.
I meant the .ldb file [ correction: should be .ldf file ]. I would try using notepad except the file is over 700 mb.
Thanks.
-- modified at 18:27 Wednesday 20th June, 2007
|
|
|
|
|
The Jet database engine uses .ldb file information to prevent users from writing data to pages that other users have locked and to determine who has other pages locked. If the Jet database engine detects a lock conflict with another user, it reads the .ldb file to get the computer and security name of the user who has the file or record locked.
In most lock conflict situations, you receive a generic "Write conflict" error message that allows you to save the record, copy it to the Clipboard, or drop the changes you made. In some circumstances, however, you receive the following error message:
Couldn't lock table ; currently in use by user <security name=""> on computer <computer name="">. .
Regards,
Satips.
|
|
|
|
|
Sorry, I meant ldf file, not ldb file. I was referring to SQL Server database.
|
|
|
|
|
You don't.
The log contains all the information required to roll forward or back to a point in time. If you want to see what the state of the database was at a particular point in time, back up the transaction log (WITH NO_TRUNCATE to keep the current log data, so you don't affect your regular backup sequence), then restore the last full backup under a different name WITH NORECOVERY , and restore the log backup WITH RECOVERY, STOPAT and the time you want to recover up to.
The log contains binary log records indicating the operations that were performed, plus the data of any rows deleted, the new data for any rows added, and both the before and after versions of rows updated. For information on the log format used by SQL Server 2000, see the book "Inside SQL Server 2000". There's an updated set of books for SQL Server 2005 - presumably you want the Storage Engine one.
|
|
|
|
|
A trigger was mysteriously added to one of our tables. What I would like to find out is who did it (nobody is admitting it). Looks like there is no easy way to find that out?
|
|
|
|
|
Hi,
I'm working with SQL server 2005 express and Visual Studio 2005 and have a problem.
I have a Database named DataBase1 and I want to backup it.
I define a SQLcommand and after do:
myCommand.CommandText = @"BACKUP DATABASE DataBase1 TO DISK = 'C:\MyFiles\DB_BAckup.bak'";
myCommand.ExecuteNonQuery();
But It doesn't work, I get an exception "Could not locate entry...."
What does it mean?
Thanks in advance.
|
|
|
|
|
|
I'm researching the possibilities of embedding a DB inside a windows form application. I have done this before with a access DB, but not a MS sql db. I noted that this might be possible using ms sql express, but does this create a sql server instance on the client's pc? I just want to be able to install the app on a clients pc without undergoing a sql 2000/2005 server setup.
Is this possible, and how do i go about it?
Thanx ion advance.
Regards,
Tintin
|
|
|
|
|
Sql Server is called 'Server' for a reason
Sql Server Express is just a cut down version, it's still a server though and will install an instance of the server.
If you want a good free embeded databse take a look at SQLite[^]. It's very good although it has nowhere near the functionality of Sql Server.
If you can't do without the features Sql Server provides then you can always do a silent install of Express during your own applications setup. Theres plenty of commandline arguments you can pass to fully automate and customise the installation.
|
|
|
|
|
Thats what I thought, although @ microsoft express website they say:
SQL Server 2005 Express Edition is the next version of MSDE and is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005. Free to download, free to redistribute, free to embed, and easy for new developers to use immediately....
What do they then mean by embeddable?
Will look at Sqlite as well..
Regards,
Tintin
|
|
|
|
|
SQL Server databases are loaded by the SQL Server service. SQL Server Express Edition is no different - it creates an instance by default named SQLEXPRESS. With SQL Server 2005, you can specify a database file to attach in the connection string, so it isn't permanently associated with the SQL Server instance.
Microsoft have now ported SQL Server 2005 Mobile Edition back to the desktop properly and called it SQL Server Compact Edition 3.1. This is an in-process database engine like Access/Jet. Find out more here[^]. However, this does not use the same format data files as the other SQL Server editions. (It's really only branded 'SQL Server', it has nothing in common).
|
|
|
|
|
Hi,
I have to extract a blob field from a sql database.
please help.
Thanks
|
|
|
|
|
|
When I run my program,I get these errors and I don't know how to solve it:
1.A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
2.Cannot open database "NORTHWND.MDF" requested by the login. The login failed.
dataAdapter.Fill(dataset,"Customers"); This is where the exception is thrown
I use windows authentication and the owner of database is current user.
Can anyone please help?
|
|
|
|