Re: Which database format is good enough? - Database Discussion Boards

The stored procedure gives me what I want, which is a ranking with points for each store in the company based on certain fields, but I also want to compare this week's vs. the same week last year transactions. I thought I could just do another select command, but it's not working. The same I did use, worked fine on it's own - but when I insert it into the already existing code, it dies... any suggestions would be greatly appreciated.
Signed - newbie Wink | ;-)

=====SAMPLE STORED PROCEDURE====

SELECT TransID, StoreNo, StoreName, TotalPoints, TotalTransactions,
		RANK() OVER (ORDER BY TotalPoints DESC) AS Ranking
		FROM (
		-- SubQuery Starts here
		select WeeklyReports.TransID, WeeklyReports.StoreNo, Stores.StoreName, 
			-- Calculate TotalPoints first
			rank() over (order by abs(WeeklyReports.ActualVsTheoFoodCost) desc)+
			rank() over (order by abs(WeeklyReports.Cash) desc)+
			rank() over (order by WeeklyReports.SalesVsBudgetPercent)+
			rank() over (order by WeeklyReports.Overtime desc)+
			rank() over (order by WeeklyReports.Deletes desc)+
			rank() over (order by WeeklyReports.Puw112 desc)+
			rank() over (order by WeeklyReports.Puw58 desc)+
			rank() over (order by WeeklyReports.PuwDay desc)+
			rank() over (order by ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) desc)+
			rank() over (order by ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) desc) as TotalPoints,
			WeeklyReports.TotalTransactions,

This is where I run into problems. If I delete this section, the stored procedure works find, but I would really like it to work.

<br />
--SELECT DISTINCT WeeklyReports.TransID, WeeklyReports.StoreNo, WeeklyReports.WeekEndDate, WeeklyReports.TotalTransactions, <br />
--	WeeklyReportsLY.WeekEndDate AS LastYearDate, WeeklyReportsLY.TotalTransactions AS LastYearTransactions, <br />
--	(WeeklyReports.TotalTransactions - WeeklyReportsLY.TotalTransactions) AS TransPM<br />
--FROM WeeklyReports INNER JOIN<br />
--	WeeklyReports AS WeeklyReportsLY ON DATEADD(week, - 52, WeeklyReports.WeekEndDate) = WeeklyReportsLY.WeekEndDate<br />
--WHERE (WeeklyReports.TotalTransactions is not null and WeeklyReports.TotalTransactions <> 0 and<br />
--	WeeklyReportsLY.TotalTransactions is not null and WeeklyReportsLY.TotalTransactions <> 0)<br />

And this is the continued part of the good procedure.

        abs(WeeklyReports.ActualVsTheoFoodCost) as FoodCostVariance,
        rank() over (order by abs(WeeklyReports.ActualVsTheoFoodCost) desc) as ptsFoodCostVariance,
        abs(WeeklyReports.Cash) as Cash,
        rank() over (order by abs(WeeklyReports.Cash) desc) as ptsCash,
        WeeklyReports.SalesVsBudgetPercent as SalesVsBudgetPct,
        rank() over (order by WeeklyReports.SalesVsBudgetPercent) as ptsSalesVsBudget,
        WeeklyReports.Overtime,
        rank() over (order by WeeklyReports.Overtime desc) as ptsOT,
        WeeklyReports.Deletes,
        rank() over (order by WeeklyReports.Deletes desc) as ptsDeletes,
        WeeklyReports.Puw112,
        rank() over (order by WeeklyReports.Puw112 desc) as ptsPuw112,
        WeeklyReports.Puw58,
        rank() over (order by WeeklyReports.Puw58 desc) as ptsPuw58,
        WeeklyReports.PuwDay,
        rank() over (order by WeeklyReports.PuwDay desc) as ptsPuwDay,
        ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) as VoidPct,
        rank() over (order by ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) desc) as ptsVoidPct,
        ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) as AllVoidPct,
        rank() over (order by ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) desc) as ptsAllVoidPct,
        MAX(WeeklyReports.WeekEndDate) AS LastWeekEndDate, WeeklyReports.WeekEndDate
    from WeeklyReports
    inner join Stores on WeeklyReports.StoreNo = Stores.StoreNo
    group by WeeklyReports.WeekEndDate, Stores.StoreName, WeeklyReports.TransID,
        WeeklyReports.StoreNo, WeeklyReports.SalesVsBudgetPercent,
        ABS(WeeklyReports.ActualVsTheoFoodCost), abs(WeeklyReports.Cash),
        WeeklyReports.Overtime, WeeklyReports.Deletes,
        WeeklyReports.Puw112, WeeklyReports.Puw58, WeeklyReports.PuwDay,
        ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2),
        ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2),
        WeeklyReports.TotalTransactions
    having (WeeklyReports.WeekEndDate =
        (select MAX(WeekEndDate) as LastWeekEndingDate from WeeklyReports as WR2))
    ) as WeeklyStoreRanking
    -- SubQuery ends here
