Re: how to make connetion with database? - Database Discussion Boards

First Prev Next

Re: "Transferring" SQL Server Databases

Michael Potter6-Oct-06 5:39

Michael Potter

6-Oct-06 5:39

Use Backup & Restore. You will have to establish security since you are changing domain/workstation. See BOL.

Using trigger to insert data into seperate database.

flippydeflippydebop5-Oct-06 23:24

flippydeflippydebop

5-Oct-06 23:24

Hello c'pians,

I have got two SQL databases on seperate machines (master/slave). I am able to connect to both the databases using Enterprise Manager. Both databases
have identical tables. What i would like to do is write a trigger which fires when a new row is inserted into the master db and inserts the same data into the slave database.

What i would like to know is, is it possible to write a trigger which can insert data into a completly seperate database?

Re: Using trigger to insert data into seperate database.

Private_Void6-Oct-06 9:11

Private_Void

6-Oct-06 9:11

It is possible and fairly straight forward. I am doing this from memory as I only have SQL Server 2005 in front of me (so forgive if directions are slightly off). Open Enterprise Manager, create a linked server on the master DB to the slave DB. Then you will be able to access the slave DB in your trigger basically as if you were acessig another (local) DB. I hope this helps.

Re: Using trigger to insert data into seperate database.

Amit Kumar G7-Oct-06 6:13

Amit Kumar G

7-Oct-06 6:13

Yes, use linked server. Steps would be

a) Create Linked server for your slave on master server by using enterprise mgr or sp_addlinkedserver.

b) Write Trigger on master table...and do somothing like as below

Insert into Slave.Table values(..............)

where slave is linked server name.....

Simple........

Amit

Re: Using trigger to insert data into seperate database.

flippydeflippydebop8-Oct-06 0:22

flippydeflippydebop

8-Oct-06 0:22

hey guys... cheers ever so much for this.. I will be looking into this first thing monday morning.

Smile | :)

Re: Using trigger to insert data into seperate database.

flippydeflippydebop8-Oct-06 0:49

flippydeflippydebop

8-Oct-06 0:49

i have just found this good guide on using linked servers

http://www.informit.com/articles/article.asp?p=21644&rl=1[^]

thanks again

how to make connetion with database?

With_problem5-Oct-06 21:28

With_problem

5-Oct-06 21:28

I want to insert data coming from remote server to database(.mdb),but i dont know how to do htis.
Do i need to make connction through odbc?how?
Pls i ahve no idea abt database programing in vc++ so if anyone can help me out..
Thanks.

Re: how to make connetion with database?

Rocky#8-Oct-06 1:18

Rocky#

8-Oct-06 1:18

well no buddy's gonna tell u the whole story abt database programming out here... but I suggest u should visit www.msdn.com or use msdn at home and check out the articles on ADO or may be ADO .NET and stuff like that.

there are things like datasets, dataadapters, odbc, etc to watch out for

wish u best of luck...

Rocky

sub-select command problems arrrrg!

ren_20015-Oct-06 13:51

ren_2001

5-Oct-06 13:51

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

Combining tables [modified]

Chris McGlothen5-Oct-06 11:35

Chris McGlothen

5-Oct-06 11:35

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

Re: Combining tables

Farhan Noor Qureshi5-Oct-06 12:12

Farhan Noor Qureshi

5-Oct-06 12:12

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

In Search Of A Good Query Tool

nullGumby5-Oct-06 7:11

nullGumby

5-Oct-06 7:11

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)

Re: In Search Of A Good Query Tool

yahao8-Oct-06 18:00

yahao

8-Oct-06 18:00

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

Which database format is good enough?

Joe Smith IX5-Oct-06 6:42

Joe Smith IX

5-Oct-06 6:42

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.

Re: Which database format is good enough?

Chris Buckett5-Oct-06 8:51

Chris Buckett

5-Oct-06 8:51

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

Re: Which database format is good enough?

Joe Smith IX5-Oct-06 22:22

Joe Smith IX

5-Oct-06 22:22

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.

Re: Which database format is good enough?

Chris Buckett5-Oct-06 22:39

Chris Buckett

5-Oct-06 22:39

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

Re: Which database format is good enough?

Joe Smith IX6-Oct-06 9:27

Joe Smith IX

6-Oct-06 9:27

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.

Re: Which database format is good enough?

Chris Buckett7-Oct-06 7:17

Chris Buckett

7-Oct-06 7:17

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

Re: Which database format is good enough?

Joe Smith IX7-Oct-06 7:58

Joe Smith IX

7-Oct-06 7:58

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.

Re: Which database format is good enough?

Jerry Hammond7-Oct-06 12:58

Jerry Hammond

7-Oct-06 12:58

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

Re: Which database format is good enough?

Jerry Hammond5-Oct-06 9:08

Jerry Hammond

5-Oct-06 9:08

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.

Left Join & Group By

phimix5-Oct-06 4:40

phimix

5-Oct-06 4:40

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: