|
I've recently used PostgreSql in a project and I'm very satisfied with the results. It's an open source db system. It isn't a file based db and the footprint is not low but it's very robust.
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Thank you all,
Now I can do some research.
|
|
|
|
|
Your example is incomlete and makes no sense in line of the question subject.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
I have a table as such:
word
-----
word - varchar
word_count -int
The word is unique.
How do I do a random selection based on the influence (weight) of word_count?
-Steven Hicks
CPACodeProjectAddict
|
|
|
|
|
One solution is to add another column to the table, which will store the sum of the word_count field up to and including the current record, then find the maximum value, generate a random number between 0 and the maximum, and select the first record greater than that number.
In MSSQL, it would look something like this:
-- Add the column to the table:
ALTER TABLE dbo.Word ADD WeightScaled int NULL
GO
-- Update the new column
DECLARE @Counter int SET @Counter = 0
UPDATE
dbo.Word
SET
@Counter = WeightScaled = @Counter + Word_Count
UPDATE
dbo.Word
SET
WeightScaled = Null
WHERE
Word_Count <= 0
GO
-- Select a random record
Declare @MaxValue int, @RandomNumber float
SELECT
@MaxValue = MAX(WeightScaled)
FROM
dbo.Word
SET @RandomNumber = rand() * @MaxValue
SELECT TOP 1
Word
FROM
dbo.Word
WHERE
WeightScaled >= @RandomNumber
ORDER BY
WeightScaled
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
And drop the column after I'm done with all the random selects?
(The data should be updatible, which means the word_count is subject to change)
-Steven Hicks
CPACodeProjectAddict
|
|
|
|
|
If you're going to be issuing the random selects again, it would probably be better to leave the column in place, and run the code to update the column after each update or batch of updates to the table. (If you're expecting the updates to be more frequent than the selects, you could update the column before each select instead.)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
The updates are done in the word collection process.
The random selects are done after the word collectino process. But it dose have the possibility that the word collection process may occur again.(after the random selects are done ofcourse)
-Steven Hicks
CPACodeProjectAddict
|
|
|
|
|
Hello,
I am having a small problem:
I am using Helper classes for DataAccess.
i have a scenario where i cannot use parameterised query due to some reasons.
Now the problem is when someone search with ' in text; it breaks ans throws exception.
I replaced this with '' but its not working.
Can anyone help on this ?
Regards,
Asim
|
|
|
|
|
How is it not working, is it still throwing an exception ? Or not finding what you expect ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Yes, it's still throwning exception.
any solutions ?
Regards,
Asim
|
|
|
|
|
Most people find the quickest way to getting a solution is to report the exact problem, by telling is the type and message of the exception, and providing a code snippet.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
I need to get the relationships that exist between the tables in a database. The database structure is unknown to me , i.e at runtime the program connects to any database be it Access or SqlServer. From that database i get the info. related to the tables such as the columns that exist in it, their datatypes,etc but not the table relationship to other tables. For SQL databse i have used this approach - I have used SQLDMO to get the table names. Then generating a select statement e.g: "SELECT * FROM Tablename" and then using DataAdapter.FillSchema method i get the structure of the table. But i also need the realtionships that exist between the tables. Could any one help me??! (Using C#)
|
|
|
|
|
Here's something that will make you scratch your head!
I have a table that has a UniqueIdentifier as its primary key, but also have an autoincrement column (InvoiceNumber) that is not part of the primary key.
This table contains about 3000 records. But for an unknown reason (UFOs???), the last 1500 rows or so have DUPLICATE autoincrement value! The incrementation is done automatically by SQL Server (MSDE).
Here's an example of the values in the InvoiceNumber column:
1
2
3
4
4
5
5
6
6
Any idea what it could be? How can I fix/prevent this?
Thank you very much for your help!
Carl
|
|
|
|
|
Either you have IDENTITY_INSERT on and a client is inserting bad values, or someone's used DBCC CHECKIDENT to reset the identity seed.
To fix, build a UNIQUE index on the column. Any client trying to insert a non-unique value will get an error. Use DBCC CHECKIDENT to check and fix the identity seed value. Ensure IDENTITY_INSERT is turned off.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Looks like someone's been playing with the database structure 
There WAS an index there, but the index has been removed and that person probably changed the identity seed.
I thought that an identity column was always unique... your information helped me find out where the problem was.
Thanks!
|
|
|
|
|
Does any body have idea where i call below Sql syntax in Qurey Builder or Enterprise Manager?
And How to Call In C#.Net
=======================
EXEC GenerateScript @server = 'Server_Name',
@uname = 'User_Name',
@pwd = 'Password',
@dbname = 'Database_Name',
@filename = 'c:\File_Name.sql'
==============================
Muhammad Waqas Butt
|
|
|
|
|
EXEC GenerateScript 'Server_Name',
'User_Name',
'Password',
'Database_Name',
'c:\File_Name.sql'
will work in query builder
SqlCommand sqlCommand = new SqlCommand("GenerateScript");
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@uname ", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, "User_Name"));
etc.
try
{
sqlCommand.Connection = connection;
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
dataAdapter.Fill(dataSet);
return dataSet;
}
finally
{
connection.Close();
}
Should be a fair representation of how to run a stored proc in C#. Assuming you have a connection object.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
This could be one of those questions that is really silly which is why i haven't found an answer to it.
If i have a DataReader with a 10,000 odd recordset should i be able to insert, delete and update the same table via another connection.
i.e. i have some code that is syncronising a couple of tables and it sorts two datareaders, a master and a slave, and the goes through both either skipping, adding, amending or deleting rows in the slave table. It seems to be working but i'm having a problem with an update that is using 5 different keys, i.e.
update x ... where k1=1 and k2=2 and k3=3 and k4=4 and k5=5
it seems to be hanging the database sometimes, not all the time.
so im trying to isolate what could and couldn't be happening and one thought is that if im modifying the table as im going along that could be screwing up the datareader...?
So when a datareader performs it's query, and you start looping through what data are you actually looping through? does .net create pages of data and repeatedly queries the database, or is there something else happening?
thanks paul
|
|
|
|
|
A DataReader works in 'firehose' mode: SQL Server uses TCP's flow control mechanisms to control the flow of data to the client. I believe the SQL Server ADO.NET Provider uses a packet size option (which I think defaults to 4KB) to decide when to listen for more data.
SQL Server holds the locks associated with the command until all the data has been received by the client - which in effect is when DataReader.Read returns false. The default transaction isolation level is READ COMMITTED, which causes Shared locks to be held. When a Shared lock is held, an operation requiring an Exclusive lock (such as an update) waits until all Shared locks on that data are released. Your second connection may end up waiting for your first, if a record that is to be updated has already been read by the first connection.
I would recommend replacing your DataReader with a DataTable and using a SqlDataAdapter to fill that table. The DataTable is a cached copy of the source data - DataAdapter.Fill does not return until all data has been retrieved. You can then reuse your first connection to perform the updates.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank Mike for the info. That locking is something i was seeing. Im not sure a DataTable is a good idea for me with the amounts of information i have to deal with. I'll probably do the whole thing in chunks bit by bit.
thanks
paul
|
|
|
|
|
Hi,
I have a question about a sql query I need to do. I have a table with 3 fields, userID, entryDate and value. My query will have a date parameter called targetDate. What I was hoping to do was retrieve a single row for each user where the entryDate is the most recent entry for that user prior to the targetDate parameter. If there are no entries prior the the targetDate for the user, no rows will be returned for that user, if they have multiple entries with entryDate < targetDate, obviously only the most recent will be returned. If anyone was able to help me with the SQL for this, that'd be great.
Thanks,
Matt
|
|
|
|
|
You have do do this with a two pronged approach. Use can use max() and group by clause to get the newest date (only) for each customer, then you can join back on the table to get the rest of the data:-
select yourTable.* from (
select userID, max(entryDate) as entryDate
from yourTable
group by userid
) k
inner join yourTable
on yourTable.userId = k.userId and yourTable.entryDate = k.entryDate
using System.Beer;
|
|
|
|
|
Thanks Jon!
That works perfectly 
|
|
|
|
|
Thanks Jon,
That works perfectly 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