GROUP BY StoreNo, StoreName, TotalPoints, TransID, TotalTransactions
ORDER BY TotalPoints DESC

===END SAMPLE STORED PROCEDUE===

-rngd

I am working on combining two tables(tbl1,tbl2) from one database into one table(tbl3) in a seperate database. tbl1 and tbl2 have the same column names as tbl3 but they lack a location ID column which will be used in tbl3.

Currently I can get the information transferred over to tbl3 via SELECT * INTO tbl3 FROM tbl1--tbl2, tbl1 and tbl2 both have the same primary key column but will have different locationID columns in the new table. I'm pretty sure I will have to use the SET IDENTITY_INSERT ON for tbl3 but how can I keep track of which table the data is coming from? Also how can I set the location ID, as this coincides with the primary field.

<modify> I am trying to do all of this in a stored procedure. I was thinking of setting the tbl2 ID values to '100 + originalValue' as both tables currently have the same values in the ID column.

Thanks in advance for any help.

-- modified at 17:53 Thursday 5th October, 2006

A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder<br\> An American football fan -
Go Seahawks!

Lil Turtle

Add a new field to tbl3, say, Source varchar(10)

Insert Into tbl3
Select *, 'tbl1' /* Assuming Source field is the last field */
From tbl1

Insert Into tbl3
Select *, 'tbl2' /* Assuming Source field is the last field */
From tbl2

Farhan Noor Qureshi

I'm trying to find a good query tool; something I can type SQL into, preferably with a Query-By-Example grid. I'm connecting to several database types including Teradata, DB2, Sybase, and SQL Server 2005 (all through ODBC).

Currently I'm using MS Access 2003--I cannot believe this is the ultimate front-end query tool. Any recommendations?

--
"stop thoughting; start thinking, and quit trying to predict unpredictable thinking"
- dMoney (2005)

try these products: http://www.embarcadero.com/index.html[^]

Hi gurus,

I've created a few database programs using Access' mdb in the past. Now that I got a bigger project that may handles about 10 user (and more data in the same time), I am considering using a better solution. The problems I have with Access database are:
1. The password can be cracked quite easily.
2. It gets less responsive as the file get larger (and more likely to get corrupted, I heard).
3. It takes some time to save changes to the file, creating bottleneck and (sometimes) non-updated data for other users (I think this is the downside of file-based database, as processing is handled by each client).

However, what I like about it are:
1. All in one file, so it's easier to backup.
2. Password not linked to windows, so (supposedly) only I who can open it.
3. Free.

At first, I am considering using SQL Express 2005, but since it links the security to Windows login, I can't prevent users from opening the database (since the program I am creating will be run under Admin login).

So, I am asking for advice: what database should I use now? I have no experience with other database other than Access. I don't need fancy bells and whistles. Preferably:
1. Password is not linked to windows. So only I and the program who know the password.
2. Server-client type (processing is handled by the server machine), so that changes are saved instantly and network traffic can be reduced greatly.
3. Can handle at least 5 users concurrently.
4. Can be backed up easily.
5. Free (this might not be possible? Smile | :)

Thanks in advance for any input, it will be greatly appreciated.

Afik, sql express is just a cut down sql server instance, in which case, you can create a user within the database, and give it a password. You then need to use this user/pw combination to access the data.

Also, end users can't just open an sql express data file, as there is no "front end" like access. They will need to either query it through access / excel / other database access method. The closest thing to a front end is the SQL Server Management Studio Express (which is a free download from MS). In which case read up on "user permissions"

I suppose it depends upon who you want to keep out of the data, if it's paying customers, then you need to think why they are not allowed open access to their data (I've made that mistake before with ms access passwords), if it's internal users (ie in a business environment), then you may have policies / procedures in place to prevent users from accessing data that's not within their remit (i.e. they get sacked for for hacking).

Anyway, other databases you may want to look at are interbase or mysql, but I'd say that sql server or sql express are probably the best way to go.

ChrisB

Thanks for replying. But I am still not convinced that SQL Express is the best choice here. Let me explain a bit.

Let say this program is for stock management in a retail store. There are several employees that need to use the program: the cashier for selling the goods, the stock boy for stocking the goods, and all employess to clock in/out.

I don't see the point of having different login for each user, all of them need to access the database, only different tables. So I plan to prompt the user a password (managed from the [USER] table) upon opening the program to determine which functions/dialogs they can access (sales dialog, inventory dialog, clock in/out dlg, etc), which all actions will be recorded in databases.

Now I want to prevent any employee to open the database and, lets say, edit the remaining stock of an item (for whatever reason, good or bad). I want to have all changes to the database can only be made thru the program. I don't want them to be able to view it either, because it has tables with confidential information that not all employees should see (passwords, profits, etc.)

Since all employees need to access and edit the database on daily basis (at least thru clocking in/out), they all will need full access to the database, correct? And because only several computer connected thru simple LAN (without internet), I don't see the point of managing several logins, they all can use the admin login.

Now, from what I understand, I CANNOT do this with SQL Express. Since I need to give full access, they can use SQL Management Studio to access the database directly. Is there no way to separate windows login and database login, like MS Access?

As for Interbase and MySql, are their passwords strong enough or can be cracked by hacker as well? (Since they are not Microsoft's, I am assuming their password is not linked to Windows). Are they file-based or client-server (which need to be installed in a Window Server)?

Thanks a lot for any input.

Joe Smith IX wrote:
don't see the point of having different login for each user, all of them need to access the database,

The application uses the database's username / password security to access the database - individual user authentication (possibly based upon a username / password) can be left to you to create in the database tables if you require it.

I would suggest you read up on "mixed mode"[^] authentication (this is where you install sql server (express) in such a way as to require a valid password to access the database (this password is part of the dsn - e.g.

"Data Source=MyServer;Initial Catalog=MyDatabase;User Id=myUsername;Password=myPassword;"

myUsername and myPassword is not the individual users (i.e. person's) username, but the username you specify in the database and your application uses to access the data. It sounds like you currently have sql express installed in "trusted mode".

If I were to then try to connect to the database from outside your application (e.g. to query the data in excel, I would be required to enter the same username / password).

Joe Smith IX wrote:
they can use SQL Management Studio to access the database directly

I would question why any user would have SQL Management Studio installed on their machine, but with the above, they would need to specify the username and password.

You can specify multiple usernames and passwords - possibly one username only allows access to specific views and stored procedures, andother username has greater access to the data. Perhaps the more restricted username would be user for a "web" interface to your application and the less restricted is used for the gui.

The point being, that usernames don't need to map directly to "users as in people."

Joe Smith IX wrote:
As for Interbase and MySql, are their passwords strong enough or can be cracked by hacker as well?

It's not their password that is strong enough, it's your password that is strong enough. If you use the password "password" or "letmein" I dare say someone will hack it fairly quickly. The same goes for SQL Server.

Hope that's of use.

ChrisB

Are you saying that SQL Express can be set/installed so that it will prompt for a username/password, regardless of what Windows login it's running on? Then it's exactly the solution what I am looking for.

However, after reading the Documentation on the installation using "Mixed Mode," and applying it (uninstall and re-install it), it didn't work. I think the "sa" login is just an alternative from Windows login, because I can now login to SQL Management Studio using sa/password under SQL authentication AS WELL AS login using windows authentication with no password (under admin login).

Am I missing something here? Thanks.

Joe Smith IX wrote:
Am I missing something here? Thanks.

Only a little bit. You are just connecting to the database engine, not any particular database itself.

Look in the "Security\Logins" tab - this lets you see the users that sql server knows about. in a defauly install you have "Builtin\Administrators" this is the windows administrators group. You also have sa.

You can create a new user id in here, and give it a password (rather than associate it with a windows account / group). Also look at the user mappings tab.

Create a database, and go into the DatabaseName\Security\ section, this is where you grant access priveledges to individual user accounts. It may be that user1 can access db1 and db2, and user2 can access db2 and db3. They will all be able to connect to the database engine.

You can restrict access down to the table / view / stored procedure level (e.g. user1 may be able to access db1.table1, but not db1.table2).

And remember, it's the connection string in the application which provides the userID to the database engine. The engine will grant permission based upon the connection string.

I would suggest that if you're serious about preventing rogue / malicious access to you data, you invest in a good book on sql server security. (about 4 years ago I went on a 2 day course for sql2k, which was well worth it). Experiment. It has very robust security features that don't need to be linked with the windows login accoutn.

ChrisB

I think I finally understand a bit more about how these groups come into play. The easier solution for me now is just simply delete the builtin\administrators and builtin\user, leaving just sa and those ###groups. I believe this break the link to windows login all together, leaving only sa login as the possible SQL login.

Thanks a lot for all your help.

Joe Smith IX wrote:
I believe this break the link to windows login all together, leaving only sa login as the possible SQL login.

Yikes! Bad, very bad, very very bad idea.

Without droning on about how bad an idea it is to leave your login to 'sa' I will direct you to a tut that shows you how to change it.

http://blogs.msdn.com/sqltips/archive/2005/08/27/457184.aspx[^]

http://blogs.msdn.com/sqltips/archive/2005/10/10/479268.aspx[^]

Good luck!

"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky

Joe Smith IX wrote:
At first, I am considering using SQL Express 2005, but since it links the security to Windows login, I can't prevent users from opening the database (since the program I am creating will be run under Admin login).

Incorrect. Take the time to RTFM. You'll be pleasently surprised at how powerful and full featured the **FREE** advanced feature SQL Server 2005 Express edition is.

"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky

Aaargghhh, I'm getting confused...

Here is my SQL statement:
SELECT DISTINCT(pb.id), name, file, alttext, votecount, showcount, rating, link, lastwin_year, lastwin_month, MAX(place) AS place FROM pb LEFT JOIN pv ON pb.id = pv.id WHERE pv.lastwin_month = $current_month AND pv.lastwin_year = $current_year GROUP BY pb.id, pv.lastwin_year, pv.lastwin_month ORDER BY rating DESC LIMIT 0, 3

The problem is that it returns 0 rows if the table pv is empty. How do I make it return 3 pb rows with highest ratings if pv is empty?

If there are 3+ rows in pv, the SQL statement works perfectly. The only fields that are unique in the two tables are pb.id and pv.id. pv.lastwin_year, pv.lastwin_month are not unique.

Thank you,

phi
When Will It Be? You Decide... OMG | :OMG:

change left join to LEFT OUTER JOIN

Mike Lasseter

A good idea, but it doesn't solve the problem Sigh | :sigh:

I still get 0 rows back.

phi
When Will It Be? You Decide... OMG | :OMG:

pv.lastwin_month = $current_month AND pv.lastwin_year = $current_year

You must allow these two columns to be null as well example

(pv.lastwin_month = $current_month or pv.lastwin_month is null) and
(pv.lastwin_year = $current_year or pv.lastwin_year is null)

Mike Lasseter

... that will retrieve some rows, but what about the rows where lastwin_month != $current_month and lastwin_month IS NOT NULL (and same for lastwin_year)? These rows are not retrieved Frown | :(

phi
When Will It Be? You Decide... OMG | :OMG:

If you care about rows that != a value, why are you specifing them in the where clause?

Mike Lasseter

What I want are the 3 rows in the pb table with the highest rating.

If these rows have a match (id) in the pv table, I want a row of pv information for each pb row as well. The information in pv is only relevant if lastwin_year and lastwin_month match the $current_year and $current_month parameters. No information should be retrieved from pv if no matching lastwin_year/lastwin_month can be found. Place should be as low as possible.

For example:

pb
---
id = 1, rating 56
id = 2, rating 63
id = 3, rating 42
id = 4, rating 70

pv
---
id = 2, lastwin_year 2006, lastwin_month 10, place 3
id = 2, lastwin_year 2006, lastwin_month 10, place 1
id = 3, lastwin_year 2006, lastwin_month 7, place 3
id = 4, lastwin_year 2005, lastwin_month 8, place 3
id = 4, lastwin_year 2005, lastwin_month 9, place 3

desired result ($current_year = 2006, $current_month = 10)
---
id = 4, rating 70, NULL
id = 2, rating 63, lastwin_year 2006, lastwin_month 10, place 1
id = 1, rating 56, NULL

phi
When Will It Be? You Decide... OMG | :OMG:

I think this is what you are trying to do

select top 3 *
from pb left outer join
(select * from pv where lastwin_year = year and lastwin_month = month) b on pb.id = b.id
order by value

-- modified at 14:40 Thursday 5th October, 2006

Mike Lasseter

Brilliant! Exactely what I needed to get the right results back.

Thank you Big Grin | :-D

phi
When Will It Be? You Decide... OMG | :OMG:

Hi,

I currently have a stored procedure which is able to delete a record of my choice..

Instead of having the RECORD deleted, I would like the RECORD to disappear from the Datagrid, and have a boolean value of the 'Deleted' Field in that RECORD be set to 1...

Is this implemented in the Stored Procedure? Any ideas would be appreciated..

This is a very common approach. There should be no problems implementing this into a stored procedure (except you will have to remove the item from the datagrid in the code).

Mike Lasseter